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-счет, используя результаты, сохраненные в ячейках «помощника».
Сначала мы вычислим среднее значение, используя функцию «СРЕДНИЙ». Выберите ячейку, в которой вы будете хранить результат функции «СРЕДНИЙ».
Введите следующую формулу и нажмите enter или используйте меню «Формулы».
= СРЗНАЧ (E2: E13)
Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите «Статистические» и нажмите «СРЕДНИЙ».
В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Номер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
В качестве альтернативы, вы можете использовать следующие шаги для ввода формулы вместо ввода:
-
Нажмите на ячейку F3 и введите
= ( - Выберите ячейку E3. (Вы можете нажать клавишу со стрелкой влево один раз или использовать мышь)
-
Введите знак минуса
- - Выберите ячейку G3, затем нажмите F4 , чтобы добавить символы «$», чтобы сделать «абсолютную» ссылку на ячейку (она будет циклически повторяться через «G3»> « $ G <». strong> $ 3 ″> «G $ 3 ″>« $ G3 ″> «G3», если вы продолжаете нажимать F4 )
-
Введите
)/ - Выберите ячейку H3 (или I3, если вы используете «STDEV.P») и нажмите F4 , чтобы добавить два символа «$».
- нажмите Ввод
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-разрядная версия не имеет никаких ограничений на объем используемой ОЗУ).
