ABC-анализ в Excel

Чтобы сделать ABC-анализ в Excel, нужны три шага: отсортировать позиции по убыванию показателя, посчитать долю накопительным итогом =СУММ($B$2:B2)/СУММ($B$2:$B$9) и присвоить группу формулой =ЕСЛИ(C2<=80%;"A";ЕСЛИ(C2<=95%;"B";"C")). Группа A — позиции, которые дают первые 80% результата, B — следующие 15%, C — оставшиеся 5%. Без сортировки накопленную долю считает =СУММЕСЛИ($B$2:$B$9;">="&B2)/СУММ($B$2:$B$9), а без формул весь анализ с цветной разметкой групп делает бесплатная процедура !SEMTools.

Это статья о проведении ABC-анализа в Excel. Смотреть также, и не путать: Функция ABS.
Также упоминается процедура бесплатной версии !SEMTools.

ABC-анализ — это один из эффективных методов классификации и распределения ресурсов.

ABC-анализ полезен в самых разных областях - маркетинге (анализ источников трафика), продажах (клиентская база, ассортиментная матрица), логистике (направления перевозок, оборачиваемость складов) и т.д.

Я и сам, как маркетолог, часто применяю его в своей работе. Возможно, вы уже слышали о нем (и поэтому вы тут), но не до конца понимаете, как сделать ABC-анализ в Excel. А это, действительно, очень просто! Постараюсь объяснить максимально просто и вместе с тем развернуто.

Объяснение принципов ABC-анализа

ABC-анализ основывается на принципе (или законе) Парето, также известном как правило 80/20. Суть заключается в том, что практических во всех областях нашей жизни около ~80% результата достигается путём приложения ~20% усилий.

На языке статистики это выглядит так, что малая часть элементов (группа A) составляет большую часть суммы по метрике (например, стоимости или объёму), тогда как большая часть элементов (группы B и C) имеет меньшую значимость.

Однако, если принцип Парето делит все на две категории (значимое и незначимое), при ABC-анализе добавляется одна промежуточная, и в итоге мы имеем три.

Метод назван согласно буквам, которыми обозначают элементы при делении на эти три категории: A, B и C. Группа A включает самые важные и ценные элементы, на которые приходится основная часть результата. Группа B содержит элементы средней значимости, а группа C включает наименее значимые элементы. Такое разделение помогает сфокусировать внимание на ключевых позициях и оптимизировать управление ресурсами.

Давайте рассмотрим на конкретных примерах, как проводить ABC-анализ в Excel и визуализировать его результаты для принятия более обоснованных решений.

Как сделать ABC-анализ в Excel по шагам

Предположим, у вас есть небольшая таблица источников трафика с визитами по каждому источнику за определенный период. Ваша задача определить наиболее приоритетные источники, чтобы сконцентрировать на них внимание и усилия. Таблица выглядит следующим образом:

Ваша задача состоит в том, чтобы выделить среди источников трафика наиболее приоритетные - те, что приводят ~80% трафика, второстепенные и наименее значимые. Хочется подчеркнуть, что данная таблица приведена исключительно в демонстрационных целях, а в реальности ABC-анализ нужен для гораздо более объемных таблиц.

Сортировка данных

Первым шагом станет сортировка данных по столбцу "Количество трафика" в порядке убывания, чтобы вверху оказались наиболее значимые каналы трафика.

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

Расчет суммы накопительным итогом

Для расчета накопительной суммы добавляется новый столбец. Начинается с первого канала и постепенно добавляется трафик каждого следующего канала к общей сумме. Таким образом, получается процент накопительной суммы для каждого канала.

Если источники в столбце A, трафик в B2:B9, то в C2 введите формулу и протяните её вниз — первая ссылка закреплена знаками $, а вторая «растёт» вместе со строкой:

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

Подсчёт доли накопительным итогом

Долю накопительным итогом получаем, разделив накопленную сумму на общую. В D2:

=C2/СУММ($B$2:$B$9)

Задайте ячейкам процентный формат (Ctrl+Shift+5).

Определение категорий A, B и C

Каналы трафика делятся на три категории на основе доли накопленным итогом:

Категория A: Каналы, составляющие 80% накопительного трафика.

Категория B: Каналы на следующем 15%-ном интервале (на отсечке более 80%, но менее 95%).

Категория C: Остальные каналы.

Группу проставляет вложенная функция ЕСЛИ, в E2:

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

Итоговая таблица будет выглядеть таким образом:

Для более яркой визуальной дифференциации групп можно использовать форматирование ячеек - заливку разными цветами.

Визуализация результатов

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

Как видите, правило Парето для моего сайта более чем соблюдено. Даже не 20%, а 7% страниц генерируют 80% просмотров всего сайта. Однако, эти статьи максимально хорошего качества, поэтому говоря об усилиях, вполне возможно, что это те же 20% усилий :)

В то же время, 83% страниц генерируют всего 5% просмотров. Очевидно, далеко не весь контент одинаково полезен и востребован.

ABC-анализ формулой без сортировки

Сортировать таблицу не обязательно. Накопленная доля позиции — это сумма всех значений, которые не меньше её собственного, делённая на общую сумму. Это ровно то, что считает СУММЕСЛИ с условием «>=»:

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

Если у двух позиций одинаковые значения, СУММЕСЛИ учтёт обе сразу, и у них будет одинаковая накопленная доля — для ABC-анализа это допустимо.

ABC-анализ в 2 клика с !SEMTools для Excel

Как видно из примеров выше, сделать ABC-анализ в Excel не так-то просто. Нужны несколько промежуточных этапов - сортировка, подсчёт суммы и доли накопительным итогом, разметка и форматирование выбранных групп.

Что, если бы можно было обойтись без всего этого, и делать ABC-анализ без сортировки?

Подумал я и создал соответствующую бесплатную процедуру в своей надстройке !SEMTools для Excel. Как работает, можно посмотреть на коротком видео примере ниже:

Как видите, данные даже не нужно сортировать. Надстройка сама упорядочивает данные в памяти, считает все доли и размечает группы A, B и C соответствующими цветами. Повторюсь - даже в стартовой бесплатной версии надстройки.

Почему ABC-анализ получается неверным

• Данные не отсортированы. Накопительная сумма через СУММ($B$2:B2) имеет смысл только после сортировки по убыванию. Либо отсортируйте таблицу, либо используйте вариант со СУММЕСЛИ.
• Все доли равны 100% или растут неправильно. В формуле не закреплена ссылка: должно быть $B$2:B2 и $B$2:$B$9, а не B2:B9.
• Первая позиция попала в группу B. Если одна позиция даёт больше 80%, по правилу «≤ 80%» она в группу A не попадёт. Многие относят к группе A и позицию, на которой накопленная доля перешагнула 80%: тогда условие — =ЕСЛИ(D2-B2/СУММ($B$2:$B$9)<80%;"A";ЕСЛИ(D2-B2/СУММ($B$2:$B$9)<95%;"B";"C")).
• В анализ попали итоговые строки. Строка «Итого» внутри диапазона удваивает общую сумму, и все позиции уходят в группу A. Исключите итоги из диапазона.
• Граница 80/95 не подходит. Пороги условны: для ассортимента часто берут 80/95, для клиентской базы — 75/95 или 50/80. Вынесите пороги в отдельные ячейки и ссылайтесь на них со знаками $.

ABC-анализ в Excel — частые вопросы

Какая формула нужна для ABC-анализа?

Две: доля накопительным итогом =СУММ($B$2:B2)/СУММ($B$2:$B$9) (после сортировки по убыванию) и группа =ЕСЛИ(C2<=80%;"A";ЕСЛИ(C2<=95%;"B";"C")).

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

Да: накопленную долю считает =СУММЕСЛИ($B$2:$B$9;">="&B2)/СУММ($B$2:$B$9), порядок строк при этом не важен.

Чем ABC-анализ отличается от правила Парето?

Парето делит позиции на две группы (20% дают 80% результата), ABC-анализ — на три: A, B и C, добавляя промежуточную группу.

Что такое XYZ-анализ?

Это деление позиций по стабильности спроса (коэффициенту вариации). Его часто совмещают с ABC-анализом: AX — важные и стабильные позиции, CZ — малозначимые и нестабильные.

Итоги: как сделать ABC-анализ в Excel

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: сортировка данных и статистические метрики в Excel.