利⽤EXCEL函数LINEST进⾏统计学中的回归分析
介绍统计学中的⼀元和多元线性回归，并通过EXCEL⾃带的统计函数LINEST、INDEX进⾏⼿⼯计算，再通过EXCEL数据分析⼯具包进⾏⾃动计算。
由于很多复杂的EXCEL⾃动化程序，需要⽤到⾃动化计算，EXCEL数据分析⼯具并不适⽤⾃动计算，反⽽EXCEL统计函数是很容易实现批量⾃动计算。
所以本⽂重点介绍EXCEL统计函数的使⽤。
统计学上的线性回归原理简介
回归是研究⼀个随机变量y对另⼀个(x)或⼀组(x1，x2，…，xn)变量的相依关系的统计分析⽅法。其中y⼜叫因变量，x叫⾃变量。
简单的记忆⽅法：x是⾃⾝可以变动的，y是因为x的变化⽽变化的，就不会把⾃变量和因变量的意义搞乱。
线性回归是⾃变量与因变量之间是线性关系的回归。
⼀般来说，因变量只有⼀个，⾃变量会有⼀个或多个。下⾯就按因变量的数量及类别为分：⼀元线性回归、多元线性回归。
⼀元线性回归
⼀元线性回归是指⼀个因变量y只与⼀个⾃变量x有相关关系，通过公式可以表⽰为如下图：
⼀元线性回归
其中a称为斜率，b称为截距。
它的意思是当x增减⼀个单位时，y会同样增减a个单位的x，如a＝2时，x增加⼀个单位，y就增加2个单位x。
通过EXCEL统计函数LINEST来实现⼀元线性回归分析，在EXCEL的A1到B10输⼊如下数据：
x y
1.1200
1.9245
2.5367
4400
4.5550
5540
5.9667
7770
1210
使⽤LINEST线性回归函数进⾏⼿⼯计算。
LINEST函数可通过使⽤最⼩⼆乘法计算与现有数据最佳拟合的直线，来计算某直线的统计值，然后返回描述此直线的数组。
也可以将 LINEST 与其他函数结合使⽤来计算未知参数中其他类型的线性模型的统计值，包括多项式、对数、指数和幂级数。因为此函数返回数值数组，所以必须以数组公式的形式输⼊。
LINEST
LINEST(known_y’s, [known_x’s], [const], [stats])
Known_y’s 必需。关系表达式 y = mx + b 中已知的 y 值集合。如果 known_y’s 对应的单元格区域在单独⼀列中，则 known_x’s 的每⼀列被视为⼀个独⽴的变量。如果 known_y’s 对应的单元格区域在单独⼀⾏中，则 known_x’s 的每⼀⾏被视为⼀个独⽴的变量。
Known_x’s 可选。关系表达式 y = mx + b 中已知的 x 值集合。known_x’s 对应的单元格区域可以包含⼀组或多组变量。如果仅使⽤⼀个变量，那么只要 known_y’s 和 known_x’s 具有相同的维数，则它们可以是任何形状的区域。如果使⽤多个变量，则 known_y’s 必须为向量（即必须为⼀⾏或⼀列）。如果省略 known_x’s，则假设该数组为 {1,2,3,…}，其⼤⼩与 known_y’s 相同。
const 可选。⼀个逻辑值，⽤于指定是否将常量 b 强制设为 0。如果 const 为 TRUE 或被省略，b 将按通常⽅式计算。如果 const 为FALSE，b 将被设为 0，并同时调整 m 值使 y = mx。
stats 可选。⼀个逻辑值，⽤于指定是否返回附加回归统计值。如果 stats 为 TRUE，则 LINEST 函数返回附加回归统计值，这时返回的数组为 {mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果 stats 为 FALSE 或被省略，LINEST 函数只返回系
数 m 和常量 b。
附加回归统计值如下：
统计值说明
se1,se2,
…,sen系数 m1,m2,…,mn 的标准误差值。
seb常量 b 的标准误差值（当 const 为 FALSE 时，seb = #N/A）。
r2判定系数。y 的估计值与实际值之⽐，范围在 0 到 1 之间。如果为 1，则样本有很好的相关性，y 的估计值与实际值之间没有差别。相反，如果判定系数为 0，则回归公式不能⽤来预测 y 值。
sey Y 估计值的标准误差。
F    F 统计或 F 观察值。使⽤ F 统计可以判断因变量和⾃变量之间是否偶尔发⽣过可观察到的关系。
df⾃由度。⽤于在统计表上查 F 临界值。将从表中查得的值与 LINEST 函数返回的 F 统计值进⾏⽐较可确定模型的置信区间。有关如何计算 df 的信息，请参阅本主题下⽂中的“说明”。
ssreg回归平⽅和。
ssresid残差平⽅和。
下⾯的图⽰显⽰了附加回归统计值返回的顺序。
在任意单元格中输⼊=LINEST(B2:B10,A2:A10,TRUE,TRUE)，计算得出来的结果为94.33。Linest函数直接计算，返回的是第⼀个⾃变量的系数，LINEST返回的是⼀个数组，即上述的图表。
如果要通过EXCEL数组来实现这种功能。选定A14:B18，在EXCEL地址栏输⼊=LINEST(B2:B10,A2:A10,TRUE,TRUE)，然后同时按CTRL+SHIFT+ENTER，返回⼀个表格，表格中的每个单元格的公式显⽰为：{=LINEST(B2:B10,A2:A10,TRUE,TRUE)}
94.3493.92
6.2025.89
0.9737.62
231.787
328,061.719,907.85
对应上表及上述的图，解释上表的各个参数的意义。
94.34就是x的系数a，
93.92为截距b，
6.2为x的系数a的标准误差值，
25.89为截距（常量）b的标准误差值，
0.97为相关系数，
37.62为y估计值的标准误差，
231.78为F统计值
7为⾃由度，
328061.71为回归平⽅和，
9907.85为残差平⽅和。
上述返回的统计值，最常⽤的是⾃变量的系数a和常量b，如果在EXCEL⾃动化程序中，很少会⽤上述的返回⼀个表格的⽅式，因为LINEST 返回的是⼀个数组，可以通过index函数取得数组中的每⼀个值。
x的系数a可以在任意单元格式输⼊=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,1)，截距b可以在任意单元格式输⼊
=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,2)，index函数第⼀个参数是指定⼀个数组，第⼆和第三个参数是指定返回的⾏列位置。所以上述的⼀元线性回归的拟合直线函数为y=94.34x+93.92，相关系数为0.97。
多元线性回归
多元线性回归是指⼀个因变量y只与多个⾃变量x有线性相关关系，通过公式可以表⽰为如下图：
a为每个⾃变量对因变量y的影响因素，我们以⼆元线性回归为例，⽤EXCEL函数LINEST进⾏分析。数据如下，填充在EXCEL的A1:C10中。
x1x2y
4  1.1200
7  1.9245
11  2.5367
144400
excel中index函数的使用方法19  4.5550
225540
22  5.9667
25770
5210
选定A14:C18，在地址栏中输⼊=LINEST(C2:C10,A2:B10,TRUE,TRUE)，按CTRL+SHIFT+ENTER，返回⼀个表格，表格中的每个单元格的公式显⽰为：{=LINEST(C2:C10,A2:B10,TRUE,TRUE)}，如下表：
69.17  6.8887.37
34.059.1428.11
0.973238.84#N/A
109.00  6.00#N/A
328,916.509,053.05#N/A
此表格和⼀元线性回归的表格⼀样，只是多了⼀列，因为多了⼀个⾃变量。多出⼀列的内容是另⼀个
⾃变量的系数和它的标准误差值。同样可以通过⽤INDEX函数取得数据的每⼀个值。根据上表可以得到拟合的线性回归函数y=69.17×1+6.88×2+87.37
⽤EXCEL数据分析⼯具进⾏回归分析
接下来通过EXCEL数据分析⼯具实现上述⼀元线性回归分析的计算，并可以验证上述的计算过程。如果你的EXCEL中不到数据分析，请先为EXCEL添加数据分析⼯具的加载宏。
选中A1:B10，打开数据分析，选中“回归”后，点击“确定”按钮。
按下图填写好⾃变量X、因变量Y的区域A1:A10,B1:B10，选择“标志”，意思是说表格中包括有表头字段，填写输出区域A26，点击“确
定”后即可。
结果如下，可以清晰的看到⽤LINEST计算出来的结果与数据分析⼯具分析得到的结果是⼀样的。
原⽂链接：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。