如何使用Microsoft 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 e