EXCEL一元线性回归

EXCEL多元线性回归

EXCEL一元线性回归

两组变量间是否存在线性关系是日常工作和学习中经常遇到的一类问题。回归这一术语最早来源于生物遗传学，由高尔顿（Francis Galton）引入。当时高尔顿的兴趣在于寻找为什么总体身高分布趋向稳定。现在我们所关心的已不是这个问题，而是想知道在已知父亲身高的情况下，儿子的身高的平均变化如何。换句话说，就是已知父亲身高来预测儿子的平均身高。假设高尔顿当时获得数据有以下14组

以这个问题为例，我们的因变量：Y=儿子的身高；自变量（现在只考虑一个）：X=父亲的身高。我想得到的是类似于

                                    Y=aX+b                               

这样一个关系。式中有两个变量a:斜率(slope)，b:截距(intercept)，需要2组数据求解。但现在一共有14组测量值，基本上不可能找到一组(a,b)满足所有测量值，这个时候我们就要寻找这样一组(a,b)，使得计算的Y'与对应的测量值Y的误差最小。

EXCEL最常见的方法如下

1） 画出X和Y的散点图(注意是散点图)

2）插入趋势线——线性

3）选项里可以设置b值是否为0；勾选显示方程

第二种方法是直接调用函数(仅限线性回归)，

    等一下，如果我要求b是0该如何是好？这个时候只需要输入

= LINEST(Y,X,TRUE,FALSE)

    第三个参数用来设置b是否为0。公式返回一个1x2的区域，第一个是a，第二个是b。

第三种方法是使用EXCEL自带的数据分析包

1）激活数据分析包Data Analysis Pack（文件-->选项-->插件）

2）启动，选择回归

3）选择Y和X对应的区域，以及输出区域(别的咱们暂且不管)

点击OK

结束这一节之前试试看LINEST(Y,X,TRUE,TRUE)的结果和方法三有什么对应关系？

2. 多元线性回归

如果我想在模型里加一个自变量X2 = 母亲的身高（或者更多更多），显然方法1和2不适用了。方法3只需选择所有的X，=LINEST(Y,Xs,TRUE,FALSE)就可以得到a1,a2和b。

除此之外我们可以利用Solver插件（当然这只是它最基本的功能之一）

1）找三个单元格（比如E8:G8）存放a1,a2和b，假设都是1；顺便定义他们的名称

2）在测量值Y旁边插入一列计算值Y_cal =a1_*X+a2_*X_2+b (顺便再定义名称)

3)找个单元格定义计算与测量的误差平方和 = SUMSQ(Y_cal-Y)

4）打开Solver(就在数据分析下面)，目标：误差平方和最小，通过调整a1,a2,b。我们还可以添加限制条件，比如a1>=0等等。

视频操作在这里

写在最后

其实问题的核心往往是："我是否应该选择线性回归来处理这个问题？" 或者是"我的数据点是否具有统计意义？"（现实生活中很多"工程师"拿着4，5组数据就敢谈相关性）。大家应该注意到了在一元线性回归的趋势线方程下有一个R2，我们有机会深入聊聊这个。（R2=0.9我就有一个好的拟合么?  未必！）