精通Excel数组公式025:LINEST数组函数

如果正在进行与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键输入公式。

精通Excel数组公式025:LINEST数组函数

图1

如果要将计算出的斜率和截距传递到垂直区域,则使用图1中右下方的公式。

使用LINEST传递一个x变量的10个统计数据

本示例展示如何显示多个统计值。在传递多个统计值时,要确定在输入LINEST前选择的单元格,遵循以下原则:选择比x变量数多1的列和5行。

如下图2所示,因为只有一个x变量,所以选择2列5行,即单元格区域E2:F6。输入LINEST公式,其中参数const为TRUE,正常计算b值,参数stats为TRUE计算多个统计值。

精通Excel数组公式025:LINEST数组函数

图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。

精通Excel数组公式025:LINEST数组函数

图3

使用LINEST传递两个x变量的12个统计数据(多次回归)

如下图4所示:

Y值=因变量=测试成绩=列D

X1值=自变量1=家庭作业成绩=列B

X2值=自变量2=准备测试的学习时间=列C

如上文所讲的,在输入公式前,先选择3列5行的单元格区域,示例中为单元格区域B3:D7,然后输入公式并按Ctrl+Shift+Enter键结束。

精通Excel数组公式025:LINEST数组函数

图4

如果不想在结果中看到错误值#N/A,可以在公式中加上IFERROR函数:

=IFERROR(LINEST(D13:D23,B13:C23,TRUE,TRUE),””)

使用LINEST传递三个x变量的14个统计数据(多次回归)

如下图5所示使用了3个x变量。

精通Excel数组公式025:LINEST数组函数

图5

注:本文为电子书《精通Excel数组公式(学习笔记版)》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。 

来源:完美Excel,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/123860

发表评论

登录后才能评论
侵权联系
返回顶部