Excel 2003完整教程:LINEST

使用最小二乘法对已知数据进行最佳直线拟合,并返回描述此直线的数组。因为此函数返回数值数组,所以必须以数组公式的形式输入。

直线的公式为:

y = mx + b or

y = m1x1 + m2x2 + ... + b(如果有多个区域的 x 值)

式中,因变量 y 是自变量 x 的函数值。M 值是与每个 x 值相对应的系数,b 为常量。注意 y、x 和 m 可以是向量。LINEST 函数返回的数组为 {mn,mn-1,...,m1,b}。LINEST 函数还可返回附加回归统计值。

语法

LINEST(known_y's,known_x's,const,stats)

Known_y's? 是关系表达式 y = mx + b 中已知的 y 值集合。

Known_x's? 是关系表达式 y = mx + b 中已知的可选 x 值集合。

Const? 为一逻辑值,用于指定是否将常量 b 强制设为 0。

Stats? 为一逻辑值,指定是否返回附加回归统计值。

附加回归统计值如下:

统计值说明
se1,se2,...,sen系数 m1,m2,...,mn 的标准误差值。
seb常量 b 的标准误差值(当 const 为 FALSE时,seb = #N/A)
r2判定系数。Y 的估计值与实际值之比,范围在 0 到 1 之间。如果为 1,则样本有很好的相关性,Y 的估计值与实际值之间没有差别。如果判定系数为 0,则回归公式不能用来预测 Y 值。有关计算 r2 的方法的详细信息,请参阅本主题后面的“说明”。
seyY 估计值的标准误差。
FF 统计或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。
df自由度。用于在统计表上查找 F 临界值。所查得的值和 LINEST 函数返回的 F 统计值的比值可用来判断模型的置信度。有关如何计算 df,请参阅在此主题中后面的“说明”。示例 4 说明了 F 和 df 的使用。
ssreg回归平方和。
ssresid残差平方和。 有关计算 ssreg 和 ssresid 的方法的详细信息,请参阅本主题后面的“说明”。

下面的图示显示了附加回归统计值返回的顺序。

工作表

说明

示例 1? 斜率和 Y 轴截距

如果您将示例复制到空白工作表中,可能会更易于理解该示例。

操作方法

  1. 创建空白工作簿或工作表。
  2. 请在“帮助”主题中选取示例。不要选取行或列标题。

    从帮助中选取示例。

    从帮助中选取示例。

  3. 按 Ctrl+C。
  4. 在工作表中,选中单元格 A1,再按 Ctrl+V。
  5. 若要在查看结果和查看返回结果的公式之间切换,请按 Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
 
1
2
3
4
5
AB
已知 y已知 x
10
94
52
73
公式公式
=LINEST(A2:A5,B2:B5,,FALSE)

注释示例中的公式必须以数组公式输入。在将公式复制到一张空白工作表后,选择以公式单元格开始的区域 A7:B7。按 F2,再按 Ctrl+Shift+Enter。如果公式不是以数组公式输入,则返回单个结果值 2。

当以数组输入时,将返回斜率 2 和 y 轴截距 1。

示例 2? 简单线性回归

如果您将示例复制到空白工作表中,可能会更易于理解该示例。

操作方法

  1. 创建空白工作簿或工作表。
  2. 请在“帮助”主题中选取示例。不要选取行或列标题。

    从帮助中选取示例。

    从帮助中选取示例。

  3. 按 Ctrl+C。
  4. 在工作表中,选中单元格 A1,再按 Ctrl+V。
  5. 若要在查看结果和查看返回结果的公式之间切换,请按 Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
 
1
2
3
4
5
6
7
AB
销售
13100
24500
34400
45400
57500
68100
公式说明(结果)
=SUM(LINEST(B2:B7, A2:A7)*{9,1})估算第 9 个月的销售值 (11000)

通常,SUM({m,b}*{x,1}) 等于 mx + b,即给定 x 值的 y 的估计值。也可以使用 TREND 函数。

示例 3? 多重线性回归

假设有开发商正在考虑购买商业区里的一组小型办公楼。

开发商可以根据下列变量,采用多重线性回归的方法来估算给定地区内的办公楼的价值。

变量代表
y办公楼的评估值
x1底层面积(平方英尺)
x2办公室的个数
x3入口个数
x4办公楼的使用年数

本示例假设在自变量(x1、x2、x3 和 x4)和因变量 (y) 之间存在线性关系。其中 y 是办公楼的价值。

开发商从 1,500 个可选的办公楼里随机选择了 11 个办公楼作为样本,得到下列数据。“半个入口”指的是运输专用入口。

如果您将示例复制到空白工作表中,可能会更易于理解该示例。

操作方法

  1. 创建空白工作簿或工作表。
  2. 请在“帮助”主题中选取示例。不要选取行或列标题。

    从帮助中选取示例。

    从帮助中选取示例。

  3. 按 Ctrl+C。
  4. 在工作表中,选中单元格 A1,再按 Ctrl+V。
  5. 若要在查看结果和查看返回结果的公式之间切换,请按 Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
 
1
2
3
4
5
6
7
8
9
10
11
12
ABCDE
底层面积 (x1)办公室的个数 (x2)入口个数 (x3)办公楼的使用年数 (x4)办公楼的评估值 (y)
23102220142,000
23332212144,000
235631.533151,000
23793243150,000
24022353139,000
24254223169,000
244821.599126,000
24712234142,900
24943323163,000
25174455169,000
25402322149,000
公式
=LINEST(E2:E12,A2:D12,TRUE,TRUE)

注释示例中的公式必须以数组公式输入。在将公式复制到一张空白工作表后,选择以公式单元格开始的区域 A14:E18。按 F2,再按 Ctrl+Shift+Enter。如果公式不是以数组公式输入,则返回单个结果值 -234.2371645。

当作为数组输入时,将返回下面的回归统计值,可用该值可识别所需的统计值。

工作表

多重回归公式,y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b,可通过第 14 行的值得到:

y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318

现在,开发商用下面公式可得到办公楼的评估价值,其中面积为 2,500 平方英尺、3 个办公室、2 个入口,已使用 25 年:

y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261

或者,可将下表复制到示例工作簿的单元格 A21。

底层面积 (x1)办公室的个数 (x2)入口个数 (x3)办公楼的使用年数 (x4)办公楼的评估值 (y)
25003225=D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

也可以用 TREND 函数计算此值。

示例 4? 使用 F 和 R2 统计

在上例中,判定系数(或 r2)为 0.99675(函数 LINEST 的输出单元格 A17 中的值),表明在自变量与销售价格之间存在很大的相关性。可以通过 F 统计来确定具有如此高的 r2 值的结果偶然发生的可能性。

假设事实上在变量间不存在相关性,但选用 11 个办公楼作为小样本进行统计分析却导致很强的相关性。术语“Alpha”表示得出这样的相关性结论错误的概率。

LINEST 输出中的 F 和 df 可被用于计算意外出现的较高 F 值的可能性。F 可与发布的 F 分布表中的值进行比较,或者 Excel 的 FDIST 可被用于计算意外出现的较高 F 值的概率。相应的 F 分布具有 v1 和 v2 自由度。如果 n 是数据点的个数,且 const = TRUE 或被删除,那么 v1 = n – df – 1 且 v2 = df。(如果 const = FALSE,那么 v1 = n – df 且 v2 = df。)Excel 的 FDIST(F,v1,v2) 将返回意外出现的较高 F 值的概率。在示例 4 中,df = 6 (cell B18) 且 F = 459.753674 (cell A18)。

假设存在 Alpha 值等于 0.05,v1 = 11 – 6 – 1 = 4 且 v2 = 6,那么 F 的临界值是 4.53。因为 F = 459.753674 远大于 4.53,所以意外出现高 F 值的可能性非常低。(如果 Alpha = 0.05,假设当 F 超过临界值 4.53 时,没有 known_y's 和 known_x's 之间的关系可被拒绝)使用 Excel 的 FDIST 可获得意外出现的较高 F 值的概率。FDIST(459.753674, 4, 6) = 1.37E-7,一个极小的概率。于是可以断定,无论通过在表中查找 F 的临界值,还是使用 Excel 的 FDIST,回归公式都可用于预测该区域中的办公楼的评估价值。请注意,使用在上一段中计算出的 v1 和 v2 的正确值是非常关键的。

示例 5? 计算 T 统计

另一个假设检验可以检验示例中的每个斜率系数是否可以用来估算示例 3 中的办公楼的评估价值。例如,如果要检验年数系数的统计显著水平,用 13.268(单元格 A15 里的年数系数的估算标准误差)去除 -234.24(年数斜率系数)。下面是 T 观察值:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

如果 t 的绝对值足够大,那么可以断定倾斜系数可用来估算示例 3 中的办公楼的评估价值。下表显示了 4 个 t 观察值的绝对值。

如果查阅统计手册里的表格,将会发现:双尾、自由度为 6、Alpha = 0.05 的 t 临界值为 2.447。该临界值还可使用 Excel 的 TINV 函数计算,TINV(0.05,6) = 2.447。既然 t 的绝对值为 17.7,大于 2.447,则年数对于估算办公楼的评估价值来说是一个显著变量。用同样方法,可以测试自变量的统计显著水平。下面是每个自变量的 t 观察值。

变量t 观察值
底层面积5.1
办公室的个数31.3
入口个数4.8
使用年数17.7

这些值的绝对值都大于 2.447;因此,回归公式的所有变量都可用来估算区域内的办公楼的评估价值。<SPAN FPRev

Excel 2003完整教程:LINEST·相关导航

上页:Excel 2003完整教程:LEN 下页:Excel 2003完整教程:LN

Excel 2003完整教程:LINEST

Excel 2003完整教程:LNExcel 2003完整教程:LOG
Excel 2003完整教程:LOG10Excel 2003完整教程:LOGEST
Excel 2003完整教程:LOGINVExcel 2003完整教程:LOGNORMDIST
Excel 2003完整教程:LOOKUPExcel 2003完整教程:LOOKUP
Excel 2003完整教程:LOOKUPExcel 2003完整教程:LOWER
Excel 2003完整教程:MATCHExcel 2003完整教程:MAX
Excel 2003完整教程:MAXAExcel 2003完整教程:MDETERM
Excel 2003完整教程:MDURATIONExcel 2003完整教程:MEDIAN
Excel 2003完整教程:MIDExcel 2003完整教程:MIN
Excel 2003完整教程:MINAExcel 2003完整教程:MINUTE
版权所有 © 中山市飞娥软件工作室 证书:粤ICP备09170368号