Excel 统计函数:TREND

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

本文内容

概要

本文介绍了 Microsoft Office Excel 2003 及更高版本的 Excel 中的 TREND 函数,说明了如何使用此函数,并且将 Excel 2003 及更高版本的 Excel 中的此函数结果与更早版本的 Excel 中的 TREND 结果进行了比较。

TREND 是通过调用相关函数 LINEST 来进行计算的。本文总结了 Excel 2003 及更高版本的 Excel 中对 LINEST 的大量更改,并说明了这些更改对于 TREND 的意义。

Microsoft Excel 2004 for Macintosh 信息

Microsoft Excel 2004 for Macintosh 中统计函数进行更新所使用的算法与 Excel 2003 及更高版本的 Excel 中统计函数进行更新所使用的算法相同。本文中介绍函数工作方式或者如何针对 Excel 2003 及更高版本的 Excel 修改函数的所有信息同样适用于 Excel 2004 for Macintosh。

更多信息

TREND(known_y's, known_x's, new_x's, constant) 函数用于执行线性回归。使用最小二乘准则,TREND 尝试根据此准则得到最佳拟合。known_y's 表示“因变量”的数据,known_x's 表示一个或多个“自变量”的数据。TREND 帮助文件讨论了可能省略第二个或第三个参数的极少数情况。

如果将最后一个参数“constant”设置为 TRUE,则您会希望回归模型包含一个系数以用于回归模型中的截距。如果将最后一个参数设置为 FALSE,则不包括截距项,并将强制拟合回归穿过原点。最后一个参数是可选的,如果省略,则被解释为 TRUE。

为便于本文其余部分进行说明,假设将数据排列在列中,这样,known_y's 是一列 y 数据,known_x's 是一列或多列 x 数据。当然,所有这些列的尺寸(长度)必须是相等的。假设将 new_x's 也排列在列中,new_x's 与 known_x's 的列数必须相同。如果没有将数据排列在列中,本文中的所有观察值同样正确,只不过讨论此个别情况(最常用的)更容易些。

在计算最佳拟合回归模型(通过实际调用 Excel 的 LINEST 函数)后,TREND 返回与 new_x's 关联的预测值。

本文举例说明 TREND 与 LINEST 如何相关,并指出了在 Microsoft Excel 2002 及更早版本的 Excel 中 LINEST 存在的问题。这些问题会转换为 TREND 的问题。虽然没有针对 Excel 2003 及更高版本的 Excel 重写 TREND 的代码,但是已对 LINEST 代码进行了大量更改(和改进)。

TREND 可有效地调用 LINEST,执行 LINEST,使用 LINEST 输出中的回归系数来计算与每行 new_x's 关联的预测的 y 值,并将此列预测的 y 值显示给您。因此,您必须知道在 LINEST 的执行过程中存在的问题。

作为本文的补充,强烈建议您阅读以下关于 LINEST 的文章。该文章包含几个示例,并记录了在 Excel 2002 及更早版本的 Excel 中 LINEST 存在的问题。

有关其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
828533 Excel 2003 及更高版本的 Excel 中的 LINEST 函数说明


因为此处主要讨论 Excel 2002 及更早版本的 Excel 中的数值问题,所以本文没有提供许多有关如何使用 TREND 的实际示例。TREND 的帮助文件中包含有用的示例。

语法

TREND(known_y's, known_x's, new_x's, constant)
参数 known_y's、known_x's 和 new_x's 必须是具有相关尺寸的数组或单元格区域。如果 known_y's 包含 1 列 m 行,则 known_x's 包含 c 列 m 行,其中 c 大于或等于 1。请注意,c 是预测值变量的数目;m 是数据点数目。new_x's 必须包含 c 列 r 行,其中 r 大于或等于 1。(如果数据排列在行中而不是排列在列中,则必须保持类似的尺寸关系。)constant 是一个逻辑参数,必须设置为 TRUE 或 FALSE(或者 0 或 1,Excel 分别将其解释为 FALSE 或 TRUE。)TREND 的最后三个参数都是可选的;有关省略第二个参数、第三个参数或将这两个参数都省略掉的选项,请参阅 TREND 帮助文件。省略第四个参数将被解释为 TRUE。

TREND 的最常见的用法包括两个包含数据的单元格区域,如 TREND(A1:A100, B1:F100, B101:F108, TRUE)。请注意,因为通常有多个预测变量,所以在此示例中第二个参数包含多列。在此示例中,有 100 个对象,每个受试对象对应一个因变量值 (known_y),每个受试对象对应五个自变量值 (known_x's)。有 8 个额外的假设受试对象,在其中要使用 TREND 来计算预测的 y 值。

用法示例

提供一个 Excel 工作表示例来说明以下重要的概念:
  • TREND 与 LINEST 的交互方式
  • 在 Excel 2002 及更早版本的 Excel 中的 TREND(或 LINEST)中因 known_x's 共线性而出现的问题
有关 LINEST 的文章提供了关于 LINEST 的第二个项目符号方面的广泛讨论。

为了说明 TREND 共线性,请创建一个空白 Excel 工作表,复制下表,选中空白 Excel 工作表中的单元格 A1,然后粘贴各项,这样下表将填满工作表中的单元格 A1:K35。
收起该表格展开该表格
y:x's:
1121
2341
3451
4671
5781
new x's:911
1214
TREND 使用列 B、C:Excel 2003 以前的版本中的值:Excel 2003 及更高版本的 Excel 中的值:
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!6.15789473684211
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!8.13157894736842
TREND 只使用列 B
=TREND(A2:A6,B2:B6,B7:B8,TRUE)6.15789473684216.15789473684211
=TREND(A2:A6,B2:B6,B7:B8,TRUE)8.131578947368428.13157894736842
Excel 2003 及更高版本的 Excel 的 LINEST 结果的拟合值
使用列 B、C使用列 B
= K24*1 + J24*B7 + I24*C7=J31*1+I31*B7
=K24*1 + J24*B8 + I24*C8=J31*1 +I31*B8
LINEST 使用列 B、C:Excel 2003 以前的版本中的值:Excel 2003 及更高版本的 Excel 中的值:
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.6578947368421050.236842105263158
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.0438596491228070.206652964726136
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!0.9868421052631580.209426954145848#N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!2253#N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!9.868421052631580.131578947368421#N/A
LINEST 只使用列 B
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.6578947368421050.2368421052631590.6578947368421050.236842105263158
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.04385964912280710.2066529647261360.0438596491228070.206652964726136
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.9868421052631580.2094269541458480.9868421052631580.209426954145848
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)224.99999999999932253
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)9.868421052631580.1315789473684219.868421052631580.131578947368421
将此表粘贴到新的 Excel 工作表中后,单击“粘贴选项”,然后单击“匹配目标格式”。在粘贴区域仍处于选中状态的情况下,根据所运行的 Excel 版本使用以下适合的过程:
  • 在 Microsoft Office Excel 2007 中,单击“开始”选项卡,单击“单元格”组中的“格式”,然后单击“自动调整列宽”。
  • 在 Excel 2003 中,指向“格式”菜单上的“列”,然后单击“最适合的列宽”。
TREND 的数据位于单元格 A1:C8 中。(单元格 D2:D6 中的各项不属于数据内容,而是用于本文后面的说明。)更早版本的 Excel 和更高版本的 Excel 的两个不同模型的 TREND 结果分别显示在单元格 E10:E16 和单元格 I10:116 中。单元格 A10:A16 中的结果将对应于您所使用的 Excel 版本。目前,本文主要讨论在调查 TREND 如何调用 LINEST 以及 TREND 如何使用 LINEST 结果时,Excel 2003 及更高版本的 Excel 中的结果。

TREND 与 LINEST 可视为进行如下交互:
  1. 您调用 TREND(known_y's, known_x's, new_x's, constant)。
  2. TREND 调用 LINEST(known_y's, known_x's, constant, TRUE)。
  3. 从 LINEST 调用获取回归系数;这些系数显示在 LINEST 输出表的第一行中。
  4. 对于每一个 new_x's 行,根据这些 LINEST 系数和该行中的 new_x's 值,计算预测的 y 值。
  5. 在步骤 4 中计算得出的值将返回到对应于该 new_x's 行的 TREND 输出的相应单元格中。
如果 TREND 要返回适当的结果,则 LINEST 最好在步骤 3 中生成适当的结果。此处的问题是由于预测值列共线性导致的。

如果可以将至少一列 c 表示为 c1、c2 及其他列的乘积和,则预测值列 (known_x's) 是共线性的。列 c 经常被称为冗余列,因为其中包含的信息可根据列 c1、c2 和其他列构建。共线性存在的基本原则是结果应该不受在原始数据中包括或删除冗余列的影响。因为在 Excel 2002 及更早版本的 Excel 中 LINEST 没有考虑到共线性,所以很容易违反此原则。如果可以将至少一列 c 表示为与列 c1、c2 及其他列的乘积和几乎相等,则预测值列接近于共线性。在这种情况下,“几乎相等”表示 c 中的各项与 c1、c2 和其他列的加权和中的对应各项的方差和非常小;例如,“非常小”可能是指小于 10^(-12)。

第 10 至 12 行中的第一个模型使用列 B 和 C 作为预测值,并请求 Excel 根据该模型设置 constant(将最后一个参数设置为 TRUE)。然后 Excel 有效地插入一个额外的预测值列,该列类似于单元格 D2:D6。很明显,列 C 中第 2 至 6 行中的各项与列 B 和 D 中对应的各项之和完全相等。因此存在共线性,这是因为列 C 是以下各列的乘积和:
  • 列 B
  • 由于 LINEST 的第三个参数(与 TREND 的第四个参数相同)被省略或为 TRUE(“正常”情况下)而插入的 Excel 的额外列 1s
这会导致此类数值问题:Excel 2002 及更早版本的 Excel 无法计算出结果,而 TREND 输出表被 #NUM! 填满了。

第 14 至 16 行中的第二个模型是任何版本的 Excel 都可成功处理的模型。不存在共线性,您可以再次请求 Excel 根据该模型设置 constant。在这里提供此模型的原因有二个。

第一,它可能是最典型的实际情况:不存在共线性。在所有的 Excel 版本中,都可很好地处理这些情况。如果您拥有的是更早版本的 Excel,您也不必担心,在最常见的实际情况下,不可能出现数值问题。

第二,此示例用于比较 Excel 2003 及更高版本的 Excel 在两种模型中的行为。大多数主要统计包都可分析共线性,从模型中删除等于其他各列乘积和的列,并出现警告消息,例如“列 C 是线性的,依赖于其他预测值列,已将该列从分析中删除”。

在 Excel 2003 及更高版本的 Excel 中,此类消息不是以警告或文本字符串的形式传送的,而是以 LINEST 输出表形式传送的。TREND 不具有向您传送此类消息的机制。在 LINEST 输出表中,等于 0 且标准误差为 0 的回归系数对应于从模型中删除的列的系数。LINEST 输出表包括在第 23 至 35 行中,对应于第 10 至 16 行中的 TREND 输出。单元格 I24:I25 中的各项显示了一个已删除的冗余预测值列。在本例中,LINEST 选择删除列 C(单元格 I24、J24、K24 中的系数分别对应于列 C、B 和 Excel 的 constant 列)。当存在共线性时,可以删除相关列中的任意一列,选择是任意的。

在第 30 至 35 行中的第二个模型中,不存在共线性,没有删除任何列。您可以看到在两个模型中预测的 y 值是相同的。此问题发生的原因是,删除等于其他各列乘积和的冗余列不会降低与生成模型的拟合良好程度。删除这些列只是因为在尝试得到最佳的最小二乘法拟合时,这些列不表示任何增加值。

另外,如果您查看单元格 I23:K35 中 Excel 2003 及更高版本的 Excel 中的 LINEST 输出,您会发现输出表的最后三行是相同的,单元格 I31:J32 与单元格 J24:K25 中的项完全一致。这证明了:在模型中包括被发现是冗余列的列 C 时获得的结果(单元格 I24:K28 中的输出)与在运行 LINEST 之前删除列 C 时获得的结果(单元格 I31:J35 中的输出)相同。这满足共线性存在的基本原则。

在单元格 A18:C21 中,本文使用 Excel 2003 及更高版本的 Excel 数据来说明 TREND 如何获取 LINEST 输出并计算相关的预测的 y 值。通过查看单元格 A20:A21 和单元格 C20:C21 中的公式,您可以了解到对于两个模型(使用列 B、C 作为预测值;只使用列 B 作为预测值)中的每一个,LINEST 系数如何与单元格 B7:C8 中的 new_x's 数据组合。

在 Excel 2003 及更高版本的 Excel 中,由于使用完全不同的方法来得出回归系数,因此 LINEST 可识别共线性。此方法称为 QR 分解。有关 LINEST 的文章介绍了对小型示例执行 QR 分解算法的步骤。

更早版本的 Excel 中的结果总结

在 Excel 2002 及更早版本的 Excel 中,TREND 结果受到 LINEST 中不准确结果的不利影响。

LINEST 是使用没有注意到共线性问题的方法计算的。共线性的存在会导致舍入错误、不适当的回归系数标准误差和不适当的自由度。有时,舍入问题会严重到 LINEST 将其输出表填满了 #NUM!。

在大多数实际情况中,如果您可以确信没有共线的(或几乎共线的)预测值列,则 LINEST 通常可提供可接受的结果。因此,在使用 TREND 时,如果您确信没有共线的(或几乎共线的)预测值列,则您同样可以放心使用。

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

LINEST 中的改进包括转为使用 QR 分解方法来确定回归系数。QR 分解具有以下优势:
  • 数值稳定性更好(通常舍入错误较少)
  • 对共线性问题的分析
本文中说明的在 Excel 2002 及更早版本的 Excel 中出现的所有问题已在 Excel 2003 及更高版本的 Excel 中得到了纠正。

结束语

因为在 Excel 2003 及更高版本的 Excel 中 LINEST 已得到了很大的改进,所以 TREND 的性能已得到改进。如果您使用的是更早版本的 Excel,请在使用 TREND 之前确保预测值列不共线。

本文和有关 LINEST 的文章中提供的许多资料刚开始可能令 Excel 2002 及更早版本的 Excel 的用户担忧。不过,请注意,共线性问题只在很少情况下出现。在没有共线性时,更早版本的 Excel 可提供可接受的 TREND 结果。

幸运的是,LINEST 的改进还对分析工具库的线性回归工具(这叫做 LINEST)和两个其他相关的 Excel 函数产生了有利影响:LOGEST 和 GROWTH。

属性

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