LINEST函数返回线性回归方程的参数。 图1 直线的公式为:y=mx b 或者为: 其中,m为直线的斜率,b为截距。 LINEST函数返回的数组为: 什么情况下使用LINEST函数? LINEST函数通过使用最小二乘法计算与现有数据最佳拟合的直线,来计算某直线的统计值,返回描述此直线的数组。它能够计算斜率、截距、斜率标准差、截距标准差、判定系数、自由度、F统计、y标准差、回归平方和、残差平方和。
LINEST函数语法 LEN函数有4个参数,其语法如下:
其中,{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统计值进行比较可以确定模型的置信区间。Ssreg为回归平方和。Ssresid为残差平方和。 回归分析时,Excel会计算每一点的y的估计值和实际值的平方差,这些平方差称为残差平方和ssresid。然后,Excel会计算总平方和(sstotal)。当参数const为TRUE或被省略时,总平方和是y 的实际值和平均值的平方差之和;当参数const为FALSE时,总平方和是y的实际值的平方和(不需要从每个y值中减去平均值)。回归平方和(ssreg)可通过公式ssreg=sstotal-ssresid计算出来。残差平方和与总平方和的比值越小,判定系数r2的值就越大。r2是用来判断从回归分析求得的公式是否足以说明变量之间关系的指示器,r2=ssreg/sstotal。 下图2显示了附加回归统计值返回的顺序。 图2
斜率:=INDEX(LINEST(known_y’s,known_x’s),1) 截距:=INDEX(LINEST(known_y’s,known_x’s),2) LINEST函数陷阱 LINEST函数返回数值数线,因此必须以数组公式的形式输入。 数据的离散程度决定了LINEST函数计算的直线的精确度。数据越接近线性,LINEST模型就越精确。LINEST函数使用最小二乘法来判断数据的最佳拟合。当只有一个自变量x时,m和b是根据下面的公式计算的: LINEST函数中使用的底层算法与SLOPE函数和INTERCEPT函数使用的底层算法不同。 LINEST函数返回的F检验值与FTEST函数返回的F检验值不同。FINEST函数返回F统计值,而FTEST返回概率。 示例1: 已知直线的x值和y值,求斜率和截距 如图3所示,在单元格区域C5:C8中是直线y=mx b的y值,单元格区域B5:B8中是该直线的x值。选择单元格B12:C12,输入数组公式: =LINEST(B5:B8,C5:C8,,FALSE) 得到该直线的斜率m和截距b。 图3 示例2: 根据1月至6月的销售额,估算第9个月的销售额 如图4所示的工作表,列出了1月到6月的销售额,现在据此估算9月份的销售额,公式为: =SUM(LINEST(B2:B7,A2:A7)*{9,1}) 图4 示例3: 为一个x变量求10个统计数字 如下图5所示的工作表,在工作表单元格区域A2:B12中是统计数据,在工作表单元格区域E3:F7中使用数组公式: =LINEST(B2:B12,A2:A12,TRUE,TRUE) 得到了关于统计数据的10个统计量。 图5 示例4: 为两个x变量求统计数字 如下图6所示,LINEST函数可以对两个x变量求统计数字。数据区域为单元格区域A2:C12,求解得到的结果区域为F4:E8,数组公式为: =IFERROR(LINEST(C2:C12,A2:C12,TRUE,TRUE),'') 使用IFERROR函数避免出现#N/A错误值。 图6 LINEST函数的作用还有很多,例如,还可以对多个x变量求统计数字,本文只是列举了一些简单的例子。 由于对统计学专业知识了解有限,文中可能有错漏之处,请相关专业专家指正。 期待以后统计学知识学得更好一些后,再分享。 本文属原创文章,转载请注明出处。 欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。 |
|