Как вычислить Z‑оценку в Excel

Что такое Z‑оценка

Z‑оценка (z‑score) — это числовая мера, показывающая, насколько далеко наблюдение находится от среднего значения выборки в единицах стандартного отклонения. Простая формула — одна строка, но важно понимать, какие данные и какую версию стандартного отклонения вы используете.

Краткое определение: Z‑оценка — это количество стандартных отклонений, на которое значение отклоняется вверх или вниз от среднего.

Формула и пояснения

Формула:

Z = (x - µ) / σ

Где:

• Z — Z‑оценка.
• x — конкретное значение (точка данных).
• µ — среднее арифметическое набора данных.
• σ — стандартное отклонение набора данных.

Интерпретация:

• Z = 0: значение равно среднему.
• Z > 0: значение выше среднего.
• Z < 0: значение ниже среднего.
• |Z| > 2 обычно считается существенно отличающимся, но пороги зависят от контекста.

Важно: для σ можно использовать оценку по выборке или по совокупности. В Excel существуют разные функции:

• =AVERAGE(range) — среднее.
• =STDEVPA(range) — стандартное отклонение (учитывает логические и текстовые значения как числа при их наличии).
• =STDEV.P(range) — стандартное отклонение совокупности (современная функция для всей популяции).
• =STDEV.S(range) — стандартное отклонение выборки (оценка по выборке).

Выбор функции влияет на результат; проверьте, работаете ли вы с полной популяцией данных или с выборкой.

Пошаговый пример в Excel

У нас есть продажи фруктов в диапазоне B2:B7, названия — в A2:A7. Мы считаем Z‑оценку для каждой строки и записываем результаты в столбец C.

1. Посчитайте среднее: в ячейке B9 введите:

   =AVERAGE(B2:B7)

   В примере среднее равно 173.333333.

2. Посчитайте стандартное отклонение: в ячейке B10 введите (в примере использовали STDEVPA):

   =STDEVPA(B2:B7)

   В примере σ = 51.5126737.

3. Вычислите Z‑оценку для каждой строки. Для первой строки (B2 = 122) введите в C2:

   =(B2-B9)/B10

   Результат: примерно -0.9965815.

4. Скопируйте формулу вниз по столбцу C для всех значений B3:B7. Окончательный результат может выглядеть так:
   • -0.9965815
   • 1.2359418
   • -0.9188677
   • 0.96416402
   • 0.77003704
   • -1.0547566

Быстрая шпаргалка по формулам (чек‑лист)

• Среднее: =AVERAGE(range)
• Стандартное отклонение (популяция): =STDEV.P(range) или =STDEVPA(range)
• Стандартное отклонение (выборка): =STDEV.S(range)
• Z‑оценка для x в B2, среднее в B9, σ в B10: =(B2-B9)/B10
• Однострочный вариант без промежуточных ячеек (динамическое вычисление): =(B2-AVERAGE($B$2:$B$7))/STDEV.P($B$2:$B$7)

Совет: фиксируйте диапазоны ($B$2:$B$7), чтобы корректно копировать формулы.

Когда Z‑оценка не подходит (ограничения и контрпримеры)

• Данные не распределены нормально: Z‑оценка основана на стандартном отклонении и более информативна для приближённо нормального распределения. Для сильно скошенных распределений лучше использовать ранговые методы (например, перцентиль).
• Небольшая выборка: при малом количестве наблюдений оценка σ может быть нестабильной; предпочтительнее STDEV.S и осторожность в интерпретации.
• Категориальные или дискретные шкалы: Z‑оценка неприменима к номинальным данным.

Примеры, когда Z‑оценка вводит в заблуждение:

• Много выбросов: один выброс существенно увеличит σ и «сожмёт» другие Z‑оценки.
• Нелинейные признаки: нормализация через Z‑оценку не исправит нелинейные зависимости.

Альтернативы и когда их использовать

• Мин‑макс нормализация: приводит данные в диапазон 0–1, полезно для алгоритмов ML, чувствительных к масштабу.
• Логарифмическое преобразование: уменьшает влияние положительно скошенных данных.
• Перцентиль или ранговая нормировка: полезно для устойчивости к выбросам.

Выбор зависит от задачи: для статистических тестов и стандартных z‑тестов используют Z‑оценку; для подготовки данных в ML часто применяют стандартизацию в сочетании с другими методами.

Практическое руководство для ролей (кто что делает)

• Аналитик данных:
  • Проверяет распределение (гистограмма, Q‑Q график).
  • Решает, использовать ли STDEV.P или STDEV.S.
  • Документирует диапазон и метод.
• BI‑специалист:
  • Встраивает вычисления в шаблон отчёта.
  • Добавляет условное форматирование для |Z| > порога.
• Разработчик ML‑пайплайна:
  • Выносит стандартизацию в предобработку.
  • Сохраняет параметры µ и σ для последующего применения к новым данным.

Критерии приёмки и тесты

Критерии приёмки для автоматизированной реализации в шаблоне Excel:

• Формулы корректно используют абсолютные ссылки для диапазона (например, $B$2:$B$7).
• Результаты для контрольного примера совпадают с ручным расчётом (погрешность < 1e‑6).
• При изменении входных чисел среднее и σ пересчитываются автоматически.

Тестовые сценарии:

1. Нормальные данные без выбросов — результаты Z разумны (около ±3 диапазон).
2. Данные с одним явным выбросом — проверьте изменение σ и Z для остальных.
3. Малый набор (n=3) — сравните STDEV.P и STDEV.S; документируйте использованную функцию.

Мини‑методология для массовой обработки

1. Добавьте столбцы: Raw, Mean, StdDev, Z.
2. В ячейку Mean запишите =AVERAGE($B$2:$B$N).
3. В StdDev запишите =STDEV.P($B$2:$B$N) или =STDEV.S(...) при необходимости.
4. В Z для каждой строки: =(B2-$Mean)/$StdDev и скопируйте вниз.
5. Добавьте фильтр и условное форматирование для |Z|>2.

Советы по совместимости и миграции

• Старые книги Excel могут использовать устаревшие имена функций (STDEVP). Проверяйте справку Excel вашей версии.
• При переносе в Google Sheets используйте те же формулы: AVERAGE и STDEV.P/ STDEV.S поддерживаются.

Диаграмма выбора метода нормализации

flowchart TD
  A[Нужно нормализовать признаки?] --> B{Данные числовые и непрерывные}
  B -->|Да| C{Распределение примерно нормальное?}
  B -->|Нет| D[Используйте ранговую/категориальную обработку]
  C -->|Да| E[Z‑оценка 'стандартизация']
  C -->|Нет| F[Лог/мин‑макс/перцентиль]
  E --> G[Сохраните µ и σ для продакшна]
  F --> G

Часто задаваемые вопросы

Нужно ли фиксировать диапазоны в формулах при копировании?

Да. Используйте абсолютные ссылки ($B$2:$B$7), чтобы при копировании формула ссылалась на правильный диапазон.

Какой порог считать «аномалией» по Z‑оценке?

Часто используют |Z| > 2 для подозрительных значений и |Z| > 3 для явных выбросов, но порог выбирают в зависимости от предметной области.

Какой стандарт отклонения выбрать — выборки или популяции?

Если у вас данные представляют собой всю исследуемую совокупность, используйте STDEV.P; если это выборка, используйте STDEV.S.

Короткое резюме

Z‑оценка — простой и мощный способ стандартизации числовых данных в Excel. Вычисляется по формуле (x - µ) / σ; в Excel удобно использовать AVERAGE и одну из функций стандартного отклонения. Проверяйте распределение данных и выбирайте подходящий метод (STDEV.P, STDEV.S или STDEVPA). Для массовой обработки автоматизируйте расчёт и сохраняйте параметры µ/σ для воспроизводимости.

Важно: выбирайте метод стандартного отклонения с учётом того, работаете ли вы с выборкой или популяцией.