统计/数值方法/Excel 中的数值计算

本文档的目的是评估 MS Excel 在统计程序方面的准确性，并得出结论：MS Excel 是否应该用于（统计）科学目的。

根据文献，如果将 Excel 用于统计计算，则有三个主要问题领域。它们是

• 概率分布，
• 单变量统计、方差分析和估计（线性&非线性）
• 随机数生成。

如果评估统计包的结果，则应考虑到结果的可接受精度应在双精度内实现（这意味着如果结果具有 15 位有效数字，则该结果被认为是准确的），前提是可靠的算法也能在双精度内提供正确的结果。如果可靠的算法无法在双精度内检索结果，那么预期该包（评估）应该达到双精度是不公平的。因此我们可以说，评估统计包的正确方法是评估统计计算底层算法的质量，而不是只计算结果的有效数字。此外，测试问题必须是合理的，这意味着它们必须适合用已知的可靠算法解决。（McCullough & Wilson, 1999, S. 28）

在后面的部分中，我们对 MS Excel 准确性的判断将基于认证值和测试。作为基础，我们有 Knüsel 的 ELV 软件用于概率分布，StRD（统计参考数据集）用于单变量统计、方差分析和估计，最后是 Marsaglia 的 DIEHARD 用于随机数生成。每个测试和认证值将在相应部分进行解释。

评估 Excel 统计分布结果

正如我们上面提到的，我们对 Excel 概率分布计算的判断将基于 Knüsel 的 ELV 程序，该程序可以计算一些基本统计分布的概率和分位数。使用 ELV，所有分布的上下尾部概率以六位有效数字计算，概率小至 10−100，所有分布的上下分位数计算，尾部概率 P 为 10−12 ≤ P ≤ ½。（Knüsel, 2003, S.1）

在我们的基准测试中，Excel 不应该显示不准确的数字。如果显示六位数字，则所有六位数字都应该是正确的。如果算法仅精确到两位数字，则仅应显示两位数字，以免误导用户（McCullough & Wilson, 2005, S. 1245）

在以下小节中，表格中的精确值取自 Knüsel 的 ELV 软件，可接受的精度为单精度，因为即使在大多数情况下，最好的算法也无法在发出概率分布时达到 15 位正确数字。

正态分布

• Excel 函数：NORMDIST
• 参数： 均值 = 0，方差 = 1，x（临界值）
• 计算： 尾部概率 Pr X ≤ x，其中 X 表示具有标准正态分布（均值 0，方差 1）的随机变量

表 1：（Knüsel, 1998, S.376）

正如我们在表 1 中看到的，Excel 97、2000 和 XP 遇到了问题，并错误地计算了尾部的较小概率（即对于 x = -8,3 或 x = -8.2）。但是，这个问题在 Excel 2003 中得到了解决（Knüsel, 2005, S.446）。

逆正态分布

• Excel 函数： NORMINV
• 参数： 均值 = 0，方差 = 1，p（X < x 的概率）
• 计算： x 值（分位数）

X 表示具有标准正态分布的随机变量。与上一节中发出的“NORMDIST”函数相反，给出了 p 并计算分位数。

如果使用，Excel 97 会打印出具有 10 位数字的分位数，尽管如果 p 很小，则这 10 位数字中的任何一个都可能不正确。在 Excel 2000 和 XP 中，Microsoft 试图修复错误，尽管结果并不充分（见表 2）。但是，在 Excel 2003 中，问题完全得到了解决。（Knüsel, 2005, S.446）

表 2：（Knüsel, 2002, S.110）

逆卡方分布

• Excel 函数： CHIINV
• 参数： p（X > x 的概率），n（自由度）
• 计算： x 值（分位数）

X 表示具有 n 个自由度的卡方分布的随机变量。

表 3：（Knüsel , 1998, S. 376）

旧版 Excel 版本：虽然旧版 Excel 版本显示十位有效数字，但如果 p 很小，则只有极少数位数字是准确的（见表 3）。即使 p 不小，准确的数字也不足以说明 Excel 对于这种分布是足够的。

Excel 2003：问题已解决。（Knüsel, 2005, S.446）

逆 F 分布

• Excel 函数： FINV
• 参数： p（X > x 的概率），n1，n2（自由度）
• 计算： x 值（分位数）

X 表示具有 n1 和 n2 个自由度的 F 分布的随机变量。

表 4：（Knüsel , 1998, S. 377）

旧版 Excel 版本：Excel 打印出具有 7 位或更多有效数字的 x 值，尽管如果 p 很小，则只有其中的一两位数字是正确的（见表 4）。

Excel 2003：问题已解决。（Knüsel, 2005, S.446）

逆 t 分布

• Excel 函数： TINV
• 参数： p（|X| > x 的概率），n（自由度）
• 计算： x 值（分位数）

X 表示具有 n 个自由度的 t 分布的随机变量。请注意，|X| 值会导致 2 尾计算。（下尾&上尾）

表 5：（Knüsel , 1998, S. 377）

旧版 Excel： 尽管只有 1 或 2 位有效数字是正确的，但 Excel 会打印出具有 9 位或更多有效数字的分位数，如果 p 很小（参见表 5）。

Excel 2003：问题已解决。（Knüsel, 2005, S.446）

泊松分布

• Excel 函数： POISSON
• 参数： λ（均值），k（案例数）
• 计算： 尾部概率 Pr X ≤ k

X 表示具有给定参数的泊松分布的随机变量。

表 6：（McCullough & Wilson，2005，S.1246）

旧版 Excel： 正确计算了非常小的概率，但没有给出接近均值的中心概率（大约 0.5 的范围内）的结果。（参见表 6）

Excel 2003： 中心概率已修复。但是，尾部结果不准确。（参见表 6）

对于 λ150 的值，可能会遇到 Excel 的奇怪行为。（Knüsel，1998，S.375）即使对于 0.01 到 0.99 之间的中心范围内的概率，以及对于不能判断为过于极端的参数值，它也会失败。

二项分布

• Excel 函数： BINOMDIST
• 参数： n（= 试验次数），υ（= 成功概率），k（成功次数）
• 计算： 尾部概率 Pr X ≤ k

-X 表示具有给定参数的二项式分布的随机变量

表 7：（Knüsel，1998，S.375）

旧版 Excel： 正如我们在表 7 中看到的那样，旧版本的 Excel 正确计算了非常小的概率，但没有给出接近均值的中心概率的结果（旧版 Excel 上的泊松分布出现相同的问题）

Excel 2003： 中心概率已修复。但是，尾部结果不准确。（Knüsel，2005，S.446）。（Excel 2003 上的泊松分布存在相同的问题）。

对于 n > 1000 的值，可能会遇到 Excel 的这种奇怪行为。（Knüsel，1998，S.375）即使对于 0.01 到 0.99 之间的中心范围内的概率，以及对于不能判断为过于极端的参数值，它也会失败。

其他问题

• Excel 97、2000 和 XP 在计算超几何分布 (HYPERGEOM) 时包含缺陷。对于某些值（N > 1030），检索不到结果。在 Excel 2003 中，这种情况得到了阻止，但仍然没有计算尾部概率的选项。因此，可以计算 Pr {X = k}，但无法计算 Pr {X ≤ k}。（Knüsel，2005，S.447）
• 用于伽马分布的函数 GAMMADIST 在 Excel 2003 中检索到不正确的值。（Knüsel，2005，S.447-448）
• 同样，用于逆贝塔分布的函数 BETAINV 在 Excel 2003 中也计算了不正确的值（Knüsel，2005，S. 448）

评估 Excel 结果以进行单变量统计、方差分析和估计（线性与非线性）

我们对 Excel 在单变量统计、方差分析和估计方面的计算的判断将基于 StRD，StRD 由美国国家标准与技术研究院 (NIST) 的统计工程部门设计，旨在帮助研究人员显式地对统计软件包进行基准测试。StRD 具有参考数据集（真实世界和生成的数据集），并具有经过认证的计算结果，这些结果使客观评估统计软件成为可能。它包含四个针对统计软件的数值基准测试套件：单变量汇总统计、单因素方差分析、线性回归和非线性回归，并且每个测试套件包含多个问题。所有问题都有难度级别：低、平均或高。

在本节中，通过评估 Excel 结果，我们将使用 LRE（对数相对误差），它可以用作统计软件包结果准确性的得分。可以通过对数相对误差来计算结果中的正确数字数量。请注意，对于双精度，计算出的 LRE 介于 0 到 15 之间，因为在双精度中最多可以有 15 个正确数字。

公式 LRE

$\lambda =LRE(x)=-log_{10}\left({\frac {|x-c|}{|x|}}\right)$

c：特定测试问题的正确答案（经过认证的计算结果）

x：Excel 对同一问题的答案

单变量统计

• Excel 函数： - AVERAGE、STDEV、PEARSON（也称为 CORREL）

• 计算（分别）： 均值、标准差、相关系数

表 8：（McCullough & Wilson，2005，S.1247）

旧版 Excel： 使用了用于计算样本方差和相关系数的不稳定算法。即使对于低难度问题（表 8 中带有字母“l”的数据集），旧版本的 Excel 也会失败。

Excel 2003： 问题已修复，性能可以接受。小于 15 的准确数字并不表示实现不成功，因为即使是可靠的算法也无法为这些平均和高难度问题（表 8 中带有字母“a”和“h”的数据集）从 StRD 中检索到 15 个正确数字。

单因素方差分析

• Excel 函数： 工具 - 数据分析 - 方差分析：单因素（需要分析工具包）
• 计算： df、ss、ms、F 统计量

由于方差分析会生成许多数值结果（例如 df、ss、ms、F），因此这里只介绍了最终 F 统计量的 LRE。在评估 Excel 的性能之前，应该考虑，用于单因素方差分析的可靠算法可以为平均难度问题提供 8 到 10 位数字，为更难的问题提供 4 到 5 位数字。

表 9：（McCullough & Wilson，2005，S.1248）

旧版 Excel： 考虑到数值解，对于困难问题只提供几位有效数字的准确性并不表示软件不好，但是，对于平均难度问题检索到 0 位有效数字，则表示在计算方差分析时软件不好。（McCullough & Wilson，1999，S. 31）。因此，早于 Excel 2003 的 Excel 版本的性能只有在低难度问题上才能接受。它为困难问题检索到零位有效数字。此外，针对“组内平方和”和“组间平方和”的负结果是 Excel 使用的糟糕算法的进一步指示。（参见表 9）

Excel 2003： 问题已修复（参见表 9）。Simon 9 测试的零位有效数字并非令人担忧的原因，因为当使用可靠算法时，也会发生这种情况。因此，性能可以接受。（McCullough & Wilson，2005，S. 1248）

线性回归

• Excel 函数： LINEST
• 计算： 线性回归所需的所有数值结果

由于 LINEST 会生成许多用于线性回归的数值结果，因此只考虑系数的 LRE 和系数标准误差。表 9 显示了每个数据集的最低 LRE 值，作为链条中最薄弱的环节，以反映最差的估计（最小 $\lambda _{\beta }$-LRE 和 $\lambda _{\sigma }$-LRE）由 Excel 为每个线性回归函数生成的。

旧版 Excel： 既没有检查输入矩阵的近奇异性，也没有正确地检查它，因此对于病态的数据集“Filip (h)”，结果中没有一个数字是正确的。实际上，Excel 应该拒绝该解决方案，并向用户发出有关数据矩阵近奇异性的警告。（McCullough & Wilson, 1999, S.32,33）。然而，在这种情况下，用户会被误导。

Excel 2003： 问题已解决，Excel 2003 的性能可以接受。（参见表 10）

表 10： (McCullough & Wilson, 1999, S. 32)

非线性回归

使用 Excel 求解非线性回归时，可以选择以下内容：

1. 导数计算方法：向前（默认）或中心数值导数
2. 收敛容差（默认值 = 1.E-3）
3. 变量缩放（重新居中）
4. 求解方法（默认 - GRG2 拟牛顿法）

Excel 的默认参数并不总是产生最佳的解决方案（与所有其他求解器一样）。因此，需要给出不同的参数并测试 Excel 求解器以进行非线性回归。在表 10 中，列 A-B-C-D 是不同非线性选项的组合。由于更改第 1 个和第 4 个选项不会影响结果，因此只更改了第 2 个和第 3 个参数进行测试。

• A：默认估计
• B：收敛容差 = 1E -7
• C：自动缩放
• D：收敛容差 = 1E -7 和自动缩放

在表 11 中，应用了最低 LRE 原则来简化评估。（与线性回归一样）

表 11 中的结果对于每个 Excel 版本（Excel 97、2000、XP、2003）都是相同的。

表 11： (McCullough & Wilson, 1999, S. 34)

正如我们在表 11 中看到的，非线性选项组合 A 生成了 21 次“0”准确数字，B 生成了 17 次，C 生成了 20 次，D 生成了 14 次，这表明 Excel 在这一领域的表现不足。期望 Excel 为所有问题找到所有精确的解决方案是不公平的，但如果它无法找到结果，则希望它警告用户并承认无法计算出该解决方案。此外，应该强调，其他统计软件包（如 SPSS、S-PLUS 和 SAS）在这些测试中只在少数情况下（0 到 3 次）表现出零位精度（McCullough & Wilson, 1999, S. 34）。

评估 Excel 的随机数生成器

许多统计程序使用随机数，并且期望生成的随机数确实是随机的。只有具有可靠的理论属性的随机数生成器才能使用。此外，应该对生成的样本进行统计检验，并且只有输出成功通过一组统计检验的生成器才能使用。（Gentle, 2003）

根据上面解释的事实，我们应该通过以下方式评估随机数生成的质量：

• 分析随机数生成的底层算法。
• 分析生成器的输出流。有很多方法可以测试 RNG 的输出。可以使用静态测试来评估生成的输出，在这些测试中，生成顺序并不重要。这些测试是拟合优度检验。评估输出流的第二种方法是对生成器运行动态测试，其中数字的生成顺序很重要。

Excel 的 RNG - 底层算法

随机数生成的目的是生成任何给定大小的样本，这些样本与从 U(0,1) 分布中获得的相同大小的样本不可区分。（Gentle, 2003）为此目的，可以使用不同的算法。Excel 的随机数生成算法是 Wichmann-Hill 算法。Wichmann-Hill 是一个适用于常见应用的实用 RNG 算法，但对于现代需求而言已经过时（McCullough & Wilson, 2005, S. 1250）。此随机数生成器的公式定义如下

$X_{i}=171.X_{i}-1mod30269$

$Y_{i}=172.Y_{i}-1mod30307$

$Z_{i}=170.Z_{i}-1mod30323$

$U_{i}={\frac {X_{i}}{30269}}+{\frac {Y_{i}}{30307}}+{\frac {Z_{i}}{30323}}mod1$

Wichmann-Hill 是一个同余生成器，这意味着它是如上式所示的递归算术 RNG。它是三个其他线性同余生成器的组合，需要三个种子：$X_{0}Y_{0}Z_{0}$.

周期，在随机数生成的意义上，是指在 RNG 开始重复之前可以对 RNG 进行的调用次数。因此，周期长是随机数生成器的质量指标。生成器的周期必须大于要使用的随机数的数量。现代应用越来越多地需要更长更长的随机数序列（例如在蒙特卡罗模拟中使用）（Gentle, 2003）

一个好的随机数生成器 (RNG) 可接受的最小周期是 $2^{60}$，而 Wichmann-Hill RNG 的周期为 6.95E+12 (≈ $2^{43}$)。除了这种不可接受的性能，微软声称 Wichmann-Hill RNG 的周期为 10E+13。即使 Excel 的 RNG 周期为 10E+13，它仍然不是一个可接受的随机数生成器，因为这个值也小于 $2^{60}$。(McCullough & Wilson, 2005, S. 1250)

此外，众所周知，Excel 的 RNG 在多次执行后会产生负值。然而，Wichmann-Hill 随机数生成器的正确实现应该只产生 0 到 1 之间的数值。(McCullough & Wilson, 2005, S. 1249)

Excel 的 RNG - 输出流

正如我们在上面讨论的那样，仅仅讨论随机数生成器的底层算法是不够的。在评估随机数生成器的质量时，还需要对随机数生成器的输出流进行一些测试。因此，随机数生成器应该产生通过一些随机性测试的输出。Marsaglia 准备了一套这样的测试，称为 DIEHARD。一个好的 RNG 应该通过几乎所有的测试，但正如我们在表 12 中看到的，Excel 只通过了其中的 11 个 (7 个失败)，尽管微软声称 Excel 的 RNG 实施了 Wichmann-Hill 算法。然而，我们知道 Wichmann-Hill 能够通过 DIEHARD 的 16 个测试 (McCullough & Wilson, 1999, S. 35)。

由于在上一节和本节中解释的原因，我们可以说 Excel 的性能不足 (因为周期长度、Wichmann Hill 算法的错误实现，该算法已经过时，DIEHARD 测试结果)

表 12: (McCullough & Wilson, 1999, S. 35)

结论

旧版本的 Excel (Excel 97, 2000, XP) 

• 在以下分布上表现不佳: 正态分布、F 分布、t 分布、卡方分布、二项分布、泊松分布、超几何分布
• 在以下计算中得到不准确的结果: 单变量统计、方差分析、线性回归、非线性回归
• 具有不可接受的随机数生成器

由于这些原因，我们可以说应该避免将 Excel 97、2000、XP 用于 (统计) 科学目的。

尽管 Excel 2003 中修复了几个错误，但仍然应该避免将 Excel 用于 (统计) 科学目的，因为它

• 在以下分布上表现不佳: 二项分布、泊松分布、伽马分布、贝塔分布
• 在非线性回归方面得到不准确的结果
• 具有过时的随机数生成器。

参考文献

• Gentle J.E. (2003) 随机数生成和蒙特卡罗方法 第二版。纽约 Springer Verlag
• Knüsel, L. (2003) 统计分布的计算 ELV 程序文档 第二版。 http://www.stat.uni-muenchen.de/~knuesel/elv/elv_docu.pdf 检索于 [2005 年 11 月 13 日]
• Knüsel, L. (1998)。关于 Microsoft Excel 97 中统计分布的准确性。计算统计与数据分析 (CSDA)，第 26 卷，第 375-377 页。
• Knüsel, L. (2002)。关于 Microsoft Excel XP 用于统计目的的可靠性。计算统计与数据分析 (CSDA)，第 39 卷，第 109-110 页。
• Knüsel, L. (2005)。关于 Microsoft Excel 2003 中统计分布的准确性。计算统计与数据分析 (CSDA)，第 48 卷，第 445-449 页。
• McCullough, B.D. & Wilson B. (2005)。关于 Microsoft Excel 2003 中统计过程的准确性。计算统计与数据分析 (CSDA)，第 49 卷，第 1244-1252 页。
• McCullough, B.D. & Wilson B. (1999)。关于 Microsoft Excel 97 中统计过程的准确性。计算统计与数据分析 (CSDA)，第 31 卷，第 27-37 页。
• PC Magazin，2004 年 4 月 6 日，第 71 页*