ABC-анализ в Excel: пошаговая инструкция с формулами

ABC-анализ показывает, какие товары приносят основную выручку, а какие занимают место на складе и в голове. Сервисы делают его за секунды, но разобраться в механике проще всего в Excel: это десять минут и пять формул. Порядок такой: отсортировать товары по выручке, посчитать долю каждого и накопительную долю, а затем присвоить группы формулой ЕСЛИ с границами 80% и 95%. Если нужно посчитать сразу — скачайте шаблон ABC-анализа: вставьте товары и выручку, группы посчитаются сами, без сортировки.

Что нужно для анализа

Одна таблица с двумя столбцами:

• артикул (или название товара);
• показатель, по которому делите товары: выручка, прибыль или количество продаж за период.

Период берите не меньше 3 месяцев, иначе случайный всплеск одной недели перевернёт группы. Выгрузку продаж по артикулам можно взять из отчёта о реализации Wildberries или отчёта о продажах Ozon — как читать эти отчёты, разобрано в статье про отчёт о реализации.

По чему делить — главный выбор. По выручке ABC покажет, что продаётся на большие суммы. По валовой прибыли — что реально зарабатывает. Для решений о закупке лучше второе: товар из группы A по выручке с нулевой маржой держит деньги и место, но ничего не зарабатывает. Подробнее о выборе показателя — в статье про ABC и XYZ-анализ.

Шаг 1. Подготовьте таблицу

Пусть артикулы в столбце A, выручка за период — в столбце B, с 2-й по 101-ю строку (100 товаров). В первой строке — заголовки.

Уберите из данных товары с нулевыми продажами — их место в отдельном списке неликвида, а не в группе C. И проверьте, что один артикул не повторяется в нескольких строках: если выгрузка по дням или складам, сначала сведите её сводной таблицей.

Шаг 2. Отсортируйте по убыванию

Выделите таблицу → «Данные» → «Сортировка» → по столбцу B, «по убыванию». Самые продаваемые товары окажутся сверху. Без сортировки накопительная доля в следующем шаге не имеет смысла.

Шаг 3. Посчитайте долю каждого товара

В C2 — доля товара в общей выручке:

=B2/СУММ($B$2:$B$101)

Протяните формулу до C101 и задайте процентный формат. Знаки доллара закрепляют диапазон суммы, чтобы он не сдвигался при протягивании.

Шаг 4. Посчитайте накопительную долю

В D2 — просто доля первого товара:

=C2

В D3 — доля товара плюс всё, что выше:

=D2+C3

Протяните D3 до конца таблицы. В последней строке должно получиться 100%. Если нет — проверьте, не попали ли в сумму пустые или текстовые ячейки.

Шаг 5. Присвойте группы

Классические границы — 80% и 95% накопительной доли:

• A — товары, которые вместе дают первые 80% выручки;
• B — следующие 15%, до 95%;
• C — оставшиеся 5%.

В E2:

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

Протяните до конца. В Google Таблицах разделитель аргументов — запятая, если у таблицы английская локаль: =IF(D2<=0.8,"A",IF(D2<=0.95,"B","C")).

Одна тонкость: товар, на котором накопительная доля перешагнула 80%, по этой формуле уйдёт в B, хотя именно он «добрал» группу A. Это нормально — на решения такая граница не влияет.

Шаг 6. Проверьте результат

Посчитайте, сколько товаров попало в каждую группу:

=СЧЁТЕСЛИ($E$2:$E$101;"A")

Обычно в группу A попадает 15–25% артикулов, но у вас может быть иначе. Если в A попала половина ассортимента, выручка распределена ровно — ABC мало что скажет, смотрите на прибыль и оборачиваемость. Если в A два-три товара, бизнес зависит от них, и главный риск — их дефицит.

ABC-анализ без сортировки

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

=СУММЕСЛИ($B$2:$B$101;">="&B2)/СУММ($B$2:$B$101)

Формула складывает выручку всех товаров, которые продают не меньше текущего, — ровно то, что после сортировки накопилось бы к этой строке. Протяните её до D101 и присвойте группы той же формулой ЕСЛИ из шага 5. Оговорка одна: у товаров с одинаковой выручкой накопительная доля выйдет одинаковой, и оба попадут в одну группу.

ABC-анализ в сводной таблице

Если выгрузка по дням или складам и один артикул встречается в нескольких строках, удобнее сразу сводная таблица:

1. «Вставка» → «Сводная таблица»; артикулы — в «Строки», выручку — в «Значения».
2. Отсортируйте сводную по сумме выручки по убыванию.
3. Перетащите выручку в «Значения» второй раз, откройте параметры этого поля, вкладку «Дополнительные вычисления», и выберите «% от суммы с нарастающим итогом в поле» с базовым полем «Артикул». Это и есть накопительная доля.
4. Рядом со сводной присвойте группы формулой ЕСЛИ из шага 5.

График Парето

Чтобы увидеть ABC-анализ на графике, в Excel 2016 и новее выделите столбцы с артикулами и выручкой → «Вставка» → «Вставить статистическую диаграмму» → в разделе «Гистограмма» выберите «Парето». Excel сам отсортирует товары по убыванию и добавит линию накопительной доли: где она проходит 80%, там и кончается группа A.

Что делать с группами

• A — не допускать дефицита. Держите страховой запас и точку дозаказа, следите за остатком каждую неделю. Как рассчитать запас — в статье про минимальный остаток и safety stock.
• B — пополнять по графику, без перестраховки.
• C — не держать лишнего. Проверьте, какие из них медленные и копят хранение: часть — кандидаты на вывод из ассортимента. Как решать, что докупать, а что выводить, — в статье про управление ассортиментом, а как свести всё в одну схему магазина — про ассортиментную матрицу.

Добавьте XYZ: стабильность спроса

ABC отвечает «сколько приносит», но не «насколько предсказуемо». Для этого считают XYZ по коэффициенту вариации продаж. Нужны продажи по каждому товару за несколько периодов — например, по неделям в столбцах F:Q (12 недель). Подробно с примером и поправкой на дни без остатка — в статье XYZ-анализ: коэффициент вариации и группы.

Коэффициент вариации в R2:

=СТАНДОТКЛОН.Г(F2:Q2)/СРЗНАЧ(F2:Q2)

Группа в S2 (частые границы — 10% и 25%):

=ЕСЛИ(R2<=10%;"X";ЕСЛИ(R2<=25%;"Y";"Z"))

Если у товара в каком-то периоде не было остатка, продажи там занижены не спросом, а дефицитом — такие недели лучше исключить, иначе стабильный товар попадёт в Z. Что делать с каждой из девяти комбинаций AX…CZ — в статье про ABC и XYZ-анализ.

Частые ошибки

• Слишком короткий период. Неделя или месяц дают случайные группы, особенно для сезонных товаров.
• Выручка вместо прибыли. Группа A по выручке может не зарабатывать.
• Дефицит в данных. Товар, которого не было в наличии полмесяца, попадает в C не потому, что его не покупают, а потому что нечего было продать. Как оценить такие потери — в статье про out-of-stock и потерянную выручку.
• Разовый анализ. Группы меняются — пересчитывайте их раз в месяц или хотя бы раз в квартал.

Автоматизация

Таблица в Excel хороша, чтобы понять механику, но её приходится обновлять вручную. Veloseller пересчитывает скорость продаж, дни покрытия и потерянную выручку по каждому SKU каждый день из API Wildberries и Ozon — и учитывает дни без остатка, которые в ручной ABC-таблице искажают картину.

Частые вопросы

Как сделать ABC-анализ в Excel?

Отсортируйте товары по выручке или прибыли по убыванию, посчитайте долю каждого товара в общей сумме и накопительную долю, а затем присвойте группы формулой ЕСЛИ с границами 80% и 95%. Весь расчёт укладывается в несколько формул и около десяти минут.

Как сделать ABC-анализ в Excel без сортировки?

Посчитайте накопительную долю формулой =СУММЕСЛИ($B$2:$B$101;">="&B2)/СУММ($B$2:$B$101): она складывает выручку всех товаров, которые продают не меньше текущего. Порядок строк не меняется. Группы присваиваются той же формулой ЕСЛИ с границами 80% и 95%.

Есть ли пример ABC-анализа в Excel?

Да, шаблон Veloseller с примером на 20 товарах: 4 из них попадают в группу A, 6 — в B, 10 — в C. Вставьте свои товары и выручку — накопительная доля посчитается формулой СУММЕСЛИ без сортировки, а сводка по группам появится на листе «Итог».

Можно ли сделать ABC-анализ поставщиков в Excel?

Да, теми же шагами: вместо артикулов — поставщики, вместо выручки — сумма закупок у каждого за период. В группе A окажутся поставщики, на которых приходится 80% закупок, — с ними в первую очередь договариваются о цене и сроках.

Что такое накопительная доля в ABC-анализе?

Сумма долей товара и всех товаров выше него в списке, отсортированном по убыванию. По ней товары делят на группы, а у последнего в списке она равна 100%.

Какие границы групп в ABC-анализе?

Чаще всего 80% и 95% накопительной доли: A — первые 80% выручки, B — следующие 15%, C — остальное.

За какой период делать ABC-анализ?

Не меньше чем за 3 месяца. Пересчитывайте его раз в месяц или хотя бы раз в квартал.

Как сделать XYZ-анализ в Excel?

Посчитайте коэффициент вариации продаж по неделям — СТАНДОТКЛОН.Г, делённое на СРЗНАЧ, — и разделите товары: до 10% — X, до 25% — Y, выше — Z.