ABC-анализ в Excel

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, B и C

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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