介绍统计学中的一元和多元线性回归,并通过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输入如下数据:
使用LINEST线性回归函数进行手工计算。 LINEST函数可通过使用最小二乘法计算与现有数据最佳拟合的直线,来计算某直线的统计值,然后返回描述此直线的数组。 也可以将 LINEST 与其他函数结合使用来计算未知参数中其他类型的线性模型的统计值,包括多项式、对数、指数和幂级数。因为此函数返回数值数组,所以必须以数组公式的形式输入。 LINEST语法LINEST(known_y’s, [known_x's], [const], [stats])
附加回归统计值如下:
下面的图示显示了附加回归统计值返回的顺序。 在任意单元格中输入=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)}
对应上表及上述的图,解释上表的各个参数的意义。
上述返回的统计值,最常用的是自变量的系数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中。
选定A14:C18,在地址栏中输入=LINEST(C2:C10,A2:B10,TRUE,TRUE),按CTRL+SHIFT+ENTER,返回一个表格,表格中的每个单元格的公式显示为:{=LINEST(C2:C10,A2:B10,TRUE,TRUE)},如下表:
此表格和一元线性回归的表格一样,只是多了一列,因为多了一个自变量。多出一列的内容是另一个自变量的系数和它的标准误差值。同样可以通过用INDEX函数取得数据的每一个值。根据上表可以得到拟合的线性回归函数y=69.17×1+6.88×2+87.37 用EXCEL数据分析工具进行回归分析接下来通过EXCEL数据分析工具实现上述一元线性回归分析的计算,并可以验证上述的计算过程。如果你的EXCEL中找不到数据分析,请先为EXCEL添加数据分析工具的加载宏。
|
|