统计学/数值方法/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 中看到的，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）

逆卡方分布

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

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

旧版 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 分布的随机变量。

旧版 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 尾计算。（下尾 & 上尾）

旧版 Excel 版本：Excel 以 9 位或更多位有效数字打印出分位数，尽管如果 p 很小，则这些数字中只有一两位是准确的（参见表 5）。

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

泊松分布

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

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

旧版 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 表示具有给定参数的二项分布的随机变量

旧版 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)
Excel 2003 上的伽马分布函数 GAMMADIST 检索到不正确的值。(Knüsel, 2005, S.447-448)
同样，Excel 2003 上的逆贝塔分布函数 BETAINV 也计算出不正确的值 (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）

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

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

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

单因素方差分析

Excel 函数： 工具 – 数据分析 – 方差分析：单因素 (需要数据分析工具库)
计算： df、ss、ms、F 统计量

由于方差分析会生成许多数值结果（例如 df、ss、ms、F），因此此处仅介绍最终 F 统计量的 LRE。在评估 Excel 的性能之前，应该考虑一下，一个可靠的单因素方差分析算法可以为中等难度的题目提供 8-10 位数字的准确度，而对于更高难度的题目可以提供 4-5 位数字的准确度。

旧版 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)

非线性回归

当使用 Excel 求解非线性回归时，可以对以下内容进行选择

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

Excel 的默认参数并不总是能够产生最佳解决方案 (就像所有其他求解器一样)。因此，需要提供不同的参数并测试 Excel 求解器以进行非线性回归。在表 10 中，A-B-C-D 列是不同非线性选项的组合。由于更改第一个和第四个选项不会影响结果，因此仅更改第二个和第三个参数以进行测试

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

在表 11 中，应用了最低 LRE 原则以简化评估。(就像线性回归一样)

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

正如我们在表 11 中看到的，非线性选项组合 A 产生了 21 次，B 产生了 17 次，C 产生了 20 次，D 产生了 14 次“0”精确数字。这表明 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}$ ）。除了这种不可接受的性能之外，Microsoft 声称 Wichmann-Hill RNG 的周期为 10E+13。即使 Excel 的 RNG 具有 10E+13 的周期，它仍然不足以成为一个可接受的随机数生成器，因为该值也小于 $2^{60}$ 。（McCullough & Wilson，2005，S. 1250）

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

Excel 的 RNG - 输出流

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

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

结论

旧版本的 Excel（Excel 97、2000、XP）

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

基于以上原因，我们建议避免使用Excel 97、2000、XP进行（统计）科学计算。

尽管Excel 2003修复了一些错误，但仍然建议避免使用Excel进行（统计）科学计算，因为：

它在以下分布上的表现不佳：二项分布、泊松分布、伽马分布、贝塔分布
非线性回归结果不准确
随机数生成器过时。

参考文献

Gentle J.E. (2003) 随机数生成与蒙特卡罗方法第二版。纽约施普林格出版社
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杂志，2004年4月6日，第71页*