精通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社群下载这本电子书的完整中文版。 

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。