Excel 中 CONFIDENCE 统计函数的说明

CONFIDENCE (alpha、sigma、n) 函数返回一个值，可用于构造总体平均值的置信区间。 置信区间是一系列以已知样本平均值为中心的值。 假定样本中的观测值来自具有已知标准偏差 sigma 的正态分布，样本中的观测值数为 n。

CONFIDENCE(alpha,sigma,n)

参数：Alpha 为概率，0 < alpha < 1。 Sigma 是一个正数，n 是对应于样本大小的正整数。

通常，alpha 是一个小概率，例如 0.05。

假设智能商 (IQ) 分数遵循标准偏差 15 的正态分布。 你在本地学校测试 50 名学生样本的 IQ，并获取样本平均值 105。 你希望计算总体平均值的 95% 置信区间。 95% 或 0.95 置信区间对应于 alpha = 1 – 0.95 = 0.05。

若要说明 CONFIDENCE 函数，请创建一个空白 Excel 工作表，复制下表，然后选择空白 Excel 工作表中的单元格 A1。 在“编辑”菜单上，单击“粘贴”。

注意: 在 Excel 2007 中，单击“开始”选项卡上的“剪贴板”组中的“粘贴”。

下表中的条目填充工作表中的单元格 A1：B7。

阿 尔 法

0.05

stdev

15

n

50

示例平均值

105

=CONFIDENCE (B1，B2，B3)

=NORMSINV (1 - B1/2) *B2/SQRT (B3)

将此表粘贴到新的 Excel 工作表后，单击“粘贴选项”按钮，然后单击“匹配目标格式”。

在粘贴的区域仍处于选中状态的情况下，指向“格式”菜单上的“列”，然后单击“自动调整所选内容”。

注意: 在 Excel 2007 中，选中粘贴的单元格区域后，单击“开始”选项卡上“单元格”组中的“格式”，然后单击“自动调整列宽”。

单元格 A6 显示 CONFIDENCE 的值。 单元格 A7 显示相同的值，因为调用 CONFIDENCE (alpha、sigma、n) 返回计算结果：

NORMSINV(1 – alpha/2) * sigma / SQRT(n)

未直接更改 CONFIDENCE，但在 Microsoft Excel 2002 中改进了 NORMSINV，然后在 Excel 2002 和 Excel 2007 之间进行了更多改进。 因此，CONFIDENCE 可能会返回不同的 (和改进的) 结果在这些更高版本的 Excel 中，因为 CONFIDENCE 依赖于 NORMSINV。

这并不意味着你应该对早期版本的 Excel 失去信心。 在 NORMSINV 中，其参数的值通常非常接近 0 或非常接近 1 时出现不准确之处。 实际上，alpha 通常设置为 0.05、0.01 或 0.001。 alpha 的值必须比该值小得多，例如 0.0000001，然后才能注意到 NORMSINV 中的舍入错误。

注意: 有关 NORMSINV 中的计算差异的讨论，请参阅有关 NORMSINV 的文章。

有关更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：

826772 Excel 统计函数：NORMSINV

Excel 2003 和 Excel 2007 的 Excel 帮助文件已重写，因为所有早期版本的帮助文件都提供了有关解释结果的误导性建议。 该示例指出，“假设我们观察到，在 50 名通勤者的示例中，上班的平均行程长度为 30 分钟，总体标准偏差为 2.5。 我们可以 95% 的置信度，即总体平均值在 30 +/- 0.692951“中，其中 0.692951 是 CONFIDENCE (0.05、2.5、50) 返回的值。

对于同一示例，结论为“上班的平均行程长度为 30 ± 0.692951 分钟，即 29.3 到 30.7 分钟。 大概，这也是关于总体平均值的声明 ，其概率为 0.95，在间隔内 [30 – 0.692951， 30 + 0.692951]。

在执行生成此示例数据的实验之前，经典统计学家 (而不是贝叶斯统计学家) 不能对总体平均值的概率分布做出任何陈述。 相反，经典统计学家处理假设检验。

例如，经典统计学家可能希望根据已知标准偏差 (（如 2.5) ）的正态分布的假设进行双侧假设检验，该假设基于已知标准偏差（例如 2.5) 、总体平均值的特定预选值 μ0）和预先选择的显著性水平 (（如 0.05) ）。 测试的结果将基于观察到的样本平均值的值 (例如 30) ，如果观察到的样本平均值在任一方向上与 μ0 太远，则总体平均值为 μ0 的零假设将被拒绝在显著性级别 0.05。 如果拒绝零假设，则解释为，样本意味着，在假设 μ0 是真正的总体平均值的假设下，小于 5% 的时间偶然发生远离 μ0。 执行此测试后，经典统计学家仍无法对总体平均值的概率分布做出任何陈述。

另一方面，贝叶斯统计学家将从总体平均值的假设概率分布开始， (命名为先验分布) ，以与经典统计学家相同的方式收集实验证据，并使用此证据修改其总体平均值的概率分布，从而获得后向分布。 Excel 不提供有助于贝叶斯统计学家进行此努力的统计函数。 Excel 的统计函数全部面向经典统计学家。

置信区间与假设检验相关。 鉴于实验证据，置信区间对假设总体平均值 μ0 的值作出简明的陈述，该值将产生对总体平均值为 μ0 的零假设的接受，而 μ0 的值将产生对总体平均值为 μ0 的零假设的拒绝。 经典统计学家不能就总体平均值在任何特定间隔内下降的可能性做出任何陈述，因为她或他从未对此概率分布做出先验假设，如果人们要使用实验证据来修改这些假设，则需要这种假设。

使用本部分开头的示例探索假设检验与置信区间之间的关系。 通过上一部分所述的 CONFIDENCE 和 NORMSINV 之间的关系，你已：

CONFIDENCE(0.05, 2.5, 50) = NORMSINV(1 – 0.05/2) * 2.5 / SQRT(50) = 0.692951

由于样本平均值为 30，因此置信区间为 30 +/- 0.692951。

现在考虑一个双侧假设检验，其显著性级别为 0.05（如前面所述），假定标准偏差为 2.5 的正态分布、样本大小为 50 和特定的假设总体平均值 μ0。 如果这是真正的总体平均值，则样本平均值将来自具有总体平均值 μ0 和标准偏差的正态分布，2.5/SQRT (50) 。 此分布大约是 μ0 的对称分布，如果 ABS (样本平均值 -μ0) > 一些截止值，则你希望拒绝 null 假设。 如果 μ0 是真正的总体平均值，则样本平均值的值 （μ0 高于此截止值）或值 μ0 –样本平均值高于此截止值时，每个样本平均值的概率为 0.05/2。 此截止值为

NORMSINV(1 – 0.05/2) * 2.5/SQRT(50) = CONFIDENCE(0.05, 2.5, 50) = 0. 692951

因此，如果以下陈述之一为 true， (总体平均值 = μ0) 拒绝零假设：

sample mean - μ0 > 0. 692951 0 – 样本平均值 > 0。 692951

由于示例中的示例 mean = 30，这两个语句将成为以下语句：

30 - μ0 > 0. 692951 μ0 – 30 > 0. 692951

重写它们以便左侧仅显示 μ0 会生成以下语句：

μ0 < 30 - 0. 692951 μ0 > 30 + 0。 692951

这些值正好不是置信区间 [30 – 0.692951， 30 + 0.692951] 中的 μ0 值。 因此，置信区间 [30 – 0.692951， 30 + 0.692951] 包含那些 μ0 的值，其中，给定样本证据，不会拒绝总体平均值为 μ0 的零假设。 对于此间隔外的 μ0 值，在给定样本证据的情况下，将拒绝总体平均值为 μ0 的零假设。

在早期版本的 Excel 中，通常对于 NORMSINV (p) 中极小或极大型的 p 值，会出现不准确的情况。 CONFIDENCE 是通过调用 NORMSINV (p) 来评估的，因此 NORMSINV 的准确性是 CONFIDENCE 用户可能关心的问题。 但是，在实践中使用的 p 值不太可能足够极端，导致 NORMSINV 中出现重大舍入错误，并且 CONFIDENCE 的性能不应成为任何版本的 Excel 用户关注的问题。

本文的大部分内容都侧重于解释 CONFIDENCE 的结果。 换句话说，我们问，“置信区间的含义是什么？ 置信区间经常被误解。 遗憾的是，Excel 2003 之前的所有 Excel 版本中的 Excel 帮助文件都导致了这种误解。 Excel 2003 帮助文件已得到改进。