Спрос по периодам · формула · пример Excel
XYZ-анализ товаров:
формула, пример и ошибки расчёта
XYZ-анализ показывает, насколько ровно меняется выбранный показатель товара по одинаковым периодам. Он помогает отделить стабильный ряд X от переменного Y и нерегулярного Z, но не определяет прибыльность, достаточный запас или будущий спрос сам по себе.
Редакция ДокиДепо · опубликовано 21 сентября 2026 · пример можно проверить в браузере
Вставьте продажи, выручку, маржу или количество за три и более сопоставимых периода. Бесплатный анализатор рассчитает среднее, коэффициент вариации, группы ABC/XYZ и подготовит CSV.
Провести XYZ-анализ онлайн →Что показывает XYZ-анализ
Для каждого товара берут ряд значений по периодам: например, продажи в штуках за шесть полных месяцев. Затем считают среднее значение, стандартное отклонение и коэффициент вариации. Коэффициент сравнивает разброс со средним, поэтому ряды разного масштаба можно оценивать по одной шкале.
| Группа | Учебная граница | Что можно утверждать | Что проверить дальше |
|---|---|---|---|
| X | CV ≤ 10% | Выбранный показатель менялся ровно | Дефицит, срок поставки и уровень наличия |
| Y | 10% < CV ≤ 25% | Колебания заметны, но умеренны | Сезонность, календарь, акции и цена |
| Z | CV > 25% | Ряд сильно менялся | Выбросы, отсутствие товара, запуск и заказные продажи |
Границы 10% и 25% — рабочий вариант для одного воспроизводимого расчёта. Их можно менять, но в сравнении двух запусков нужно сохранять одинаковые пороги, показатель и период.
Формула коэффициента вариации
σ — стандартное отклонение значений по выбранным периодам. Если среднее равно нулю, коэффициент не определяется: такой товар нужно вынести в отдельную группу, а не объявлять стабильным.
В анализаторе ДокиДепо выбранные периоды считаются полным анализируемым отрезком, поэтому используется стандартное отклонение по совокупности. Это соответствует функции СТАНДОТКЛОН.Г в русской версии Excel. Если вы рассматриваете строки как выборку из большей совокупности, методику нужно изменить и зафиксировать отдельно.
Как посчитать XYZ-анализ в Excel
- Одна строка — один товар. В первом столбце оставьте артикул, в следующих — одинаковые недели или месяцы.
- Проверьте единицу. Не смешивайте штуки, рубли и маржу в одном расчёте.
- Посчитайте среднее. Для значений B2:G2 используйте
=СРЗНАЧ(B2:G2). - Посчитайте CV. Используйте
=СТАНДОТКЛОН.Г(B2:G2)/СРЗНАЧ(B2:G2)*100. - Назначьте группу. Например, X до 10%, Y до 25%, всё выше — Z.
- Сохраните причины исключений. Отсутствие товара, неполный период и пропуск данных нельзя молча превращать в ноль.
Пример расчёта трёх товаров
| Товар | Значения за 6 месяцев | Среднее | CV | Группа |
|---|---|---|---|---|
| Кофе | 100; 104; 98; 103; 101; 102 | 101,33 | 1,95% | X |
| Чай | 80; 100; 70; 105; 85; 110 | 91,67 | 15,64% | Y |
| Подарочный набор | 0; 120; 10; 95; 0; 140 | 60,83 | 97,07% | Z |
Из результата следует только характер ряда в выбранном окне. Кофе был ровнее, а подарочный набор — гораздо изменчивее. Чтобы решить, сколько закупать, нужны как минимум фактический остаток, товар в пути, срок поставки, минимальный и целевой уровни.
Пять ошибок, которые меняют группу
- Ноль вместо отсутствия данных. Пустая ячейка означает «неизвестно». Ноль допустим, только если товар был доступен и продаж действительно не было.
- Дефицит внутри периода. Продажи ограничены наличием. Нулевая или низкая продажа при пустой полке не измеряет исходный спрос.
- Периоды разной длины. Полный месяц и половина месяца несопоставимы без нормализации.
- Смешанные показатели. Нельзя сравнивать выручку одного товара с количеством другого.
- Механическое решение по букве. Группа Z не означает «удалить товар», а X не гарантирует, что запас достаточен.
XYZ и ABC отвечают на разные вопросы
| Метод | Вопрос | Расчёт |
|---|---|---|
| ABC | Каков вклад товара в общий результат? | Доля и накопленная доля после сортировки |
| XYZ | Насколько меняется показатель по периодам? | Коэффициент вариации каждого ряда |
| ABC/XYZ | Как совместить вклад и изменчивость? | Матрица AX, AY, AZ, BX, BY, BZ, CX, CY, CZ |
Совмещённая матрица полезна как очередь вопросов. Например, у AZ большой вклад и сильные колебания: сначала проверьте дефицит, акции и сезонность. У CX вклад меньше и ряд ровнее: сравните затраты на обслуживание позиции с её ролью в ассортименте.
Что делать после расчёта
Частые вопросы
Что показывает XYZ-анализ?
Изменчивость выбранного показателя товара по сопоставимым периодам. Он не показывает прибыльность и не заменяет прогноз, проверку дефицита или расчёт запаса.
Какая формула нужна в Excel?
Для полного выбранного ряда B2:G2: =СТАНДОТКЛОН.Г(B2:G2)/СРЗНАЧ(B2:G2)*100. Если среднее равно нулю, CV не определяется.
Сколько периодов брать?
Минимум три позволяют выполнить расчёт, но этого мало для уверенного вывода о сезонности. Берите достаточно полных сопоставимых периодов для своей задачи и явно сохраняйте окно анализа.
Можно ли считать по выручке?
Можно, если один и тот же показатель используется для всех товаров и периодов. Цена и скидки тогда влияют на ряд. Для физической изменчивости спроса чаще проверяют количество отдельно.
Почему товар группы Z нельзя сразу удалить?
Сильные колебания могут возникнуть из-за дефицита, запуска, акции, сезонности, заказной модели или ошибки данных. Сначала нужно проверить причину.
Источники метода
- NIST: коэффициент вариации как отношение стандартного отклонения к среднему.
- Microsoft: статистические функции Excel и стандартное отклонение по совокупности.
Страница объясняет воспроизводимый расчёт по истории и не обещает прогноз спроса. Решение о закупке требует данных о наличии, сроке поставки, ограничениях поставщика, сезонности и допустимом риске дефицита.