KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как рассчитать z-оценки в Excel: подробное руководство

АвторАманда ЛиДата изменения

В области статистики и анализа данных крайне важно понимать, как ваши данные соотносятся со средним значением. Z-оценка, также известная как стандартная оценка, показывает, насколько далеко конкретная точка данных отстоит от среднего значения набора данных — в единицах стандартного отклонения. Независимо от того, анализируете ли вы результаты тестов, финансовые показатели или любой другой числовой массив, расчёт Z-оценок поможет глубже понять поведение ваших данных.

Использование Excel для расчёта z-оценок обеспечивает простоту и эффективность, позволяя быстро анализировать большие объёмы данных для стандартизированного сравнения и выявления выбросов. В этом руководстве мы объясним, что такое z-оценка, как её рассчитать в Excel, приведём примеры формул, покажем, как интерпретировать z-оценки в ваших данных, и поделимся ключевыми советами, которые важно учитывать при выполнении этих расчётов.

Снимок экрана с общей формулой для расчета z-оценок

Что такое z-оценка?

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

Понятие z-оценки тесно связано с нормальным распределением — фундаментальным понятием в статистике, описывающим распределение, при котором большинство наблюдений сосредоточено вокруг центрального пика, а вероятности появления значений симметрично убывают по мере удаления от среднего в обе стороны. В контексте нормального распределения:

Диаграмма, иллюстрирующая кривую нормального распределения в статистике

  • Примерно 68 % данных попадают в интервал одного стандартного отклонения (±1 z-оценка) от среднего значения, что свидетельствует об умеренном разбросе относительно среднего.
  • Около 95 % наблюдений укладываются в пределы двух стандартных отклонений (±2 z-оценки), что указывает на значительное, но не экстремальное отклонение.
  • Почти 99,7 % данных укладываются в диапазон трёх стандартных отклонений (±3 z-оценки), охватывая практически все наблюдения в распределении и подчёркивая экстремальные отклонения.

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

Как найти z-оценку в Excel?

В Excel нет единой специализированной функции для прямого расчёта z-оценок. Сначала нужно вычислить среднее значение ()μ) и стандартное отклонение (σ) вашего набора данных. Получив эти ключевые статистики, вы сможете использовать один из двух основных методов для определения z-оценки:

  • Метод ручного расчёта: Примените формулу z-оценки:
    =(x-μ)/σ
  • где:
  • x — это анализируемая точка данных,
    μ — среднее значение вашего набора данных,
    σ — стандартное отклонение вашего набора данных.
  • Использование функции STANDARDIZE: Для более интегрированного подхода Excel предлагает функцию STANDARDIZE, которая напрямую вычисляет z-оценку по заданной точке данных, среднему значению и стандартному отклонению:
    =STANDARDIZE(x, mean, standard_dev)

Примеры формул для расчёта z-оценки в Excel

Допустим, у вас есть набор данных в столбце A, охватывающий ячейки с A2 по A101. Вот как можно рассчитать z-оценки для этих значений:

  1. Вычисление среднего значения ()μ): Используйте функцию AVERAGE(range), чтобы найти среднее значение ()μ) вашего набора данных.
    =AVERAGE(A2:A101)
    Снимок экрана, показывающий, как вычислить среднее значение с помощью функции СРЗНАЧ в Excel
  2. Вычисление стандартного отклонения ()σ): выберите подходящую формулу в зависимости от контекста ваших данных.
    Важно: Правильный выбор функции для вашего набора данных критически важен для точных расчётов. (Для моих данных в диапазоне A2:A101, представляющих всю генеральную совокупность, я буду использовать первую формулу.)
    • Используйте функцию STDEV.P(range), если ваши данные представляют всю совокупность (то есть они не являются выборкой из более крупной группы).
      =STDEV.P(A2:A101)
    • Используйте функцию STDEV.S(range), если ваши данные являются выборкой из более крупной совокупности или вы хотите оценить стандартное отклонение совокупности на основе вашей выборки.
      =STDEV.S(A2:A101)
      Снимок экрана, показывающий, как вычислить стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в Excel
  3. Расчёт z-оценки для точки данных в ячейке A2: воспользуйтесь любой из приведённых ниже формул — обе дадут одинаковый результат. (В данном случае я выберу вторую формулу.)
    • Рассчитайте вручную, вычитая среднее значение из точки данных и деля результат на стандартное отклонение.
      =(A2 - $E$2) / $E$3
    • Используйте функцию STANDARDIZE(x, mean, standard_dev).
      =STANDARDIZE(A2, $E$2, $E$3)

      Примечание:Знаки доллара ()$) указывают формуле всегда ссылаться на конкретные ячейки (E2 для среднего значения, E3 для стандартного отклонения), независимо от того, куда вы её копируете.

      Снимок экрана, показывающий, как рассчитать z-оценку для одного значения данных в Excel

  4. Расчёт z-оценок для каждого значения в вашем наборе данных: Скопируйте формулу из шага 3 вниз по столбцу, чтобы рассчитать z-оценки для всех значений в вашем наборе данных.Совет:Дважды щёлкните маркер заполнения ячейки, чтобы быстро применить формулу ко всему столбцу.

    GIF, демонстрирующий, как скопировать формулу z-оценки вниз по столбцу в Excel

Советы:
  • Чтобы упростить расчёт z-оценок по всему набору данных без отдельного ввода формул для среднего и стандартного отклонения в отдельных ячейках, используйте одну из следующих комплексных формул напрямую.
    =(A2 - AVERAGE($A$2:$A$101)) / STDEV.P($A$2:$A$101)
    =STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))
  • Поддержание постоянной точности за счёт использования трёх десятичных знаков для z-оценок — это надёжная практика в научной и статистической работе. Добейтесь этого, выделив ячейки с z-оценками и воспользовавшись опцией «Уменьшить разрядность» в группе «Число» на вкладке «Главная».

    Снимок экрана с опцией «Уменьшить разрядность» в Excel для форматирования z-оценок до трех знаков после запятой

Интерпретация z-оценок в данных

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

Связь со средним значением
  • Z-оценка = 0: Указывает на средний уровень производительности — значение данных в точности совпадает со средним.
  • Z-оценка > 0: Указывает на значения выше среднего — чем дальше от среднего, тем выше производительность.
  • Z-оценка < 0: Обозначает значения ниже среднего — чем ниже оценка, тем сильнее отклонение вниз от среднего.
Степень отклонения
  • |Z-оценка| < 1: Такие точки данных расположены близко к среднему значению, находятся в основном теле нормального распределения и указывают на стандартный уровень производительности.
  • |Z-оценка| < 2: Указывает на умеренное отклонение от среднего значения, обозначая наблюдения как необычные, но всё ещё находящиеся в пределах нормального диапазона изменчивости.
  • |Z-оценка| > 2: Выделяет необычные точки данных, значительно отклоняющиеся от среднего значения, что может сигнализировать о выбросах или существенных отклонениях от ожидаемой нормы.

Пример пояснения:

  • Z-оценка, равная 0,66, означает, что точка данных находится на 0,66 стандартных отклонений выше среднего значения. Это указывает, что значение выше среднего, но всё ещё относительно близко к нему и попадает в типичный диапазон изменчивости.
  • Напротив, z-оценка, равная –2,1, означает, что точка данных расположена на 2,1 стандартного отклонения ниже среднего значения. Такое значение существенно ниже среднего и указывает на значительное отклонение от типичного диапазона.

Что следует помнить при расчёте z-оценок в Excel

При использовании Excel для расчёта z-оценок точность и достоверность имеют первостепенное значение. Вот несколько ключевых аспектов, которые необходимо учитывать, чтобы обеспечить надёжность результатов:

  • Проверьте нормальность распределения: z-оценки наиболее эффективны для данных, подчиняющихся нормальному распределению. Если ваш набор данных не следует нормальному распределению, применение z-оценок может оказаться неуместным. Перед использованием z-оценок рекомендуется провести тест на нормальность.
  • Убедитесь, что используете формулу правильно: выберите подходящую функцию стандартного отклонения — STDEV.P для генеральной совокупности и STDEV.S для выборки — в зависимости от характеристик вашего набора данных.
  • Используйте абсолютные ссылки для среднего значения и стандартного отклонения: при применении формул к нескольким ячейкам указывайте абсолютные ссылки (например,)$A$1) на среднее значение и стандартное отклонение в формуле z-оценки — это гарантирует согласованность расчётов.
  • Осторожно обращайтесь с выбросами: они значительно влияют и на среднее значение, и на стандартное отклонение, что может исказить рассчитанные z-оценки.
  • Обеспечьте целостность данных: перед расчётом z-оценок убедитесь, что ваш набор данных чист и свободен от ошибок. Некорректные записи, дубликаты или нерелевантные значения могут серьёзно исказить среднее и стандартное отклонение, что приведёт к вводящим в заблуждение z-оценкам.
  • Избегайте преждевременного округления или усечения: Excel способен обрабатывать большое количество десятичных знаков, и сохранение этих значений помогает предотвратить накопление ошибок округления, которые могут исказить итоговый анализ.

Выше представлена вся актуальная информация о расчёте z-оценок в Excel. Надеемся, что это руководство окажется для вас по-настоящему полезным! Хотите освоить ещё больше советов и приёмов работы в Excel? Перейдите по этой ссылке, чтобы получить доступ к нашей обширной коллекции из тысяч обучающих материалов.