如何使用Microsoft Excel计算Z分数
Z分数是一个统计值，它告诉您特定值与整个数据集的平均值相差多少标准偏差。您可以使用AVERAGE和STDEV.S或STDEV.P公式来计算数据的平均值和标准偏差，然后使用这些结果来确定每个值的Z得分。
什么是Z分数？AVERAGE，STDEV.S和STDEV.P函数的作用是什么？
Z分数是比较来自两个不同数据集的值的简单方法。它定义为距数据点均值的标准偏差数。通用公式如下所示：
=（DataPoint-AVERAGE（DataSet））/ STDEV（DataSet）
这是一个示例，可以帮助您弄清楚。假设您想比较两个由不同老师教的代数学生的测试结果。您知道第一位学生在一门课程的期末考试中获得了95％的成绩，而另一门课程的学生则获得了87％的分数。
乍一看，95％的成绩会给人留下深刻的印象，但是，如果第二班的老师考试更加困难，该怎么办？您可以根据每个班级的平均分数和每个班级的分数标准差来计算每个学生分数的Z分数。比较两个学生的Z分数，可以发现得分为87％的学生比其他班级的学生表现要好于得分为98％的学生比其他班级的学生。
您需要的第一个统计值是“平均值”，而Excel的“ AVERAGE”功能将计算该值。它只是将一个单元格范围内的所有值相加，然后将总和除以包含数值的单元格数量（忽略空白单元格）。
我们需要的另一个统计值是“标准偏差”，而Excel具有两个不同的功能，可以用略有不同的方式计算标准偏差。
早期版本的Excel仅具有“ STDEV”功能，该功能可在将数据视为总体的“样本”时计算标准偏差。 Excel 2010将其分为两个用于计算标准偏差的函数：
- STDEV.S： 此功能与先前的“ STDEV”功能相同。在将数据视为总体的“样本”时，它会计算标准差。人口样本可能类似于为研究项目收集的特定蚊子，或被留出并用于碰撞安全测试的汽车。
- STDEV.P： 该函数在将数据视为整体时将计算标准差。整个人口就像地球上的所有蚊子或特定模型生产中的每辆汽车一样。
您选择的是基于您的数据集的。差异通常很小，但是对于相同的数据集，“ STDEV.P”功能的结果将始终小于“ STDEV.S”功能的结果。假设数据存在更多可变性是一种较为保守的方法。
让我们看一个例子
对于我们的示例，我们有两列（“值”和“ Z分数”）以及三个“帮助”单元格，用于存储“ AVERAGE”，“ STDEV.S”和“ STDEV.P”函数的结果。 “值”列包含以500为中心的十个随机数，“ Z分数”列是我们将使用存储在“帮助”单元格中的结果来计算Z分数的位置。
首先，我们将使用“ AVERAGE”函数计算值的平均值。选择要存储“ AVERAGE”功能结果的单元格。
输入以下公式，然后按 进入 -或者-使用“公式”菜单。
=平均（E2：E13）
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，然后单击“平均”。
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。您无需担心“ Number2”字段。
现在按“确定”。
接下来，我们需要使用“ STDEV.S”或“ STDEV.P”函数来计算值的标准偏差。在此示例中，我们将向您展示如何计算两个值（从“ STDEV.S”开始）。选择要存储结果的单元格。
要使用“ STDEV.S”功能计算标准偏差，请键入此公式，然后按Enter键（或通过“公式”菜单访问它）。
= STDEV.S（E3：E12）
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“ STDEV.S”命令。
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。您也不必担心此处的“ Number2”字段。
现在按“确定”。
接下来，我们将使用“ STDEV.P”功能计算标准偏差。选择要存储结果的单元格。
要使用“ STDEV.P”功能计算标准偏差，请键入此公式并按Enter（或通过“公式”菜单访问它）。
= STDEV.P（E3：E12）
要通过“公式”菜单访问该功能，请选择“更多功能”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“ STDEV.P”公式。
在“函数参数”窗口中，选择“值”列中的所有单元格作为“ Number1”字段的输入。同样，您无需担心“ Number2”字段。
现在按“确定”。
现在，我们已经计算出数据的均值和标准差，我们就拥有了计算Z分数所需的全部条件。我们可以使用一个简单的公式来引用包含“ AVERAGE”和“ STDEV.S”或“ STDEV.P”函数结果的单元格。
在“ Z分数”列中选择第一个单元格。在此示例中，我们将使用“ STDEV.S”函数的结果，但是您也可以使用“ STDEV.P”的结果。
输入以下公式，然后按Enter：
=（E3- $ G $ 3）/ $ H $ 3
或者，您可以使用以下步骤输入公式，而不用输入：
- 单击单元格F3并键入
=( - 选择单元格E3。 （您可以按 左箭头键 一次或使用鼠标）
- 输入减号
- - 选择单元格G3，然后按 F4 添加“ $”字符以对单元格进行“绝对”引用（它将在“ G3”>“$G$3”>“ G$3″ > “$G3”>“ G3”，如果您继续按 F4)
- 类型
)/ - 选择单元格H3（如果使用的是“ STDEV.P”，则选择I3），然后按 F4 添加两个“ $”字符。
- 按Enter
已经为第一个值计算了Z分数。它比平均值低0.15945标准偏差。要检查结果，可以将标准偏差乘以该结果（6.271629 * -0.15945），并检查结果是否等于该值与平均值之间的差（499-500）。这两个结果相等，因此该值很有意义。
让我们计算其余值的Z分数。从包含公式的单元格开始突出显示整个“ Z分数”列。
按Ctrl + D，它将在顶部的单元格中向下复制所有其他选定的单元格中的公式。
现在，该公式已“填充”到所有单元格，并且由于使用“ $”字符，每个公式始终将引用正确的“ AVERAGE”和“ STDEV.S”或“ STDEV.P”单元格。如果出现错误，请返回并确保输入的公式中包含“ $”字符。
在不使用“帮助程序”单元格的情况下计算Z得分
辅助单元格存储结果，就像存储“ AVERAGE”，“ STDEV.S”和“ STDEV.P”功能的结果一样。它们可能有用，但并不总是必需的。您可以使用以下通用公式来计算Z分数时完全跳过它们。
这是使用“ STDEV.S”功能的一种：
=（Value-AVERAGE（值））/ STDEV.S（值）
还有一个使用“ STEV.P”功能的代码：
=（Value-AVERAGE（值））/ STDEV.P（值）
在函数中为“值”输入单元格范围时，请确保添加绝对引用（使用F4的“ $”），以便在“填充”时不会计算其他范围的平均值或标准偏差每个公式中的单元格数
如果数据量较大，则使用辅助单元可能会更有效，因为它不会每次都计算“ AVERAGE”和“ STDEV.S”或“ STDEV.P”函数的结果，从而节省了处理器资源和加快了计算结果所需的时间。
同样，“ $ G $ 3”比“ AVERAGE（$ E $ 3：$ E $ 12）”占用更少的字节存储空间和更少的RAM装载。这很重要，因为标准的32位版本的Excel限于2GB RAM（64位版本对可以使用多少RAM没有任何限制）。
