Спрос по периодам · формула · пример Excel
XYZ-анализ товаров:
формула, пример и ошибки расчёта
XYZ-анализ показывает, насколько ровно меняется выбранный показатель товара по одинаковым периодам. Он помогает отделить стабильный ряд X от переменного Y и нерегулярного Z, но не определяет прибыльность, достаточный запас или будущий спрос сам по себе.
В этой инструкции
Редакция ДокиДепо · обновлено 27 сентября 2026 · один ряд и все шаги расчёта
Один товар можно разобрать прямо ниже. Для сравнения ассортимента откройте анализатор: введите два или больше товаров за три и более сопоставимых периода. Используйте неотрицательное количество или денежный показатель; процентную маржу и отрицательные значения сюда переносить нельзя. Бесплатный анализатор рассчитает среднее, коэффициент вариации, группы ABC/XYZ и подготовит CSV.
Провести XYZ-анализ онлайн →Один товар · от 3 до 24 периодов · Excel
Разберите свой ряд по шагам
Проверьте, откуда берутся среднее, разброс и буква XYZ. Пустое значение останется пропуском. При дефиците, неполном периоде или непроверенных условиях группа не назначается, даже если числа позволяют посчитать разброс.
Введите один товар или откройте учебный ряд кофе.
Вклад каждого периода
Квадраты отклонений суммируются и делятся на число всех периодов n. Корень из этой величины — стандартное отклонение. Затем оно делится на среднее и умножается на 100%. На экране и в файле до 6 десятичных знаков; группа определяется до округления. При среднем около нуля CV чувствителен к малым изменениям.
Файл содержит исходный ввод, условия, расчёт и причины отсутствия группы. Это снимок без формул. После изменения данных выполните расчёт заново. Данные формы обрабатываются локально и не сохраняются после закрытия страницы.
Что показывает 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 |
Из результата следует только характер ряда в выбранном окне. Кофе был ровнее, а подарочный набор — гораздо изменчивее. Чтобы решить, сколько закупать, нужны как минимум фактический остаток, товар в пути, срок поставки, минимальный и целевой уровни.
Формула Excel с проверкой шести периодов
Для примера с шестью месяцами используйте вариант, который отличает неполные данные от ряда с нулевым средним:
=ЕСЛИ(СЧЁТ(B2:G2)<>6;"Проверьте периоды";ЕСЛИ(МИН(B2:G2)<0;"Отрицательные значения";ЕСЛИ(СРЗНАЧ(B2:G2)=0;"Нет базы";СТАНДОТКЛОН.Г(B2:G2)/СРЗНАЧ(B2:G2)*100)))
Шесть подтверждённых нулей дадут «Нет базы». Пять чисел и пустая ячейка — «Проверьте периоды». Шесть одинаковых положительных значений — 0%. Число 6 относится именно к этому окну: при другом количестве периодов измените и диапазон, и проверку количества. Текстовые числа сначала приведите к числовому типу и проверьте причины пропуска. СТАНДОТКЛОН.Г игнорирует пустые ячейки и текст в диапазоне, поэтому отдельная проверка полноты обязательна. Эта формула не обнаруживает дефицит, акции и неполный месяц: отметьте их до назначения группы.
Пять ошибок, которые меняют группу
- Ноль вместо отсутствия данных. Пустая ячейка означает «неизвестно». Ноль допустим, только если товар был доступен и продаж действительно не было.
- Дефицит внутри периода. Продажи ограничены наличием. Нулевая или низкая продажа при пустой полке не измеряет исходный спрос.
- Периоды разной длины. Полный месяц и половина месяца несопоставимы без нормализации.
- Смешанные показатели. Нельзя сравнивать выручку одного товара с количеством другого.
- Механическое решение по букве. Группа 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 и стандартное отклонение по совокупности.
Страница объясняет воспроизводимый расчёт по истории и не обещает прогноз спроса. Решение о закупке требует данных о наличии, сроке поставки, ограничениях поставщика, сезонности и допустимом риске дефицита.