Как вычислить Z‑оценку в Excel (AVERAGE, STDEV.S, STDEV.P)

Важно: выбирайте STDEV.S для выборки и STDEV.P для полной совокупности. При нулевой дисперсии (все значения равны) Z‑оценка не определена (деление на ноль).

Что такое Z‑оценка и как работают AVERAGE, STDEV.S и STDEV.P

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

=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)

Кратко:

• AVERAGE — вычисляет среднее (арифметическое) по диапазону: сумма значений / количество чисел (пропускает пустые ячейки).
• STDEV.S — стандартное отклонение для выборки (sample). Используется, когда данные представляют собой выборку из большей популяции. На практике это STDEV в старых версиях Excel.
• STDEV.P — стандартное отклонение для полной совокупности (population). Используется, когда у вас есть все элементы популяции.

Разница между STDEV.S и STDEV.P в знаменателе: STDEV.S использует n−1 (несмещённая оценка), STDEV.P использует n. Для одного и того же набора STDEV.P обычно меньше или равен STDEV.S.

Кейс из практики: два ученика получили 95% и 87% в разных классах. Сравнение по чистым баллам вводит в заблуждение, а сравнение по Z‑оценкам показывает относительную позицию внутри каждого класса.

Пример: таблица со значениями и Z‑оценками

В примере у нас две колонки: “Values” и “Z‑Score”, плюс три вспомогательные ячейки для AVERAGE, STDEV.S и STDEV.P. Колонка “Values” содержит 10 чисел, центрированных около 500, а колонка “Z‑Score” — рассчитанные Z‑оценки.

Шаги (подробно):

1. Выделите ячейку для хранения среднего и введите формулу:

=AVERAGE(E2:E13)

Или используйте меню Формулы → More Functions → Statistical → AVERAGE и укажите диапазон.

2. Нажмите OK — теперь в ячейке отображается среднее.

3. Для стандартного отклонения выборки используйте:

=STDEV.S(E3:E12)

Или через Формулы → More Functions → Statistical → STDEV.S.

4. Для стандартного отклонения популяции используйте:

=STDEV.P(E3:E12)

5. Когда среднее и стандартное отклонение рассчитаны, формула Z‑оценки для первой строки выглядит так (с абсолютными ссылками на вспомогательные ячейки):

=(E3-$G$3)/$H$3

Здесь G3 — среднее (AVERAGE), H3 — стандартное отклонение (STDEV.S или STDEV.P). Абсолютные ссылки ($) нужны, чтобы при заполнении формулой вниз все строки ссылались на одну и ту же ячейку со средним и стандартным отклонением.

6. Проверка корректности: умножьте стандартное отклонение на Z и сравните с разницей между исходным значением и средним. Например: 6.271629 * −0.15945 ≈ −1, а 499 − 500 = −1.

7. Чтобы распространить формулу на остальные значения, выделите диапазон в колонке Z‑Score (начиная с ячейки с формулой) и нажмите Ctrl+D или используйте маркер заполнения.

Если при заполнении формул появляются ошибки, проверьте наличие символов “$” в ссылках на ячейки со средним и стандартным отклонением.

Как вычислить Z‑оценку без вспомогательных ячеек

Можно сразу использовать вложенные функции — тогда для каждой строки будет вычисляться AVERAGE и STDEV заново:

=(Value-AVERAGE(Values))/STDEV.S(Values)

и для популяции:

=(Value-AVERAGE(Values))/STDEV.P(Values)

При вводе диапазона убедитесь, что используете абсолютные ссылки, например $E$3:$E$12, чтобы при заполнении формулы вниз диапазон не смещался.

Примечание по производительности: для больших наборов данных использование вспомогательных ячеек экономит процессорное время, потому что AVERAGE и STDEV считаются один раз, а не для каждой строки. Это снижает количество вычислений и память, особенно в 32‑битной версии Excel с лимитом ~2 ГБ.

Когда лучше использовать helper‑ячейки — рекомендации

• Набор данных небольшой (несколько сотен строк): можно использовать вложенные формулы — простая и компактная запись.
• Набор данных большой (тысячи–миллионы строк или сложные вычисления): используйте helper‑ячейки (вычислить среднее и стандартное отклонение один раз).
• Если вы часто обновляете диапазон или добавляете столбцы/строки: helper‑ячейки удобнее, так как в них можно централизованно изменить диапазон.

Практические проблемы и как их решать

1. Стандартное отклонение равно нулю (все значения одинаковы).
   • Симптом: деление на ноль, #DIV/0!.
   • Решение: проверяйте STDEV.S(…)<>0 перед делением или используйте условное выражение IF.

=IF(STDEV.S($E$3:$E$12)=0, "сигма=0", (E3-AVERAGE($E$3:$E$12))/STDEV.S($E$3:$E$12))

2. Пустые и текстовые ячейки.

   • AVERAGE игнорирует текст и пустые ячейки, STDEV тоже. Но если диапазон содержит ошибки (#N/A, #DIV/0!, и т.д.), формулы вернут ошибку.
   • Решение: очистить данные или использовать функции, фильтрующие ошибки (например, IFERROR / AGGREGATE / FILTER в современных версиях Excel).

3. Выбросы (outliers).

   • Z‑оценка чувствительна к выбросам, так как они влияют на среднее и стандартное отклонение.
   • Подход: сперва проанализируйте данные (boxplot, межквартильный размах), возможно, используйте робастные меры (медиана и MAD) или Winsorization.

4. Нормальность распределения.

   • Z‑оценки предполагают понятие «сколько стандартных отклонений», но интерпретация как вероятностей корректна для приближенно нормального распределения. Если данные сильно не нормальны, используйте ранговые методы или преобразования (лог, Box‑Cox).

Альтернативные подходы и расширения

• Медианная стандартизация: (x − медиана) / MAD (Median Absolute Deviation) — более устойчива к выбросам.
• Robust Z‑score: (x − median) / (1.4826 * MAD) — используется в задачах с сильными выбросами.
• Перцентиль или ранги: для несимметричных распределений лучше сравнивать по процентилю.

Чеклист по ролям (быстрая проверка перед отчётом)

Аналитик:

• Проверить, выборка или популяция → STDEV.S или STDEV.P.
• Убедиться, что диапазон верный и зафиксирован ($).
• Обработать нулевое стандартное отклонение.

Технический писатель/учитель:

• Показать пример проверки (умножение sigma × z).
• Вставить поясняющий комментарий к формуле.

Data Scientist:

• Оценить нормальность распределения (гистограмма, тесты).
• Рассмотреть медианный подход при выбросах.

Короткая методология (микропроцесс)

1. Очистить данные (удалить ошибки/нечисла или пометить их).
2. Определить область данных и зафиксировать ссылки ($).
3. Рассчитать среднее (AVERAGE) и стандартное отклонение (STDEV.S или STDEV.P).
4. Рассчитать Z‑оценки по формуле (Value − Mean) / SD.
5. Проверить результаты на крайних значениях и на корректность (сравнить SD*z с разницей Value−Mean).
6. При необходимости использовать робастные меры.

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

• Для набора данных с известным средним и SD формула должна возвращать ожидаемые значения (ручная проверка для 3–5 точек).
• Заполнить колонку Z‑оценок и убедиться, что абсолютные ссылки на среднее и SD не изменились при копировании формулы.
• Для одинаковых значений всех ячеек итог — корректная обработка деления на ноль (не аварийное завершение).

Частые ошибки и как их избегать

• Забыл добавить $ к ссылкам на ячейки со средним и SD — исправьте F4.
• Использовал STDEV вместо STDEV.S/STDEV.P в последних версиях Excel — используйте современные наименования.
• Пытаетесь интерпретировать Z‑оценку как вероятность при сильно не‑нормальном распределении — используйте ранговые методы.

Таблица сравнений: STDEV.S vs STDEV.P

• Контекст: STDEV.S — выборка; STDEV.P — популяция.
• Формула знаменателя: STDEV.S использует n−1, STDEV.P использует n.
• Результат: STDEV.P ≤ STDEV.S (обычно немного меньше).
• Выбор: если у вас все наблюдения популяции → STDEV.P; если это выборка → STDEV.S.

Практические сниппеты и шаблоны

Формула с helper‑ячейками (рекомендуется для больших наборов):

G3: =AVERAGE($E$3:$E$12)
H3: =STDEV.S($E$3:$E$12)
F3: =(E3-$G$3)/$H$3

Формула без helper‑ячейки (вложенная):

=(E3-AVERAGE($E$3:$E$12))/STDEV.S($E$3:$E$12)

Формула с защитой от деления на ноль:

=IF(STDEV.S($E$3:$E$12)=0, NA(), (E3-AVERAGE($E$3:$E$12))/STDEV.S($E$3:$E$12))

Edge‑case галерея (типовые ситуации)

• Наличие текстовых меток в диапазоне — AVERAGE игнорирует их, но если часть столбца имеет числа в виде текста, преобразуйте их в числа.
• Диапазон включает ошибки (#N/A) — вся формула вернёт ошибку; используйте IFERROR или очистите данные.
• Очень большие числа (плавающая точность) — возможны незначительные погрешности при вычислениях.

Краткий глоссарий

• Z‑оценка: стандартизированное значение, показывающее отклонение от среднего в единицах стандартного отклонения.
• Среднее (Mean): арифметическое среднее.
• STDEV.S: стандартное отклонение для выборки (n−1).
• STDEV.P: стандартное отклонение для популяции (n).

Советы по производительности и совместимости

• Excel 32‑бит ограничен объёмом доступной памяти (~2 ГБ). Вложенные формулы, которые много раз вычисляют AVERAGE/STDEV, могут использовать больше ресурсов. Helper‑ячейки снижают нагрузку.
• В Excel с динамическими массивами и большими таблицами используйте структурированные ссылки (Tables) и вычисляйте агрегаты один раз.
• Для автоматизации больших расчётов рассмотрите перенос тяжёлых вычислений в Power Query, Power Pivot или внешние аналитические движки (Python, R). Эти варианты дают контроль над памятью и параллелизмом.

Заключение

Z‑оценки — простой и мощный способ стандартизировать значения и сравнить наблюдения между разными наборами данных. В Excel это делается с помощью AVERAGE и STDEV.S/STDEV.P, после чего используется формула (Value − Mean) / SD. Для больших наборов данных используйте вспомогательные ячейки, чтобы не пересчитывать AVERAGE и STDEV для каждой строки. При наличии выбросов или не‑нормальных распределений рассмотрите робастные альтернативы (медиана, MAD).

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

• Определите, выборка или популяция.
• Рассчитайте среднее и SD (helper‑ячейка).
• Постройте формулу Z и заполните столбец вниз.
• Проверяйте крайние случаи и производительность.

Источники знаний: базовые определения статистики и документация Excel по функциям AVERAGE, STDEV.S и STDEV.P.