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

Z-показатель - это статистическое значение, которое указывает, сколько стандартных отклонений происходит от конкретного значения от среднего значения всего набора данных. Вы можете использовать формулы AVERAGE и STDEV.S или STDEV.P для расчета среднего и стандартного отклонения ваших данных, а затем использовать эти результаты для определения Z-показателя каждого значения..

Что такое Z-Score и что делают функции AVERAGE, STDEV.S и STDEV.P?

Z-Score - это простой способ сравнения значений из двух разных наборов данных. Он определяется как число стандартных отклонений от среднего значения точки данных. Общая формула выглядит так:

= (DataPoint-СРЕДНЕЕ (набор данных)) / СТАНДОТКЛОН (набор данных)

Вот пример, чтобы помочь уточнить. Скажем, вы хотите сравнить результаты тестов двух студентов по алгебре, которых учили разные учителя. Вы знаете, что первый студент получил 95% на итоговом экзамене в одном классе, а студент в другом классе набрал 87%.

На первый взгляд, 95% баллов более впечатляющие, но что, если учитель второго класса сдает более сложный экзамен? Вы можете рассчитать Z-балл для каждого учащегося на основе средних баллов в каждом классе и стандартного отклонения баллов в каждом классе. Сравнение Z-баллов двух учеников показало, что ученик с результатом 87% показал лучшие результаты по сравнению с остальными учениками своего класса, чем ученик с результатом 98% по сравнению с остальными учениками..

Первое статистическое значение, которое вам нужно, - это среднее значение, и функция Excel AVERAGE вычисляет это значение. Он просто складывает все значения в диапазоне ячеек и делит эту сумму на количество ячеек, содержащих числовые значения (игнорирует пустые ячейки).

Другое статистическое значение, которое нам нужно, - это «стандартное отклонение», а в Excel есть две разные функции для вычисления стандартного отклонения немного разными способами..

В предыдущих версиях Excel была только функция «STDEV», которая вычисляет стандартное отклонение при обработке данных как «выборки» совокупности. Excel 2010 разбил это на две функции, которые вычисляют стандартное отклонение:

• STDEV.S: Эта функция идентична предыдущей функции «STDEV». Он рассчитывает стандартное отклонение, рассматривая данные как «выборку» популяции. Образец популяции может быть чем-то вроде конкретных комаров, собранных для исследовательского проекта, или автомобилей, которые были отложены и использованы для испытаний на безопасность при столкновении..
• STDEV.P: Эта функция вычисляет стандартное отклонение, рассматривая данные как всю совокупность. Вся популяция будет похожа на всех комаров на Земле или на каждый автомобиль в серийном производстве определенной модели..

То, что вы выбираете, зависит от вашего набора данных. Разница обычно будет небольшой, но результат функции «STDEV.P» всегда будет меньше, чем результат функции «STDEV.S» для того же набора данных. Это более консервативный подход к предположению, что в данных больше вариативности.

Давайте посмотрим на пример

В нашем примере у нас есть два столбца («Значения» и «Z-Score») и три «вспомогательных» ячейки для хранения результатов функций «AVERAGE», «STDEV.S» и «STDEV.P». Столбец «Значения» содержит десять случайных чисел с центром в районе 500, а в столбце «Z-счет» мы рассчитаем Z-счет, используя результаты, хранящиеся в ячейках «помощника»..

Сначала мы вычислим среднее значение, используя функцию «AVERAGE». Выберите ячейку, в которой вы будете хранить результат функции «СРЕДНИЙ».

Введите следующую формулу и нажмите войти -или - используйте меню «Формулы».

= СРЗНАЧ (Е2: Е13)

Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите «Статистические» и нажмите «СРЕДНИЙ».

В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Номер1». Вам не нужно беспокоиться о поле «Number2».

Теперь нажмите «ОК».

Затем нам нужно рассчитать стандартное отклонение значений с помощью функции «STDEV.S» или «STDEV.P». В этом примере мы покажем вам, как рассчитать оба значения, начиная с «STDEV.S». Выберите ячейку, в которой будет сохранен результат.

Чтобы рассчитать стандартное отклонение с помощью функции «STDEV.S», введите эту формулу и нажмите Enter (или откройте ее через меню «Формулы»).

= STDEV.S (Е3: Е12)

Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите параметр «Статистические», прокрутите немного вниз, а затем щелкните команду «STDEV.S»..

В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Номер1». Вам не нужно беспокоиться о поле «Number2» здесь, либо.

Теперь нажмите «ОК».

Далее мы рассчитаем стандартное отклонение с помощью функции «STDEV.P». Выберите ячейку, в которой будет сохранен результат.

Чтобы рассчитать стандартное отклонение с помощью функции «STDEV.P», введите эту формулу и нажмите Enter (или откройте ее через меню «Формулы»).

= STDEV.P (Е3: Е12)

Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите параметр «Статистические», немного прокрутите вниз и нажмите формулу «STDEV.P»..

В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Номер1». Опять же, вам не нужно беспокоиться о поле «Number2».

Теперь нажмите «ОК».

Теперь, когда мы вычислили среднее и стандартное отклонение наших данных, у нас есть все, что нужно для расчета Z-показателя. Мы можем использовать простую формулу, которая ссылается на ячейки, содержащие результаты функций «AVERAGE» и «STDEV.S» или «STDEV.P».

Выберите первую ячейку в столбце «Z-Score». Мы будем использовать результат функции «STDEV.S» для этого примера, но вы также можете использовать результат из «STDEV.P».

Введите следующую формулу и нажмите Enter:

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

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

1. Нажмите на ячейку F3 и введите знак равно
2. Выберите ячейку E3. (Вы можете нажать стрелка влево ключ один раз или используйте мышь)
3. Введите знак минус -
4. Выберите ячейку G3 и нажмите F4 добавить символы «$», чтобы сделать «абсолютную» ссылку на ячейку (она будет циклически повторяться через «G3»> «$г$3 ">« G$3 ">"$G3 ">« G3 », если вы продолжаете нажимать F4)
5. Тип ) /
6. Выберите ячейку H3 (или I3, если вы используете «STDEV.P») и нажмите F4 добавить два символа «$».
7. нажмите Ввод

Z-счет был рассчитан для первого значения. Это 0,15945 стандартных отклонений ниже среднего. Чтобы проверить результаты, вы можете умножить стандартное отклонение на этот результат (6.271629 * -0.15945) и убедиться, что результат равен разнице между значением и средним (499-500). Оба результата равны, поэтому значение имеет смысл.

Давайте вычислим Z-баллы остальных значений. Выделите весь столбец «Z-Score», начиная с ячейки, содержащей формулу.

Нажмите Ctrl + D, которая копирует формулу в верхней ячейке вниз через все остальные выделенные ячейки.

Теперь формула «заполнена» для всех ячеек, и каждая из них всегда будет ссылаться на правильные ячейки «AVERAGE» и «STDEV.S» или «STDEV.P» из-за символов «$». Если вы получили ошибки, вернитесь и убедитесь, что символы «$» включены в формулу, которую вы ввели.

Вычисление Z-счета без использования ячеек «Помощник»

Вспомогательные ячейки хранят результат, например, те, которые хранят результаты функций «AVERAGE», «STDEV.S» и «STDEV.P». Они могут быть полезны, но не всегда необходимы. Вы можете вообще пропустить их при расчете Z-счета, используя вместо этого следующие обобщенные формулы.

Вот один из них, использующий функцию «STDEV.S»:

= (Значение среднемассовое (значения)) / STDEV.S (значения)

И тот, который использует функцию «STEV.P»:

= (Значение среднемассовое (значения)) / STDEV.P (значения)

При вводе диапазонов ячеек для «Значений» в функциях обязательно добавьте абсолютные ссылки («$» с помощью F4), чтобы при заполнении не вычислять среднее или стандартное отклонение другого диапазона клеток в каждой формуле.

Если у вас большой набор данных, может быть более эффективно использовать вспомогательные ячейки, потому что он не рассчитывает результат функций «AVERAGE» и «STDEV.S» или «STDEV.P» каждый раз, экономя ресурсы процессора и ускорение времени, необходимого для расчета результатов.

Кроме того, «$ G $ 3» требует меньше байтов для хранения и меньше оперативной памяти для загрузки, чем «AVERAGE ($ E $ 3: $ E $ 12)». Это важно, потому что стандартная 32-разрядная версия Excel ограничена 2 ГБ ОЗУ (64-разрядная версия не имеет никаких ограничений на объем используемой ОЗУ)..