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

Пошаговая инструкция, как сделать ABC-анализ в Excel по выручке за период.

Считаем долю каждого товара, накопленный итог и раздаём группы A, B и C формулой ЕСЛИ на примере из 12 позиций.

Отдельно разобрали три способа без ручной сортировки и частые ошибки.

Статью написал:
Ваня Буявец, продюсер, основатель Checkroi
Ваня Буявец
Основатель Checkroi, продюсер, эксперт в выборе онлайн-курсов
Все 3286 статей автора Подписаться на Телеграм-канал
Одобрено экспертом:
Наташа Буявец, основатель Checkroi, эксперт по онлайн-курсам
Наташа Буявец
Основательница Checkroi, продюсер Youtube-каналов, эксперт по онлайн-курсам
Все 3940 экспертных мнений Подписаться на Телеграм-канал
Обложка: Как сделать ABC-анализ в Excel: пошагово с формулами и примером

Чтобы сделать ABC-анализ в Excel, нужны всего две колонки: название товара и его выручка за период. Отсортируйте выручку по убыванию, посчитайте долю каждой позиции формулой =B2/СУММ($B$2:$B$13), затем накопленную долю =СУММ($C$2:C2). Последний шаг: функция ЕСЛИ раздаёт группы. До 80 % накопленной доли идёт группа A, до 95 % группа B, остальное C.

Вся работа занимает 10–15 минут, если данные уже собраны. Ниже разберём каждый шаг на примере небольшого магазина кофейных принадлежностей. Покажем ещё три способа: без сортировки, через сводную таблицу и одной формулой в Excel 365. Если с функциями Excel вы пока на «вы», держите под рукой таблицу основных функций с примерами. А если хочется освоить программу целиком, загляните в подборку курсов по Excel.

ABC-анализ делит ассортимент на три группы по вкладу в результат. Его пишут и кириллицей, АВС-анализ, а в разговоре часто говорят «абс-анализ». Метод опирается на принцип Парето: небольшая часть товаров приносит основную часть денег. Сам принцип наглядно показывает диаграмма Парето, а ABC-анализ переводит его в таблицу, с которой удобно работать закупщику и категорийному менеджеру.

Что подготовить перед расчётом

Рой готовит выгрузку продаж для ABC-анализа

Выгрузите продажи за один период из учётной системы, CRM или кабинета маркетплейса. Для ABC-анализа ассортимента хватит квартала, для сезонных товаров берите год.

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

Товар Выручка за квартал, ₽
Фильтры 64 000
Кофемолка 412 000
Темпер 22 000
Турка 198 000
Салфетки 6 000
Чашка 58 000
Френч-пресс 287 000
Щётка 14 000
Весы 47 000
Кофе 1 кг 156 000
Мерная ложка 9 000
Питчер 31 000

Итого 1 304 000 ₽. Перед расчётом проверьте три вещи:

  • Один товар встречается один раз. Если выгрузка идёт по чекам и «Кофемолка» стоит в сотне строк, сначала сложите продажи через сводную таблицу или найдите повторы по инструкции о поиске дублей в столбце.
  • Выручка записана числами, а не текстом. Числа-текст прижаты к левому краю ячейки и помечены зелёным уголком. Функция СУММ такие ячейки пропускает, и доли выйдут неверными.
  • Нет возвратов и минусовых строк. Отрицательная выручка ломает накопленный итог: он перевалит за 100 % и вернётся обратно.

Таблицу удобно оформить как «умную»: тогда формулы сами растянутся на новые строки. Как это сделать, мы показали в инструкции о таблицах в Excel.

CheckroiCheckroiПодборка курсов по Microsoft Excel680 курсов • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→

Пошаговая инструкция: ABC-анализ в Excel за 4 шага

Скриншоты ниже сделаны в Google Таблицах: формулы там те же, функции в русском интерфейсе называются так же, как в Excel.

Одно отличие касается записи. В нашей таблице стоит американский формат, поэтому аргументы разделяются запятой, а дробь пишется через точку: 0.8. В русской версии Excel пишите точку с запятой и запятую: 0,8. Все формулы в тексте даны в варианте для русского Excel.

Шаг 1: отсортируйте выручку по убыванию

Выделите весь диапазон с заголовками, у нас это A1:B13. В Excel откройте вкладку «Данные» и нажмите «Сортировка». В окне выберите столбец в поле «Сортировать по», в поле «Сортировка» оставьте «Значения ячеек», в поле «Порядок» укажите «По убыванию». Есть и короткий путь: на вкладке «Главная» в группе «Сортировка и фильтр» выберите сортировку от максимального к минимальному. Подробности есть в справке Microsoft о сортировке.

В Google Таблицах путь другой: «Данные» → «Сортировать диапазон» → «Расширенные настройки сортировки диапазонов». Поставьте флажок «Данные со строкой заголовка», иначе заголовок уедет вниз вместе с цифрами. Порядок действий описан в справке Google Таблиц.

Меню «Данные» в Google Таблицах с пунктом «Сортировать диапазон» над выделенной таблицей выручки
Выделили обе колонки, товар и выручку, и открыли меню «Данные». В Excel ту же задачу решает кнопка «Сортировка» на вкладке «Данные»

Сортировать нужно обе колонки сразу. Если выделить только выручку, названия товаров останутся на месте и отъедут от своих сумм. Excel в таком случае предложит расширить выделение, соглашайтесь. Больше приёмов сортировки, включая сортировку по нескольким столбцам, собрано в инструкции о сортировке в Excel.

Шаг 2: посчитайте долю каждого товара

В C1 напишите заголовок «Доля», в C2 введите формулу и протяните её до C13:

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

Знаки $ закрепляют диапазон суммы. Без них при протягивании знаменатель сползёт вниз и каждая следующая доля окажется завышенной. Переключать ссылку между относительной и абсолютной удобно клавишей F4, механику описывает справка Microsoft о типах ссылок.

Выделите столбец C и включите процентный формат кнопкой со знаком процента на вкладке «Главная». Кофемолка показывает 31,6 %: почти треть выручки магазина приходится на один товар.

Формула доли товара в выручке =B2/СУММ($B$2:$B$13) в Google Таблицах, кофемолка 31,6 %
Знаки доллара закрепили диапазон суммы, поэтому формулу можно протянуть вниз. Кофемолка даёт 31,6 % выручки

Проверка: сумма столбца C должна дать ровно 100 %. Если вышло больше, ищите пропущенный $. Про формулы с процентами подробнее рассказано в материале о том, как посчитать процент от числа в Excel.

Шаг 3: посчитайте накопленную долю

Накопленная доля показывает, какую часть выручки дают текущий товар и все товары выше него. В D1 напишите «Накоплено», в D2 введите формулу и протяните вниз:

=СУММ($C$2:C2)

Здесь закреплено только начало диапазона. При протягивании конец растёт: в D3 формула превращается в =СУММ($C$2:C3), в D4 в =СУММ($C$2:C4). В последней строке должно получиться 100 %.

Накопленная доля =СУММ($C$2:C3) в Google Таблицах для ABC-анализа
Начало диапазона закреплено, конец растёт с каждой строкой. Кофемолка и френч-пресс вместе дают 53,6 % выручки

Первые три товара вместе дают 68,8 % выручки. С четвёртым, кофе в зёрнах, накопленная доля переходит порог: 80,8 %.

Шаг 4: присвойте группы A, B и C

В E1 напишите «Группа», в E2 введите формулу с вложенной функцией ЕСЛИ:

=ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C"))

Читается она так: если накопленная доля не больше 80 %, ставим A. Иначе проверяем второе условие: не больше 95 % даёт B, всё остальное C. Синтаксис вложенных условий разобран в материале о функции ЕСЛИ в Excel, а исходное описание есть в справке Microsoft.

Группы A, B и C по формуле ЕСЛИ в Google Таблицах, кофе в зёрнах с накопленной долей 80,75 % попал в группу B
Формула отправила кофе в зёрнах в группу B: его накопленная доля 80,75 % чуть выше порога. В русском Excel пишите 0,8 и точку с запятой между аргументами

Готовая таблица выглядит так:

Товар Выручка, ₽ Доля Накоплено Группа
Кофемолка 412 000 31,6 % 31,6 % A
Френч-пресс 287 000 22,0 % 53,6 % A
Турка 198 000 15,2 % 68,8 % A
Кофе 1 кг 156 000 12,0 % 80,8 % B
Фильтры 64 000 4,9 % 85,7 % B
Чашка 58 000 4,4 % 90,1 % B
Весы 47 000 3,6 % 93,7 % B
Питчер 31 000 2,4 % 96,1 % C
Темпер 22 000 1,7 % 97,8 % C
Щётка 14 000 1,1 % 98,8 % C
Мерная ложка 9 000 0,7 % 99,5 % C
Салфетки 6 000 0,5 % 100 % C

Итог: 3 товара из 12 приносят 68,8 % выручки, ещё 4 дают 24,9 %, а оставшиеся 5 позиций вместе собирают 6,3 %. Чтобы группы читались с одного взгляда, раскрасьте их: «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Текст содержит».

Канал основателя Checkroi Вани Буявца3 800 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединиться

Где проводить границу: товар на пороге 80 %

Рой решает, в какую группу отнести пачку кофе

Посмотрите на кофе в зёрнах. До него накоплено 68,8 %, вместе с ним 80,8 %. Наша формула отправила его в B, потому что 80,8 больше 80. Но 11,2 из его 12 процентных пунктов лежат ниже порога, и по смыслу это товар группы A.

Решение: сравнивайте с порогом не конец интервала, а его начало, то есть накопленную долю до товара. Формула меняется на одну операцию:

=ЕСЛИ(D2-C2<0,8;"A";ЕСЛИ(D2-C2<0,95;"B";"C"))

С ней кофе в зёрнах уходит в A, а питчер с началом интервала 93,7 % переходит из C в B. В небольшом ассортименте такая правка меняет решение по закупке. В каталоге на 3 000 позиций разница почти незаметна. Выберите одно правило и применяйте его во всех отчётах, иначе группы будут прыгать от месяца к месяцу без изменений в продажах.

Сами пороги тоже не закон. Классика 80/15/5 подходит большинству магазинов. Если в группу A попадает больше 20–25 % ассортимента, это знак, что продажи распределены ровно и метод мало что даёт. Встречаются схемы 75/20/5 и 50/30/20; но пороги не меняйте между периодами, которые вы сравниваете.

Другие способы сделать ABC-анализ в Excel

У классического способа с сортировкой есть минус: при новой выгрузке таблицу приходится сортировать заново. Три альтернативы:

Способ Когда подходит Версия Excel
Формула без сортировки Данные обновляются, порядок строк трогать нельзя Любая
Сводная таблица Сырая выгрузка по чекам, товар повторяется в сотнях строк Любая
Функция СОРТ Нужен отсортированный отчёт рядом с исходником Microsoft 365, 2021, 2024

Формула без сортировки через СУММЕСЛИ

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

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

Для кофе в зёрнах формула сложит 412 000, 287 000, 198 000 и 156 000 и вернёт те же 80,8 %. Группы раздаёт та же ЕСЛИ. Как работает условие ">="&B2, разобрано в инструкции о функции СУММЕСЛИ, синтаксис есть в справке Microsoft.

У способа одна особенность. Если у двух товаров одинаковая выручка, оба получат одинаковую накопленную долю, в которую войдут суммы обоих. На практике это редко меняет группу, но знать об этом стоит.

Через сводную таблицу

Сводная таблица одновременно складывает повторы и считает нарастающий итог. Порядок действий:

  1. Выделите выгрузку, откройте вкладку «Вставка» и выберите сводную таблицу.
  2. Перетащите «Товар» в строки, а «Выручку» в значения дважды.
  3. Отсортируйте товары по сумме выручки по убыванию.
  4. Щёлкните правой кнопкой по второму полю значений и выберите «Показать значения как» → «% от суммы с нарастающим итогом в поле». Базовым полем укажите поле с названиями товаров.
  5. Рядом со сводной таблицей допишите столбец с формулой ЕСЛИ для групп.

Названия пунктов сверены со справкой Microsoft о вычислениях в сводных таблицах. В Excel для Mac нужный вариант может прятаться в «Дополнительных параметрах».

Одной формулой в Excel 365

В новых версиях Excel отсортированную копию таблицы выдаёт функция СОРТ. Введите её в свободную ячейку, например G2:

=СОРТ(A2:B13;2;-1)

Второй аргумент говорит сортировать по второму столбцу, третий, -1, задаёт порядок по убыванию. Результат сам разольётся на 12 строк и обновится, когда изменится исходник. Дальше к нему добавляются те же формулы доли и накопленной доли. Функция работает в Microsoft 365, Excel 2021 и 2024, это указано в справке по СОРТ. Как устроены такие разливающиеся формулы, объясняет материал о массивах в Excel.

Какой показатель брать для анализа

Чаще всего берут выручку, но это не единственный вариант. От показателя зависит, на какой вопрос ответит анализ:

Показатель Что покажет Подвох
Выручка Какие товары дают оборот Дорогой товар с нулевой наценкой попадёт в A
Валовая прибыль Какие товары зарабатывают деньги Нужна себестоимость по каждой позиции
Количество продаж в штуках Что чаще всего берут покупатели Дешёвые расходники обгонят основной товар
Сумма закупок На каких поставщиков уходит бюджет Подходит для ABC-анализа поставщиков, а не ассортимента

Полезно сделать два анализа, по выручке и по прибыли, и сравнить группы. Товар, который стоит в A по выручке и в C по прибыли, первый кандидат на пересмотр цены или условий закупки.

ABC-анализ отвечает на вопрос «сколько приносит товар», но не показывает, насколько стабильно он продаётся. Для этого его совмещают с XYZ-анализом, который делит товары по разбросу продаж из месяца в месяц. Считается XYZ через коэффициент вариации, а на пересечении двух анализов получается матрица из девяти групп.

Что делать с группами после анализа

Товары магазина, разложенные по группам A, B и C

Таблица с буквами сама по себе ничего не меняет. Её ценность в том, что каждой группе соответствует свой режим работы с запасом и ценой:

Группа В нашем примере Что делать
A 3 товара, 68,8 % выручки Следить за остатками еженедельно, иметь страховой запас, первыми договариваться о скидках у поставщика
B 4 товара, 24,9 % выручки Пересматривать запас раз в месяц, искать, что из группы можно подтянуть в A акцией или выкладкой
C 5 товаров, 6,3 % выручки Сократить запас до минимума, проверить на вывод из ассортимента

С группой C торопиться не стоит. Дешёвые фильтры и мерные ложки часто покупают вместе с кофемолкой из группы A. Уберёте их, и покупатель уйдёт за всем набором в другой магазин. Перед выводом товара посмотрите, с чем его берут в одном чеке.

Обратный случай тоже бывает. Если в группу A попал товар с минимальной наценкой, он делает оборот, но почти не зарабатывает. Такой товар держат ради трафика, а прибыль ищут в группе B.

Как часто пересчитывать

Для быстрого ассортимента вроде продуктов или расходников анализ обновляют раз в месяц. Для техники и сезонных товаров хватит квартала, но сравнивайте одинаковые периоды: зимний квартал с прошлогодним зимним, а не с летним.

Удобно сохранять каждый расчёт на отдельном листе с датой. Через три-четыре периода видно, какие товары стабильно держатся в A, а какие перепрыгивают между группами. Вторые и есть главный риск для закупки.

Ваня БуявецКанал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в канал

Частые ошибки и что с ними делать

Большинство ошибок видно по одной проверке: в последней строке накопленная доля должна быть ровно 100 %. Если там другое число, ищите причину в списке:

Симптом Причина Что сделать
Сумма долей больше 100 % В формуле доли нет $, знаменатель сполз Исправить на $B$2:$B$13 и протянуть заново
У товаров чужая выручка Отсортировали один столбец вместо всей таблицы Отменить сортировку и выделить диапазон целиком
Накопленная доля скачет вниз В данных есть возвраты с минусом Убрать минусовые строки или считать по чистым продажам
Один товар в нескольких группах Позиция повторяется в выгрузке Сначала сложить продажи сводной таблицей
Сумма долей больше 100 %, хотя $ на месте Часть сумм записана текстом: деление их видит, а СУММ пропускает Перевести столбец в числа и пересчитать

Ещё одна ошибка не видна в формулах. Если сравнить квартал без новинок с кварталом, где половина ассортимента появилась в середине срока, новые товары попадут в C просто потому, что продавались меньше времени. Сравнивайте товары, которые были в продаже весь период, а новинки разбирайте отдельно.

Кому пригодится ABC-анализ

Метод нужен всем, кто отвечает за ассортимент и закупки. Это категорийный менеджер, который решает, какие позиции держать на полке, и закупщик, у которого бюджет на складской запас ограничен. Если вы только идёте в профессию, посмотрите, как стать менеджером по закупкам: ABC-анализ в Excel там входит в базовый набор навыков.

Продавцам на маркетплейсах метод помогает выбрать, на какие товары тратить рекламный бюджет и сколько товара везти на склад площадки. Подробно о работе с площадками рассказано в материале о том, как стать менеджером маркетплейсов.

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

В производстве метод применяют к запасам сырья и комплектующих. Логика та же, только вместо выручки берут стоимость годового расхода. Группа A здесь требует точного планирования поставок, группа C закупается крупными партиями раз в несколько месяцев.

Где освоить Excel системно

ABC-анализ собирается из четырёх базовых приёмов: сортировки, абсолютных ссылок, функции СУММ и вложенного ЕСЛИ. Если какой-то шаг дался с трудом, стоит подтянуть основы, а дальше переходить к сводным таблицам, Power Query и дашбордам. В подборке ниже курсы по Excel с ценами и программами, выбирайте по своему уровню.

КурсШколаСтоимость со скидкойВ рассрочкуДлитель­ностьОбзор курса от Checkroi
Excel
Перейти на сайт курса
НетологияНетология31 900 ₽1691 ₽/мес.8 недельОбзор курса
Power BI & Excel PRO
Перейти на сайт курса
НетологияНетология38 500 ₽3208 ₽/мес.2 месяцаОбзор курса
Excel для работы
Перейти на сайт курса
Академия ЭдюсонЭдюсон16 500 ₽1375 ₽/мес.2 неделиОбзор курса
Excel + Google Таблицы с нуля до PRO
Перейти на сайт курса
SkillboxSkillbox29 899 ₽2965 ₽/мес.4 месяцаОбзор курса
Excel для аналитика
Перейти на сайт курса
Moscow Business AcademyMoscow Business Academy17 200 ₽2866 ₽/мес.2 месяцаОбзор курса

Больше программ — в полном каталоге курсов по Microsoft Excel

Часто задаваемые вопросы

Какие проценты брать для групп A, B и C?

Чаще всего берут 80, 15 и 5 % выручки: группа A до 80 % накопленной доли, B до 95 %, C всё остальное. Пороги можно сдвинуть, например на 75/20/5, но в периодах, которые вы сравниваете, они должны быть одинаковыми.

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

Посчитайте накопленную долю формулой =СУММЕСЛИ($B$2:$B$13;">="&B2)/СУММ($B$2:$B$13). Она складывает выручку всех товаров, которые продаются не хуже текущего, поэтому порядок строк не важен. Группы раздаёт та же функция ЕСЛИ.

Какой формулой присвоить группы A, B и C?

Вложенной ЕСЛИ по столбцу накопленной доли: =ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C")). В Google Таблицах с американским форматом дробь пишется через точку, а аргументы разделяются запятой.

За какой период брать данные для ABC-анализа?

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

Чем ABC-анализ отличается от XYZ-анализа?

ABC делит товары по вкладу в выручку или прибыль, а XYZ по стабильности продаж от месяца к месяцу. XYZ считают через коэффициент вариации, а вместе два анализа дают матрицу из девяти групп.

Можно ли сделать ABC-анализ через сводную таблицу?

Да. Добавьте выручку в значения дважды, отсортируйте товары по убыванию и для второго поля выберите «Показать значения как» → «% от суммы с нарастающим итогом в поле». Группы допишите рядом формулой ЕСЛИ.

Читайте Checkroi первым в Google
Добавьте Checkroi в избранные источники — и наши разборы курсов и обзоры школ будут показываться выше в вашей выдаче Google.
Оставить комментарий
0 комментариев
Форма комментария

Оставьте комментарий

Напишите, что думаете. Нам важно ваше мнение!