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

XYZ-анализ помогает понять, насколько стабильно продаются товары и какие позиции можно прогнозировать, а какие ведут себя хаотично. Это полезно для закупок, планирования остатков и управления ассортиментом. Сделать XYZ-анализ можно вручную в Excel, но быстрее и удобнее — через инструмент Utily.

Попробовать инструмент Utily: [XYZ-анализ]

Короткий ответ

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

Основная инструкция

Что такое XYZ-анализ простыми словами

Если ABC-анализ отвечает на вопрос, какие товары дают основной вклад в выручку или прибыль, то XYZ-анализ отвечает на другой вопрос:

насколько стабильно эти товары продаются во времени

Это значит, что XYZ-анализ помогает увидеть:

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

Обычно товары делят так:

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

Зачем он нужен бизнесу

XYZ-анализ полезен, если вы хотите:

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

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

Какие данные нужны для XYZ-анализа

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

Например:

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

Пример структуры

Товар	Янв	Фев	Мар	Апр	Май	Июн
Товар A	120	118	123	121	119	122
Товар B	90	130	70	115	85	140
Товар C	0	40	0	90	10	0

Важно, чтобы периоды были одинаковыми по длине и логике.

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

Главный показатель здесь — коэффициент вариации.

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

Формула выглядит так:

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

Где:

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

Чем ниже коэффициент вариации, тем стабильнее товар.

Как делят товары на X, Y и Z

Часто используют такую логику:

• X — коэффициент вариации до 10%
• Y — от 10% до 25%
• Z — выше 25%

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

Что означают группы

X

Товары с очень стабильным спросом.
Их легче прогнозировать, по ним проще планировать закупки и поддерживать остатки.

Y

Товары с умеренной нестабильностью.
Они могут иметь сезонность, всплески спроса или колебания, но в целом ещё управляемы.

Z

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

Как сделать XYZ-анализ в Excel пошагово

Шаг 1. Подготовьте данные по периодам

Соберите продажи по каждому товару в одинаковых временных интервалах.

Например:

• 6 месяцев;
• 12 месяцев;
• 8 недель.

Чем качественнее и чище данные, тем полезнее результат.

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

В Excel используйте формулу:

=AVERAGE(B2:G2)

Где B2:G2 — продажи товара по периодам.

Это даст средний объём продаж товара.

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

В Excel обычно используют:

=STDEV.P(B2:G2)

или, в некоторых версиях, близкую формулу для стандартного отклонения.

Это покажет, насколько продажи колеблются относительно среднего.

Шаг 4. Посчитайте коэффициент вариации

Формула в Excel:

=H2/I2*100

Где:

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

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

Шаг 5. Присвойте группу X, Y или Z

Можно использовать такую формулу:

=IF(J2<=10,"X",IF(J2<=25,"Y","Z"))

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

После этого каждый товар получит свою группу.

Пример

Возьмём 3 товара с продажами за 6 месяцев.

Исходные данные

Товар	Янв	Фев	Мар	Апр	Май	Июн
Товар A	100	102	98	101	99	100
Товар B	70	95	80	110	85	100
Товар C	0	60	10	90	0	75

Шаг 1. Считаем среднее

• Товар A: среднее = 100
• Товар B: среднее = 90
• Товар C: среднее = 39,2

Шаг 2. Считаем разброс

У товара A колебания минимальные.
У товара B — умеренные.
У товара C — резкие и нестабильные.

Шаг 3. Считаем коэффициент вариации

Допустим, после расчёта получили:

Товар	Среднее	Стандартное отклонение	Коэффициент вариации	Группа
Товар A	100	1,4	1,4%	X
Товар B	90	14,7	16,3%	Y
Товар C	39,2	38,0	96,9%	Z

Что это значит

• Товар A продаётся очень стабильно → группа X
• Товар B продаётся с умеренными колебаниями → группа Y
• Товар C ведёт себя хаотично → группа Z

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

Товары группы X

По ним проще:

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

Это обычно самые удобные товары с точки зрения управления.

Товары группы Y

По ним полезно:

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

Товары группы Z

По ним обычно нужно:

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

Когда XYZ-анализ особенно полезен

Он особенно нужен, если вы:

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

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

Типичные ошибки

1. Брать слишком мало периодов

Если данных мало, анализ становится менее надёжным. Лучше использовать хотя бы несколько одинаковых периодов, а не 2–3 точки.

2. Смешивать разные типы периодов

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

3. Анализировать грязные данные

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

4. Делать вывод только по группе

Товар из группы Z — не всегда “плохой товар”. Иногда он сезонный, иногда акционный, иногда просто нишевой. XYZ-анализ — это инструмент интерпретации, а не приговор.

5. Использовать XYZ отдельно от других анализов

Сам по себе XYZ полезен, но особенно сильный эффект даёт в связке с ABC-анализом. Тогда вы видите не только стабильность, но и значимость позиции.

FAQ

Что важнее: XYZ или ABC-анализ?

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

Можно ли делать XYZ-анализ не по товарам, а по клиентам?

Теоретически да, если у вас есть продажи по периодам и вам важна стабильность поведения. Но чаще его используют именно для товаров и ассортимента.

Какой порог для X, Y и Z правильный?

Часто используют 10% и 25% как базовые границы, но в некоторых бизнесах их адаптируют под реальность.

Обязательно ли считать коэффициент вариации в Excel вручную?

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

Если товар новый и данных мало, XYZ-анализ сработает?

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

Похожие задачи

После XYZ-анализа обычно полезно ещё:

• сделать ABC-анализ товаров, чтобы понять их вклад в выручку или прибыль;
• собрать ABC/XYZ-анализ ассортимента, чтобы одновременно видеть и значимость, и стабильность.

Если хотите быстро получить группы X, Y и Z без ручной сборки формул и проверки Excel-таблицы, удобнее сделать это в Utily.

Сделать анализ в Utily: [XYZ-анализ]