ABC-анализ за 1 час: 7 шагов в Excel

ABC-анализ 5 000 SKU в Excel занимает 45–60 минут, если знать последовательность действий. На выходе — классификация ассортимента на три группы, визуализация кривой Парето и план действий для каждой группы. Ниже — 7 шагов от сырых данных до управленческих решений, с формулами, описанием экранов и типичными ошибками на каждом этапе.

Шаг 1: выгрузка данных — 10 минут на подготовку файла

Из учётной системы (1С, ERP, CRM) выгрузите таблицу со следующими столбцами: код SKU, наименование, выручка за период (или маржа, или количество отгрузок — в зависимости от цели анализа). Период — 12 месяцев. Если компания работает менее года, берите максимально доступный период, но не менее 6 месяцев.

Очистите данные. Удалите внутренние перемещения, возвраты, тестовые операции, SKU с нулевой выручкой за период. Проверьте дубли — один товар, заведённый под двумя кодами, исказит результат. На типичной выгрузке 3–7% строк требуют корректировки. Для поиска дублей отсортируйте по наименованию и визуально пройдите первые 100–200 строк — дубли обычно стоят рядом.

Отдельно обработайте возвраты: если SKU имеет отрицательную выручку за период (возвратов больше, чем продаж), не удаляйте строку — оставьте с нулём. Это важный сигнал: товар генерирует логистические затраты, но не приносит дохода.

Результат шага 1: таблица с N строками (где N — количество активных SKU) и минимум 3 столбцами: код, наименование, значение показателя. Для ассортимента 5 000 SKU это файл на 150–200 КБ. Сохраните исходный файл отдельно — вы ещё вернётесь к нему при анализе выводов.

Типичная ошибка на этом шаге: включить в выгрузку данные по всем юридическим лицам или филиалам. Если у вас 3 склада, делайте отдельный ABC по каждому — консолидированный анализ скроет локальные особенности.

Вторая типичная ошибка: не исключить акционные продажи. Если вы провели распродажу SKU со скидкой 60%, его выручка за этот месяц аномально высока — он попадёт в группу A, хотя в нормальных условиях это C. Решение: отфильтруйте операции с промо-тегом или скидкой выше 30%.

Какой объём данных считается достаточным: для ассортимента 500–1 000 SKU — файл обрабатывается вручную за 20–30 минут. Для 1 000–5 000 — за 40–60 минут. Для 5 000–20 000 — нужны сводные таблицы и макросы, время — 1,5–2 часа. Свыше 20 000 SKU — Excel работает медленно, рассмотрите Python или Power BI.

Шаг 2: сортировка — 1 минута, но с проверкой

Отсортируйте таблицу по столбцу «выручка» (или выбранному показателю) от большего к меньшему. Верхние строки — самые «тяжёлые» SKU, нижние — самые «лёгкие». Уже на этом этапе вы увидите первый паттерн: обычно 50–100 верхних строк (1–2% ассортимента) составляют 30–40% общей выручки.

В Excel: выделите весь диапазон данных (Ctrl+Shift+End от ячейки A1) → Данные → Сортировка → по столбцу выручки → от наибольшего к наименьшему. Критичный момент: убедитесь, что сортируется весь диапазон, а не только один столбец — иначе данные «рассыплются» (коды SKU перестанут соответствовать наименованиям и суммам).

Проверка: после сортировки первая строка данных должна содержать SKU с максимальной выручкой, последняя — с минимальной (ненулевой). Если последняя строка показывает отрицательное значение — вернитесь к шагу 1 и обработайте возвраты.

Шаг 3: кумулятивный процент — 5 минут, 2 формулы

Добавьте два столбца. Первый — «% от общей выручки»: вклад каждого SKU в общий оборот. Формула для ячейки D2 (предполагая, что выручка в столбце C, данные начинаются со строки 2):

=C2/СУММ($C$2:$C$5001)*100

Знак $ фиксирует диапазон суммирования — при копировании формулы вниз знаменатель не сдвигается. Замените 5001 на номер последней строки ваших данных. Альтернатива: назовите диапазон C2:C5001 именем «Выручка» (Формулы → Присвоить имя) и используйте формулу =C2/СУММ(Выручка)*100 — нагляднее и устойчивее к ошибкам.

Второй столбец — «Кумулятивный %»: нарастающая сумма процентов сверху вниз. Для первой строки данных (E2): =D2. Для второй и далее (E3): =E2+D3. Скопируйте формулу до последней строки. Последняя строка должна показать 100% (или 99,99% с учётом округления).

Кумулятивный процент — основа для определения границ^pareto. SKU, кумулятивный процент которых ≤ 80%, попадают в группу A. От 80% до 95% — группа B. От 95% до 100% — группа C.

Типичная ошибка: забыть закрепить диапазон суммирования в формуле % (знак $). В результате каждая строка делится не на общую сумму, а на сдвинутый диапазон — проценты получаются бессмысленными. Проверка: сумма столбца «% от общей выручки» должна равняться 100%.

Шаг 4: границы A/B/C — 5 минут на калибровку

Стандартные границы: A = 0–80%, B = 80–95%, C = 95–100%. Это не догма — границы подбирают под конкретный ассортимент двумя способами.

Способ 1 — по точке перегиба кривой Парето. Постройте график (см. шаг 5) и найдите место, где кривая меняет крутизну — это естественная граница между A и B. Если перегиб приходится на 75%, используйте границу A = 75%. Перегиб виден невооружённым глазом: кривая идёт круто вверх, потом «сгибается» и становится пологой.

Способ 2 — по количеству SKU в группах. Если при границе 80% в группу A попадает 35% SKU — это слишком много для приоритетного управления. Один закупщик физически не сможет ежедневно контролировать 1 750 SKU из 5 000. Сдвиньте границу до 70% или 75%, чтобы A содержала 15–25% SKU — управляемое количество для одного человека.

Добавьте столбец «Группа» с формулой:

=ЕСЛИ(E2<=80;"A";ЕСЛИ(E2<=95;"B";"C"))

Если вы сдвинули границы — замените 80 и 95 на свои значения. Результат: каждый SKU получает метку A, B или C.

Контрольные цифры для ассортимента 5 000 SKU при стандартных границах 80/95/100:

Если ваше распределение сильно отличается от таблицы выше — пересмотрите границы. Если группа A содержит менее 10% SKU — граница слишком узкая, и вы рискуете недооценить важные позиции. Если более 30% — слишком широкая, и «приоритетный контроль» размывается.

Шаг 5: визуализация — 10 минут на 2 графика

Два графика — минимум для представления результатов руководству или коллегам. Без визуализации ABC-анализ остаётся таблицей — а таблицу на 5 000 строк сложно «продать» на совещании.

Первый график — кривая Парето. Ось X — номер SKU (от 1 до N, по убыванию выручки). Ось Y — кумулятивный %. Получается характерная «кривая насыщения»: резкий рост слева (A-группа), плавный подъём в середине (B), почти горизонтальный «хвост» справа (C).

В Excel: вставка → график → точечный с линиями. Выделите диапазон с номерами строк (столбец A или просто 1, 2, 3…) как X и кумулятивный % (столбец E) как Y. Нанесите на график две горизонтальные линии на уровне 80% и 95% — это границы групп. Как добавить: Добавить данные → новый ряд с постоянным значением 80 (по всему диапазону X) и аналогично для 95. Линии визуально отсекают три зоны.

Второй график — столбчатая диаграмма диспропорции. Три группы столбцов: A, B, C. По два значения в каждой: % выручки и % SKU. Этот график мгновенно показывает несоразмерность: A — 80% выручки при 20% SKU, C — 5% выручки при 50% SKU. На совещании этот график работает лучше любых таблиц — руководитель сразу видит, где «деньги» и где «хвост».

В Excel: вставка → гистограмма с группировкой. Данные: 3 строки (A, B, C), 2 столбца (% выручки, % SKU). Добавьте подписи значений и контрастные цвета (зелёный для выручки, серый для SKU).

Дополнительный график (для продвинутых): тепловая карта по категориям. Если ваш ассортимент разбит на товарные категории (например, «краны», «трубы», «фитинги»), постройте ABC внутри каждой категории и визуализируйте долю A/B/C по категориям. Это покажет, какие категории «перегружены» группой C.

Шаг 6: анализ — 15 минут на 4 управленческих вопроса

Анализ результатов — не «посмотрели на график и разошлись». Это ответы на четыре конкретных вопроса, каждый из которых ведёт к действию.

Вопрос 1: сколько SKU в каждой группе и управляем ли объём? Если в группе A — 1 500 SKU, один закупщик не сможет управлять ими приоритетно. Нужно либо сузить границу (с 80% до 70%), либо добавить ресурс (второго закупщика или автоматизацию через ROP). Эмпирическое правило: один закупщик качественно управляет 200–400 SKU в ручном режиме, 800–1 200 — с автоматизацией.

Вопрос 2: какие SKU из группы C можно вывести из ассортимента? Выделите SKU группы C, которые: (а) не продавались более 6 месяцев, (б) имеют отрицательную маржу, (в) не являются обязательным ассортиментом по договору. Это кандидаты на вывод. Типично — 15–25% от группы C подлежат выводу или распродаже. На ассортименте 5 000 SKU это 300–750 позиций, занимающих 5–12 млн ₽ складского запаса.

Вопрос 3: есть ли SKU из группы A с низкой маржой? Добавьте столбец «маржа» к таблице и отфильтруйте: группа A + маржа < 10%. Такие позиции «крутят объём, но не зарабатывают». Действия: пересмотр закупочной цены (переговоры с поставщиком), повышение отпускной цены (если рынок позволяет), замена на аналог с лучшей маржинальностью. На реальном ассортименте 5–12% SKU группы A имеют маржу ниже 10%.

Вопрос 4: совпадает ли ваш ABC с ABC по марже? Постройте второй ABC-анализ, но по столбцу «валовая прибыль» вместо «выручки». Сравните группы. Расхождения покажут «скрытых чемпионов» (C по выручке, A по марже) и «пустышек» (A по выручке, C по марже). На реальных данных совпадение составляет 55–70% — оставшиеся 30–45% — зона для оптимизации.

Запишите ответы на 4 вопроса в отдельный лист Excel — это «протокол анализа», который вы покажете руководству и используете в шаге 7.

Шаг 7: план действий — 15 минут на 3 решения с дедлайнами

По каждой группе — конкретные действия с ответственным и сроком.

Группа A: приоритетный контроль. Установите индивидуальный ROP (точку перезаказа) и страховой запас по формуле для каждого SKU. Мониторинг остатков — ежедневно. Закупщик лично контролирует поставки. Цель — fill rate 95–99%. Любой stockout фиксируйте как инцидент с разбором причин. Дедлайн на настройку ROP: 2–3 недели для 500–1 000 SKU (30–60 минут на SKU — расчёт, проверка, согласование с поставщиком).

Группа B: стандартный автомат. Стандартный ROP, мониторинг — еженедельно. Автоматизированный перезаказ по порогу. Пересмотр группы ежемесячно — часть SKU мигрирует в A или C. Дедлайн: 2–4 недели на настройку автоматического перезаказа для всей группы.

Группа C: оптимизация и вывод. Минимальный запас или заказ «под клиента». Вывод неликвида (SKU без продаж >6 месяцев) — через распродажу со скидкой 30–55%. Пересмотр MOQ (минимальный объём заказа) с поставщиками: если поставщик требует 100 штук, а вы продаёте 8 в месяц — договаривайтесь о снижении MOQ или ищите альтернативного поставщика. Освободившуюся площадь — под расширение группы A. Дедлайн: 4–8 недель на первый цикл вывода.

Срок реализации полного плана — 2–4 недели для настройки ROP по группе A, 4–8 недель для оптимизации группы C. Первые результаты (снижение запасов, рост fill rate) видны через 6–8 недель после старта.

Практический пример плана для группы C: из 3 740 SKU группы C выделите 3 подгруппы. C1 — активные с низким оборотом (1–2 продажи/мес): 1 600 SKU, держите минимальный запас (1 штука). C2 — эпизодические (1 продажа в 2–3 мес): 1 250 SKU, переведите на заказ «под клиента», уберите со склада. C3 — мёртвые (0 продаж за 6+ мес): 890 SKU, распродажа со скидкой 35–55% в течение 4 недель, непроданное — списание. Высвобождение: площадь под 140 паллетомест, оборотный капитал 5–8 млн ₽.

Мониторинг после внедрения: создайте еженедельный отчёт из 5 строк. Средний запас (₽), fill rate (%), количество SKU с нулевым остатком, количество SKU без продаж > 60 дней, объём заказов группы A (₽). Если fill rate падает ниже 90% две недели подряд — пересмотрите ROP по группе A. Если количество SKU без продаж растёт — ускорьте зачистку группы C.

Шаблон для скачивания: 7 столбцов и 4 формулы

Шаблон содержит 7 столбцов: Код SKU | Наименование | Выручка за 12 мес | % от общей | Кумулятивный % | Группа (A/B/C) | Действие.

Формулы (для Excel с русской локализацией):

• % от общей: =C2/СУММ($C$2:$C$N)*100
• Кумулятивный %: =D2 (первая строка), =E_предыдущая+D_текущая (далее)
• Группа: =ЕСЛИ(E2<=80;"A";ЕСЛИ(E2<=95;"B";"C"))
• Действие: =ЕСЛИ(F2="A";"Приоритетный контроль";ЕСЛИ(F2="B";"Стандартный ROP";"Оптимизировать/вывести"))

Для английской локализации Excel замените точки с запятой на запятые и ЕСЛИ на IF, СУММ на SUM.

Для полноценного анализа добавьте столбец XYZ (коэффициент вариации спроса) — и получите ABC-XYZ матрицу с 9 группами. Подробнее — в статье ABC-XYZ анализ: устройство и применение.

Типичные ошибки: 7 ловушек, которые обесценивают ABC

Ошибка 1: период короче 6 месяцев. На данных за 3 месяца ABC нестабилен. Один крупный разовый заказ выведет SKU в группу A, хотя он может быть типичным C. Для сезонного бизнеса — только полный годовой цикл.

Ошибка 2: включение неактивных SKU. SKU, выведенные из ассортимента 4 месяца назад, имеют выручку за первые 8 месяцев периода и ноль за последние 4. Они «занимают место» в группах и искажают границы. Решение: исключите SKU с нулевой выручкой за последние 3 месяца.

Ошибка 3: консолидация разнородных каналов. Если вы продаёте через опт, розницу и маркетплейсы — делайте ABC по каждому каналу отдельно. Позиция, являющаяся A для маркетплейса (100 штук/день мелкими заказами), может быть C для опта (1 поставка в квартал по 500 штук). Консолидированный ABC скроет эту разницу.

Ошибка 4: игнорирование «хвоста». Типичная реакция на ABC: «группу A поняли, группу C — потом». «Потом» не наступает, группа C продолжает занимать 40–50% площади и 60% внимания закупщика. Начинайте с двух параллельных действий: усиление контроля A и зачистка C.

Ошибка 5: однократный анализ. ABC, построенный раз в год, к середине периода устаревает. За 6 месяцев 15–25% SKU мигрируют между группами. Пересчитывайте ежемесячно и отслеживайте миграцию.

Ошибка 6: неправильный выбор показателя. ABC по выручке — для управления запасами. По марже — для оптимизации прибыли. По строкам отгрузок — для складских операций. Если вы строите ABC для закупщика — берите выручку. Для финансового директора — маржу. Для начальника склада — строки отгрузок.

Ошибка 7: фиксированные границы 80/95/100 без проверки. Для 70% ассортиментов стандартные границы работают. Для 30% — нет. Всегда проверяйте: сколько SKU попало в A? Если менее 10% или более 30% — границы нужно корректировать.

Как обнаружить ошибку: проведите «санитарную проверку» результатов. Откройте первые 10 SKU группы A — вы их узнаёте? Это действительно ваши хиты? Если нет — в данных ошибка (дубли, возвраты, акции). Откройте последние 10 SKU группы A — они точно важнее первых 10 SKU группы B? Если разница в выручке между ними менее 5% — граница проведена неточно, сдвиньте на 2–3% вверх или вниз.

Частота пересчёта по отраслям: FMCG — ежемесячно (ассортимент подвижный, сезонность), стройматериалы — ежеквартально (медленный оборот), электроника — ежемесячно (быстрое устаревание), фармацевтика — ежемесячно (сроки годности, регуляторика).

Кейс: оптовый дистрибьютор сантехники — минус 21% запасов за 8 недель

Компания — дистрибьютор сантехники и инженерной сантехники. Ассортимент 6 800 SKU, оборот 380 млн ₽/год, средний запас 58 млн ₽. Закупщик работал по интуиции: заказывал «что кончается» и «что поставщик предложил со скидкой». Fill rate — 84%, оборачиваемость — 6,6.

Провели ABC-анализ по выручке за 12 месяцев по инструкции выше. Результат: группа A — 1 020 SKU (15%), группа B — 2 040 SKU (30%), группа C — 3 740 SKU (55%).

Что обнаружили при анализе (шаг 6). В группе C: 890 SKU без продаж за 9+ месяцев — «мёртвый» запас на 8,2 млн ₽. 340 SKU с отрицательной маржой — закупочная цена выросла на 10–25% за год, а отпускная не обновлялась. В группе A: 62 SKU с маржой ниже 5% — «пустышки», которые генерировали оборот, но не прибыль. Fill rate по группе A — 88% (ниже среднего, потому что закупщик тратил одинаковое время на все 6 800 SKU).

Действия за 8 недель. (1) Установили ROP по формуле для 1 020 SKU группы A — закупщик теперь тратил 80% времени на A, а не распылялся на весь ассортимент. (2) Запустили распродажу 890 «мёртвых» SKU со скидкой 35–55% — реализовали на 5,1 млн ₽, списали безнадёжные 1,4 млн ₽. (3) Повысили отпускную цену на 340 убыточных SKU — 210 остались в ассортименте с маржой 8–12%, 130 потеряли спрос и перешли в C (их тоже вывели). (4) Перевели 420 SKU группы C на заказ «под клиента» — высвободили 140 паллетомест. (5) Для 62 низкомаржинальных SKU группы A провели переговоры с поставщиками — по 40 позициям получили скидку 4–8%, по 22 — нашли альтернативных поставщиков.

Результат через 8 недель: средний запас снизился с 58 до 45,8 млн ₽ (−21%). Fill rate вырос с 84% до 92%. Fill rate по группе A — 97%. Высвобожденные 12,2 млн ₽ пошли на увеличение запаса по группе A — замкнутый цикл оптимизации. О метриках управления запасами — в материале управление запасами: 8 ключевых метрик. О проведении инвентаризации — инвентаризация склада.

Дополнительные эффекты через 6 месяцев. Оборачиваемость выросла с 6,6 до 8,3 — это означает, что каждый рубль, вложенный в запас, генерирует на 26% больше выручки. Закупщик тратит 3 часа в неделю на анализ и пополнение группы A (вместо прежних 8 часов на весь ассортимент) — высвобожденное время перенаправлено на переговоры с поставщиками и поиск альтернативных каналов поставки.

Типичная ошибка после первого успешного ABC-анализа — остановиться и не пересчитывать. Через 3–4 месяца ассортимент «плывёт»: 8–12% SKU мигрируют между группами из-за изменения спроса, прихода новинок и ухода устаревших позиций. Если закупщик продолжает работать по старой классификации, через 6 месяцев 15–20% решений по запасам основаны на устаревших данных. Рекомендация: пересчитывайте ABC ежемесячно (10 минут в Excel, если шаблон настроен) и сравнивайте с предыдущим месяцем. Если более 10% SKU сменили группу — пересмотрите стратегию для мигрировавших позиций.

Второй уровень использования ABC-анализа для складской логистики: размещение товара по ABC-группам. Группу A размещайте на «золотых» ячейках — на уровне пояса (3-й ярус сверху, 1-й снизу), ближе к зоне отгрузки. Группу C — на верхних ярусах и дальних рядах. На складе 5 000 паллетомест перераспределение по ABC сокращает среднее расстояние комплектации на 18–25%, что экономит 1,5–2 часа работы каждого комплектовщика в смену.

Как выполнить ABC-перераспределение на складе без остановки:

1. Выгрузите текущее размещение из WMS: адрес → SKU → группа ABC.
2. Определите «золотые» ячейки: первые 2 ряда от зоны отгрузки, высота 0,8–1,5 м.
3. Сравните: какие SKU из группы C занимают «золотые» ячейки (их нужно переместить вглубь), какие SKU из группы A стоят на дальних адресах (их нужно переместить на «золото»).
4. Сформируйте задания на перемещение в WMS — по 20–30 паллет в день, в промежутках между отгрузками.
5. За 2–3 недели перемещайте по 100–150 паллет без влияния на текущие операции.

Стоимость: 0 ₽ (только время кладовщика, 1,5–2 часа/день в течение 2–3 недель). Результат: рост производительности комплектации на 15–20% — бесплатно.

FAQ

Какой показатель взять для ABC: выручку, маржу или количество отгрузок? Зависит от цели. Для управления запасами — выручку (показывает, где заморожен капитал). Для оптимизации прибыльности — маржу. Для планирования складских операций — количество строк отгрузок (показывает нагрузку на комплектовщиков). Лучший вариант — сделать все три среза и сравнить. Первый раз — начните с выручки, это самый наглядный показатель.

12 месяцев — жёсткое требование к периоду? Нет, но минимум — 6 месяцев. На данных за 3 месяца ABC нестабилен: один крупный разовый заказ выведет SKU в группу A, хотя по сути это C. Для сезонного бизнеса (стройматериалы, садовый инвентарь, отопление) берите полный годовой цикл — иначе зимние товары окажутся в C летом и наоборот.

Как часто обновлять ABC-анализ? Ежемесячно. За 6 месяцев 15–25% SKU мигрируют между группами. ABC, построенный раз в год, к середине периода устаревает и даёт ложные приоритеты. Автоматизация помогает: макрос в Excel обновляет ABC за 2–3 минуты, WMS-модуль — автоматически по расписанию.

Что делать с новыми SKU, которых нет в истории? Новые SKU (менее 3 месяцев на складе) не включайте в ABC. Присвойте им временную группу B (консервативная оценка), пересмотрите через 3–6 месяцев после накопления данных. Если новинка — аналог существующего SKU, используйте данные аналога как ориентир для начального запаса.

ABC в Excel vs ABC в WMS — есть разница в результатах? По формулам — нет. По оперативности — колоссальная. Excel — это ежемесячный ручной пересчёт на 2–3 минуты. WMS пересчитывает автоматически по расписанию и сразу применяет результат к правилам размещения, отбора и ROP. На 5 000+ SKU ручной ABC в Excel — рабочий инструмент для первого анализа, но не для постоянного управления. Если вы делаете ABC впервые — Excel достаточно. Если планируете пересчитывать ежемесячно — автоматизируйте.

Можно ли менять границы 80/95/100? Да, и нужно, если стандартные границы дают нерабочий результат. Если при 80% в группе A — 40% SKU, сузьте до 70% или 75%. Границы подбирают так, чтобы группа A была управляемой (15–25% SKU) и содержала реально критичные позиции. Проверочный вопрос: «если завтра весь товар группы A исчезнет со склада — компания остановится?» Если ответ «нет» — граница слишком широкая.

Как совместить ABC-анализ с сезонным ассортиментом? Для сезонных товаров стройте 2 отдельных ABC: за летний период (апрель–сентябрь) и зимний (октябрь–март). Товар, попадающий в группу A зимой и C летом (например, обогреватели), получает метку «сезонный A» — перед сезоном увеличивайте запас до уровня A, после сезона — сокращайте до уровня C. На практике 10–20% ассортимента демонстрируют сезонную миграцию между группами. Без сезонного ABC вы либо затоварены вне сезона (замороженный капитал 3–8 млн ₽ для склада с 5 000 SKU), либо недозатарены в пик (потери от stockout 5–12% выручки за пиковый месяц).

Как автоматизировать ABC в Excel макросом? Создайте макрос VBA, который выполняет шаги 2–4 за 15 секунд: сортировка по убыванию, расчёт кумулятивного процента, присвоение группы. Запуск — по кнопке после вставки свежих данных. Макрос сокращает ежемесячный пересчёт с 40 минут до 3 минут. Для написания макроса достаточно базового знания VBA — 30 строк кода. Если VBA не ваша тема — запросите у ИТ-отдела. Альтернатива: Power Query в Excel (встроенный инструмент с 2016 года) — создайте запрос один раз, обновляйте данные ежемесячно нажатием одной кнопки. Третий вариант — выгрузка данных в Google Sheets через коннектор 1С: пересчёт ABC происходит автоматически каждые 24 часа, доступ к таблице — у закупщика и руководителя отдела закупок. Экономия времени по сравнению с ручным пересчётом — 8–10 часов в месяц на одного аналитика. На годовом горизонте это 96–120 часов: эквивалент 12–15 рабочих дней.