Как найти Z-значение с помощью Microsoft Excel

Работа со статистикой заключается в поиске способов осмысления имеющихся у вас данных, и Microsoft Excel — мощный инструмент для этого. Z-оценка — это базовая статистическая мера, которая сообщает вам количество стандартных отклонений конкретной точки данных от среднего значения выборки. Это значительно упрощает сравнение различных значений с точки зрения того, как они соотносятся со средним значением для группы. Вычисление z-показателя в Excel — это простой процесс, независимо от используемой версии Excel или размера набора данных.

Упорядочивание данных

Прежде чем приступить к фактическим расчетам, лучше всего расположить основные данные в одном столбце и убедиться, что у вас есть место для других битов информации, которые вам нужны, прежде чем использовать Excel в качестве калькулятора z-оценки. Используя пример класса из 20 учеников, которые прошли тест, вы можете составить список имен учеников в одном столбце (например, столбец А, строки со 2 по 21), их записанные баллы в соседнем столбце. (B) и пустое место для каждого z-показателя в столбце рядом с ним (C). Вам также потребуется по одной ячейке для среднего значения и стандартного отклонения набора данных, которые вы можете поместить куда угодно, но в этом примере они находятся в ячейках D2 и E2 соответственно.

Вычисление среднего и стандартного отклонения

В Excel есть встроенные функции для заполнения пробелов для среднего значения и стандартного отклонения ваших данных. Перейдите к ячейке, которую вы оставили для среднего значения, и введите «=СРЗНАЧ(диапазон )" с диапазоном ячеек, содержащих данные (например, результаты тестов), где указано "диапазон ." В этом примере вы должны ввести "=СРЗНАЧ(B2:B21)", но вы также можете выбрать ячейки с помощью мыши после раскрытия круглых скобок. Это вернет среднее значение для данных.

Введите "=STDEV(диапазон )", чтобы найти стандартное отклонение данных в версиях Excel до 2010 г., и либо "=STDEV.S(диапазон )" или "=STDEV.P(диапазон )" в более новых версиях Excel. Версия "S" предназначена для данных, которые представляют только выборку всего населения, например, если вы хотите сделать выводы о баллах всей школы, но иметь данные только из одного класса. "P " версия предназначена для данных всего населения, когда вас интересует только ваш класс или если у вас есть все оценки школы. Снова введите или щелкните диапазон ячеек, содержащих ваши данные, где указано " диапазон ." В этом примере вы должны ввести "=STDEV.P(B2:B21)."

Использование формулы Z-оценки

Формула для расчета z-показателя в Excel проста. С Х вместо рассматриваемой точки данных, x̄ для среднего значения выборки и σ для стандартного отклонения формула:

Я =(Х − x̄ ) ÷ σ

В Excel с установленным до сих пор расположением ячеек это легко сделать. Перейдите к ячейке рядом с первой точкой данных, которой в примере является ячейка C2 рядом с данными в B2. Введите «=(B2-$D$2)/$E$2», чтобы указать ему вычесть среднее значение из точки данных, а затем разделить результат на стандартное отклонение и нажать «Ввод» для подтверждения. Проще говоря, без использования примеров ссылок на ячейки, формула выглядит так:«=([точка данных] – [среднее значение]) / [стандартное отклонение]» с соответствующими ссылками на ячейки вместо квадратных скобок.

В этом примере знаки доллара ($D$2 и $E$2) сообщают Excel, что нужно сохранять именно эти ячейки при перетаскивании формулы вниз. Щелкните правый нижний угол ячейки с формулой и перетащите ее вниз, чтобы скопировать формулу во весь столбец z-оценки. Это изменяет ссылку на первую ячейку так, чтобы она ссылалась на точку данных непосредственно слева, и возвращала ряд z-показателей, говорящих вам, сколько стандартных отклонений от среднего значения каждого результата. Отрицательный результат означает, что оценка ниже среднего, а положительный результат означает, что она выше среднего.