如何使用 Microsoft Excel 计算 Z 分数

已发表: 2022-01-29

Z 分数是一个统计值，它告诉您某个特定值恰好与整个数据集的平均值有多少标准偏差。您可以使用 AVERAGE 和 STDEV.S 或 STDEV.P 公式来计算数据的平均值和标准差，然后使用这些结果来确定每个值的 Z 分数。

什么是 Z 分数，AVERAGE、STDEV.S 和 STDEV.P 函数有什么作用？

Z 分数是比较来自两个不同数据集的值的一种简单方法。它被定义为远离数据点平均值的标准偏差数。一般公式如下所示：

 =(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)

这是一个帮助澄清的例子。假设您想比较由不同老师教的两个代数学生的测试结果。你知道第一个学生在一个班级的期末考试中得到了 95%，而另一个班级的学生得到了 87%。

乍看之下，95%的成绩更令人印象深刻，但如果二班的老师给出了更难的考试呢？您可以根据每个班级的平均成绩和每个班级的成绩标准差来计算每个学生的 Z-Score。比较这两名学生的 Z 分数可以发现，与班上其他学生相比，得分为 87% 的学生比得分为 98% 的学生与班上其他学生相比表现更好。

您需要的第一个统计值是“平均值”，Excel 的“平均”函数会计算该值。它只是将单元格范围内的所有值相加，然后将该总和除以包含数值的单元格数（它忽略空白单元格）。

我们需要的另一个统计值是“标准偏差”，Excel 有两个不同的函数来计算标准偏差，方式略有不同。

以前的 Excel 版本只有“STDEV”函数，它计算标准偏差，同时将数据视为总体的“样本”。 Excel 2010 将其分解为两个计算标准偏差的函数：

STDEV.S：此功能与之前的“STDEV”功能相同。它计算标准偏差，同时将数据视为总体的“样本”。人口样本可能类似于为研究项目收集的特定蚊子或被搁置并用于碰撞安全测试的汽车。
STDEV.P：此函数计算标准偏差，同时将数据视为整个总体。整个人口就像地球上的所有蚊子或特定型号生产中的每辆汽车。

您选择哪个取决于您的数据集。差异通常很小，但对于相同的数据集，“STDEV.P”函数的结果总是小于“STDEV.S”函数的结果。假设数据中存在更多可变性是一种更保守的方法。

让我们看一个例子

在我们的示例中，我们有两列（“Values”和“Z-Score”）和三个“helper”单元格，用于存储“AVERAGE”、“STDEV.S”和“STDEV.P”函数的结果。 “值”列包含十个以 500 为中心的随机数，“Z-Score”列是我们将使用存储在“帮助”单元格中的结果计算 Z-Score 的地方。

首先，我们将使用“AVERAGE”函数计算值的平均值。选择要存储“AVERAGE”函数结果的单元格。

输入以下公式并按回车键 - 或 - 使用“公式”菜单。

 =平均（E2：E13）

要通过“公式”菜单访问该函数，请选择“更多函数”下拉菜单，选择“统计”选项，然后单击“平均”。

在 Function Arguments 窗口中，选择“Values”列中的所有单元格作为“Number1”字段的输入。您无需担心“Number2”字段。

现在按“确定”。

接下来，我们需要使用“STDEV.S”或“STDEV.P”函数计算值的标准偏差。在此示例中，我们将向您展示如何计算这两个值，从“STDEV.S”开始。选择将存储结果的单元格。

要使用“STDEV.S”函数计算标准偏差，请输入此公式并按 Enter（或通过“公式”菜单访问）。

 =STDEV.S(E3:E12)

要通过“公式”菜单访问该函数，请选择“更多函数”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“STDEV.S”命令。

在 Function Arguments 窗口中，选择“Values”列中的所有单元格作为“Number1”字段的输入。您也不必担心这里的“Number2”字段。

现在按“确定”。

接下来，我们将使用“STDEV.P”函数计算标准偏差。选择将存储结果的单元格。

要使用“STDEV.P”函数计算标准偏差，请输入此公式并按 Enter（或通过“公式”菜单访问）。

=STDEV.P(E3:E12)

要通过“公式”菜单访问该函数，请选择“更多函数”下拉菜单，选择“统计”选项，向下滚动一点，然后单击“STDEV.P”公式。

在 Function Arguments 窗口中，选择“Values”列中的所有单元格作为“Number1”字段的输入。同样，您无需担心“Number2”字段。

现在按“确定”。

既然我们已经计算了数据的平均值和标准差，我们就拥有了计算 Z 分数所需的一切。我们可以使用一个简单的公式来引用包含“AVERAGE”和“STDEV.S”或“STDEV.P”函数结果的单元格。

选择“Z-Score”列中的第一个单元格。我们将在本例中使用“STDEV.S”函数的结果，但您也可以使用“STDEV.P”的结果。

输入以下公式，然后按 Enter：

 =(E3-$G$3)/$H$3

或者，您可以使用以下步骤来输入公式，而不是键入：

单击单元格 F3 并键入=(
选择单元格 E3。（您可以按一次左箭头键或使用鼠标）
输入减号-
选择单元格 G3 然后按F4添加“$”字符以对单元格进行“绝对”引用（它将循环通过“G3”>“ $ G $ 3”>“G $ 3”>“ $ G3”> “G3”，如果您继续按F4 ）
类型)/
选择单元格 H3（或 I3，如果您使用“STDEV.P”）并按F4添加两个“$”字符。
按回车

已为第一个值计算 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(Values))/STDEV.S(Values)

还有一个使用“STEV.P”功能：

 =(Value-AVERAGE(Values))/STDEV.P(Values)

在函数中输入“值”的单元格范围时，请务必添加绝对引用（使用 F4 的“$”），以便在“填充”时不会计算不同范围的平均值或标准偏差每个公式中的单元格。

如果您有一个大型数据集，使用辅助单元格可能更有效，因为它不会每次都计算“AVERAGE”和“STDEV.S”或“STDEV.P”函数的结果，从而节省处理器资源和加快计算结果的时间。

此外，与“AVERAGE($E$3:$E$12).”相比，“$G$3”需要更少的字节来存储和更少的 RAM 来加载。这一点很重要，因为标准 32 位版本的 Excel 被限制为 2GB 的 RAM（64 位版本对可以使用多少 RAM 没有任何限制）。