Формула ABC-анализа в Excel

ABC-анализ раскладывает список товаров, клиентов или поставщиков на три группы по вкладу в общий результат. Метод опирается на принцип Парето: небольшая часть позиций даёт большую часть выручки, прибыли или заказов.

Классические границы — около 80 % результата на группу A, 15 % на группу B и 5 % на группу C. Это ориентир, а не норма: в узком ассортименте группа A бывает шире, в широкой рознице — уже. Поэтому в калькуляторе границы A и B вы задаёте сами, а группа C — всё, что осталось ниже границы B.

Считать можно по любому измеримому показателю: выручке, маржинальной прибыли, числу заказов, объёму запасов. Важно, чтобы показатель был один на весь расчёт и все позиции считались за одинаковый период.

Расчёт занимает пять колонок и не требует надстроек. Пусть в столбце A — названия товаров, в столбце B — выручка.

Шаг 1. Отсортируйте строки по выручке по убыванию: выделите диапазон и выберите сортировку от наибольшего к наименьшему. Без сортировки накопительный итог теряет смысл, и группы получаются случайными.

Шаг 2. Доля позиции в общей сумме. В C2 формула =B2/СУММ($B$2:$B$6). Знаки доллара фиксируют диапазон суммы, чтобы при протягивании вниз он не съезжал. Поставьте процентный формат.

Шаг 3. Накопленный итог. В D2 формула =C2, в D3 — =D2+C3, и так вниз. Или одной формулой =СУММ($C$2:C2) с протягиванием. В последней строке накопительный итог равен 100 %.

Шаг 4. Группа. В E2 формула =ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C")). Вместо 0,8 и 0,95 подставьте свои границы, делённые на 100.

Шаг 5. Проверка. Сумма долей должна дать 100 %, накопительный итог не убывать, а группа A стоять только сверху списка. Если это не так, сбилась сортировка или диапазон суммы.

Формула АВС-анализа в Excel состоит из двух частей, и их легко перепутать. Первая — доля позиции: выручка строки, делённая на сумму всего столбца, с закреплённым диапазоном. Вторая — накопительный итог: сумма долей от первой строки до текущей.

Накопительный итог и есть та самая формула, по которой ставят границы. Когда он переваливает за 80 %, позиции ниже уходят в группу B; когда за 95 % — в группу C. В Экселе это видно прямо по столбцу: граница проходит между двумя строками, где накопленная доля пересекла порог.

Абс-анализ, или АБС-анализ, — то же самое: так метод иногда называют по первым буквам латиницы. Формула в Экселе не меняется: сортировка, доля, накопленный итог, группа.