История выдуманная, но основана на реальных разговорах с десятками кладовщиков и логистов. Лена — собирательный образ. Пример приводится только для наглядности типичных проблем ручной работы в Excel.
Лена — начальница склада в магазине зоотоваров. Каждый понедельник она садится за компьютер, открывает Excel и начинает:
- Скачать отчёт из 1С по продажам за неделю.
- Скопировать его в файл «Анализ_склад.xlsx».
- Вручную обновить столбцы с выручкой, отгрузками, остатками.
- Заново посчитать ABC, FMR, оборачиваемость (потому что формулы в прошлом листе порвались, когда она вставила новые строки).
- Потом полчаса материть Excel и себя. Потом сделать чай. Потом забыть, зачем всё это было.
В понедельник вечером Лена ненавидит свою работу.
А могла бы любить, если бы один раз настроила автоматизацию и каждую неделю просто нажимала кнопку «Обновить всё». Новые продажи — новые анализы. Без копирования, без вставки, без слёз.
Об этом и будет статья.
Связь с прошлым: вы уже знаете что считать, теперь научимся как
В предыдущих статьях мы разобрали:
- ABC, FMR, оборачиваемость — кто есть кто.
- EOQ — сколько заказывать.
- Страховой запас — когда заказывать.
- Неликвиды — что выкинуть.
Всё это работает. Но требует свежих данных. Если вы до сих пор обновляете таблицы руками, то:
- Либо делаете это редко (и анализы устаревают за месяц).
- Либо делаете это часто и ненавидите жизнь.
Автоматизация решает обе проблемы. Вы настраиваете один раз — и данные обновляются по щелчку, хоть каждую неделю. А время, которое вы тратили на копирование, можно потратить на чай. Или на реальный порядок на складе.
Что автоматизировать в первую очередь
Не нужно автоматизировать всё сразу. Начните с самого частого и самого нудного:
- Еженедельное обновление продаж и остатков — чтобы не копировать из 1С каждый раз.
- Подтягивание цен и себестоимости — чтобы не сверять справочники.
- Расчёт ABC/FMR/оборачиваемости — чтобы не перетаскивать формулы на новые строки.
- Визуализация — чтобы графики и таблицы перерисовывались сами.
Всё это можно сделать в обычном Excel (через Power Query) или в Google Таблицах (через QUERY и IMPORTRANGE). Макросы (VBA) не понадобятся. Даже если вы не программист.
Инструмент 1. Power Query в Excel (просто и мощно)
Power Query — это надстройка, которая встроена в Excel начиная с 2016 года. Она умеет:
- Загружать данные из любого источника (другая книга Excel, CSV, 1С-выгрузка, база данных).
- Чистить их (удалять лишние столбцы, заменять ошибки, сворачивать таблицы).
- Соединять несколько таблиц (например, продажи + справочник товаров).
- Обновлять всё это кнопкой.
На примере. Готовим шаблон для ABC-анализа
Шаг 1. Получаем исходные данные
Предположим, вы раз в неделю выгружаете из 1С два отчёта:
- Продажи.xlsx — столбцы: Дата, Товар, Выручка.
- Остатки.xlsx — столбцы: Товар, Остаток на начало, Остаток на конец.
Вы кладёте эти файлы в одну папку, например, C:\Склад\Исходники.
Шаг 2. Загружаем данные в Power Query
В Excel: Данные → Получить данные → Из файла → Из папки. Выбираете папку с исходниками. Power Query покажет список файлов. Нажимаете «Объединить и загрузить» — и все файлы из папки склеиваются в одну таблицу с дополнительным столбцом «Имя файла» (чтобы знать, откуда какая строка).
Шаг 3. Фильтруем и сворачиваем
В редакторе Power Query:
- Убираете лишние столбцы (оставляете только нужные).
- Сворачиваете продажи по товарам (Группировка → Сумма выручки).
- Присоединяете остатки (Объединить запросы → по полю Товар).
- Добавляете столбец для оборачиваемости (формула на языке M, но можно и потом в Excel).
Шаг 4. Закрыть и загрузить
После нажатия «Закрыть и загрузить» Excel создаёт таблицу с данными, как вы их обработали. Эта таблица связана с исходной папкой. Как только вы положите в папку новые версии Продажи.xlsx и Остатки.xlsx, вы нажимаете на ленте «Обновить всё» — и таблица пересчитывается сама.
Пример формул внутри Power Query (для тех, кто хочет глубже)
Не пугайтесь, они на обычном английском:
= Table.SelectRows(Source, each [Выручка] > 0) — убрать нулевые продажи.
= Table.Group(previous, {"Товар"}, {{"Сумма_выручки", each List.Sum([Выручка]), type number}}) — свернуть по товарам.
= Table.Join(продажи, "Товар", остатки, "Товар") — объединить продажи и остатки.
Но можно делать и без формул — через меню.
Инструмент 2. Google Таблицы + QUERY (для тех, кто в облаке)
Если ваш склад использует Google Таблицы, автоматизация ещё проще. Не нужно сохранять файлы на диск — всё в облаке.
Шаг 1. Подготовьте листы
- Лист Продажи — вы вставляете данные из 1С (можно настроить автоматический импорт через Google Apps Script, но это уже для продвинутых).
- Лист Товары — справочник (группа ABC, норма оборачиваемости и т.д.).
- Лист Анализ — здесь будет результат.
Шаг 2. Используйте функцию QUERY
В ячейке A1 на листе Анализ пишете:
=QUERY(Продажи!A:C, "select A, sum(B) where B is not null group by A label sum(B) 'Выручка'", 1)
Эта формула сама:
- Читает столбцы A, B, C на листе Продажи.
- Группирует по товару (столбец A).
- Суммирует выручку (столбец B).
- Добавляет заголовок.
При добавлении новых строк на лист Продажи результат QUERY обновляется автоматически (через пару секунд).
Шаг 3. Добавьте расчёт долей и групп
Рядом с результатом QUERY можно написать обычные формулы Excel (СУММ, ЕСЛИ). Они тоже будут пересчитываться при изменении исходных данных.
Пример:
В столбце C формула доли: =B2/СУММ(B:B)
В столбце D накопленный итог: =СУММ(C$2:C2)
В столбце E группа ABC: =ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C"))
Всё работает автоматически. Единственный минус — при появлении новых товаров формулы нужно будет протянуть вручную. Но можно использовать ARRAYFORMULA, чтобы они протягивались сами.
Что делать, если данные не в Excel, а в 1С или другой программе
Самый простой способ — настроить регулярную выгрузку из 1С в CSV или Excel. Обычно это делается один раз: бухгалтер или программист настраивает регламентное задание, которое каждое утро сохраняет отчёт в сетевую папку. А ваш Power Query оттуда его забирает.
Если такой возможности нет, можно использовать надстройки (например, «Загрузка из 1С:Предприятие 8» для Excel). Или настроить прямое подключение к базе через ODBC (но это уже требует специалиста).
Для Google Таблиц есть скрипты, которые импортируют CSV из облачных хранилищ (Google Диск, OneDrive, Dropbox). Несколько строчек кода, и раз в день таблица обновляется сама.
План автоматизации для ленивых (но умных)
Если вы не хотите разбираться в Power Query или QUERY прямо сейчас, начните с малого:
Неделя 1. Перестаньте создавать новый файл каждую неделю. Заведите один файл-шаблон, где на листе «Исходники» вы вручную вставляете свежие данные (копируете из 1С и вставляете значениями). Формулы на других листах уже ждут.
Неделя 2. Научитесь делать «Умные таблицы» в Excel (Ctrl+T). Они сами расширяют диапазон формул при добавлении новых строк. Больше не надо перетягивать формулы вниз.
Неделя 3. Освойте Power Query на одном примере: загрузите папку с файлами, как показано выше. Попробуйте обновить данные кнопкой.
Неделя 4. Подключите к Power Query ваши реальные выгрузки из 1С. Настройте обновление одним нажатием.
Через месяц вы будете смеяться, вспоминая понедельники Лены.
Пример готового файла-автомата (словесная схема)
Вы создаёте файл Склад_анализ_авто.xlsx с тремя листами:
- Исходные — сюда Power Query тянет данные из папки. На этом листе вы ничего не трогаете.
- Расчёты — здесь ссылки на первый лист и формулы ABC, FMR, оборачиваемости. Используйте Умные таблицы, чтобы диапазоны расширялись.
- Сводка — итоговая матрица и рекомендации. Ссылается на лист «Расчёты».
Ваш еженедельный ритуал:
- В понедельник утром забираете выгрузки из 1С (или они уже сами упали в сетевую папку).
- Открываете Склад_анализ_авто.xlsx.
- Нажимаете Данные → Обновить всё.
- Ждёте 10 секунд.
- Смотрите свежие группы ABC и списки неликвидов.
Всё. Больше никакого копирования.
Заключение: автоматизация — это не страшно, это полезно
Многие боятся автоматизации, думая, что это программирование для айтишников. На самом деле Power Query и QUERY созданы именно для обычных пользователей Excel. Они работают на русском языке (в версиях для России), команды понятные, кнопки знакомые.
Один вечер настройки — и вы освободите себе несколько часов в неделю. А свободное время можно потратить на то, чтобы наконец разобрать тот угол склада, который никто не трогал с прошлого года. Или выпить чай. Лена выбрала бы чай.