使用 Excel 2003 中分析工具库的 t-检验时可能得到不正确的结果和有误导性的标签

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

本文内容

概要

本文描述名为“成对双样本平均值”工具的分析工具库 t-检验。对于 Microsoft Office Excel 2003 及 Excel 更高版本,此工具尚未更改。但是,如果缺少数据,此工具将得出不正确的结果。此外,不管是否缺少数据,此工具输出中的标签都带有误导性。

用于 Macintosh 的 Microsoft Excel 2004 信息

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

更多信息

通常,当您有同一治疗实验中之前和之后的测量数据时,将会应用成对双样本 t-检验(有时名为匹配成对 t-检验)。例如,您可能测量了受检者在 30 天节食计划之前和之后的体重。

通常,您会删除有关缺少之前或之后测量数据的任何受检者的数据。受检者的数据不完整会使有关该受检者的信息毫无用处。不幸的是,此分析工具库工具的执行并不像通常做法那样进行。首先,此分析工具库工具会计算具有之前测量数据的受检者的数量,以及具有之后测量数据的受检者的数量。如果这些总数不同,您将收到一条错误消息,并且此分析工具库工具将不会继续。因此,举例来说,如果有 49 名受检者同时具有之前和之后的测量数据,而第 50 名受检者只有之前的测量数据,则该分析工具库工具将不会进行分析。

如果缺少之前数据的受检者数量等于缺少之后数据的受检者数量,并且此数量为正,则该工具将执行不正确的分析。例如,假设有 50 名受检者。受检者 A 缺少之前的测量数据,受检者 B 缺少之后的测量数据,其他 48 名受检者没有缺少数据。该工具将计算出 49 个之前的测量数据和 49 个之后的测量数据;它的执行方式就好像有 49 名没有缺少数据的受检者一样。这样就与您去除缺少之前值或之后值的任何受检者的意图事与愿违。本例中受检者的数量应为 48,而不是 49。因此,该工具使用的自由度数字不正确。此外,由于工具既没有丢弃受检者 A 的之后测量数据,也没有丢弃受检者 B 的之前测量数据,因此将会在计算用于 t-统计的样本均值时包括这两个测量数据。这样,这些计算所得的样本均值将不正确。

总之,在缺少数据的情况下使用该工具是不适当的,因为该工具要么不进行计算,要么使用不正确的公式进行计算。当缺少之前数据的受检者数量与缺少之后数据的受检者数量相等时,将会发生后一种情况。

本文的“用法示例”一节中的例子阐释了这些问题,同时指出了该工具的输出中会引起混淆的标签。如果您在使用该工具前无法验证是否缺少数据,本文的“替代方法”一节中给出了一种替代方法。

用法示例

为了阐明缺少数据的问题,请创建一个空白 Excel 工作表,然后复制下表。选中空白 Excel 工作表中的 A1 单元格,然后粘贴条目以使表格填充工作表中的 A1:I52 单元格。
收起该表格展开该表格
实验 1实验 2实验 3经过修改的实验 3,删除
之前之后之前之后之前之后缺少数据的受检者
200170200170200170200170
190180190180190180190180
180175180175180175180175
170175170175170175170175
160165160165160165160165
150140150140150140150140
140130140130130130125
130125130125130125120125
120125120125120125110100
110100110100110100
100100100100
双尾 t-检验的行为
=TTEST(A3:A13, B3:B13,2,1)=TTEST(C3:C13, D3:D13, 2, 1)=TTEST(E3:E13, F3:F13, 2, 1)
=TTEST(C3:C12, D3:D12, 2, 1)=TTEST(G3:G11, H3:H11, 2, 1)
用于实验 1 的 ATP 工具:
t-检验:成对双样本平均值
可变因素 1可变因素 2
平均值150144.090909090909
方差1100914.090909090909
观察值数1111
Pearson 相关系数0.952384533866487
假设平均差0
df10
t 统计值1.92092590483801
P(T<=t) 单尾0.0418403929085198
t 单尾临界值1.81246110219722
P(T<=t) 双尾0.0836807858170396
t 双尾临界值2.22813884242587
用于实验 2 的 ATP 工具:
由于数据点数量不相等,因此将不会进行计算
用于实验 3 的 ATP 工具:
t-检验:成对双样本平均值
可变因素 1可变因素 2
平均值151148.5
方差1210778.055555555556
观察值数1010
Pearson 相关系数0.936537537274845
假设平均差0
自由度9
t 统计值0.141327169509421
P(T<=t) 单尾0.445362157564494
t 单尾临界值1.83311292255007
P(T<=t) 双尾0.890724315128988
t 双尾临界值2.26215715817358
将此表粘贴到 Excel 工作表中后,单击“粘贴选项”按钮,然后单击“匹配目标格式”。在粘贴的区域仍然处于选定状态的情况下,根据您所运行的具体 Excel 版本使用以下过程之一:
  • 在 Microsoft Office Excel 2007 中,单击“开始”选项卡,在“单元格”组中单击“格式”,然后单击“自动调整列宽”。
  • 在 Excel 2003 中,指向“格式”菜单上的“列”,然后单击“最适合的列宽”。
您可以使用该工作表来比较 Excel 的 TTEST 函数的结果与分析工具库工具的结果。实验 1 显示了有关 11 名受检者的完整数据。A16 单元格中 TTEST 的值是 t-统计值将大于观察值的概率(假设 t-分布的自由度为 10)。此值 (0.837) 也显示在该工具位于 B32 单元格的输出中。A32 单元格中的标签应显示为“P(T >= |t|) 双尾”(而不是“P(T<=t) 双尾”),但数值答案是正确的,因为实验 1 中没有缺少数据。同样,A30 单元格中的标签应显示为“P(T >= |t|) 单尾”(而不是“P(T<=t) 单尾”)。“t 临界值”截止值是正确的。它们对应于默认的显著性级别 0.05,并且使用正确的自由度数字 10。

实验 2 缺少某一名受检者的“之后”测量数据,未缺少其他数据。该工具拒绝计算。单元格 A16 和 A17 中 TTEST 的值相同。在 A16 单元格中,使用了数据单元格区域 C3:D13;此区域包括最后一名受检者,也就是唯一一名缺少数据的受检者。在 A17 单元格中,使用了数据单元格区域 C3:D12;此区域对应于具有前 10 名受检者并且不缺少数据的实验。结果相同的事实表明,当在 A16 单元格中调用 TTEST 时,TTEST 会正确地丢弃缺少数据的受检者。

实验 3 缺少两名不同的受检者的测量数据,其中一名缺少“之前”的测量数据,另一名缺少“之后”的测量数据。经过修改的实验 3 显示了其余 9 名不缺少数据的受检者。E16 和 E17 单元格中的 TTEST 结果相同。在 E16 单元格中,对 E3:F13 单元格中的实验 3 数据调用了 TTEST。在 E17 单元格中,对 G3:H11 单元格中实验 3 经过修改的数据调用了 TTEST。结果是相同的,原因是 TTEST 正确地丢弃了实验 3 中的第 7 名和第 11 名受检者,即缺少数据的两名受检者。如果检查工具的实验 3 输出,B44 和 C44 单元格中之前和之后观察值的数量在每种情况下都是 10。很容易就可以验证出 SUM(E3:E13) 为 1510,而 SUM(F3:F13) 为 1485;因为每个区域中都有 10 个观察值,各自的平均值为 151 和 148.5,如 B42 和 C42 单元格中所示。因此,工具没有丢弃任何受检者,并且在其分析中包括了第 7 名受检者的之后测量数据,以及第 11 名受检者的之前测量数据。B47 单元格中的自由度数字不正确,因为应该有 9 名受检者,而自由度为 8。这就使得 B50 和 B52 单元格中的截止值条目不正确(除此之外,A50 和 A52 单元格中对应于这些条目的标签也带有误导性。)

Excel 所有版本中的结果

不幸的是,此工具对于 Excel 2003 及 Excel 更高版本并没有得到纠正。

针对 Excel 所有版本的建议替代方法

可以在使用该工具前去除缺少数据的受检者。不过,您可能不想用这种方式编辑 Excel 工作表。以下步骤中显示了去除缺少数据的受检者的一个过程。要去除缺少数据的受检者,请按照下列步骤操作:
  1. 将两个数据区域复制到工作表的一个新范围中。
  2. 从两个区域共同的底部向上浏览数据。
    1. 如果底部一行包含缺少的数据,则清除底部一行。这将会缩小数据区域。转到步骤 3。
    2. 确定在底部一行上方,但最靠近缺少数据的底部的行 r。
      1. 复制行 r 下方的所有数据。
      2. 选中行 r,然后将复制的数据粘贴到其中。
      3. 清除底部数据行(该行现在将在最后一行数据的下一行重复)。这将会缩小数据区域。
  3. 重复步骤 2,直至再没有缺少的数据。
注意:如果您能保证没有缺少观察数据,则可以使用该工具。

您可以实现该工具的大部分(但不是全部)输出,而不用变换数据。您需要耗费很大的精力才能找到“平均值”、“方差”和“观察值数”的正确值。该工具将单独检查“之前”和“之后”数据,因此找到的值不正确。该工具的自由度是“观察值数 - 1”得出的公值;因此,如果缺少数据,自由度同样也不正确。需要耗费很大的精力才能找到 t 统计值,因为您必须同时检查“之前”和“之后”数据。

不过,您可以通过将 PEARSON 或 CORREL 应用到两个数据区域来找到 Pearson 相关系数。这两个 Excel 函数都能正确处理缺少数据的情况。此外,通过调用 Excel 的 TTEST 函数(该函数可正确处理缺少数据的情况),您可以找到与数据关联的 t 单尾和双尾概率。对于实验 3 中的单尾和双尾概率,您可以分别调用 TTEST(E3:E13, F3:F13, 1, 1) 和 TTEST(E3:E13, F3:F13, 2, 1)。您还可以验证这些函数的结果是否与实验 1 中工具的结果一致,在实验 1 中,工具执行正常,原因是没有缺少数据。针对实验 1 的对应调用分别为 TTEST(A3:A13, B3:B13, 1, 1) 和 TTEST(A3:A13, B3:B13, 2, 1)。

对于临界截止值,您必须确定自由度数字。在实验 1、2 和 3 中,正确的自由度数字分别为 10、9 和 8。这些数字始终比数据中没有缺少之前或之后测量数据的有用受检者的数量少 1。举例来说,对于实验 3,您可以在 J3 单元格中输入 =IF(OR(ISBLANK(E3), ISBLANK(F3)), 0, 1),然后将此公式向下填充到 J4:J13 中,并通过在 J14 单元格输入以下公式来找到自由度:=SUM(J3:J13) – 1。

在确定了自由度后,您可以使用 Excel 的 TINV 函数。在显著性级别为 0.05 的情况下,针对实验 1、2 和 3 的调用将分别为 TINV(0.05, 10)、TINV(0.05, 9) 和 TINV(0.05, 8)。这些函数将返回“t 双尾临界值”的值。要获得“t 单尾临界值”,您将使用显著性级别加倍的相似调用,比如,分别为 TINV(0.10, 10)、TINV(0.10, 9) 和 TINV(0.10, 8)。

结束语

除非能确保没有缺少数据点,否则不要使用分析工具库的“t-检验成对双样本平均值”工具。本文介绍了通过使用 Excel 函数(而不是分析工具库)实现该工具大部分功能的建议措施。

该工具还提供了带有误导性的“P(T<=t)”标签。本文描述了正确的显示。

属性

文章编号: 829252 - 最后修改: 2007年2月26日 - 修订: 3.0
这篇文章中的信息适用于:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
关键字:?
kbprb kbformula kbfunctions kbfuncstat kbexpertisebeginner KB829252
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