ABC-анализ товаров в Excel: как найти свои 20% товаров, дающих 80% прибыли

23.03.2026

Известный принцип Парето гласит: 20% товаров дают 80% выручки. Но как найти именно эти 20%? И как управлять остальными 80%? На эти вопросы отвечает ABC-анализ — один из самых мощных инструментов оптимизации ассортимента в розничной торговле и управлении складом.

Почему ABC-анализ работает

Если вы работаете в розничной торговле, E-commerce или управляете складом, то знаете: не все товары одинаково важны. Один товар может приносить половину прибыли, а сотни других товаров работают себе в убыток или едва окупают место на полке.

ABC-анализ позволяет:

Сократить затраты на хранение — понять, какие товары занимают место впустую
Оптимизировать закупки — закупать нужные товары, отказаться от убыточных
Увеличить прибыль — сосредоточиться на товарах, которые действительно приносят деньги
Управлять складом эффективнее — размещать популярные товары в удобных местах

Что такое ABC-классификация

ABC-классификация делит все товары на три группы:

Группа A (80% прибыли) — малое количество самых ценных товаров. Это могут быть 5-10% от всего ассортимента, но они приносят 70-80% выручки. К этим товарам нужно относиться особенно внимательно.

Группа B (15% прибыли) — товары среднего значения. Обычно это 10-20% товаров, которые приносят 15-20% выручки. Управление стандартное.

Группа C (5% прибыли) — остальной ассортимент. Это может быть 70-80% товаров, но они приносят лишь 5% выручки. Часто это убыточные позиции.

Пример реального бизнеса: магазин электроники

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

|-------|---|---|---|

| iPhone 15 Pro | 15 шт | 120 000 ₽ | 1 800 000 ₽ |

| Samsung Galaxy S24 | 12 шт | 80 000 ₽ | 960 000 ₽ |

| iPhone 15 | 8 шт | 90 000 ₽ | 720 000 ₽ |

| Чехол универсальный | 180 шт | 500 ₽ | 90 000 ₽ |

| Зарядка быстрая | 45 шт | 2 000 ₽ | 90 000 ₽ |

| Защитная плёнка | 200 шт | 150 ₽ | 30 000 ₽ |

Всего выручка: 3 690 000 ₽

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

Шаг 1. Подготовка данных

Создайте таблицу в Excel со следующими столбцами:

A: Товар

B: Выручка

C: Доля выручки

D: Накопленная доля

E: Категория

Вводим наши данные:

Строка 1: Заголовки

Строка 2: iPhone 15 Pro | 1800000

Строка 3: Samsung Galaxy S24 | 960000

Строка 4: iPhone 15 | 720000

Строка 5: Чехол универсальный | 90000

Строка 6: Зарядка быстрая | 90000

Строка 7: Защитная плёнка | 30000

Шаг 2. Расчёт доли выручки каждого товара

В ячейке C2 вводим формулу для расчёта доли одного товара от общей выручки:

`excel

=B2/SUM($B$2:$B$7)

Копируем эту формулу вниз. В результате получим:

iPhone 15 Pro: 48.78% (1 800 000 / 3 690 000)

Samsung Galaxy S24: 26.02%

iPhone 15: 19.51%

Чехол: 2.44%

Зарядка: 2.44%

Плёнка: 0.81%

Шаг 3. Сортировка по выручке

Это критичный шаг. Выделяем всю таблицу (A1:E7) и сортируем по столбцу B (Выручка) в порядке убывания. После сортировки дорогие товары будут в начале.

Шаг 4. Расчёт накопленной доли (кривая Лоренца)

В ячейке D2:

`excel

=C2

В ячейке D3:

`excel

=D2+C3

И копируем D3 вниз. Получим:

iPhone 15 Pro: 48.78%

Samsung Galaxy S24: 74.80% (48.78% + 26.02%)

iPhone 15: 94.31% (74.80% + 19.51%)

Чехол: 96.75%

Зарядка: 99.19%

Плёнка: 100.00%

Шаг 5. Присвоение группы (A, B или C)

В ячейке E2 вводим формулу:

`excel

=IF(D2<=0.8,"A",IF(D2<=0.95,"B","C"))

Формула работает так:

Если накопленная доля <= 80%, это товар группы A
Если <= 95%, это товар группы B
Иначе товар группы C

Копируем формулу вниз. В нашем примере:

iPhone 15 Pro: A (48.78%)

Samsung Galaxy S24: A (74.80%)

iPhone 15: A (94.31%)

Чехол: B (96.75%)

Зарядка: B (99.19%)

Плёнка: C (100%)

Все три смартфона — это группа A! Они приносят 94.31% выручки. Средние товары — чехол и зарядка. Плёнка — убыток, который занимает место.

Визуализация: диаграмма Парето в Excel

Создаём диаграмму для наглядности:

Выделяем столбцы A (Товар) и D (Накопленная доля)
Вставляем график (Insert > Chart)
Выбираем тип "XY (Scatter)" или "Column"
На диаграмме видно, как быстро выручка «накапливается» на первых товарах

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

ABC-анализ в розничном магазине: что дальше?

После анализа для каждой группы применяем разные стратегии:

Товары группы A (80% выручки)

Никогда не кончаются: следим, чтобы на полке всегда было достаточно товара
Повышаем наценку аккуратно: можем позволить себе небольшое увеличение цены
Закупаем в больших объёмах: договариваемся о лучшей цене у поставщика
Размещаем в лучшем месте: в центре магазина, на уровне глаз

Товары группы B (15% выручки)

Заказываем по стандартной схеме: обычное управление запасами
Стараемся избежать дефицита: но не переплачиваем за хранение избытка
Тестируем новые позиции: проверяем, могут ли они вырасти в группу A

Товары группы C (5% выручки)

Снижаем или прекращаем закупки: проанализируем, почему они не продаются
Может быть, это опечатка: проверяем, правильно ли считается выручка
Уходит на скидки: выдаём скидку на остаток и освобождаем место
Удаляем из ассортимента: если товар не продаётся даже со скидкой

XYZ-анализ: второй слой классификации

ABC-анализ работает с выручкой. Но что, если товар приносит хорошую прибыль, но продаётся нестабильно? На помощь приходит XYZ-анализ, который оценивает стабильность продаж.

Разделяем товары по волатильности:

Группа X — стабильные продажи (коэффициент вариации 0-25%)

Группа Y — средняя волатильность (25-75%)

Группа Z — нестабильные продажи (75%+)

Формула коэффициента вариации:

`excel

=(STDEV(диапазон_продаж)/AVERAGE(диапазон_продаж))

Если товар из группы A, но в группе Z (нестабильный спрос), нужно подстраховаться под сезонность или тренды.

ABC-XYZ матрица: полная картина

Комбинируя ABC и XYZ, получаем 9 позиций:

X (стабильно) Y (средне) Z (скачки)

A AX AY AZ

(звёзды) (попечение) (риск)

B BX BY BZ

(рабочие) (наблюдение) (неточное)

C CX CY CZ

(переток) (очистка) (убыток)

AX — королевские товары: стабильные, прибыльные. Закупаем с уверенностью.

AZ — опасные товары: приносят прибыль, но скачут продажи. Подстраховываемся.

CZ — балласт: нестабильный спрос и малая выручка. Удаляем.

Ошибки при анализе товаров

Ошибка 1: Анализируем выручку вместо прибыли

Товар может приносить большую выручку, но иметь малую маржу. Например, яйца в супермаркете — большие объёмы продаж, но малая прибыль. Лучше анализировать именно прибыль, а не выручку.

Формула в таком случае:

`excel

=B2*Маржа_товара

Ошибка 2: Забываем про сезонность

Ёлки продаются только в декабре, но это не значит, что их нужно убирать из ассортимента. Анализируем товары внутри одного сезона.

Ошибка 3: Делаем анализ раз в год

Тренды в продажах меняются. ABC-анализ нужно делать ежемесячно или хотя бы ежеквартально. В Excel можно автоматизировать этот процесс.

Автоматизация ABC-анализа в Excel

Если товаров сотни или тысячи, анализ вручную невозможен. Создаём шаблон в Excel:

Подготавливаем данные в одной таблице (вкладка "Исходные данные")
Создаём отчёт на второй вкладке, где формулы автоматически считают доли и присваивают категории
Используем VLOOKUP для связи товаров с их категориями
Настраиваем фильтры по группам A, B, C для быстрого анализа

`excel

=VLOOKUP(A2,'Исходные данные'!$A$2:$E$1000,5,FALSE)

Такой шаблон экономит часы на анализе и позволяет быстро реагировать на изменения.

Практический пример: управление складом

Вы управляете складом на 10 000 товарных позиций. Место дорогое. ABC-анализ показывает:

Группа A (2000 товаров, 80% выручки): размещаем на стеллажах на уровне глаз, удобном доступе
Группа B (3000 товаров, 15% выручки): стандартное размещение
Группа C (5000 товаров, 5% выручки): хранилище в углу, раз в месяц перевозим на другой склад

Результат: пикирующие товары легче найти, комплектование заказов быстрее, складское пространство используется эффективнее.

Выводы

ABC-анализ — это не магия, а простая математика, которая помогает увидеть реальную картину вашего бизнеса. 20% товаров действительно дают 80% прибыли. Вопрос в том, знаете ли вы, какие это товары.

С помощью этого анализа вы:

Понимаете, на чём зарабатываете деньги
Избавляетесь от убыточных товаров
Оптимизируете закупки и хранение
Увеличиваете общую прибыль

Начните с простого анализа своего каталога прямо сегодня. Даже небольшие изменения в ассортименте часто дают заметный прирост в прибыли.

Используйте ABC-анализ не как разовый отчёт, а как постоянный инструмент управления. И ваш бизнес станет заметно более эффективным.