ABC/XYZ-анализ в Excel: от продаж до матрицы ассортимента
Пошагово собираем рабочий ABC/XYZ-анализ: считаем вклад товара, стабильность спроса, матрицу AX–CZ и превращаем результат в решения по запасам и закупкам.
Что показывает ABC/XYZ-анализ и какие решения из него получать
ABC и XYZ отвечают на разные управленческие вопросы. ABC показывает экономическую значимость позиции: какую долю выручки, валовой прибыли или оборота она формирует. XYZ оценивает стабильность спроса: насколько продажи конкретного SKU меняются от периода к периоду. Поэтому товар может быть очень важным по деньгам, но плохо прогнозируемым, или наоборот — продаваться стабильно, но давать небольшой вклад.
После объединения двух классификаций получается девять групп от AX до CZ. AX — важные и стабильные позиции, для которых обычно оправдан высокий уровень доступности. AZ — важные, но нестабильные: их нельзя закупать только по среднему спросу. CX — стабильные товары с небольшим вкладом, а CZ требуют проверки причин присутствия в ассортименте. Матрица не выдаёт готовый приказ «закупить» или «удалить», а помогает определить, где нужен точный прогноз, страховой запас, ручной контроль или пересмотр ассортимента.
Перед расчётом сформулируйте решение, которое хотите принять. Для управления оборотом ABC логично считать по выручке, для оценки экономической ценности — по валовой прибыли, а для складской логистики иногда полезен анализ по количеству отгруженных единиц. Один и тот же ассортимент может получить разные классы при разных показателях — это нормально.
Структура Excel-файла: 4 листа вместо одной перегруженной таблицы
Для рабочего анализа удобно разделить книгу на четыре листа. «Продажи» содержит исходные данные: Дата, SKU, Товар, Количество, Выручка, Себестоимость и при необходимости Канал. «ABC» агрегирует выбранный показатель по SKU и рассчитывает доли. «XYZ» хранит продажи по неделям или месяцам и коэффициент вариации. «Матрица» объединяет классы и содержит итоговые рекомендации.
Исходный диапазон лучше превратить в таблицу Excel через Ctrl+T. Тогда новые строки автоматически попадают в формулы, фильтры и сводные таблицы. Не вставляйте ручные итоги внутрь исходных данных и не объединяйте ячейки. SKU должен быть стабильным идентификатором: одинаковые названия товаров, переименования и разные варианты написания не должны создавать ложные позиции.
На итоговом листе достаточно колонок SKU, Товар, Выручка или Валовая прибыль, Доля, Накопительная доля, ABC, Среднее за период, Стандартное отклонение, CV, XYZ, Группа и Решение. Отдельная колонка «Решение» превращает классификацию в рабочий инструмент: например, «поддерживать запас», «проверить сезонность», «закупать под заказ» или «проверить рентабельность».
ABC-анализ: реальные формулы доли и накопительного итога
Сначала агрегируйте выбранный показатель по каждому SKU и отсортируйте позиции по убыванию. Допустим, сумма выручки находится в столбце C, начиная с C2. Общую выручку можно получить формулой =СУММ($C$2:$C$101). Доля первой позиции в D2: =C2/СУММ($C$2:$C$101). Ячейку оформите как процент и протяните формулу вниз.
Накопительная доля в E2 считается как =СУММ($D$2:D2). После протягивания вниз диапазон будет расширяться от первой позиции до текущей. Класс можно назначить формулой =ЕСЛИ(E2<=80%;"A";ЕСЛИ(E2<=95%;"B";"C")). При границах 80/15/5 группа A формирует первые 80% результата, B — участок от 80 до 95%, C — остаток. Эти пороги являются рабочим ориентиром, а не универсальным законом.
Есть важная деталь на границе групп. Если одна крупная позиция переводит накопительную долю, например, с 76% сразу на 84%, бизнесу может быть логичнее оставить её в A, а не механически отправлять в B. Поэтому формулу нужно воспринимать как правило классификации, которое должно быть одинаковым и заранее зафиксированным, а не как абсолютную истину.
XYZ-анализ: среднее, стандартное отклонение и коэффициент вариации
XYZ требует ряда сопоставимых периодов. Например, продажи товара за 8 недель находятся в F2:M2. Среднее значение: =СРЗНАЧ(F2:M2). Стандартное отклонение для выборки: =СТАНДОТКЛОН.В(F2:M2). Коэффициент вариации CV показывает разброс относительно среднего: =ЕСЛИ(СРЗНАЧ(F2:M2)=0;"";СТАНДОТКЛОН.В(F2:M2)/СРЗНАЧ(F2:M2)). Результат оформляется как процент.
Для стартовой классификации можно принять X при CV до 10%, Y — от 10% до 25%, Z — выше 25%: =ЕСЛИ(N2="";"—";ЕСЛИ(N2<=10%;"X";ЕСЛИ(N2<=25%;"Y";"Z"))). Но границы нужно проверять на распределении собственного ассортимента. В сезонном бизнесе или при редких продажах слишком строгие пороги превратят почти весь каталог в Z и анализ потеряет смысл.
Нули требуют отдельного внимания. Если товар не продавался четыре недели из восьми, высокая вариативность может быть реальной характеристикой спроса, следствием отсутствия товара на складе или технической проблемой выгрузки. XYZ не умеет отличать эти причины. Перед расчётом проверьте out-of-stock: отсутствие продаж из-за отсутствия остатка нельзя автоматически трактовать как отсутствие спроса.
Практический пример с числами: от пяти товаров до групп AX–CZ
Предположим, пять товаров дали за период 1 000 000 ₽ выручки: товар A — 520 000 ₽, B — 260 000 ₽, C — 120 000 ₽, D — 70 000 ₽, E — 30 000 ₽. Их доли равны 52%, 26%, 12%, 7% и 3%, а накопительные доли — 52%, 78%, 90%, 97% и 100%. При правиле 80/95 позиции A и B попадают в класс A, C — в B, D и E — в C.
Теперь возьмём недельные продажи товара A: 98, 102, 101, 99, 103, 97, 100 и 100 единиц. Среднее равно 100. Стандартное отклонение выборки — примерно 2 единицы, CV около 2%, поэтому товар получает X. Для товара B ряд 40, 75, 30, 90, 20, 80, 35, 70 даст значительно больший CV и может попасть в Z. После объединения классов получатся, например, AX и AZ — одинаково важные по ABC, но требующие разной логики запаса.
Если AX продаётся в среднем по 100 единиц в неделю и поставка занимает две недели, базовая потребность на срок поставки — около 200 единиц до учёта страхового запаса. Для AZ простой расчёт 2 × среднее опаснее: нестабильность спроса выше, поэтому нужно дополнительно смотреть пики, срок поставки и цену дефицита. Именно здесь ABC/XYZ начинает влиять на деньги, а не остаётся красивой маркировкой.
Создать ABC/XYZ-анализ под свой ассортимент
Опишите товары, периоды и показатель — SmartXLSX подготовит XLSX со структурой, расчётами и итоговой матрицей.
Открыть инструмент →Как читать матрицу AX–CZ и связать её с запасами
AX обычно получает самый высокий приоритет контроля наличия: товар значим и предсказуем. AY тоже важен, но запас лучше пересматривать чаще. AZ требует анализа причин скачков — акции, сезонность, крупные разовые клиенты, маркетплейсы — и осторожного страхового запаса. BX/BY/BZ управляются похожим образом, но экономический риск ошибки ниже, чем у группы A.
Для CX разумно проверить стоимость администрирования: стабильность делает позицию удобной, но вклад невелик. CY и CZ стоит разбирать по назначению. Низкая выручка не означает, что товар нужно удалить: он может быть обязательным дополнением к ключевому продукту, поддерживать ассортиментную полноту или иметь высокую маржу. Поэтому рядом с группой полезно хранить маржинальность и роль товара.
Свяжите анализ с оборачиваемостью. ABC/XYZ объясняет значимость и стабильность, а показатель дней запаса показывает, сколько капитала фактически заморожено на складе. Для этого полезно перейти к соседнему руководству по оборачиваемости запасов: сочетание двух анализов быстрее выявляет одновременно важные, нестабильные и перезатаренные позиции.
Типичные ошибки, которые делают классификацию недостоверной
Первая ошибка — смешивать периоды разной длины: продажи за 7 дней нельзя без корректировки сравнивать с продажами за 31 день. Вторая — анализировать новый товар с двумя неделями истории наравне с SKU, который продаётся год. Третья — считать ABC по выручке, когда реальная задача связана с прибылью: товар с большим оборотом и почти нулевой маржой может ошибочно выглядеть главным.
Четвёртая ошибка — считать среднее процентов или коэффициентов вместо исходных данных. Пятая — игнорировать возвраты и отмены, если выгрузка маркетплейса включает их отдельно. Шестая — считать отсутствие продаж спросом в ноль, когда товара просто не было на складе. Седьмая — менять пороги каждый месяц так, чтобы результат выглядел удобнее: тогда группы нельзя сравнивать во времени.
Проверяйте контрольные суммы. Сумма долей ABC должна быть около 100%, последняя накопительная доля — 100%, число SKU в матрице должно совпадать с числом уникальных анализируемых SKU. Для XYZ отдельно посчитайте строки с пустым средним и недостаточной историей. Такие проверки занимают несколько минут и ловят ошибки раньше, чем по неверной матрице будет сделана закупка.
Как автоматизировать обновление и использовать анализ регулярно
Если анализ обновляется еженедельно, не копируйте старый файл и не переписывайте формулы. Храните исходные продажи в единой таблице, обновляйте выгрузку и пересчитывайте расчётные листы. Для больших выгрузок удобно использовать сводную таблицу или Power Query для агрегации по SKU и периодам, оставляя сами правила ABC/XYZ прозрачными на расчётном листе.
Полезно хранить дату расчёта и предыдущую группу товара. Тогда можно видеть миграции: AX → AY, BZ → AZ и другие изменения. Переход товара в более значимую или менее стабильную группу — повод проверить спрос, а не просто перекрасить ячейку. Для руководителя достаточно небольшой панели: выручка группы A, число AX/AZ, стоимость запасов по группам и список крупнейших изменений.
Если не хочется вручную собирать листы, формулы и оформление, в SmartXLSX можно описать ассортимент, периоды и нужный показатель обычными словами. Сервис подготовит структуру XLSX под задачу. Для дальнейшей работы полезно связать результат с анализом продаж и оборачиваемостью запасов — тогда классификация становится частью единой системы управления ассортиментом.
Частые вопросы
Сколько периодов нужно для XYZ-анализа?
Для оперативного анализа часто используют 8–12 недель или 6–12 месяцев. Главное — чтобы периоды были сопоставимыми и отражали реальный цикл спроса.
ABC лучше считать по выручке или валовой прибыли?
Выбирайте показатель под решение: выручку — для вклада в оборот, валовую прибыль — для экономической ценности. При сильно различающейся марже полезно сравнить оба варианта.
Какие границы использовать для X, Y и Z?
Можно начать с CV до 10% для X, 10–25% для Y и выше 25% для Z, но затем проверить распределение своего ассортимента и сезонность.
Что делать, если средние продажи равны нулю?
Не рассчитывайте CV делением на ноль. Пометьте позицию отдельно и проверьте историю, наличие на складе и корректность исходных данных.
Можно делать ABC/XYZ для Wildberries и Ozon?
Да. Нужна история по SKU и одинаковым периодам. Желательно заранее учесть возвраты, отмены и дни отсутствия товара на складе.
Как часто обновлять ABC/XYZ-анализ?
Для активного ассортимента обычно достаточно еженедельного или ежемесячного пересчёта. Частота должна соответствовать скорости изменения спроса и закупочному циклу.