Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как рассчитать Z-оценки в Excel: Полное руководство

Author Amanda Li Last modified

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

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

 A screenshot of the generic formula to calculate z-scores

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

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

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

A diagram illustrating the normal distribution curve in statistics

  • Примерно 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(диапазон) функцию, чтобы найти среднее значение (μ) вашего набора данных. 
    =AVERAGE(A2:A101)
    A screenshot showing how to calculate the mean using the AVERAGE function in Excel
  2. Рассчитайте стандартное отклонение (σ): Выберите соответствующую формулу в зависимости от контекста ваших данных.
    Важно: Выбор правильной функции для вашего набора данных имеет решающее значение для обеспечения точных расчетов. (Для моих данных в A2:A101, представляющих всю совокупность, я буду использовать первую формулу.)
    • ИспользуйтеSTDEV.P(диапазон) функцию, если ваши данные представляют собой всю совокупность (то есть нет более крупной группы, из которой взяты эти значения). 
      =STDEV.P(A2:A101)
    • Используйте STDEV.S(диапазон) функцию, если ваши данные являются выборкой из более крупной совокупности или вы хотите оценить стандартное отклонение совокупности на основе вашей выборки. 
      =STDEV.S(A2:A101)
      A screenshot showing how to calculate the standard deviation using the STDEV.P function in Excel
  3. Рассчитайте z-оценку для точки данных в A2: Используйте любую из следующих формул, которая даст одинаковый результат. (В данном случае я выберу вторую формулу.)
    • Рассчитайте вручную путем вычитания среднего значения из точки данных и деления этого результата на стандартное отклонение. 
      =(A2 - $E$2) / $E$3
    • Используйте STANDARDIZE(x, mean, standard_dev) функцию. 
      =STANDARDIZE(A2, $E$2, $E$3)

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

      A screenshot showing how to calculate a z-score for a single data point in Excel

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

    A GIF showing how to copy the z-score formula down a column in 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-оценками и используя опцию «Уменьшить десятичные знаки», найденную в группе Число на вкладке Главная.

    A screenshot of the Decrease Decimal option in Excel for formatting z-scores to three decimal places

Интерпретация 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, пожалуйста, нажмите здесь, чтобы получить доступ к нашей обширной коллекции из тысяч учебников.