Как посчитать Z‑Score в Excel

Что такое Z‑Score

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

Краткое определение терминов:

• x — значение, которое требуется стандартизировать.
• µ — среднее арифметическое набора данных.
• σ — стандартное отклонение набора данных.

Формула: Z = (x - µ) / σ

Важно: используйте STDEV.S если у вас выборка, STDEV.P если данные представляют всю популяцию. STDEVPA учитывает логические значения и текстовые представления чисел.

Быстрый рецепт в Excel

1. Посчитайте среднее: =AVERAGE(диапазон)
2. Посчитайте стандартное отклонение: =STDEV.S(диапазон) или =STDEV.P(диапазон) или =STDEVPA(диапазон)
3. Примените формулу Z: =(ячейка - ячейкасосредним) / ячейкасостд

Альтернатива: функция STANDARDIZE возвращает Z‑Score сразу: =STANDARDIZE(x, mean, stdev)

Примеры формул:

=AVERAGE(B2:B7)
=STDEV.S(B2:B7)
=(B2-$B$9)/$B$10
=STANDARDIZE(B2,$B$9,$B$10)

(В формулах используйте абсолютные ссылки на ячейки со средним и стд, чтобы протянуть формулу вниз.)

Пошаговый пример в таблице

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

1. Посчитайте среднее в B9: =AVERAGE(B2:B7).
2. Посчитайте стд в B10: =STDEV.S(B2:B7) или =STDEV.P(B2:B7) (см. рекомендации ниже).
3. В C2 вставьте: =(B2-$B$9)/$B$10 и протяните вниз.

На нашем примере среднее равно 173.333333, стандартное отклонение примерно 51.512674. Z‑Score для значения 122 (апельсины) будет (122 - 173.333333) / 51.512674 ≈ -0.9966.

Когда использовать STDEV.S, STDEV.P или STDEVPA

• STDEV.S — используйте, если ваша таблица — выборка (часть большой популяции).
• STDEV.P — если у вас данные всей популяции (включены все элементы).
• STDEVPA — учитывает логические значения (TRUE/FALSE) и текстовые представления чисел.

Решающее правило: если вы не уверены и данные — наблюдения из выборки, обычно берут STDEV.S.

flowchart TD
  A[Есть все элементы популяции?] -->|Да| B[Использовать STDEV.P]
  A -->|Нет или не уверены| C[Использовать STDEV.S]
  C --> D{В данных есть логические/текстовые значения?}
  D -->|Да| E[Рассмотреть STDEVPA с предварительной очисткой]
  D -->|Нет| F[STDEV.S]

Автоматизация и полезные приёмы

• Функция STANDARDIZE: =STANDARDIZE(x, mean, stdev) — возвращает Z‑Score одним вызовом.
• Таблицы Excel (Ctrl+T): используйте структурированные ссылки, чтобы формулы автоматически применялись к новым строкам.
• Power Query: при больших наборах данных трансформируйте и нормализуйте в Power Query, затем выгружайте в лист.
• XLOOKUP/INDEX‑MATCH: находите строки по условиям и затем рассчитывайте Z‑Score для найденных значений.
• Массовое применение: в новых версиях Excel можно использовать динамические массивы и формулу типа =STANDARDIZE(B2:B7, AVERAGE(B2:B7), STDEV.S(B2:B7)) для получения массива Z‑оценок.

Замечание: при использовании динамического массива формула вернёт диапазон значений; убедитесь, что соседние ячейки не блокируют вывод.

Практические сценарии и чек‑листы по ролям

Аналитик:

• Проверить пропуски и текст в числовом столбце.
• Выбрать STDEV.S или STDEV.P.
• Вычислить среднее и стандартное отклонение.
• Протянуть формулу или использовать STANDARDIZE.

BI‑разработчик:

• Автоматизировать расчёт в ETL (Power Query) или DAX (Power BI имеет функции для стандартизации).
• Добавить индикатор выбросов (например, |Z|>3).
• Документировать, какой метод стд использован.

Студент/исследователь:

• Проверить, что выборка независима.
• Проставить формат чисел и округление до нужного знака.

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

1. Формулы возвращают ожидаемые значения при проверке на простом наборе (ручной пример).
2. Среднее и стандартное отклонение рассчитаны верно для тех же ячеек.
3. Абсолютные ссылки используются для ячеек со средним и стд при копировании формул.
4. Обработаны пустые и текстовые значения (либо удалены, либо учтены умышленно).
5. Документация в листе объясняет, выбран ли STDEV.S или STDEV.P.

Когда метод даёт неверные результаты

• Наличие нечисловых значений в диапазоне может исказить STDEVPA или AVERAGE.
• Малый объём данных (например, n<5) делает Z‑оценки менее надёжными для принятия решений.
• Данные с сильно асимметричным распределением (сильная скошенность) — Z‑оценки не заменят более подходящую нормализацию (логарифм, Box‑Cox).

Примеры альтернатив:

• Логарифмическая трансформация при положительной скошенности.
• Робастные метрики: медиана и MAD (median absolute deviation) вместо среднего и σ.

Шаблон: быстрая инструкция (SOP)

1. Скопировать исходные данные в отдельный лист.
2. Очистить столбец: удалить/заменить текст и пустые ячейки.
3. Создать ячейки со средним и стд, использовать абсолютные ссылки.
4. Вставить формулу Z или STANDARDIZE и протянуть/развернуть массив.
5. Добавить столбец с флагом выброса: =ABS(C2)>3
6. Документировать метод и версию Excel.

Краткая шпаргалка (cheat sheet)

• Формула Z вручную: =(x - mean) / stdev
• Функция: =STANDARDIZE(x, mean, stdev)
• Среднее: =AVERAGE(range)
• Стд для выборки: =STDEV.S(range)
• Стд для популяции: =STDEV.P(range)
• Учитывать логические значения: =STDEVPA(range)

Риски и рекомендации по качеству данных

• Всегда проверяйте на пропуски. Заполните или удалите их по принятым правилам.
• Убедитесь, что формат чисел установлен корректно (числовой формат, отделитель дробной части).
• При больших выбросах рассмотрите робастные методы.

Итог

Z‑Score — простой и мощный инструмент для нормализации данных и обнаружения выбросов. В Excel вы можете применять как ручную формулу, так и STANDARDIZE. Выбор между STDEV.S, STDEV.P и STDEVPA зависит от природы ваших данных. Автоматизируйте расчёт через таблицы, Power Query или динамические массивы, чтобы обеспечить воспроизводимость и удобство.

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

Короткое резюме: используйте AVERAGE + STDEV.S и формулу (x-mean)/stdev или STANDARDIZE для быстрого расчёта Z‑Score; применяйте робастные методы при сильной скошенности.