Как вы знаете, в Microsoft Excel^{(Microsoft Excel)} можно выполнять множество математических вычислений . В этом руководстве мы познакомим вас с этапами расчета Z-показателя^(Z-Score) в Excel .

Если вы впервые используете Excel , мы рекомендуем вам потратить пять минут на изучение этого руководства для начинающих по Microsoft Excel^{(Microsoft Excel beginner’s tutorial)} . Вы узнаете, как работают основные функции, навигационные ярлыки, создание рабочих книг, форматирование данных и все, что нужно знать об использовании программы для работы с электронными таблицами в качестве новичка.

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

Что такое Z-показатель?

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

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

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

Как рассчитать Z-показатель в Excel

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

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

Вычислить среднее значение^{(Calculate Mean Average)}

Чтобы вычислить среднее значение вашего набора данных, введите =AVERAGE( , выберите первое значение^{(first value)} в наборе данных, нажмите клавишу столбца^{(column key)} , выберите последнее значение^{(last value)} в диапазоне набора данных, нажмите клавишу закрывающей скобки^{(closing parenthesis)} и нажмите Enter . Формула должна выглядеть как показано ниже:

=AVERAGE(B2:B11)

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

Рассчитать стандартное отклонение^{(Calculate Standard Deviation)}

Excel также позволяет довольно легко вычислить стандартное отклонение вашего набора данных^{(calculate the standard deviation of your dataset)} за несколько кликов. 

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

=STDEV.P(B2:B11)

Рассчитать Z-значение в Excel: метод 1^{(Calculate Z-Score in Excel: Method 1)}

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

1. Перейдите на вкладку « Формулы^(Formulas) » и выберите « Другие формулы^{(More Formulas)} » .

1. Наведите указатель мыши на параметр « Статистические данные^{(Statistical)} » и выберите « СТАНДАРТИЗИРОВАТЬ^{(STANDARDIZE)} » .

Откроется новое окно « Аргументы функции^{(Function Arguments)} », в котором вы сможете рассчитать Z-оценку^(Z-Score) распределения.

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

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

1. Наконец, введите ссылку на ячейку стандартного отклонения в поле «Standard_dev» и нажмите F4 , чтобы заблокировать ссылку на ячейку. Инструмент отобразит предварительный просмотр Z-значения^(Z-value) . Нажмите OK , чтобы продолжить.

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

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

Рассчитать Z-показатель в Excel: метод 2^{(Calculate Z-Score In Excel: Method 2)}

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

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

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

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

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

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

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

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

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

Станьте^(Become) экспертом в Excel : репозиторий учебных пособий

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

Мы рекомендуем прочитать связанные с Excel учебные пособия по вычислению дисперсии^{(calculating variance)} , удалению повторяющихся строк^{(removing duplicate rows)} в вашем наборе данных и тому, как использовать итоговые функции в Excel^{(use summary functions in Excel)} для суммирования данных. 

How to Calculate Z-Score in Excel

As you know, there are many mathematical calculations you can perform with Microsoft Excel. In this tutorial, we’ll walk yоu through the steps involved in calculating Z-Sсore in Exсel.

If this is your first time using Excel, we recommend you take five minutes to peruse this Microsoft Excel beginner’s tutorial. You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using the spreadsheet program as a beginner.

When you’re done with that, proceed to the next section to learn how to calculate Z score in Excel. But first, let’s briefly explain Z-Score, its uses, why you might need to calculate one, and how to get it done.

What Is a Z-Score?

Z-Score (also known as a “Standard Score”) is a metric that highlights the relationship between values in a distribution. More precisely, it describes the position of values in a dataset with respect to their mean and standard deviation. Z-Score allows for accurate measurement and comparison of values in a dataset.

The mathematical formula for calculating Z-Score is (x-µ) / σ; where x = Cell Value, µ = Mean, and σ = Standard Deviation.

Companies sometimes use Z-Score to forecast and estimate impending bankruptcy. Additionally, it’s a great metric for ascertaining the financial position of an institution. Researchers also utilize Z-Score to compare observations obtained from different samples or populations.

How to Calculate Z-Score in Excel

Since Z-Score is a function of mean and standard deviation, you’ll need to first calculate the average and standard deviation of your dataset. Although you can extract the mean and standard deviation in any cell, we created dedicated columns for  “Mean” and “Standard Deviation” in our worksheet. We also created a column for “Z-Score.”

Our sample document contains the performance ratings of 10 employees in a paper company. Now let’s calculate the Z-Score of the employees’ rating.

Calculate Mean Average

To calculate the mean average of your dataset, type =AVERAGE(, select the first value in the dataset, press the column key, select the last value within the dataset range, press the closing parenthesis key, and press Enter. The formula should look like the one below:

=AVERAGE(B2:B11)

You should see the mean or average value of the dataset in the cell you entered the formula.

Calculate Standard Deviation

Excel also makes it pretty easy to calculate the standard deviation of your dataset in a few clicks. 

Select a cell in the “Standard Deviation” column, type =STDEV.P(, then select the first value in the range, press the column key, select the last value, enter the closing parenthesis, and press Enter. If you’re in doubt, the resulting formula should be similar to the one below:

=STDEV.P(B2:B11)

Calculate Z-Score in Excel: Method 1

Excel has a STANDARDIZE function that provides the Z-Score of a dataset in a distribution. Select the first cell in the Z-Score column and follow the steps below.

1. Go to the Formulas tab and select More Formulas.

1. Hover your mouse on the Statistical option and select the STANDARDIZE.

That’ll launch a new Function Arguments window where you’ll be able to calculate the Z-Score of the distribution.

1. Enter the cell reference of the first value in the “X” field.

1. Enter the cell reference of the arithmetic average in the “Mean” field and press the F4 on your keyboard to lock the cell reference.

1. Finally, enter the standard deviation’s cell reference in the “Standard_dev” field and press F4 to lock the cell reference. The tool will display a preview of the Z-value. Press OK to proceed.

To get the Z-Score for other values, hover the cursor to the bottom-right corner of the cell and drag the plus (+) icon down the column.

Excel will copy the formula down the column and automatically generate the Z-Score for other values in the corresponding rows.

Calculate Z-Score In Excel: Method 2

As mentioned earlier, you can obtain a datapoint’s Z-Score by subtracting the mean of the dataset from the datapoint and dividing the result by the standard deviation. Using the (x-µ) / σ, you can calculate z-score in Excel by inputting these values manually. 

1. Select the first cell in the Z-Score column, type an equal sign (=) followed by an open parenthesis, and select the first value in the column containing the datasets whose Z-Score you want to calculate. Afterward, type a hyphen, select the arithmetic mean, press F4 to make the mean absolute/fixed, and press the close parenthesis icon. Finally, press the forward-slash (/) key, select the standard deviation, and press F4 to lock the cell reference.

The final formula should look similar to this: =(B2-$C$2)/$D$2. Press Enter to execute the formula.

Note that the formula will only calculate the Z-Score for the first value in the selected cell.

1. Hover your mouse to the bottom-right corner of the first Z-Score cell and drag the plus (+) icon down the column.

Interpreting Z-Score

Your dataset will most likely contain a mix of negative and positive Z-Scores. A positive Z-Score indicates that the value/score is higher than the dataset’s mean average. A negative Z-Score, of course, tells the opposite: the value lies below the mean average. If a datapoint’s Z-Score is zero (0), that’s because its value equals the arithmetic mean.

The bigger a data point, the higher its Z-Score. Go through your worksheet and you’ll realize that the small values have lower Z-Scores. Likewise, values smaller than the arithmetic mean will have negative Z-Scores.

In our sample worksheet, for instance, you’ll discover that “Michael” had the highest rating (78) and highest Z-Score (1.679659). “Dwight” and “Kevin” on the other hand both had the lowest ratings (34) and lowest Z-Score (-1.59047).

Become an Excel Expert: Tutorial Repository

You now know how to calculate the Z-Score of a data set. Drop a comment below if you have questions or other useful tips regarding calculating Z-Score in Excel. 

We recommend reading Excel-related tutorials on calculating variance, removing duplicate rows in your dataset, and how to use summary functions in Excel to summarize data. 

Related posts

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Как использовать анализ «что, если» в Excel

• Как исправить строку в Excel

• Подключение Excel к MySQL

• Как создать несколько связанных раскрывающихся списков в Excel

• Как отследить зависимых в Excel

• Группировать строки и столбцы на листе Excel

• Почему вы должны использовать именованные диапазоны в Excel

• 2 способа использования функции транспонирования Excel

• Что такое массив VBA в Excel и как его запрограммировать

• 10 советов и рекомендаций по Excel на 2019 год

• Как поделиться файлом Excel для удобной совместной работы

• 3 способа разделить ячейку в Excel

• Центрируйте данные рабочего листа в Excel для печати

• Как расположить по алфавиту в Excel

• Как быстро вставить несколько строк в Excel

• Как открыть Word и Excel в безопасном режиме

• 5 способов преобразовать текст в числа в Excel