Как рассчитать Z-Score в Excel

Как вы знаете, есть много математических вычислений, которые вы можете выполнить с помощью Microsoft Excel. В этом руководстве мы расскажем, как рассчитать Z-Score в Excel.

Если вы впервые используете Excel, мы рекомендуем уделить пять минут, чтобы ознакомиться с этим. Руководство для начинающих по Microsoft Excel. Вы узнаете, как работают основные функции, ярлыки навигации, создание книг, форматирование данных и все, что нужно знать об использовании программы электронных таблиц в качестве новичка.

Когда вы закончите с этим, перейдите к следующему разделу, чтобы узнать, как рассчитать Z-оценку в Excel. Но сначала давайте кратко объясним Z-Score, его использование, почему вам может потребоваться его вычисление и как это сделать.

Что такое Z-Score?

Z-оценка (также известная как «стандартная оценка») – это показатель, который подчеркивает взаимосвязь между значениями в распределении. Точнее, он описывает положение значений в наборе данных по отношению к их среднему значению и стандартному отклонению. Z-Score позволяет точно измерять и сравнивать значения в наборе данных.

Математическая формула для расчета Z-Score: (x-µ) / σ; где x = значение ячейки, µ = среднее значение и σ = стандартное отклонение.

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

Как рассчитать Z-Score в Excel

Поскольку Z-Score является функцией среднего и стандартного отклонения, вам необходимо сначала вычислить среднее и стандартное отклонение для вашего набора данных. Хотя вы можете извлечь среднее значение и стандартное отклонение в любой ячейке, мы создали специальные столбцы для «Среднее» и «Стандартное отклонение» на нашем листе. Мы также создали столбец «Z-Score».

Наш образец документа содержит рейтинги эффективности 10 сотрудников бумажной компании. Теперь посчитаем Z-Score рейтинга сотрудников.

Рассчитать среднее значение

Чтобы вычислить среднее значение для вашего набора данных, введите = СРЕДНЕЕ (, выберите первое значение в наборе данных, нажмите клавишу столбца, выберите последнее значение в диапазоне набора данных, нажмите закрывающую круглую скобку и нажмите Enter. Формула должна выглядеть как показано ниже:

= СРЕДНИЙ (B2: B11)

Вы должны увидеть среднее или среднее значение набора данных в ячейке, в которой вы ввели формулу.

Рассчитать стандартное отклонение

Excel также позволяет легко рассчитать стандартное отклонение вашего набора данных за несколько щелчков мышью.

Выберите ячейку в столбце «Стандартное отклонение», введите = STDEV.P (, затем выберите первое значение в диапазоне, нажмите клавишу столбца, выберите последнее значение, введите закрывающую скобку и нажмите Enter. Если вы если есть сомнения, полученная формула должна быть похожей на приведенную ниже:

= СТАНДОТКЛОН.P (B2: B11)

Рассчитать Z-Score в Excel: метод 1

В Excel есть функция СТАНДАРТИЗАЦИЯ, которая предоставляет Z-оценку набора данных в распределении. Выберите первую ячейку в столбце Z-Score и выполните следующие действия.

1. Перейдите на вкладку “Формулы” и выберите “Другие формулы”.

2. Наведите указатель мыши на параметр «Статистика» и выберите «СТАНДАРТИЗИРОВАТЬ».

Это запустит новое окно аргументов функции, в котором вы сможете вычислить Z-Score распределения.

3. Введите ссылку на ячейку первого значения в поле «X».

4. Введите ссылку на ячейку среднего арифметического в поле «Среднее» и нажмите F4 на клавиатуре, чтобы заблокировать ссылку на ячейку.

5. Наконец, введите ссылку на ячейку стандартного отклонения в поле «Standard_dev» и нажмите F4, чтобы заблокировать ссылку на ячейку. Инструмент отобразит предварительный просмотр Z-значения. Нажмите ОК, чтобы продолжить.

Чтобы получить Z-Score для других значений, наведите курсор на правый нижний угол ячейки и перетащите значок плюса (+) вниз по столбцу.

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

Рассчитать Z-показатель в Excel: метод 2

Как упоминалось ранее, вы можете получить Z-Score точки данных, вычтя среднее значение набора данных из точки данных и разделив результат на стандартное отклонение. Используя (x-µ) / σ, вы можете рассчитать z-оценку в Excel, введя эти значения вручную.

1. Выберите первую ячейку в столбце Z-Score, введите знак равенства (=), за которым следует открытая скобка, и выберите первое значение в столбце, содержащем наборы данных, Z-Score которых вы хотите вычислить. После этого введите дефис, выберите среднее арифметическое, нажмите F4, чтобы сделать среднее абсолютным / фиксированным, и нажмите значок закрывающей круглой скобки. Наконец, нажмите клавишу с косой чертой (/), выберите стандартное отклонение и нажмите F4, чтобы заблокировать ссылку на ячейку.

Окончательная формула должна выглядеть примерно так: = (B2- $ C $ 2) / $ D $ 2. Нажмите Enter, чтобы выполнить формулу.

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

2. Наведите указатель мыши на правый нижний угол первой ячейки Z-Score и перетащите значок плюса (+) вниз по столбцу.

Интерпретация Z-показателя

Ваш набор данных, скорее всего, будет содержать смесь отрицательных и положительных Z-баллов. Положительный Z-Score указывает, что значение / оценка выше среднего среднего значения набора данных. Отрицательный Z-Score, конечно, говорит об обратном: значение ниже среднего. Если Z-Score точки данных равен нулю (0), это потому, что его значение равно среднему арифметическому.

Чем больше точка данных, тем выше ее Z-Score. Просмотрите свой рабочий лист, и вы поймете, что маленькие значения имеют более низкие Z-баллы. Аналогично, значения, меньшие среднего арифметического, будут иметь отрицательные Z-баллы.

Например, в нашем образце рабочего листа вы обнаружите, что «Майкл» имел самый высокий рейтинг (78) и самый высокий Z-Score (1,679659). «Дуайт» и «Кевин», с другой стороны, имели самый низкий рейтинг (34) и самый низкий Z-Score (-1,59047).

Станьте экспертом по Excel: репозиторий учебников

Теперь вы знаете, как рассчитать Z-Score набора данных. Оставьте комментарий ниже, если у вас есть вопросы или другие полезные советы по расчету Z-Score в Excel.

Мы рекомендуем читать руководства по Excel на расчет дисперсии, удаление повторяющихся строк в наборе данных, и как использовать сводные функции в Excel Обобщить данные.