Пошаговое руководство по созданию ABC-анализа в Excel

ABC-анализ в Excel — это метод ранжирования товаров, клиентов или поставщиков по степени их важности для бизнеса. Чтобы сделать его, нужно отсортировать данные по убыванию показателя (например, выручки), рассчитать долю каждого элемента в общем итоге, найти накопительную сумму и распределить позиции на три группы: A (топ-70–80% результата), B (следующие 15–20%) и C (остальные 5–10%). Весь процесс занимает 5–10 минут и требует только базовых формул.

Суть метода и критерии распределения

Метод основан на принципе Парето: 20% усилий дают 80% результата. В контексте склада или продаж это означает, что малая доля ассортимента приносит основную прибыль.

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

• Группа A: Самые ценные позиции. Обычно это 10–20% номенклатуры, дающие 70–80% оборота. Требуют ежедневного контроля и точного прогнозирования.
• Группа B: Промежуточная категория. 20–30% товаров с долей 15–20% в обороте. Контроль периодический (раз в неделю/месяц).
• Группа C: Малоценные позиции. 50–60% ассортимента, приносящих лишь 5–10% выручки. Закупаются редко или большими партиями «про запас».

Подготовка данных перед расчетом

Для корректного анализа вам понадобится таблица минимум из двух столбцов:

1. Наименование объекта (товар, клиент, поставщик).
2. Аналитический показатель (выручка, количество продаж, маржинальная прибыль или объем закупок).

Убедитесь, что в данных нет пустых строк, а числовые значения записаны корректно (без текстовых символов вроде «шт.» в ячейках с суммами). Анализ лучше проводить за фиксированный период (месяц, квартал или год), чтобы картина была объективной.

Инструкция: как сделать ABC-анализ в Excel за 5 шагов

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

Шаг 1. Расчет общей суммы

Сначала нужно узнать общий оборот, чтобы впоследствии вычислять доли. В любой свободной ячейке (например, D1) введите формулу: =СУММ(B:B) Запомните адрес этой ячейки или закрепите её в формулах через абсолютную ссылку.

Шаг 2. Расчет доли каждого элемента

Создайте столбец «Доля». В ячейке C2 введите формулу деления текущей продажи на общую сумму: =B2/$D$1 (Знаки доллара $ фиксируют ячейку с итогом, чтобы она не смещалась при копировании). Протяните формулу вниз до конца списка. Для наглядности примените к столбцу процентный формат.

Шаг 3. Сортировка данных

Это критически важный этап. Без него накопительный итог не будет работать корректно.

1. Выделите всю таблицу.
2. Перейдите на вкладку Данные → Сортировка.
3. Выберите сортировку по столбцу «Оборот» (или тому, по которому считаете) в порядке от больших к меньшим.

Теперь самые прибыльные товары находятся вверху списка.

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

Создайте столбец «Накопительная доля».

• В первой ячейке (D2) просто сошлитесь на долю первого товара: =C2.
• Во второй ячейке (D3) сложите накопительную долю предыдущей строки и текущую долю: =D2+C3.
• Протяните эту формулу до конца таблицы. В последней строке значение должно быть равно 1 (или 100%).

Шаг 5. Автоматическое присвоение групп

Создайте финальный столбец «Группа». Используем вложенную функцию ЕСЛИ для автоматической категоризации на основе накопительной доли. Формула для ячейки E2: =ЕСЛИ(D2<=0,7; "A"; ЕСЛИ(D2<=0,9; "B"; "C"))

Логика формулы: Если накопительная доля меньше или равна 70% — это группа A. Если больше 70%, но меньше или равна 90% — группа B. Все, что выше 90% — группа C.

Готовый шаблон формул для копирования

Если вы хотите сразу внедрить расчет, вот сводная таблица формул для стандартной структуры (Товар в A, Оборот в B, Итог суммы в B1000):

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

Частые ошибки при проведении анализа

1. Отсутствие сортировки. Самая грубая ошибка. Если не отсортировать товары от дорогого к дешевому, принцип Парето нарушится, и в группу «A» попадут случайные позиции.
2. Неверный выбор показателя. Анализ только по количеству проданных штук (без учета цены) может вывести в лидеры дешевый расходный материал, который не приносит реальной прибыли. Лучше использовать выручку или маржу.
3. «Застывшие» данные. Ассортимент и спрос меняются. Проводите ABC-анализ регулярно (раз в квартал), иначе вы будете оптимизировать запасы на основе устаравшей информации.
4. Игнорирование новинкок. Новые товары могут еще не набрать обороты и попасть в группу «C», хотя имеют высокий потенциал. Их стоит выделять в отдельную категорию или анализировать вручную.

FAQ

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

Что делать, если граница между группами проходит внутри одного товара? Такое бывает редко при больших объемах данных. Строго следуйте математике: товар относится к той группе, на которой «закрылся» диапазон. Например, если на товаре №15 накопительная доля стала 70,5%, он уже относится к группе B.

Подходит ли этот метод для услуг? Да, абсолютно. Вместо «товара» подставьте «услугу» или «клиента», а вместо «оборота» — «время затраченное» или «прибыль с контракта». Логика расчета остается неизменной.