LINEST 函数可通过使用最小二乘法计算与现有数据最佳拟合的直线&＃xff0c;来计算某直线的统计值&＃xff0c;然后返回描述此直线的数组。也可以将 LINEST 与其他函数结合使用来计算未知参数中其他类型的线性模型的统计值&＃xff0c;包括多项式、对数、指数和幂级数。因为此函数返回数值数组&＃xff0c;所以必须以数组公式的形式输入。请按照本文中的示例使用此函数。
直线的公式为&＃xff1a;y &＃61; mx &＃43; b
- 或 -y &＃61; m1x1 &＃43; m2x2 &＃43; ... &＃43; b
如果有多个区域的 x 值&＃xff0c;其中因变量 y 值是自变量 x 值的函数。m 值是与每个 x 值相对应的系数&＃xff0c;b 为常量。注意&＃xff0c;y、x 和 m 可以是向量。LINEST 函数返回的数组为 {mn,mn-1,...,m1,b}。LINEST 函数还可返回附加回归统计值。语法LINEST(known_y&＃39;s, [known_x&＃39;s], [const], [stats])
LINEST 函数语法具有以下[color&＃61;rgb(153, 153, 153) !important]参数&＃xff1a;语法Known_y&＃39;s 必需。关系表达式 y &＃61; mx &＃43; b 中已知的 y 值集合。
如果 known_y&＃39;s 对应的单元格区域在单独一列中&＃xff0c;则 known_x&＃39;s 的每一列被视为一个独立的变量。
如果 known_y&＃39;s 对应的单元格区域在单独一行中&＃xff0c;则 known_x&＃39;s 的每一行被视为一个独立的变量。
Known_x&＃39;s 可选。关系表达式 y &＃61; mx &＃43; b 中已知的 x 值集合。
known_x&＃39;s 对应的单元格区域可以包含一组或多组变量。如果仅使用一个变量&＃xff0c;那么只要 known_y&＃39;s 和 known_x&＃39;s 具有相同的维数&＃xff0c;则它们可以是任何形状的区域。如果使用多个变量&＃xff0c;则 known_y&＃39;s 必须为向量(即必须为一行或一列)。
如果省略 known_x&＃39;s&＃xff0c;则假设该数组为 {1,2,3,...}&＃xff0c;其大小与known_y&＃39;s 相同。
const 可选。一个逻辑值&＃xff0c;用于指定是否将常量 b 强制设为 0。
如果 const 为 TRUE 或被省略&＃xff0c;b 将按通常方式计算。
如果 const 为 FALSE&＃xff0c;b 将被设为 0&＃xff0c;并同时调整 m 值使 y &＃61; mx。
stats 可选。一个逻辑值&＃xff0c;用于指定是否返回附加回归统计值。
如果 stats 为 TRUE&＃xff0c;则 LINEST 函数返回附加回归统计值&＃xff0c;这时返回的数组为 {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。
如果 stats 为 FALSE 或被省略&＃xff0c;LINEST 函数只返回系数 m 和常量 b。
附加回归统计值如下&＃xff1a;统计值说明
se1,se2,...,sen系数 m1,m2,...,mn 的标准误差值。
seb常量 b 的标准误差值(当 const 为 FALSE 时&＃xff0c;seb &＃61; #N/A)。
r2判定系数。y 的估计值与实际值之比&＃xff0c;范围在 0 到 1 之间。如果为 1&＃xff0c;则样本有很好的相关性&＃xff0c;y 的估计值与实际值之间没有差别。相反&＃xff0c;如果判定系数为 0&＃xff0c;则回归公式不能用来预测 y 值。有关如何计算 r2 的信息&＃xff0c;请参阅本主题下文中的“说明”。
seyY 估计值的标准误差。
FF 统计或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。
df自由度。用于在统计表上查找 F 临界值。将从表中查得的值与LINEST 函数返回的 F 统计值进行比较可确定模型的置信区间。有关如何计算 df 的信息&＃xff0c;请参阅本主题下文中的“说明”。
ssreg回归平方和。
ssresid残差平方和。有关如何计算 ssreg 和 ssresid 的信息&＃xff0c;请参阅本主题下文中的“说明”。
下面的图示显示了附加回归统计值返回的顺序。
说明可以使用斜率和 y 轴截距描述任何直线&＃xff1a;
斜率 (m)&＃xff1a;
通常记为 m&＃xff0c;如果需要计算斜率&＃xff0c;则选取直线上的两点&＃xff0c;(x1,y1) 和 (x2,y2)&＃xff1b;斜率等于 (y2 - y1)/(x2 - x1)。
Y 轴截距 (b)&＃xff1a;
通常记为 b&＃xff0c;直线的 y 轴的截距为直线通过 y 轴时与 y 轴交点的数值。
直线的公式为 y &＃61; mx &＃43; b。如果知道了 m 和 b 的值&＃xff0c;将 y 或 x 的值代入公式就可计算出直线上的任意一点。还可以使用 TREND 函数。当只有一个自变量 x 时&＃xff0c;可直接利用下面公式得到斜率和 y 轴截距值&＃xff1a;
斜率&＃xff1a;
&＃61;INDEX(LINEST(known_y&＃39;s,known_x&＃39;s),1)
Y 轴截距&＃xff1a;
&＃61;INDEX(LINEST(known_y&＃39;s,known_x&＃39;s),2)数据的离散程度决定了 LINEST 函数计算的直线的精确度。数据越接近线性&＃xff0c;LINEST 模型就越精确。LINEST 函数使用最小二乘法来判定数据的最佳拟合。当只有一个自变量 x 时&＃xff0c;m 和 b 是根据下面的公式计算出的&＃xff1a;
其中&＃xff0c;x 和 y 是样本平均值&＃xff1b;即&＃xff0c;x &＃61; AVERAGE(known x&＃39;s)&＃xff0c;y &＃61; AVERAGE(known_y&＃39;s)。直线和曲线拟合函数 LINEST 和 LOGEST 可用来计算与给定数据拟合程度最高的直线或指数曲线&＃xff0c;但需要判断两者中哪一个与数据拟合程度最高。可以用函数 TREND(known_y&＃39;s,known_x&＃39;s) 来计算直线&＃xff0c;或用函数GROWTH(known_y&＃39;s, known_x&＃39;s) 来计算指数曲线。这些不带new_x&＃39;s 参数的函数可在实际数据点上根据直线或曲线来返回 y 预测值的数组&＃xff0c;然后可以将预测值与实际值进行比较。可能需要用图表方式来直观地比较二者。
回归分析时&＃xff0c;Excel 会计算每一点的 y 的估计值和实际值的平方差。这些平方差之和称为残差平方和 (ssresid)。然后 Excel 会计算总平方和 (sstotal)。当参数 const &＃61; TRUE 或被省略时&＃xff0c;总平方和是 y 的实际值和平均值的平方差之和。当参数 const &＃61; FALSE 时&＃xff0c;总平方和是 y 的实际值的平方和(不需要从每个 y 值中减去平均值)。回归平方和 (ssreg) 可通过公式 ssreg &＃61; sstotal - ssresid 计算出来。残差平方和与总平方和的比值越小&＃xff0c;判定系数 r2 的值就越大&＃xff0c;r2 是用来判断从回归分析求得的公式是否足以说明变量之间关系的指示器。r2 &＃61; ssreg/sstotal。
在某些情况下&＃xff0c;一个或多个 X 列可能没有出现在其他 X 列中的附加预测值(假设 Y&＃39;s 和 X&＃39;s 位于列中)。换句话说&＃xff0c;删除一个或多个 X 列可能会得到同样精度的 y 预测值。在这种情况下&＃xff0c;应从回归模型中省略这些多余的 X 列。这种现象被称为“共线”&＃xff0c;因为任何多余的 X 列都可被表示为多个非多余 X 列的和。LINEST 函数会检查是否存在共线&＃xff0c;并在识别出多余的 X 列之后从回归模型中删除所有这些列。由于包含 0 系数以及 0 se 数值&＃xff0c;因此已删除的 X 列能在 LINEST 输出中被识别出来。如果一个或多个多余的列被删除&＃xff0c;则将影响 df&＃xff0c;原因是 df 取决于实际用于预测目的的 X 列的数量。有关计算 df 的详细信息&＃xff0c;请参阅const &＃61; TRUE 或被省略&＃xff0c;则 LINEST 函数可有效地插入所有 1 数值的其他 X 列以便为截距建立模型。如果在一列中&＃xff0c;1 对应于每个男性对象&＃xff0c;0 对应于女性对象&＃xff1b;而在另一列中&＃xff0c;1 对应于每个女性对象&＃xff0c;0 对应于男性对象&＃xff0c;那么后一列就是多余的&＃xff0c;因为其中的项可通过从所有 1 值的另一列(通过 LINEST 函数添加)中减去“男性指示器”列中的项来获得。
在没有 X 列因共线而被从模型中删除时&＃xff0c;请用以下方法计算 df 的值&＃xff1a;如果 known_x’s 有 k 列且 const &＃61; TRUE 或被省略&＃xff0c;那么 df &＃61; n – k – 1。如果 const &＃61; FALSE&＃xff0c;那么 df &＃61; n - k。在这两种情况下&＃xff0c;每次由于共线而删除一个 X 列都会使 df 的值加 1。
对于返回结果为数组的公式&＃xff0c;必须以数组公式的形式输入。
注释 在 Excel Web App 中&＃xff0c;不能创建数组公式。当输入一个数组常量(如 known_x&＃39;s)作为参数时&＃xff0c;请使用逗号分隔同一行中的各值&＃xff0c;使用分号分隔各行。分隔符可能会因区域设置的不同而有所不同。
注意&＃xff0c;如果 y 的回归分析预测值超出了用来计算公式的 y 值的范围&＃xff0c;它们可能是无效的。
LINEST 函数中使用的下层算法与 SLOPE 和 INTERCEPT 函数中使用的下层算法不同。当数据未定且共线时&＃xff0c;这些算法之间的差异会导致不同的结果。例如&＃xff0c;如果参数 known_y&＃39;s 的数据点为 0&＃xff0c;参数 known_x&＃39;s的数据点为 1&＃xff1a;
LINEST 会返回值 0。LINEST 函数的算法用来返回共线数据的合理结果&＃xff0c;在这种情况下至少可找到一个答案。
SLOPE 和 INTERCEPT 会返回错误 #DIV/0!。SLOPE 和 INTERCEPT函数的算法只用来查找一个答案&＃xff0c;在这种情况下可能有多个答案。
除了使用 LOGEST 计算其他回归分析类型的统计值外&＃xff0c;还可以使用LINEST 计算其他回归分析类型的范围&＃xff0c;方法是将 x 和 y 变量的函数作为 LINEST 的 x 和 y 系列输入。例如&＃xff0c;下面的公式&＃xff1a;
&＃61;LINEST(yvalues, xvalues^COLUMN($A:$C))
将在您使用 y 值的单个列和 x 值的单个列计算下面的方程式的近似立方(多项式次数 3)值时运行&＃xff1a;
y &＃61; m1*x &＃43; m2*x^2 &＃43; m3*x^3 &＃43; b
可以调整此公式以计算其他类型的回归&＃xff0c;但是在某些情况下&＃xff0c;需要调整输出值和其他统计值。LINEST 函数返回的 F 检验值与示例示例 1 - 斜率和 Y 轴截距
如果将示例复制到一个空白工作表中&＃xff0c;可能会更容易理解该示例。1
2
3
4
5
6
7ABC
已知y已知x
10
94
52
73
公式公式结果
&＃61;LINEST(A2:A5,B2:B5,,FALSE)A7&＃61;2, B7&＃61;1
要点 在 Excel 桌面应用程序中&＃xff0c;示例中的公式必须以数组公式输入。在将示例复制到空白工作表中后&＃xff0c;请选中以公式单元格开始的单元格区域 A7:B7&＃xff0c;按 F2&＃xff0c;再按 Ctrl&＃43;Shift&＃43;Enter。如果公式不是以数组公式的形式输入&＃xff0c;则返回单个结果值 2。
当以数组输入时&＃xff0c;将返回斜率 2 和 y 轴截距 1。示例 2 - 简单线性回归
如果将示例复制到一个空白工作表中&＃xff0c;可能会更容易理解该示例。1
2
3
4
5
6
7
8
9ABC
月份销售额
13100
24500
34400
45400
57500
68100
公式说明结果
&＃61;SUM(LINEST(B2:B7, A2:A7)*{9,1})估算九月份的销售额11000
通常&＃xff0c;SUM({m,b}*{x,1}) 等于 mx &＃43; b&＃xff0c;即给定 x 值的 y 的估计值。还可以使用 TREND 函数。示例 3 - 多重线性回归
假设有开发商正在考虑购买商业区里的一组小型办公楼。
开发商可以根据下列变量&＃xff0c;采用多重线性回归的方法来估算给定地区内的办公楼的价值。变量代表
y办公楼的评估值
x1底层面积(平方英尺)
x2办公室的个数
x3入口个数
x4办公楼的使用年数
本示例假设在自变量(x1、x2、x3 和 x4)和因变量 (y) 之间存在线性关系。其中 y 是办公楼的价值。
开发商从 1,500 个可选的办公楼里随机选择了 11 个办公楼作为样本&＃xff0c;得到下列数据。“半个入口”指的是运输专用入口。
如果将示例复制到一个空白工作表中&＃xff0c;可能会更容易理解该示例。1
2
3
4
5
6
7
8
9
10
11
12
13
14ABCDE
底层面积 (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
公式
&＃61;LINEST(E2:E12,A2:D12,TRUE,TRUE)
要点 在 Excel 桌面应用程序中&＃xff0c;示例中的公式必须以数组公式输入。在将示例复制到空白工作表中后&＃xff0c;请选中以公式单元格开始的单元格区域 A14:E18&＃xff0c;按 F2&＃xff0c;再按 Ctrl&＃43;Shift&＃43;Enter。如果公式不是以数组公式的形式输入&＃xff0c;则返回单个结果值 -234.2371645。
当作为数组输入时&＃xff0c;将返回下面的回归统计值&＃xff0c;用该值可识别所需的统计值。
将第 14 行的值代入多重回归公式 y &＃61; m1*x1 &＃43; m2*x2 &＃43; m3*x3 &＃43; m4*x4 &＃43; b 可得到&＃xff1a;y &＃61; 27.64*x1 &＃43; 12,530*x2 &＃43; 2,553*x3 - 234.24*x4 &＃43; 52,318
现在&＃xff0c;开发商用下面公式可得到办公楼的评估价值&＃xff0c;其中面积为 2,500 平方英尺、3 个办公室、2 个入口&＃xff0c;已使用 25 年&＃xff1a;y &＃61; 27.64*2500 &＃43; 12530*3 &＃43; 2553*2 - 234.24*25 &＃43; 52318 &＃61; $158,261
或者&＃xff0c;可将下表复制到为本示例创建的工作表的单元格 A21。底层面积 (X1)办公室的个数 (X2)入口个数 (X3)办公楼的使用年数 (X4)办公楼的评估值 (Y)
25003225&＃61;D14*A22 &＃43; C14*B22 &＃43; B14*C22 &＃43; A14*D22 &＃43; E14
也可以用 TREND 函数计算此值。示例 4 - 使用 F 和 R2 统计值
在上例中&＃xff0c;判定系数 r2 为 0.99675(函数 LINEST 的输出单元格 A17 中的值)&＃xff0c;表明自变量与销售价格之间存在很强的相关性。可以通过 F 统计值来确定具有如此高的 r2 值的结果偶然发生的可能性。
假设事实上在变量间不存在相关性&＃xff0c;但选用 11 个办公楼作为小样本进行统计分析却导致很强的相关性。术语“Alpha”表示得出这样的相关性结论错误的概率。
LINEST 函数输出中的 F 和 df 值可被用于评估偶然出现较高 F 值的可能性。F 可与发布的 F 分布表中的值进行比较&＃xff0c;或者 Excel 中的 FDIST 函数可被用于计算偶然出现较高 F 值的概率。适当的 F 分布具有 v1 和 v2 自由度。如果 n 是数据点的个数且 const &＃61; TRUE 或被省略&＃xff0c;那么 v1 &＃61; n – df – 1 且 v2 &＃61; df。(如果 const &＃61; FALSE&＃xff0c;那么 v1 &＃61; n – df 且 v2 &＃61; df。)语法为 FDIST(F,v1,v2) 的 FDIST 函数将返回偶然出现较高 F 值的概率。在本例中&＃xff0c;df &＃61; 6(单元格 B18)且 F &＃61; 459.753674(单元格 A18)。
假设 Alpha 值为 0.05&＃xff0c;v1 &＃61; 11 – 6 – 1 &＃61; 4 且 v2 &＃61; 6&＃xff0c;那么 F 的临界值为 4.53。由于 F &＃61; 459.753674 远大于 4.53&＃xff0c;所以偶然出现高 F 值的可能性非常低。(因为&＃xff0c;在 Alpha &＃61; 0.05 的情况下&＃xff0c;当 F 超过临界值 4.53 时&＃xff0c;known_y’s 和 known_x’s 之间没有关系这一假设不成立。)使用 Excel 中的 FDIST 函数可获得偶然出现高 F 值的概率。例如&＃xff0c;FDIST(459.753674, 4, 6) &＃61; 1.37E-7&＃xff0c;是一个极小的概率。于是可以断定&＃xff0c;无论通过在表中查找 F 的临界值&＃xff0c;还是使用 FDIST 函数&＃xff0c;回归公式都可用于预测该区域中的办公楼的评估价值。请注意&＃xff0c;使用在上一段中计算出的 v1 和 v2 的正确值是非常关键的。
京公网安备 11010802041100号