有关 Excel 中增强的统计函数对分析工具库的影响的说明

文章翻译 文章翻译
文章编号: 829208 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

本文论述 Microsoft Office Excel 2003 和 Excel 更高版本中统计函数的数值改进之处对分析工具库 (ATP) 工具的影响。大多数 ATP 工具在计算结果的过程中都调用 Excel 的统计函数。通过本文可以找到有关许多单独 Excel 统计函数的文章。此外,本文还针对某些 ATP 工具论述了一些非常有用的将来改进之处。

用于 Mac 的 Microsoft Excel 2004 的信息

用于 Mac 的 Excel 2004 中的统计函数已更新,更新所用的算法与用于更新 Microsoft Office Excel 2003 及 Excel 更高版本中的统计函数的算法相同。本文中描述某一函数工作方式或者如何针对 Excel 2003 和 Excel 更高版本对某一函数加以修改的任何信息同样也适用于用于 Mac 的 Excel 2004。

更多信息

ATP 的代码没有经过直接编辑,只是在三个 ATP ANOVA 工具中有所改进。

在 Excel 2003 及 Excel 更高版本中使用时,若干 ATP 工具的数值性能已得到改善,因为工具会调用一个 Excel 统计函数,而该函数对于 Excel 2003 及 Excel 更高版本已有改进。如果 Excel 早期版本和 Excel 更高版本的结果不同,Excel 2003 及 Excel 更高版本的值更准确。

大多数用户将不会注意到 Excel 不同版本间的结果差异。这是因为差异通常是由四舍五入误差导致的,而这些误差只有极个别情况下才会造成影响。但是,本文首先必须指出由于 Microsoft Excel 2002 及 Excel 早期版本中的公式错误而导致出现差异的一种情况。请避免在这些版本中使用该工具。

另一个例子涉及到 Excel 2002 及 Excel 早期版本中一个不正确的公式,该公式在 Excel 2003 及 Excel 更高版本中仍然存在。请避免在这种情况下为所有版本的 Excel 使用 ATP 工具。

首先,如果必须单击选中“常数为零”复选框,请避免使用“回归”工具。此问题在 Excel 2003 及 Excel 更高版本中已得到更正。当“常数为零”复选框处于清除状态时(实践中较典型的情况),没有必要避免使用“回归”工具。

第二,Excel 所有版本的用户都应避免使用“ATP t-检验:成对双样本平均值”工具,除非您能够保证不会缺少数据观察值。如果缺少一个或多个观察值,该工具得出的答案将不合理(或者根本不会得出答案)。

有关“ATP 匹配成对双样本 t-检验”工具的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
829252 使用 Excel 中分析工具库的 t-检验时可能得到不正确的结果和有误导性的标签
如果想要使用此工具,并且在缺少数据(或者即使是可能缺少数据)的情况下,Excel 中的 TTEST 函数将会对其进行正确的处理。

本文稍后将有单独的几节对个别 ATP 工具加以讨论。未列出的工具没有受到 Excel 2003 及 Excel 更高版本中改进之处的影响。

ANOVA:单因素、包含重复的双因素和无重复的双因素

这三个 ANOVA 工具中的每个工具都已经过重写,以便将计算过程升级为数值上更为稳固的两遍算法。这些改进之处类似于对平均值总方差进行计算的统计函数的改进之处,此类函数的例子包括:VAR、STDEV、SLOPE、PEARSON。

有关 ATP ANOVA 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
829215 有关 Excel 中分析工具库 ANOVA 工具的数值改进之处的说明

相关系数

此工具未更改。但是,“相关系数”工具和在 Excel 所有版本中保持不变的“协方差”工具略有不同。“相关系数”工具返回一个下三角相关系数表,其中 1 位于对角线上,而各相关系数则偏离对角线。该工具使用 CORREL 来计算偏离对角线的项,并用 CORREL 返回的值来填充这些项。(因此,如果任何数据项发生变化,表中的项不会变化。将此行为与“协方差”的行为进行比较。)

协方差

此工具返回一个下三角协方差表,其中方差位于对角线上,而协方差则偏离对角线。对角线上的单元格包含公式“=VARP(...)”,因此,如果数据项发生变化,表中的结果也会变化。Office Excel 2003 及 Excel 更高版本中的 VARP 已经过改进。

有关 VARP 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
826393 Excel 统计函数:VARP
“协方差”工具使用 COVAR 来计算偏离对角线的项,并用 COVAR 返回的值填充这些项。因此,如果数据项发生变化,偏离对角线的项不会变化。

描述统计

此工具为其计算的所有内容调用 Excel 统计函数。由于 Excel 2003 及 Excel 更高版本中的 VAR 和 STDEV 已有改进,因此,在极个别情况下,可能会因为四舍五入误差而出现不同的值。

有关 VAR 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
826112 Excel 统计函数:VAR

F-检验双样本方差

如同“描述统计”工具一样,此工具也调用 VAR。同样,在极个别情况下也可能会因为四舍五入误差而出现不同的值。

随机数生成

此工具用随机观察值填充一个区域。这些观察值直接放在单元格中,以便在重新计算表单时不会用新观察值重新计算和替换这些单元格值。但是,Excel 中的内置 RAND 函数会在每次重新计算表单时将现有的随机数替换为新值。您可以使用 RAND 来保留值。为此,请复制区域中的结果,然后使用“选择性粘贴”命令将值粘贴到同一区域中。

“随机数生成 (RNG)”工具还会依据各种可能性分布生成随机观察值,而 RAND 对应于该工具中的单一选项:与 0 和 1 间的区域保持一致。本文介绍如何将 RAND 与 Excel 中的统计函数结合使用来生成这样的观察值。

因此,就功能而言,您可以通过巧妙地使用 RAND 来模拟 ATP 随机数工具。有时这非常有用,特别是在需要许多随机数的情况下。

对于 Excel 2002 及早期版本,众所周知,ATP 随机数生成器和 RAND 在随机性标准测试上的表现差强人意。之所以效果不尽如人意,原因是伪随机数序列在开始重复出现之前的周期长度太短。只有在需要多个随机数时,才会出现此问题。

Excel 2003 及 Excel 更高版本中的 RAND 已经过改进,因此,RAND 现在可以通过所有此类标准测试。RAND 的随机数序列将在生成 1 万亿个数字后才会开始重复自身。

有关 RAND 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
828795 Excel 2007 和 Excel 2003 中 RAND 函数的说明
但是,ATP 的单独随机数生成器没有升级。如同 Excel 2002 及 Excel 早期版本中的 RAND 版本一样,ATP 的单独随机数生成器在随机性标准测试的表现上差强人意,并且其重复周期较短。只有在需要非常长的随机数序列(例如,一百万)时,这一点才会产生负面影响。

RNG 工具依据各种可能性分布和 Uniform[0,1](用于通过 RAND 输出随机数的分布)提供随机观察值。该 ATP 工具先获得一个 Uniform[0,1] 随机数(或多个此类数字),然后依据以下特定分布之一将答案转换为一个观察值。为了方便因为要生成多个观察值而选择使用 RAND 的用户,本文建议采用下表中使用 RAND 的公式。该表后面提供了一些有关分析工具库正态分布情况的告诫性意见。
收起该表格展开该表格
分布使用 RAND() 的 Excel 公式
Bernoulli 分布 (p)=IF(RAND() <= p, 1, 0)
二项式分布 (n,p)=CRITBINOM(n, p, RAND())
离散分布请参阅下文
正态分布 (mu, sigma)=NORMINV(RAND(), mu, sigma)
模式化分布不真正随机
Poisson 分布 (平均值)请参阅下文
平均分布 (低值, 高值)= 低值 + (高值 – 低值) * RAND()
在正态分布 (mu, sigma) 的情况下,您之所以选择使用 RAND 和此表中的公式(而不是 ATP 随机数工具),原因有两个。首先,RAND 是一种比 ATP 随机数生成器更好的 Uniform[0,1] 随机数生成器。第二,ATP 的工具不会调用 Excel 的 NORMINV 函数,而是有它自己的内置反向正态分布函数。与 Excel 2003 及 Excel 更高版本中的 NORMINV 相比,此函数不够准确。不论是它使用的正态分布近似值(Excel 使用经过显著改进的 NORMSDIST 函数),还是二进制搜索的精确性(Excel 能够进一步地执行搜索,确保得到更接近于 NORMINV 的概率参数的值),此函数都要略逊一筹。简而言之,如果在这种情况下使用 ATP,将无法充分利用 Excel 2003 及 Excel 更高版本在 NORMINV、NORMSDIST 和 RAND 函数上的改进。

对于离散分布中的观察值,假设值位于列 B 中,并且值的概率位于列 C 中。有人随后想用观察值(该值严格小于列 B 该行中的值)的概率填充列 A 中的每一行。假设有 10 个值,并且此数据位于单元格 A1:C10 中。那么,由于 A1 包含观察值(该值严格小于第一个值)的概率,因此它必须设置为 0。您可以使用 VLOOKUP(RAND(), A1:C10, 2);VLOOKUP 的第四个参数是可选的,并且必须设置为忽略或设置为 TRUE。“2”意味着您想要返回第二列(本例中为列 B)中的值。

ATP 使用的方法改编自“Numerical Recipes in C, The Art of Scientific Computing”(C 语言数值方法大全:科学计算的艺术)一书(第二版,Press, W.H.、S.A. Teukolsky、W. T. Vetterling 和 B.P. Flannery 著,剑桥大学出版社 1992 年出版)中生成 Poisson 观察值的方法(第 293-295 页)。可以采用两种方法轻松地利用现有的 Excel 函数。

第一种方法使用 POISSON 随机变量(带有平均值 m)具有分布的观察值,对于较大的 n,可以通过 BINOMIAL(n, m/n) 准确地求得该分布的近似值。您随后可以调用 CRITBINOM(n, m/n, RAND())。n 的选择取决于 m;比 m 大 1,000 倍的 n 应足够大。

第二种方法与幂 POISSON 分布相关。如果事件依据 POISSON 过程以 m/每单位时间的速率发生,则事件之间的时间具有一个平均值为 1/m 的幂分布。对于 POISSON 观察值,您可以从这个幂分布中取得一个观察值序列,并计算在其和超出 1 之前会有多少个观察值出现。要从此幂分布中获得观察值,请使用 GAMMAINV(RAND(), 1, 1/m)。如果 m 相对接近于 0,则此方法将很适合。

回归

“回归”工具调用 Excel 的 LINEST。有关 LINEST 的文章描述了 Excel 2003 及 Excel 更高版本的广泛改进。

有关 LINEST 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
828533 Excel 2003 及 Excel 更高版本中 LINEST 函数的说明
如果使用 Excel 2002 或 Excel 的早期版本,请注意 ATP 回归工具与 LINEST 同样具有的两个缺点:
  • 对于强制使回归通过原点的情况,回归平方和、R 平方值和 F 统计值总是不正确。

    对于 LINEST,这意味着“第三个参数设置为 FALSE,而不是设置为 TRUE 或忽略”。对于 ATP 工具,它意味着“‘常数为零’复选框处于选中状态”。
  • LINEST 和 ATP 工具对于共线性问题都不敏感。有关 LINEST 的文章介绍了 Excel 2003 及 Excel 更高版本中 LINEST 的计算方法,该方法设计为可以在存在共线性或非共线性时找到共线性或非共线性,并适当地加以处理。
Excel 2003 及 Excel 更高版本中已经解决了这两个 LINEST 缺点。ATP 回归工具的性能将同样得到改善。没有对工具的代码进行任何更改;它的改善是通过调用改进的 Excel 函数实现的。本文作者认为,LINEST 的改进是最重要的统计函数改进。

下表显示了在“常数为零”复选框处于选定状态的情况下 Excel 早期版本和 Excel 更高版本的“回归”工具输出。它举例说明了前面提到的第一个缺点。在 Excel 的早期版本中,回归平方和为负,R 平方值也一样。
收起该表格展开该表格
X 值Y 值
111
212
313
Excel 2002 及早期版本
汇总输出
回归统计值
复相关 R 值65535
R 平方值-20.4285714
调整的 R 平方值-20.9285714
标准误差4.629100499
观察值3
ANOVA
dfSSMSF显著性 F
回归1-40.85714286-40.85714286-1.90666667#NUM!
残差242.8571428621.42857143
总计32
Excel 2003 及 Excel 更高版本
汇总输出
回归统计值
复相关 R 值0.949342311
R 平方值0.901250823
调整的 R 平方值0.401250823
标准误差4.629100499
观察值3
ANOVA
dfSSMSF显著性 F
回归1391.1428571391.142857118.253333330.14637279
残差242.8571428621.42857143
总计3434

t-检验:成对双样本平均值

正如前面提到的,如果可能缺少一个或多个数据值,请避免使用此工具。此检验的原型应用是一个在治疗前后对受检者进行测量的实验(比如 60 天节食计划前后的体重)。如果没有缺少任何观察值,该工具将表现正常。如果缺少的前后观察值的数量不同,您将会收到一条错误消息,并且工具将不会计算任何内容。如果缺少观察值,并且缺少的前后观察值的数量相等,则工具将返回包含若干错误的答案。

标准过程如下:如果缺少前面或后面的测量值,则从数据中删除一个受检者,并对只包含那些同时具有前后测量值的受检者的数据进行分析。Excel 的 TTEST 函数会根据此标准过程处理缺少的数据。

其他两个 t-检验工具(“双样本等方差假设”和“双样本异方差假设”)不受此影响。

z-检验:双样本平均值

本文前面提到过:随机数生成工具的正态分布情况不会调用 NORMSINV 函数(或者更准确地说,调用 NORMSINV 的 NORMINV),但它有自己用于查找普通相反值的过程(只是该过程功能较差)。

z-检验工具却会调用 NORMSINV 函数,并充分利用 Excel 2003 及 Excel 更高版本的改进之处。

Excel 早期版本中的结果

在 Excel 2003 及 Excel 更高版本中使用时,有些 ATP 工具的性能已得到了改善,因为这些工具会调用 Excel 统计函数,而这些函数对于 Excel 2003 及 Excel 更高版本已有改进。LINEST 的其中一个改进之处表明,当 LINEST 的第三个参数设置为 FALSE 时,ATP 回归工具在 Excel 2002 及 Excel 早期版本中会返回不正确的结果(如果“常数为零”复选框处于选定状态)。对于 Excel 函数已有改进的其他情况,早期版本的用户不太可能注意到不同之处(大多数这些不同之处都涉及到极个别情况下的四舍五入误差)。

通过编辑 ATP 代码,代换一个在数值上更稳固的算法(本质与 Excel 的 VAR 的改进相同),三个 ATP ANOVA 工具已得到改进。只有在极个别情况下,Excel 早期版本中这些工具的用户才会注意到不同之处。

针对所有版本用户的警告:只要有最轻微的缺少数据的可能,就要避免使用“t-检验:成对双样本平均值”。

Excel 2003 及 Excel 更高版本中的结果

Excel 的统计函数已进行了显著改进。这也使调用这些函数的许多 ATP 工具得到了改进。有一个 ATP 工具(随机数生成器)未利用改进的 RAND 函数(因为它是采用独立的方式实现的,并且不调用 RAND)。这一点令人遗憾,但更加令人遗憾的是正态分布随机观察值的特殊情况。反向正态分布也是用独立的方式实现的,并且不调用经过显著改进的 NORMSINV 函数。

下表列出了对于 Excel 2003 及 Excel 更高版本已得到改进的 ATP 工具及它们调用的那些 Excel 函数。请读者查阅有关调用的每个 Excel 函数的单独文章。
收起该表格展开该表格
ATP 工具调用的 Excel 函数
ANOVA:单因素VAR、FINV
ANOVA:包含重复的双因素VAR、FINV
ANOVA:无重复的双因素VAR、FINV
相关系数
协方差
描述统计STDEV、TINV、VAR
指数平滑
F-检验双样本方差VAR、FINV
傅立叶分析
直方图
移动平均
随机数生成
排位与百分比排位
回归LINEST
抽样RAND
t-检验:成对双样本平均值VAR、PEARSON、TINV
t-检验:双样本等方差假设VAR、TINV
t-检验:双样本异方差假设VAR、TINV
z-检验:双样本平均值NORMSDIST、NORMSINV
对于除 LINEST 和 RAND 之外所有出现在上表中的函数,您都只有在极个别情况下才可能看到 Excel 早期版本和 Excel 更高版本之间由于四舍五入误差引起的差异。如前面所述,LINEST 已有显著改进。RAND 也得到了改进。奇怪的是,“抽样”工具名为 RAND,但 RNG 工具却依赖于独立的生成器,在需要较长的随机观察值序列时,该生成器的表现差强人意。

结束语

除了三个 ANOVA 工具中每个工具的 ATP 代码有变化外,ATP 代码没有经过重写。不过,它可以通过调用改进的 Excel 函数而获益,如“ATP 工具”表中所示。“t-检验:成对双样本平均值”检验中的缺点对于 Excel 2003 或 Excel 更高版本尚未得到修复。最引人注目的改进可能是在“回归”工具中,当“常数为零”复选框处于选中状态时,LINEST 不再会返回不正确的结果,并且 LINEST 设计为可以适当地处理共线性。

属性

文章编号: 829208 - 最后修改: 2007年2月26日 - 修订: 4.0
这篇文章中的信息适用于:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
关键字:?
kbinfo kbformula kbfunctions kbfuncstat kbexpertisebeginner KB829208
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com