有关 Excel 中分析工具库 ANOVA 工具的数值改进之处的说明

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

本文内容

概要

本文描述三个分析工具库 ANOVA 工具中每一种工具的数值改进之处。本文还阐述了极个别情况下在 Microsoft Excel 2002 及 Excel 早期版本中的不正确结果。

更多信息

许多函数都要求计算有关某个平均值的总方差。为准确地进行此计算,Microsoft OfficeExcel 2003 及 Excel 更高版本使用一个两遍过程,该过程将在第一遍中查找平均值,然后在第二遍中计算有关该平均值的方差。

在精度运算中,相同的结果会出现在使用“计算器公式”的 Excel 早期版本中。此公式之所以这样命名,原因是当统计人员在使用计算器(而不是计算机)时,它就已经被广泛使用了。利用计算器公式,Excel 的早期版本将计算观察值的平方和,然后从此总和中减去以下数量:
((sum of observations)^2) / number of observations
此计算以单遍方式在数据中进行。

在有限精度运算中,计算器公式在极个别情况下会受到四舍五入误差的限制。Excel 2002 及 Excel 早期版本为大多数需要计算平均值总方差的函数(比如 VAR、STDEV、SLOPE 和 PEARSON)使用计算器公式。不过,Excel 的这些版本也为 CORREL、COVAR 和 DEVSQ 函数使用在数值上更稳固的两遍过程。

统计计算的专家建议不要使用计算器公式。在有关统计计算上,计算器公式用文字可以表述为“如何不去做它”。不幸的是,在 Excel 2002 及 Excel 早期版本中,所有三个分析工具库 (ATP) ANOVA 工具都广泛使用计算器公式或同等的单遍方法。

Excel 2003 及 Excel 更高版本为所有三个 ATP ANOVA 模型都使用两遍过程。本文论述了 ATP 的三个 ANOVA 模型在计算方面的以下改进之处:
  • 单因素
  • 包含重复的双因素
  • 无重复的双因素
本文稍后将论述这些模型。

由于 Excel 一直为 DEVSQ 使用两遍过程,因此本文将频繁使用该函数来描述改进的过程。这些修改了的过程要么会实际上调用 DEVSQ,要么就使用其功能与 DEVSQ 的功能完全相同的代码。

对于每个 ANOVA 工具,ATP 输出包含一个汇总表(带有“计数”、“和”、“平均值”及“方差”的值),以及一个 ANOVA 表(带有各种平方和以及 SS、df、MS、F 和 P-值的值)。汇总表中的结果是通过调用 Excel 函数 COUNT、SUM、AVERAGE 和 VAR 计算得出的。在这四个函数中,只有 VAR 受四舍五入误差所限。

Excel 2002 及 Excel 早期版本通过使用计算器公式来实现 VAR。以下有关 VAR 的文章介绍了Excel 2003 及Excel更高版本 中的改进之处。本文还允许您用数值数据进行试验,以便看到在 Excel 早期版本中可能发生的四舍五入误差。

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


由于本文论述三个 ANOVA 模型,因此本文的重点将放在 ANOVA 输出表上。在每种情况下,汇总表在Excel 2003 及Excel更高版本 中都工作正常。在 Excel 2002 及 Excel 早期版本中,如果数据具有极端值,“方差”列中会出现问题。

不过,本文将在介绍模型的各节中包括汇总表,因为在您查看“附录”中经过修改的示例时,用这些表进行比较将十分有用。

模型 1:单因素

包含数据的简单示例如下所示:
收起该表格展开该表格
ANOVA 1 基本模型:
123
244
365
486
57
68
Anova:单因素
汇总
计数平均值方差
列 16213.53.5
列 242056.666667
列 36335.53.5
ANOVA
方差来源SSdfMSFP-值F 临界值
组之间12.7526.3751.5068180.2578973.805567
组内55134.230769
总计67.7515
Excel 2002 及 Excel 早期版本使用以下伪代码来计算平方和:
GrandSum = 0;
GrandSumOfSqs = 0; 
GrandSampleMeanSqrd = 0; 
GrandMeanSqrd = 0; 
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
   GrandSum = GrandSum + sum of observations in s-th sample;
   GrandSumOfSqs = GrandSumOfSqs + sum of squared observations in s-th sample;
   GrandSampleMeanSqrd = GrandSampleMeanSqrd  +
      (sum of observations in s-th sample^2)/size of s-th sample;
   GrandSampleSize = GrandSampleSize + size of s-th sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs – GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd – GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs – GrandSampleMeanSqrd;
此方法本质上是计算器公式。此方法将计算观察值的平方和,然后从平方和中减去一个数量,就好像 VAR 计算观察值的平方和,然后减去<观察值的和>^2/<样本大小>。模型 2 和模型 3 的类似伪代码已被忽略。

同样,对于模型 2 和模型 3,将计算平方和并从平方和中减去一个数量(就像在计算器公式中)。不幸的是,基础统计学教科书经常会建议为 ANOVA 采用像本文前面所示的方法。

Excel 2003 及 Excel 更高版本使用不同的方法来计算 ANOVA 表的 SS 列中的各个条目。为了举例说明,本文假设前面示例中的数值数据出现在 A2:C7 单元格中,其中 B6 和 B7 单元格中缺少数据。
  • “总计”SS 只是通过将 DEVSQ 应用于所有数据计算得出的,比如 DEVSQ(A2:C7)。即使缺少数据,DEVSQ 也能正常工作。
  • “组之间”SS 等于“总计”SS 减去应用于每一列的 DEVSQ 的和,比如 DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7)。
  • “组内”SS 等于“总计”SS 减去“组之间”SS 的差。
如果 ANOVA 表 SS 列中的条目计算正确无误,则该表中的其他条目也同样准确。

模型 2:包含重复的双因素

包含数据的简单示例如下所示:
收起该表格展开该表格
ANOVA 2 基本模型组 1组 2组 3
试验 1123
244
365
试验 2486
5107
6128
Anova:包含重复的双因素
汇总组 1组 2组 3总计
试验 1
计数3339
6121230
平均值2443.333333
方差1412.5
试验 2
计数3339
15302166
平均值51077.333333
方差1416.25
总计
计数666
214233
平均值3.575.5
方差3.5143.5
ANOVA
方差来源SSdfMSFP-值F 临界值
样本72172366.22E-054.747221
37218.59.250.0037093.88529
交互作用924.52.250.1479733.88529
之内24122
总计14217
同样,如果 SS 列中的条目计算正确无误,则输出的 ANOVA 部分中的所有其他条目也同样准确。

下面是Excel 2003 及Excel更高版本 的计算过程。此过程使用 DEVSQ 计算 ANOVA 表的 SS 列中的各个条目。为了举例说明,本例假设数值数据出现在 B2:D7 单元格中。
  • “总计”SS 只是通过将 DEVSQ 应用于所有数据上计算得出的,比如 DEVSQ(B2:D7)。
  • “样本”SS 等于“总计”SS 减去应用于每个样本的 DEVSQ 的和,比如 DEVSQ(B2:D4) + DEVSQ(B5:D7)。
  • “列”SS 等于“总计”SS 减去应用于每一列的 DEVSQ 的和,比如 DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7)。
  • “之内”SS 是应用于每个试验或组对的 DEVSQ 的和,比如 DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7)。
  • “交互作用”SS =“总计”SS –“样本”SS –“列”SS –“之内”SS。

模型 3:无重复的双因素

包含数据的简单示例如下所示:
收起该表格展开该表格
ANOVA 3 基本模型
贫乏123
244
365
中等486
5107
6128
充足71410
8126
9102
Anova:无重复的双因素
汇总计数平均值方差
贫乏3621
3103.3333331.333333
3144.6666672.333333
中等31864
3227.3333336.333333
3268.6666679.333333
充足33110.3333312.33333
3268.6666679.333333
321719
94557.5
9788.66666716
9515.6666676.25
ANOVA
方差来源SSdfMSFP-值F 临界值
176.6667822.083335.760870.0014762.591094
68.66667234.333338.9565220.0024553.633716
误差61.33333163.833333
总计306.666726
如果 SS 列中的值计算正确无误,ANOVA 表中的所有其他值也同样正确。

Excel 2003 及 Excel 更高版本使用以下计算过程。该过程使用 DEVSQ 计算 ANOVA 表的 SS 列中的值。为了举例说明,本例假设前面示例中所示的单元格区域为 A1:D10 单元格。因此,数值数据出现在 B2:D10 单元格中。
  • “总计”SS 只是通过将 DEVSQ 应用于所有数据上计算得出的,比如 DEVSQ(B2:D10)。
  • “行”SS 等于“总计”SS 减去应用于每一行的 DEVSQ 的和,比如 DEVSQ(B2:D2) + DEVSQ(B3:D3) + DEVSQ(B4:D4) + DEVSQ(B5:D5) + DEVSQ(B6:D6) + DEVSQ(B7:D7) + DEVSQ(B8:D8) + DEVSQ(B9:D9) + DEVSQ(B10:D10)。
  • “列”SS 等于“总计”SS 减去应用于每一列的 DEVSQ 的和,比如 DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10)。
  • “误差”SS 等于“总计”SS 减去“行”SS 再减去“列”SS。

Excel 2002 及 Excel 早期版本中的结果

在数据中存在多个有效数字但同时又稍有差异的极个别情况下,计算器公式会导致不正确的结果。本文后面的附录将提供此类极个别情况下四舍五入问题的示例。

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

Excel 2003 及 Excel 更高版本使用一个在数据中进行两遍处理的过程。在第一遍中,Excel 2003 及 Excel 更高版本计算数据值的和及计数。依据这些数据,Excel 能够计算样本均值(平均值)。

在第二遍中,Excel 计算每个数据点和样本均值之间的差值平方,然后再计算这些差值平方的和。因此,Excel 2003 及Excel更高版本 中的结果在数值上更加可靠。

结束语

与 Excel 早期版本相比,Excel 2003 及 Excel更高版本中的两遍方法 改进了所有三个 ATP ANOVA 工具的数值性能。通过使用Excel 2003 及 Excel 更高版本获得的结果的准确性绝对不会比通过使用 Excel 早期版本获得的结果差。

不过,在大多数实际情况中,这些结果之间并没有任何差异。这是因为数据通常不会表现出下面的附录中阐述的那种不寻常行为。当数据包含数据值之间的差异相对较小的大量有效数字时,Excel 早期版本中很可能会出现数据不可靠的情况。

如果使用 Excel 早期版本,并且想要看看Excel 2003或 Excel 更高版本是否会得出不同的 ANOVA 结果,请将在 Excel 早期版本中使用 ANOVA 工具时得到的结果与使用 DEVSQ 过程时得到的结果进行比较。

注意:在本文前面针对与每个工具关联的 ANOVA 表进行的讨论中,已经论述了使用 DEVSQ 的过程。

要验证汇总表中的“方差”对于每个区域是否正确,请使用 DEVSQ(<区域>)/(COUNT(<区域>) – 1)。

附录:Excel 2002 及 Excel 早期版本的数值性能示例

对于模型 1、2 和 3 中的每个基本示例,本文前面已经提供了 ATP 工具的输出。其中包括汇总表和 ANOVA 表。各个示例中的数据已经过修改以创建一个“强调的”示例。这是通过为每个数据值加上 10^8 完成的。为每个数据值加上像 10^8 这样的常数不会影响汇总表中的“方差”(但将明显影响“平均值”以及“和”)。它同时不应影响 ANOVA 表中的任何条目。

如果比较汇总表中的“方差”和 ANOVA 表中的 SS,您将注意到,除了模型 3 中用“<---”指向的一个条目外,所有这些值在以下全部三个模型中的计算都不正确。

在所有强调型情形中,您通过使用 Excel 2003 及 Excel 更高版本得到的 ANOVA 结果和基本情形中之前的结果一致(它们应该相同)。

ANOVA 1 强调模型(带有较大数据值)

收起该表格展开该表格
100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
Anova:单因素
汇总
计数平均值方差
列 166000000211E+084.8
列 244000000201E+088
列 366000000331E+081.6
ANOVA
方差来源SSdfMSFP-值F 临界值
组之间020013.805567
组内64134.923077
总计6415

ANOVA 2 强调模型(带有较大数据值)

收起该表格展开该表格
组 1组 2组 3
试验 1100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
试验 2100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
Anova:包含重复的双因素
汇总组 1组 2组 3总计
试验 1
计数3339
3000000063000000123000000129E+08
平均值1000000021000000041000000041E+08
方差0404
试验 2
计数3339
3000000153000000303000000219E+08
平均值1000000051000000101000000071E+08
方差0406
总计
计数666
600000021600000042600000033
平均值100000004100000007100000005.5
方差4.814.41.6
ANOVA
方差来源SSdfMSFP-值F 临界值
样本64164240.0003674.747221
3221660.0156253.88529
交互作用3221660.0156253.88529
之内32122.666666667
总计12817

ANOVA 3 强调模型(带有较大数据值)

收起该表格展开该表格
贫乏100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
中等100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
充足100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
Anova:无重复的双因素
汇总计数平均值方差
行 133000000061000000020
行 233000000101000000032
行 333000000141000000052
行 433000000181000000064<---
行 533000000221000000076
行 6330000002610000000910
行 7330000003110000001012
行 8330000002610000000910
行 9330000002110000000718
列 199000000451000000058
列 2990000007810000000914
列 399000000511000000064
ANOVA
方差来源SSdfMSFP-值F 临界值
12881620.1132812.591094
3221620.1677723.633716
误差128168
总计28826

属性

文章编号: 829215 - 最后修改: 2007年2月26日 - 修订: 2.0
这篇文章中的信息适用于:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
关键字:?
kbinfo kbprogramming kbfunctions kbfuncstat kbexpertisebeginner KB829215
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