KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中计算 z 分数：全面指南

作者Amanda Li修改日期

在统计学与数据分析领域，把握数据点与平均值之间的相对关系至关重要。z 分数（又称标准分数）提供了一种精准的度量方式，以标准差为单位，衡量数据点偏离数据集均值的程度。无论您分析的是考试成绩、财务数据，还是其他任何数值型数据集，计算 z 分数都能为您深入洞察数据行为提供有力支持。

使用 Excel 计算 z 分数既简单又高效，可快速分析大型数据集，轻松实现标准化比较与异常值检测。本教程将带您了解 z 分数的定义、如何在 Excel 中计算 z 分数、提供实用公式示例、解读数据中的 z 分数含义，并分享执行这些计算时的关键技巧。

计算Z分数的通用公式的截图

什么是 z 分数？

z 分数（又称标准分数）是一种统计指标，用于衡量特定数据点与数据集均值之间的距离，并以标准差为单位进行表达。该指标对于判断数据点偏离平均值的程度及方向（高于或低于均值）至关重要。本质上，z 分数将数据点转换至统一尺度，从而便于在不同数据集或不同总体之间进行直接比较，无论其原始测量单位或分布形态如何。

z 分数的概念与正态分布紧密相关。正态分布是统计学中的一个核心概念，其特点是大多数观测值围绕中心峰值聚集，且数值出现的概率从均值向两侧对称递减。在正态分布的背景下：

统计学中正态分布曲线的示意图

  • 约 68% 的数据落在均值的一个标准差（±1 z 分数）范围内，表明其与平均值仅有中等程度的偏离。
  • 约 95% 的观测值落在两个标准差（±2 z 分数）范围内，呈现出显著但非极端的偏离。
  • 近 99.7% 的数据落在三个标准差（±3 z 分数）范围内，几乎囊括了分布中的全部观测值，并清晰凸显出极端偏离的情况。

z 分数是统计分析中的关键工具，能够将来自不同数据集的单个观测值标准化，从而轻松比较不同分布中的得分。通过转换为 z 分数，可直观判断某一观测值在特定分布中是异常还是典型，使其成为离群值检测、假设检验和数据标准化等众多应用场景中不可或缺的利器。

如何在 Excel 中计算 Z 分数？

Excel 中没有可直接计算 z 分数的单一专用函数。您需先计算数据集的均值（μ）和标准差（σ），获得这些关键统计量后，即可通过以下两种主要方法确定 z 分数：

  • 手动计算方法：应用 z 分数公式：
    =(x-μ)/σ
  • 其中：
  • x 是您正在检查的数据点，
    μ 是您数据集的均值，
    σ 是您数据集的标准差。
  • 使用 STANDARDIZE 函数：若采用更集成的方法，Excel 的 STANDARDIZE 函数可直接根据数据点、均值和标准差计算 z 分数：
    =STANDARDIZE(x, mean, standard_dev)

在 Excel 中计算 z 分数的公式示例

假设您在 A 列中拥有从 A2 到 A101 的数据集，以下是计算这些值 z 分数的方法：

  1. 计算均值（μ）：使用 AVERAGE(range)函数即可快速求出您数据集的均值（μ）。
    =AVERAGE(A2:A101)
    展示如何在Excel中使用AVERAGE函数计算平均值的截图
  2. 计算标准差（σ）：请根据您的数据背景选择合适的公式。
    重要提示：为确保计算精准，务必选用适合您数据集的函数。（例如，对于代表总体的 A2:A101 数据，应使用第一个公式。）
    • 如果您的数据代表整个总体（即这些值并非从更大群体中抽样而来），请使用 STDEV.P(range)函数。
      =STDEV.P(A2:A101)
    • 如果您的数据是更大总体的一个样本，或希望基于样本估算总体标准差，请使用 STDEV.S(range)函数。
      =STDEV.S(A2:A101)
      展示如何在Excel中使用STDEV.P函数计算标准差的截图
  3. 计算 A2 中数据点的 z 分数：使用以下任一公式，结果相同。（本例中，我们将选用第二个公式。）
    • 手动计算的方法是：从数据点中减去均值，再将结果除以标准差。
      =(A2 - $E$2) / $E$3
    • 使用 STANDARDIZE(x, mean, standard_dev)函数。
      =STANDARDIZE(A2, $E$2, $E$3)

      注意：美元符号（$）可确保公式始终引用特定单元格（E2 为均值，E3 为标准差），无论公式复制到何处。

      展示如何在Excel中为单个数据点计算Z分数的截图

  4. 为数据集中每个值计算 z 分数：将第 3 步中的公式向下复制到整列，即可为数据集中的每个值计算 z 分数。提示：双击单元格的填充柄，即可快速扩展公式。展示如何将Z分数公式向下复制到Excel列中的GIF动图
提示：
  • 为了在整个数据集中更便捷地计算 z 分数，无需在不同单元格中分别输入均值和标准差公式，您可直接使用以下任一综合公式。
    =(A2 - AVERAGE($A$2:$A$101)) / STDEV.P($A$2:$A$101)
    =STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))
  • 在科学与统计工作中，将 z 分数统一保留三位小数以确保精度一致，是一种值得推崇的做法。您只需选中 z 分数所在单元格，然后在“开始”选项卡的“数字”组中，点击“减少小数位数”即可轻松实现。

    Excel中用于将Z分数格式化为三位小数的“减少小数位数”选项的截图

解读数据中的 z 分数

z 分数对于理解数据点在数据集中的位置及其意义至关重要——它直接衡量该数据点距离均值有多少个标准差，从而清晰揭示其相对位置与罕见程度。

与均值的关系
  • z 分数 = 0：表示该数据点恰好位于均值处，属于平均水平。
  • z 分数 > 0：表示数值高于平均水平，距离均值越远，表现越突出。
  • z 分数 < 0：表示低于平均水平的数值，分数越低，偏离均值的程度越大。
偏离程度
  • |z 分数| < 1：这些数据点接近平均水平，位于正态分布的主体范围内，表明表现正常。
  • |z 分数| < 2：表明该观测值与均值存在中等程度的偏离，虽不常见，但仍处于正常变异范围内。
  • |z 分数| > 2：突出显示显著偏离均值的异常数据点，可能表明存在离群值或严重背离预期常态的情况。

示例说明：

  • z 分数为 0.66 表示该数据点比均值高出 0.66 个标准差，说明其高于平均水平，但仍相对接近均值，处于典型的变异范围之内。
  • 相反，z 分数为-2.1 表示该数据点低于均值 2.1 个标准差，显著偏离平均水平，明显处于典型范围之外。

在 Excel 中计算 z 分数时需注意的事项

在 Excel 中计算 Z 分数时，精度与准确性至关重要。为确保结果可靠，请务必关注以下关键事项：

  • 检查数据是否符合正态分布：z 分数最适用于呈正态分布的数据。若您的数据集偏离正态分布，z 分数可能不适合作为分析工具。在使用 z 分数进行分析前，请务必先进行正态性检验。
  • 确保正确使用公式：请根据数据集特性选择合适的标准差函数——STDEV.P 用于总体，STDEV.S 用于样本。
  • 在 z 分数公式中对均值和标准差使用绝对引用：在多个单元格中应用公式时，请对均值和标准差使用绝对引用（例如）$A$1），以确保计算结果一致。
  • 警惕离群值：离群值会显著影响均值与标准差，进而导致计算出的 z 分数出现偏斜。
  • 确保数据完整性：在计算 z 分数前，请务必确保数据集干净且无错误。错误的数据条目、重复项或无关数值会显著影响均值与标准差，进而导致 z 分数产生误导性结果。
  • 避免过早四舍五入或截断：Excel 能够处理大量小数位，保留这些位数有助于防止累积的舍入误差扭曲最终分析结果。

以上即为在 Excel 中计算 z 分数的全部相关内容，希望本教程对您有所帮助！如需掌握更多 Excel 实用技巧，请点击此处，立即访问我们涵盖数千篇教程的丰富资源库，轻松提升工作效率！