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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выберите ячейку в столбце «Стандартное отклонение», введите = СТАНДОТКЛОН.P (, затем выберите первое значение в диапазоне нажмите ключ столбца, выберите последнее значение, введите закрывающая скобкаи нажмите Войти. Если вы сомневаетесь, полученная формула должна быть похожей на приведенную ниже:

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

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

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

1. Перейти к Формулы вкладка и выберите Больше формул.

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

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

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

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

1. Наконец, введите ссылку на ячейку стандартного отклонения в поле 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. нажимать Войти для выполнения формулы.

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

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

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

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

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

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

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

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

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