Z 分数是一个统计学概念，它量化了某个数值与数据集平均值之间的偏差程度，以标准差为单位进行衡量。 你可以通过使用 AVERAGE 和 STDEV.S 或 STDEV.P 公式计算数据的平均值和标准差，然后利用这些计算结果来确定每个数值的 Z 分数。
什么是 Z 分数？AVERAGE、STDEV.S 和 STDEV.P 函数的作用是什么？
Z 分数提供了一种简便的方法来比较来自不同数据集的数值。 它表示一个数据点偏离数据集平均值的标准差数量。 其基本公式如下：
=(数据点-AVERAGE(数据集))/STDEV(数据集)
为了更好地理解，我们来看一个例子。 假设你想要比较两位不同老师所教的代数学生的测验成绩。 一位学生在期末考试中取得了 95% 的成绩，而另一位学生取得了 87% 的成绩。
乍看之下，95% 的成绩似乎更令人印象深刻。 但是，如果第二个班级的老师给出的测试难度更高呢？ 通过计算每个学生成绩相对于他们各自班级平均成绩和标准差的 Z 分数，我们可以进行更准确的比较。 比较这些 Z 分数可能会显示，尽管 87% 的学生分数较低，但相对于班级其他学生而言，他的表现实际上优于得了 95% 的学生。
首先，我们需要计算“平均值”。 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” 函数结果的单元格。
输入以下公式并按回车键 – 或 – 使用“公式”菜单。
=AVERAGE(E2:E13)
要通过 “公式” 菜单访问该函数，选择 “更多函数” 下拉菜单，选择 “统计” 选项，然后单击 “平均值”。
在 “函数参数” 窗口中，选择 “数值” 列中的所有单元格作为 “数值 1” 字段的输入。 您无需关注 “数值 2” 字段。
现在点击 “确定”。
接下来，我们需要使用 “STDEV.S” 或 “STDEV.P” 函数计算数值的标准差。 在此示例中，我们将向您演示如何计算这两个值，从 “STDEV.S” 开始。 选择用于存储结果的单元格。
要使用 “STDEV.S” 函数计算标准差，请输入此公式并按 Enter （或通过 “公式” 菜单访问）。
=STDEV.S(E3:E12)
要通过 “公式” 菜单访问该函数，选择 “更多函数” 下拉菜单，选择 “统计” 选项，向下滚动，然后单击 “STDEV.S” 命令。
在 “函数参数” 窗口中，选择 “数值” 列中的所有单元格作为 “数值 1” 字段的输入。 您也不必担心这里的 “数值 2” 字段。
现在点击 “确定”。
接下来，我们将使用 “STDEV.P” 函数计算标准差。 选择用于存储结果的单元格。
要使用 “STDEV.P” 函数计算标准差，请输入此公式并按 Enter （或通过 “公式” 菜单访问）。
=STDEV.P(E3:E12)
要通过 “公式” 菜单访问该函数，选择 “更多函数” 下拉菜单，选择 “统计” 选项，向下滚动，然后单击 “STDEV.P” 公式。
在 “函数参数” 窗口中，选择 “数值” 列中的所有单元格作为 “数值 1” 字段的输入。 同样，您无需担心 “数值 2” 字段。
现在点击 “确定”。
既然我们已经计算了数据的平均值和标准差，那么我们现在就拥有了计算 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（或 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” 函数的公式：
=(数值-AVERAGE(数值范围))/STDEV.S(数值范围)
这是使用 “STDEV.P” 函数的公式：
=(数值-AVERAGE(数值范围))/STDEV.P(数值范围)
在函数中输入 “数值” 的单元格范围时，请确保添加绝对引用（使用 F4 的 “$”），以便在 “填充” 时，它不会为每个公式中的单元格计算不同范围的平均值或标准差。
如果你有一个大型数据集，使用辅助单元格可能会更有效，因为它不会每次都计算 “AVERAGE” 和 “STDEV.S” 或 “STDEV.P” 函数的结果，从而节省处理器资源并加快计算结果的时间。
此外，“$G$3” 与 “AVERAGE($E$3:$E$12).” 相比，需要更少的字节来存储和更少的 RAM 来加载。这一点很重要，因为标准的 32 位版本的 Excel 被限制为 2GB 的 RAM（64 位版本对可以使用多少 RAM 没有任何限制）。