Z分数是一个统计值，表示数据点与整个数据集平均值的偏差标准差数。Excel提供了AVERAGE和STDEV.S或STDEV.P函数来计算平均值和标准差，从而计算Z分数。STDEV.S用于处理数据样本，而STDEV.P适用于整个数据集。通过使用这些函数，您可以比较不同数据集中的值，例如比较不同老师教授的代数学生的成绩。在Excel中，可以通过选择合适的函数和使用公式计算每个值的Z分数，无需额外辅助单元格。
A Z-Score is a statistical value that tells you how many standard deviations a particular value happens to be from the mean of the entire data set. You can use AVERAGE and STDEV.S or STDEV.P formulas to calculate the mean and standard deviation of your data and then use those results to determine the Z-Score of each value.
Z分数是一个统计值，它告诉您特定值与整个数据集的平均值相差多少标准偏差。 您可以使用AVERAGE和STDEV.S或STDEV.P公式来计算数据的平均值和标准偏差，然后使用这些结果确定每个值的Z得分。
什么是Z分数？AVERAGE，STDEV.S和STDEV.P函数的作用是什么？ (What is a Z-Score and what do the AVERAGE, STDEV.S, and STDEV.P functions do?)
A Z-Score is a simple way of comparing values from two different data sets. It is defined as the number of standard deviations away from the mean a data point lies. The general formula looks like this:
Z分数是比较来自两个不同数据集的值的简单方法。 定义为距数据点均值的标准偏差数。 通用公式如下所示：
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Here’s an example to help clarify. Say you wanted to compare the test results of two Algebra students taught by different teachers. You know the first student got a 95% on the final exam in one class, and the student in the other class scored 87%.
这是一个示例以帮助阐明。 假设您想比较两名由不同老师教的代数学生的测试结果。 您知道第一位学生在一门课程的期末考试中获得了95％的成绩，而另一门课程的学生则获得了87％的分数。
At first glance, the 95% grade is more impressive, but what if the teacher of the second class gave a more difficult exam? You could calculate the Z-Score of each student’s score based on the average scores in each class and the standard deviation of the scores in each class. Comparing the Z-Scores of the two students could reveal that the student with the 87% score did better in comparison to the rest of their class than the student with the 98% score did in comparison to the rest of their class.
乍一看，95％的成绩会给人留下深刻的印象，但是，如果第二班的老师考试难度更高怎么办？ 您可以根据每个班级的平均分数和每个班级的分数标准差来计算每个学生分数的Z分数。 比较两个学生的Z分数，可以发现得分为87％的学生比其他班级的学生要好，得分为98％的学生比其他班级的学生要好。
The first statistical value you need is the ‘mean’ and Excel’s “AVERAGE” function calculates that value. It simply adds up all of the values in a cell range and divides that sum by the number of cells containing numerical values (it ignores blank cells).
您需要的第一个统计值是“平均值”，Excel的“ AVERAGE”功能将计算该值。 它只是将一个单元格范围内的所有值相加，然后将其总和除以包含数值的单元格数量(忽略空白单元格)。
The other statistical value we need is the ‘standard deviation’ and Excel has two different functions to calculate the standard deviation in slightly different ways.
我们需要的另一个统计值是“标准偏差”，Excel具有两个不同的函数，它们以略有不同的方式计算标准偏差。
Previous versions of Excel only had the “STDEV” function, which calculates the standard deviation while treating the data as a ‘sample’ of a population. Excel 2010 broke that into two functions that calculate the standard deviation:
早期版本的Excel仅具有“ STDEV”功能，该功能可在将数据视为总体的“样本”时计算标准偏差。 Excel 2010将其分为两个用于计算标准偏差的函数：
STDEV.S: This function is identical to the previous “STDEV” function. It calculates the standard deviation while treating the data as a ‘sample’ of a population. A sample of a population might be something like the particular mosquitoes collected for a research project or cars that were set aside and used for crash safety testing.
STDEV.S：此功能与先前的“ STDEV”功能相同。 它在将数据视为总体的“样本”时计算标准差。 人口样本可能类似于为研究项目收集的特定蚊子，或被留出并用于碰撞安全测试的汽车。
STDEV.P: This function calculates the standard deviation while treating the data as the entire population. An entire population would be something like all mosquitoes on Earth or every car in a production run of a specific model.
STDEV.P：该函数在将数据视为整体时将计算标准偏差。 整个人口就像地球上的所有蚊子或特定模型生产中的每辆汽车一样。
Which you choose is based on your data set. The difference will usually be small, but the result of the “STDEV.P” function will always be smaller than the result of the “STDEV.S” function for the same data set. It is a more conservative approach to assume there is more variability in the data.
您选择的是基于数据集的。 差异通常很小，但是对于相同的数据集，“ STDEV.P”功能的结果将始终小于“ STDEV.S”功能的结果。 假定数据存在更多可变性是一种较为保守的方法。
让我们看一个例子 (Let’s Look at an Example)
For our example, we have two columns (“Values” and “Z-Score”)and three “helper” cells for storing the results of the “AVERAGE,” “STDEV.S,” and “STDEV.P” functions. The “Values” column contains ten random numbers centered around 500, and the “Z-Score” column is where we will calculate the Z-Score using the results stored in the ‘helper’ cells.
对于我们的示例，我们有两列(“值”和“ Z分数”)和三个“帮助”单元格，用于存储“ AVERAGE”，“ STDEV.S”和“ STDEV.P”函数的结果。 “值”列包含以500为中心的十个随机数，“ Z分数”列是我们将使用存储在“帮助”单元格中的结果来计算Z分数的位置。
First, we will calculate the mean of the values using the “AVERAGE” function. Select the cell where you will store the result of the “AVERAGE” function.
首先，我们将使用“ AVERAGE”功能计算值的平均值。 选择要存储“ AVERAGE”功能结果的单元格。
Type in the following formula and press enter -or- use the “Formulas” menu.
键入以下公式，然后按Enter键-或者-使用“公式”菜单。
=AVERAGE(E2:E13)
To access the function through the “Formulas” menu, select the “More Functions” drop-down, select the “Statistical” option, and then click on “AVERAGE.”
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，然后单击“平均”。
In the Function Arguments window, select all of the cells in the “Values” column as the input for the “Number1” field. You don’t need to worry about the “Number2” field.
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。 您无需担心“ Number2”字段。
Now press “OK.”
现在按“确定”。
Next, we need to calculate the standard deviation of the values using either the “STDEV.S” or “STDEV.P” function. In this example, we will show you how to calculate both values, starting with “STDEV.S.” Select the cell where the result will be stored.
接下来，我们需要使用“ STDEV.S”或“ STDEV.P”函数来计算值的标准偏差。 在此示例中，我们将向您展示如何从“ STDEV.S”开始计算两个值。 选择要存储结果的单元格。
To calculate the standard deviation using the “STDEV.S” function, type in this formula and press Enter (or access it through the “Formulas” menu).
要使用“ STDEV.S”功能计算标准偏差，请键入此公式并按Enter(或通过“公式”菜单访问它)。
=STDEV.S(E3:E12)
To access the function through the “Formulas” menu, select the “More Functions” drop-down, select the “Statistical” option, scroll down a bit, and then click the “STDEV.S” command.
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“ STDEV.S”命令。
In the Function Arguments window, select all of the cells in the “Values” column as the input for the “Number1” field. You don’t need to worry about the “Number2” field here, either.
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。 您也不必担心此处的“ Number2”字段。
Now press “OK.”
现在按“确定”。
Next, we will calculate the standard deviation using the “STDEV.P” function. Select the cell where the result will be stored.
接下来，我们将使用“ STDEV.P”功能计算标准偏差。 选择要存储结果的单元格。
To calculate the standard deviation using the “STDEV.P” function, type in this formula and press Enter (or access it through the “Formulas” menu).
要使用“ STDEV.P”功能计算标准偏差，请键入此公式并按Enter(或通过“公式”菜单访问它)。
=STDEV.P(E3:E12)
= STDEV.P(E3：E12)
To access the function through the “Formulas” menu, select the “More Functions” drop-down, select the “Statistical” option, scroll down a bit, and then click the “STDEV.P” formula.
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“ STDEV.P”公式。
In the Function Arguments window, select all of the cells in the “Values” column as the input for the “Number1” field. Again, you won’t need to worry about the “Number2” field.
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。 同样，您无需担心“ Number2”字段。
Now press “OK.”
现在按“确定”。
Now that we have calculated the mean and standard deviation of our data, we have all we need to calculate the Z-Score. We can use a simple formula that references the cells containing the results of the “AVERAGE” and “STDEV.S” or “STDEV.P” functions.
现在，我们已经计算出数据的均值和标准差，我们已经拥有了计算Z分数所需的全部条件。 我们可以使用一个简单的公式来引用包含“ AVERAGE”和“ STDEV.S”或“ STDEV.P”函数结果的单元格。
Select the first cell in the “Z-Score” column. We will use the result of the “STDEV.S” function for this example, but you could also use the result from “STDEV.P.”
在“ Z分数”列中选择第一个单元格。 在此示例中，我们将使用“ STDEV.S”函数的结果，但是您也可以使用“ STDEV.P”的结果。
Type in the following formula and hit Enter:
输入以下公式，然后按Enter：
=(E3-$G$3)/$H$3
Alternatively, you could use the following steps to enter the formula instead of typing:
或者，您可以使用以下步骤输入公式，而不用输入：
Click cell F3 and type
=(单击单元格F3并键入
=(Select cell E3. (You can press the left-arrow-key once or use the mouse)
选择单元格E3。 (您可以按一次左箭头键或使用鼠标)
Type the minus sign
-输入减号
-Select cell G3 then press F4 to add the “$” characters to make an ‘absolute’ reference to the cell (it will cycle through “G3” > “$G$3″ > “G$3″ > “$G3″ > “G3” if you continue pressing F4)
选择单元格G3，然后按F4键添加“ $”字符，以对该单元格做出“绝对”引用(它将循环通过“ G3”>“ $ G $ 3”>“ G $ 3”>“ $ G3”>如果继续按F4，则为“ G3”)
Type
)/类型
)/Select cell H3 (or I3 if you are using “STDEV.P”) and press F4 to add the two “$” characters.
选择单元格H3(如果使用的是“ STDEV.P”，则选择I3)，然后按F4键添加两个“ $”字符。
- Press Enter 按Enter
The Z-Score has been calculated for the first value. It is 0.15945 standard deviations below the mean. To check the results, you can multiply the standard deviation by this result (6.271629 * -0.15945) and check that the result is equal to the difference between the value and the mean (499-500). Both results are equal, so the value makes sense.
已为第一个值计算了Z分数。 它比平均值低0.15945标准偏差。 要检查结果，可以将标准偏差乘以该结果(6.271629 * -0.15945)，并检查结果是否等于该值与平均值之间的差(499-500)。 两个结果相等，因此该值有意义。
Let’s calculate the Z-Scores of the rest of the values. Highlight the whole ‘Z-Score’ column starting with the cell containing the formula.
让我们计算其余值的Z分数。 从包含公式的单元格开始突出显示整个“ Z-Score”列。
Press Ctrl+D, which copies the formula in the top cell down through all the other selected cells.
按Ctrl + D，将公式复制到顶部其他所有选定单元格的顶部单元格中。
Now the formula has been ‘filled-down’ to all of the cells, and each will always reference the correct “AVERAGE” and “STDEV.S” or “STDEV.P” cells because of the “$” characters. If you get errors, go back and make sure the “$” characters are included in the formula you entered.
现在，该公式已“填充”到所有单元格中，并且由于使用“ $”字符，每个公式始终将引用正确的“ AVERAGE”和“ STDEV.S”或“ STDEV.P”单元格。 如果出现错误，请返回并确保输入的公式中包含“ $”字符。
在不使用“辅助”单元的情况下计算Z分数 (Calculating the Z-Score without using ‘Helper’ Cells)
Helper cells store a result, like the ones storing the results of the “AVERAGE,” “STDEV.S,” and “STDEV.P” functions. They can be useful but aren’t always necessary. You can skip them altogether when calculating a Z-Score by using the following generalized formulas, instead.
辅助单元存储结果，就像存储“ AVERAGE”，“ STDEV.S”和“ STDEV.P”功能的结果一样。 它们可能有用，但并不总是必需的。 您可以使用以下通用公式来计算Z分数时完全跳过它们。
Here’s one using the “STDEV.S” function:
这是使用“ STDEV.S”功能的一个：
=(Value-AVERAGE(Values))/STDEV.S(Values)
And one using the “STEV.P” function:
还有一个使用“ STEV.P”功能的代码：
=(Value-AVERAGE(Values))/STDEV.P(Values)
When entering the cell ranges for the “Values” in the functions, be sure to add absolute references (“$” using F4) so that when you ‘fill-down’ you aren’t calculating the average or standard deviation of a different range of cells in every formula.
在函数中为“值”输入单元格范围时，请确保添加绝对引用(使用F4的“ $”)，以便在“填充”时不会计算其他范围的平均值或标准偏差每个公式中的单元格数
If you have a large data set, it may be more efficient to use helper cells because it doesn’t calculate the result of the “AVERAGE” and “STDEV.S” or “STDEV.P” functions each time, saving processor resources and speeding up the time it takes to calculate the results.
如果数据量较大，则使用辅助单元可能会更有效，因为它不会每次都计算“ AVERAGE”和“ STDEV.S”或“ STDEV.P”函数的结果，从而节省了处理器资源和加快了计算结果所需的时间。
Also, “$G$3” takes fewer bytes to store and less RAM to load than “AVERAGE($E$3:$E$12).”. This is important because the standard 32-bit version of Excel is limited to 2GB of RAM (the 64-bit version does not have any limitations on how much RAM can be used).
另外，“ $ G $ 3”比“ AVERAGE($ E $ 3：$ E $ 12)”占用更少的字节存储空间和更少的RAM装载。 这很重要，因为标准的32位版本的Excel限于2GB RAM(64位版本对可以使用多少RAM没有任何限制)。
翻译自: https://www.howtogeek.com/400178/how-to-calculate-a-z-score-using-microsoft-excel/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
