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

ABC-анализ показывает, какие товары приносят основную выручку, а какие занимают место на складе и в голове. Сервисы делают его за секунды, но разобраться в механике проще всего в Excel: это десять минут и пять формул. Ниже — пошаговая инструкция на примере выгрузки с маркетплейса, с готовыми формулами для Excel и Google Таблиц.

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

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

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

Период берите не меньше 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")

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

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

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

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

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

Коэффициент вариации в 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%. Берите период от 3 месяцев, делите по прибыли, а не только по выручке, и не путайте «мало продаётся» с «не было в наличии». Для полной картины добавьте XYZ через коэффициент вариации — и пересчитывайте раз в месяц.