精通Excel数组公式025：LINEST数组函数
excelperfect
如果正在进行与x-y直线数据集相关的统计计算，那么一定会喜欢LINEST函数。当使用最小二乘法将数据拟合到一条直线时，LINEST函数可以进行许多统计计算。下面列出了该函数可以进行的一些统计计算：
1.判定系数
2.自由度
3.F统计
4.截距
5.斜率
6.截距的标准差
7.斜率的标准差
8.y标准差
9.回归平方和
10.残差平方和
使用LINEST将斜率和截距传递到水平单元格区域
如下图1所示，由于要使用数组函数LINEST传递两个值，步骤如下：
1.选择单元格D2:E2。
2.为LINEST函数的第1个参数指定y值，即学生最终的分数所在的区域B2:B12。
3.为LINEST函数的第2个参数指定x值，即学生为准备测试学习的时间所在的区域A2:A12。
4.忽略参数const，此时默认TRUE，正常计算截距。
5.忽略参数stats，此时默认FALSE，只计算斜率和截距。
6.使用Ctrl+Shift+Enter键输入公式。
图1
如果要将计算出的斜率和截距传递到垂直区域，则使用图1中右下方的公式。
使用LINEST传递一个x变量的10个统计数据
本示例展示如何显示多个统计值。在传递多个统计值时，要确定在输入LINEST前选择的单元格，遵循以下原则：选择比x变量数多1的列和5行。
如下图2所示，因为只有一个x变量，所以选择2列5行，即单元格区域E2:F6。输入LINEST公式，其中参数const为TRUE，正常计算b值，参数stats为TRUE计算多个统计值。
图2
在垂直列中显示LINEST的结果
如下图3所示，将LINEST函数生成的5×2的数组显示在一列中。公式中：
LINEST($B$2:$B$12,$A$2:$A$12,TRUE,TRUE)
生成5行2列的结果数组。
MOD(ROWS(E$1:E1)-1,5)+1
在公式向下复制时生成值1,2,3,4,5,1,2,3,4,5。
INT((ROWS(E$1:E1)-1)/5)+1)
在公式向下复制时生成值1,1,1,1,1,2,2,2,2,2。
图3
使用LINEST传递两个x变量的12个统计数据（多次回归）
如下图4所示：
Y值=因变量=测试成绩=列D
X1值=自变量1=家庭作业成绩=列B
X2值=自变量2=准备测试的学习时间=列C
如上文所讲的，在输入公式前，先选择3列5行的单元格区域，示例中为单元格区域B3:D7，然后输入公式并按Ctrl+Shift+Enter键结束。
图4
如果不想在结果中看到错误值#N/A，可以在公式中加上IFERROR函数：
=IFERROR(LINEST(D13:D23,B13:C23,TRUE,TRUE),'')
使用LINEST传递三个x变量的14个统计数据（多次回归）
如下图5所示使用了3个x变量。
图5
注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。