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

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

Все дашборды в этой статье построены с помощью FineBI

Что такое XYZ-анализ продаж и зачем он нужен

XYZ-анализ продаж — это метод классификации товаров по степени стабильности спроса. В его основе лежит расчет колебаний продаж за выбранный период. Чем ниже разброс значений, тем выше предсказуемость товара и тем проще управлять его запасом.

Если говорить прикладным языком:

• X — товары со стабильным спросом
• Y — товары с умеренными колебаниями
• Z — товары с сильной нестабильностью спроса

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

Когда метод помогает оценить стабильность спроса и выявить колебания продаж

XYZ-анализ особенно полезен в следующих сценариях:

• при управлении складскими остатками;
• при планировании закупок на месяц, квартал или сезон;
• при настройке минимальных и страховых запасов;
• при выявлении товаров с нерегулярным спросом;
• при оценке надежности ассортимента для промо и регулярных поставок.

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

Чем XYZ-анализ отличается от ABC-анализа и когда их стоит использовать вместе

ABC-анализ показывает вклад товара в выручку, маржу или объем продаж, а XYZ-анализ — стабильность спроса. Это два разных измерения.

Например:

• товар может быть A-категории по выручке, но Z-категории по стабильности;
• товар может приносить немного денег, но продаваться ровно и предсказуемо — тогда он попадет в X.

Совместное использование ABC и XYZ особенно ценно, потому что позволяет получить более глубокую сегментацию:

• AX — ключевые и стабильные товары;
• AZ — важные, но нестабильные позиции;
• CX — второстепенные, но предсказуемые;
• CZ — наименее приоритетные и непредсказуемые.

Именно такая матрица часто становится основой для решений по запасам, промо-активности и пересмотру ассортимента.

Какие решения можно принять по итогам анализа

После XYZ-анализа продаж можно принимать вполне конкретные управленческие решения:

• установить разные правила пополнения запасов для групп X, Y и Z;
• определить, какие товары стоит прогнозировать автоматически, а какие — вручную;
• сократить складской запас по нестабильным позициям;
• выделить товары, требующие отдельного анализа сезонности;
• скорректировать ассортиментную матрицу;
• пересмотреть частоту заказов поставщикам.

Какие данные подготовить в Excel перед началом

Чтобы xyz анализ продаж в Excel дал корректный результат, важно начать не с формул, а с подготовки данных. Большинство ошибок возникает именно на этом этапе: неполные периоды, смешанные интервалы, дубли товаров и некорректные числовые значения.

Какие показатели понадобятся: товары, периоды и объемы продаж

Минимальный набор данных для анализа:

• товар или SKU — уникальный идентификатор позиции;
• периоды продаж — например, месяцы, недели или кварталы;
• объем продаж — в штуках, упаковках, литрах или иной единой единице измерения.

При необходимости можно добавить:

• категорию товара;
• бренд;
• склад;
• регион;
• канал продаж.

Но для базового расчета достаточно трех сущностей: что продается, когда продается и в каком объеме.

Как оформить таблицу, чтобы формулы считались без ошибок

Оптимальный формат для Excel — таблица, где:

• в строках находятся товары;
• в столбцах — одинаковые периоды;
• в ячейках — объемы продаж по каждому периоду.

Например:

Товар	Янв	Фев	Мар	Апр	Май	Июн
Товар 1	120	115	118	122	119	121
Товар 2	80	95	60	110	75	130

Дополнительно рядом удобно создать расчетные столбцы:

• среднее значение;
• стандартное отклонение;
• коэффициент вариации;
• группа XYZ.

На что обратить внимание при очистке и проверке исходных данных

Перед расчетом проверьте:

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

Если один товар имеет данные за 12 месяцев, а другой только за 4, сравнение будет некорректным. Для xyz анализа продаж важна единая временная база.

Как сделать XYZ-анализ продаж в Excel: пошаговая инструкция

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

Key Metrics (KPIs)

Перед расчетом полезно зафиксировать ключевые показатели, на которых строится xyz анализ продаж:

• Объем продаж по периоду — количество проданных единиц товара за неделю, месяц или квартал.
• Среднее значение продаж — средний объем продаж товара за выбранные периоды.
• Стандартное отклонение — показывает, насколько сильно продажи отклоняются от среднего значения.
• Коэффициент вариации — отношение стандартного отклонения к среднему, обычно в процентах; главный показатель для XYZ-классификации.
• Категория X — минимальные колебания спроса, высокая предсказуемость.
• Категория Y — умеренные колебания, возможна сезонность или зависимость от акций.
• Категория Z — сильные колебания и низкая предсказуемость спроса.

Шаг 1. Соберите и структурируйте данные по периодам

Сначала определите, какой интервал подходит для вашей задачи:

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

Главное правило: для всех товаров должны использоваться одинаковые периоды. Если один SKU анализируется по неделям, а другой по месяцам, коэффициент вариации теряет смысл.

Практический совет: если ассортимент широкий, начните с 6–12 одинаковых периодов. Этого достаточно, чтобы увидеть базовую стабильность спроса.

Шаг 2. Рассчитайте среднее значение продаж

Следующий этап — вычислить средний объем продаж по каждому товару.

В Excel для этого используется функция:

=СРЗНАЧ(B2:G2)

Если у вас данные по месяцам расположены в столбцах B:G, формула посчитает среднее значение по строке.

Чтобы быстро применить расчет ко всем товарам:

1. введите формулу для первой строки;
2. нажмите на маркер заполнения в правом нижнем углу ячейки;
3. протяните формулу вниз по всему списку.

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

Шаг 3. Найдите стандартное отклонение

Теперь нужно определить, насколько сильно колеблются продажи относительно среднего. Для этого используется стандартное отклонение.

В Excel чаще всего применяют формулу:

=СТАНДОТКЛОН.P(B2:G2)

или, в зависимости от версии Excel:

=СТАНДОТКЛОН(B2:G2)

Если вы анализируете полный набор данных по периодам, чаще уместно использовать вариант с .P. Если работаете с выборкой, может использоваться .S.

Чем выше стандартное отклонение, тем менее стабилен спрос по товару.

Шаг 4. Определите коэффициент вариации и присвойте категории X, Y, Z

Коэффициент вариации — ключевой показатель для xyz анализа продаж. Он рассчитывается как:

Коэффициент вариации = стандартное отклонение / среднее значение × 100%

В Excel формула может выглядеть так:

=I2/H2*100

Где:

• H2 — среднее значение;
• I2 — стандартное отклонение.

Далее нужно присвоить товару категорию. Часто используют такие пороги:

• X — до 10%
• Y — от 10% до 25%
• Z — выше 25%

Формула для автоматической классификации:

=ЕСЛИ(J2<=10;"X";ЕСЛИ(J2<=25;"Y";"Z"))

Где J2 — коэффициент вариации.

Важно: пороги не являются универсальными для любой отрасли. В рознице, дистрибуции, FMCG, B2B-поставках и e-commerce допустимые границы могут отличаться. Но приведенная схема — хороший базовый стандарт для старта.

Пошаговый пример расчета с формулами

Теперь разберем, как может выглядеть xyz анализ продаж в Excel на практике.

Пример таблицы для XYZ-анализа

Ниже пример структуры таблицы:

Товар	Янв	Фев	Мар	Апр	Май	Июн	Среднее	Станд. откл.	Коэфф. вариации, %	Группа
Товар A	100	102	98	101	99	100	100,0	1,41	1,41	X
Товар B	80	95	85	110	90	100	93,3	10,80	11,58	Y
Товар C	30	120	15	140	20	90	69,2	52,81	76,32	Z

Такой формат удобен тем, что все ключевые расчеты находятся в одной строке по каждому товару.

Формулы для среднего, отклонения и коэффициента вариации

Предположим, продажи находятся в диапазоне B2:G2.

Среднее:

=СРЗНАЧ(B2:G2)

Стандартное отклонение:

=СТАНДОТКЛОН.P(B2:G2)

Коэффициент вариации:

=I2/H2*100

Категория XYZ:

=ЕСЛИ(J2<=10;"X";ЕСЛИ(J2<=25;"Y";"Z"))

Если хотите избежать ошибок деления на ноль, используйте более безопасный вариант:

=ЕСЛИ(H2=0;"";I2/H2*100)

А для категории:

=ЕСЛИ(J2="";"";ЕСЛИ(J2<=10;"X";ЕСЛИ(J2<=25;"Y";"Z")))

Как проверить, что результаты считаются корректно

Вот несколько быстрых способов верификации:

• товар с почти одинаковыми продажами по всем периодам должен попасть в X;
• товар с заметными, но не экстремальными колебаниями — в Y;
• товар с резкими скачками — в Z;
• если среднее очень мало, коэффициент вариации может быть завышен — это нормально, но требует интерпретации с учетом бизнеса;
• если формула возвращает ошибку, проверьте пустые ячейки, текстовые значения и нулевое среднее.

Как интерпретировать полученные группы

Интерпретация категорий должна быть привязана к операционным решениям.

Группа X

• стабильный спрос;
• высокая предсказуемость;
• подходит для регулярного пополнения;
• можно держать более точные нормативы запаса.

Группа Y

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

Группа Z

• нерегулярный или трудно прогнозируемый спрос;
• высокий риск как излишков, так и дефицита;
• стоит осторожнее формировать запас;
• часто нужны ручная проверка, заказ под клиента или дополнительная сегментация.

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

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

Ошибки при выборе периодов и неполных данных

Самые частые проблемы:

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

Если товар вышел в продажу только три месяца назад, сравнивать его с позициями, имеющими историю за год, нужно отдельно и осторожно.

Почему не стоит сравнивать сезонные и несезонные товары без поправок

Сезонные товары по определению могут показывать высокую вариативность. Это не всегда означает, что товар «плохой» или неуправляемый. Иногда это отражение естественного цикла спроса.

Поэтому практический подход такой:

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

Как упростить анализ с помощью сортировки, фильтров и условного форматирования

Чтобы сделать Excel-таблицу удобной для работы, используйте:

• сортировку по коэффициенту вариации от меньшего к большему;
• фильтры по категориям X, Y, Z;
• условное форматирование для цветовой подсветки групп;
• сводные таблицы для анализа по категориям, брендам и складам;
• закрепление областей для больших таблиц.

Что делать после XYZ-анализа

Ценность анализа проявляется не в самих буквах X, Y и Z, а в действиях, которые следуют после них.

Как использовать результаты в планировании запасов и продаж

После классификации можно выстроить разные правила управления:

• для X — автоматическое пополнение и точные нормативы;
• для Y — учет сезонности, маркетинговых акций и более частый пересмотр прогноза;
• для Z — ограничение складского запаса, заказ под потребность или ручное согласование.

Это помогает снизить одновременно две проблемы: замороженные остатки и упущенные продажи.

Когда стоит повторять анализ и обновлять данные

XYZ-анализ — не разовая задача. Его нужно пересчитывать регулярно, потому что структура спроса меняется.

Практические рекомендации:

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

Как объединить результаты с другими методами сегментации

Максимальный эффект xyz анализ продаж дает в связке с другими методами:

• ABC-анализом — для оценки важности товара по выручке или марже;
• анализом оборачиваемости — для контроля запасов;
• RFM или клиентской аналитикой — если нужно связать поведение покупателей и ассортимент;
• прогнозированием спроса — для построения более точных моделей пополнения.

Лучшие практики внедрения XYZ-анализа в компании

Если смотреть на задачу как консультант, важна не только формула, но и процесс внедрения. Ниже — рекомендации, которые дают реальный эффект.

1. Зафиксируйте единые правила расчета

Сразу определите:

• какой период используется;
• какие товары входят в анализ;
• в каких единицах измеряется спрос;
• какие пороги применяются для X, Y и Z.

Без единых правил разные подразделения будут получать разные результаты по одним и тем же товарам.

2. Разделите ассортимент на однородные группы

Не смешивайте в одном массиве:

• сезонные и несезонные товары;
• новые и зрелые SKU;
• редкие проектные продажи и регулярный спрос;
• разные каналы с несопоставимой частотой заказов.

Это повышает качество интерпретации и снижает риск ошибочных решений.

3. Встройте результаты в операционные регламенты

XYZ-анализ полезен только тогда, когда по каждой группе есть понятное действие:

• какая частота заказа;
• какой страховой запас;
• кто согласует пополнение;
• какие товары требуют ручного прогноза.

Иначе классификация останется просто отчетом.

4. Автоматизируйте обновление данных

Если расчет ведется вручную в Excel, команда быстро столкнется с ограничениями:

• много времени на обновление;
• высокий риск ошибок в формулах;
• сложность масштабирования по складам, регионам и категориям;
• отсутствие единой версии отчета.

Как упростить XYZ-анализ продаж с помощью FineBI

Когда ассортимент растет, а анализ нужно обновлять регулярно, ручной расчет в Excel становится узким местом. Сделать такую модель вручную сложно; используйте FineBI, чтобы задействовать готовые шаблоны и автоматизировать весь этот процесс.

FineBI помогает:

• автоматически загружать данные из учетных систем;
• рассчитывать метрики без ручного копирования формул;
• строить дашборды по группам X, Y, Z;
• объединять XYZ- и ABC-анализ в одной модели;
• отслеживать динамику категорий по складам, филиалам и менеджерам;
• быстро делиться результатами с руководством.

Получите готовые шаблоны дашбордов в Fine Gallery

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