Z-Score — это статистическое значение, которое сообщает вам, на сколько стандартных отклонений конкретное значение оказывается от среднего значения для всего набора данных. Вы можете использовать формулы AVERAGE и STDEV.S или STDEV.P для вычисления среднего и стандартного отклонения ваших данных, а затем использовать эти результаты для определения Z-Score каждого значения.
Оглавление
Что такое Z-Score и для чего нужны функции AVERAGE, STDEV.S и STDEV.P?
Z-Score — это простой способ сравнения значений из двух разных наборов данных. Он определяется как количество стандартных отклонений от среднего значения точки данных. Общая формула выглядит так:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Вот пример, который поможет прояснить ситуацию. Допустим, вы хотите сравнить результаты тестов двух студентов алгебры, преподаваемых разными учителями. Вы знаете, что первый ученик получил 95% на выпускном экзамене в одном классе, а ученик другого класса набрал 87%.
На первый взгляд, оценка 95% впечатляет, но что, если учитель второго класса сдал бы более сложный экзамен? Вы можете рассчитать Z-Score каждого ученика на основе средних баллов в каждом классе и стандартного отклонения баллов в каждом классе. Сравнение Z-баллов двух учеников могло показать, что ученик с баллом 87% справился лучше по сравнению с остальной частью своего класса, чем ученик с баллом 98% по сравнению с остальной частью их класса.
Первое необходимое статистическое значение — это «среднее», и функция Excel «СРЕДНИЙ» вычисляет это значение. Он просто складывает все значения в диапазоне ячеек и делит эту сумму на количество ячеек, содержащих числовые значения (пустые ячейки игнорируются).
Другая необходимая нам статистическая величина — это «стандартное отклонение», и в Excel есть две разные функции для вычисления стандартного отклонения несколько разными способами.
В предыдущих версиях Excel была только функция «СТАНДОТКЛОН», которая вычисляет стандартное отклонение при обработке данных как «выборки» генеральной совокупности. Excel 2010 разбил это на две функции, которые вычисляют стандартное отклонение:
СТАНДОТКЛОН.S: эта функция идентична предыдущей функции «СТАНДОТКЛОН». Он вычисляет стандартное отклонение, обрабатывая данные как «образец» генеральной совокупности. Выборка группы может быть чем-то вроде конкретных комаров, собранных для исследовательского проекта, или автомобилей, которые были отложены и использованы для испытаний на безопасность при столкновении.
СТАНДОТКЛОН.P: эта функция вычисляет стандартное отклонение при обработке данных как всей генеральной совокупности. Целая популяция была бы чем-то вроде всех комаров на Земле или каждой производственной машины определенной модели.
То, что вы выберете, зависит от вашего набора данных. Разница обычно небольшая, но результат функции «STDEV.P» всегда будет меньше результата функции «STDEV.S» для того же набора данных. Это более консервативный подход, предполагающий, что данные более изменчивы.
Давайте посмотрим на пример
В нашем примере у нас есть два столбца («Значения» и «Z-оценка») и три «вспомогательные» ячейки для хранения результатов функций «СРЕДНИЙ», «СТАНДОТКЛОН.S» и «СТАНДОТКЛОН.P». Столбец «Значения» содержит десять случайных чисел с центром вокруг 500, а столбец «Z-Score» — это то место, где мы будем вычислять Z-Score, используя результаты, хранящиеся в «вспомогательных» ячейках.
Сначала мы вычислим среднее значение с помощью функции «СРЕДНИЙ». Выберите ячейку, в которой вы сохраните результат функции «СРЕДНИЙ».
Введите следующую формулу и нажмите ввод -или- используйте меню «Формулы».
=AVERAGE(E2:E13)
Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите параметр «Статистические данные», а затем нажмите «СРЕДНИЙ».
В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Число1». Вам не нужно беспокоиться о поле «Число2».
Теперь нажмите «ОК».
Затем нам нужно рассчитать стандартное отклонение значений, используя функцию «СТАНДОТКЛОН.S» или «СТАНДОТКЛОН.P». В этом примере мы покажем вам, как вычислить оба значения, начиная с «СТАНДОТКЛОН. S». Выберите ячейку, в которой будет сохранен результат.
Чтобы рассчитать стандартное отклонение с помощью функции «СТАНДОТКЛОН.S», введите эту формулу и нажмите Enter (или откройте ее через меню «Формулы»).
=STDEV.S(E3:E12)
Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите параметр «Статистические», прокрутите немного вниз и нажмите команду «СТАНДОТКЛОН.S».
В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Число1». Вам также не нужно беспокоиться о поле «Число2».
Теперь нажмите «ОК».
Затем мы рассчитаем стандартное отклонение с помощью функции «СТАНДОТКЛОН.П». Выберите ячейку, в которой будет сохранен результат.
Чтобы рассчитать стандартное отклонение с помощью функции «СТАНДОТКЛОН.P», введите эту формулу и нажмите Enter (или откройте ее через меню «Формулы»).
= СТАНДОТКЛОН.P (E3: E12)
Чтобы получить доступ к функции через меню «Формулы», выберите раскрывающийся список «Дополнительные функции», выберите параметр «Статистические», прокрутите немного вниз и затем щелкните формулу «STDEV.P».
В окне «Аргументы функции» выберите все ячейки в столбце «Значения» в качестве входных данных для поля «Число1». Опять же, вам не нужно беспокоиться о поле «Число2».
Теперь нажмите «ОК».
Теперь, когда мы рассчитали среднее значение и стандартное отклонение наших данных, у нас есть все необходимое для расчета Z-Score. Мы можем использовать простую формулу, которая ссылается на ячейки, содержащие результаты функций «СРЕДНИЙ» и «СТАНДОТКЛОН.S» или «СТАНДОТКЛОН.P».
Выберите первую ячейку в столбце «Z-Score». В этом примере мы будем использовать результат функции «STDEV.S», но вы также можете использовать результат «STDEV.P».
Введите следующую формулу и нажмите Enter:
=(E3-$G$3)/$H$3
В качестве альтернативы вы можете использовать следующие шаги, чтобы ввести формулу вместо ввода:
Щелкните ячейку F3 и введите = (
Выберите ячейку E3. (Вы можете один раз нажать клавишу со стрелкой влево или использовать мышь)
Введите знак минус —
Выберите ячейку G3, затем нажмите F4, чтобы добавить символы «$», чтобы сделать «абсолютную» ссылку на ячейку (она будет циклически проходить через «G3»> «$ G $ 3 ″>« G $ 3 ″> «$ G3 ″>« G3 »). ”Если вы продолжите нажимать F4)
Тип )/
Выберите ячейку H3 (или I3, если вы используете «STDEV.P») и нажмите F4, чтобы добавить два символа «$».
нажмите Ввод
Z-Score был рассчитан для первого значения. Это на 0,15945 стандартного отклонения ниже среднего. Чтобы проверить результаты, вы можете умножить стандартное отклонение на этот результат (6,271629 * -0,15945) и проверить, равен ли результат разнице между значением и средним значением (499-500). Оба результата равны, поэтому значение имеет смысл.
Давайте посчитаем Z-баллы остальных значений. Выделите весь столбец «Z-Score», начиная с ячейки, содержащей формулу.
Нажмите Ctrl + D, чтобы скопировать формулу из верхней ячейки вниз через все остальные выделенные ячейки.
Теперь формула «заполнена» для всех ячеек, и каждая из них всегда будет ссылаться на правильные ячейки «AVERAGE» и «STDEV.S» или «STDEV.P» из-за символов «$». Если вы получаете ошибки, вернитесь и убедитесь, что символы «$» включены в введенную вами формулу.
Расчет Z-Score без использования ячеек-помощников
Ячейки-помощники хранят результат, как и те, которые хранят результаты функций «СРЕДНИЙ», «СТАНДОТКЛОН.S» и «СТАНДОТКЛОН.P». Они могут быть полезны, но не всегда необходимы. Вы можете полностью пропустить их при вычислении Z-Score, используя вместо этого следующие обобщенные формулы.
Вот пример, использующий функцию «СТАНДОТКЛОН.S»:
=(Value-AVERAGE(Values))/STDEV.S(Values)
И один, использующий функцию «STEV.P»:
=(Value-AVERAGE(Values))/STDEV.P(Values)
При вводе диапазонов ячеек для «Значений» в функциях обязательно добавляйте абсолютные ссылки («$» с помощью F4), чтобы при «заполнении» вы не вычисляли среднее или стандартное отклонение другого диапазона. ячеек в каждой формуле.
Если у вас большой набор данных, может быть более эффективным использование вспомогательных ячеек, поскольку они не вычисляют каждый раз результат функций «AVERAGE» и «STDEV.S» или «STDEV.P», экономя ресурсы процессора и ускорение времени, необходимого для расчета результатов.
Кроме того, «$ G $ 3» требует меньше байтов для хранения и меньше ОЗУ для загрузки, чем «СРЕДНИЙ ($ E $ 3: $ E $ 12).». Это важно, потому что стандартная 32-разрядная версия Excel ограничена 2 ГБ ОЗУ (64-разрядная версия не имеет ограничений на то, сколько ОЗУ можно использовать).