excel回归方程怎么做(excel回归方程)

使用Excel和python来做回归分析

聊完方差分析，就不得不说回归分析。

回归分析是一种应用广泛的统计分析方法，在金融，医学等领域都已经成功应用，而且是比较简单也比较常用的算法了，是经得起考验的，结果解读也很友好。

这次我们就先以最常见的Excel表格来做回归分析,Excel表格的功能远比我们想的强大（一般的回归分析，只要是数据量不是很大，Excel完全可以搞得定，而且上手十分容易，不需要一行代码，就可以轻松搞定）

一般是利用最小二乘法来计算出回归模型的参数值。但是得到的回归方程到底有没有统计学意义，还需要对回归方程进行各种检验，主要是回归方程显著性检验，回归系数显著性检验，残差分析等。

数据集：波士顿房价数据

波士顿房价数据已被用于许多涉及回归问题的机器学习论文中，所有我们拿这个成熟的数据集来预测房价练练手！数据如下：

CRIM 城镇人均犯罪率

ZN 占地面积超过2.5万平方英尺的住宅用地比例

INDUS 城镇非零售业务地区的比例

CHAS 查尔斯河虚拟变量 (= 1 如果土地在河边；否则是0)

NOX 一氧化氮浓度（每1000万份）

RM 平均每居民房数

AGE 在1940年之前建成的所有者占用单位的比例

DIS 与五个波士顿就业中心的加权距离

RAD 辐射状公路的可达性指数

TAX 每10,000美元的全额物业税率

PTRATIO 城镇师生比例

B 1000(Bk - 0.63)^2 其中 Bk 是城镇的黑人比例

LSTAT 人口中地位较低人群的百分数

MEDV 以1000美元计算的自有住房的中位数

做回归分析前，先看下特征的相关性，如下：

注：Excel内置的相关系数是pearson相关系数

好像和价格的相关性都差不多，那我们做一个回归拟合看看，Excel界面如下：

得到结果如下：

残差图

特征残差拟合图

注：回归方程的显著性检验是F检验也就是方差分析，回归方程系数检验是T检验

结果说明：

1，回归统计：R Square表示拟合度，就是方程对数据的拟合程度，当然是越大越好，此时为0.74

2，方差分析：F=108.057，这个F是啥？是F统计量，回归方程的显著性检验是用的F检验，sig F=6.9468E-135 <0.05,所有这个方程是显著的，有意义的！

3，回归系数coefficient显著性，看T——Start值，看特征对于的P值，如图：特征INDUS,AGE对应的T统计量均小于1，P值大于0.05，说明这两个系数与y（price）的线性关系不显著，不应该保留在回归方程中，也可以再结合特征残差拟合图来看一下特征的有效性

看来得把这两个线性关系不显著的特征去掉之后再重新做回归!

这一次的回归方程和回归系数都有显著性，但是这个R Square=0.740545，好像和不剔除变量没什么差别，甚至还小一点，这就有点尴尬了。

整体的方程拟合度才0.74，效果不是很好。

我们用python来做一下看看

首先来筛选特征，有两种方法SelectKBest和f_regression

注：f_regression 是单因素线性回归F检验，SelectKBest方法可以调用检验方法，如：卡方检验chi2，还有针对分类的方差分析的f_classif,当然也可以调用f_regression方法，还可以选择前k个分数较高的特征，去掉其他的特征。所以这个方法更强大，推荐使用这个。

结果如下：

这两种方法得到的结果一样，对应的特征的P值都小于0.05，没法去掉一些特征

再注：目前python提供的有方差分析，卡方检验的方法，但是一直没有发现T检验的方法。可能是因为T检验其实也算是一种F检验的缘故吧，至于是不是因为这，我也不知道，这只是我猜的！但是用Excel做的时候即使去掉两个不显著的特征，好像对回归结果没有什么大的影响，所有在python中干脆都显著了，

数据分成训练集和测试集，通过在训练集和测试集上的得分对比来判断是否过拟合还是欠拟合，可以直接得到回归系数和截距，如下如：

从结果看，回归方程的拟合度在0.73左右，和用Excel做的差不多，没有提高，反而略微降低了。

绘制交叉验证预测图

从结果看，拟合的并不是很好。

后记：

结合Excel和python做的回归方程拟合度都是在0.74左右，效果只能是一般，看来得换一种方法来做。

我们接下来准备用K近邻回归算法来做预测。

分享一个用用Excel做回归分析

Excel数据分析工具库是个很强大的工具，可以满足基本的统计分析，这里介绍用Excel数据分析工具库中的回归做回归分析。

本节知识点：

Excel数据分析工具库—回归线性回归和非线性回归简单线性回归和多重线性回归逻辑斯蒂回归

一、什么是回归分析（Regression）

1、定义

确定两种或两种以上变量间相关关系的一种统计分析方法。通过数据间相关性分析的研究，进一步建立自变量(i=1,2,3,…)与因变量Y之间的回归函数关系，即回归分析模型，从而预测数据的发展趋势。

2、分类

按照涉及的变量的多少，分为一元回归和多元回归分析；按照因变量的多少，可分为简单回归分析和多重回归分析；按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。

二、线性回归

1、简单线性回归

简单线性回归又叫一元线性回归，即回归模型中只有一个自变量和一个因变量，其回归方程可以表示为：

Y=a+bx+

其中，Y表示因变量，x表示自变量，a是 常数，b是斜率，

是随机误差。

2、最小二乘法：

如何确定参数a和b,则要用最小二乘法来实现。通过最小化误差的平方和寻找数据的最佳函数匹配，即使得观测点和估计点的距离的平方和最小。

3、线性回归分析的步骤：

确定自变量和因变量绘制散点图，确定回归模型类型估计模型参数，建立回归模型：最小二乘法进行模型参数估计对回归模型进行检验利用回归模型进行预测

4、多重线性回归

定义：一个因变量与多个自变量的线性回归问题，是一元线性回归的推广。其回归方程可以写为：

多重线性回归方程中回归系数的估计也是用到最小二乘法

三、用Excel做回归分析

我们研究销售额Y和推广费用X1之间的关系，数据如下：

首先我们用数据分析—相关系数分析计算一下自变量和因变量之间的相关系数为0.95157，为强相关。

绘制散点图如下：

然后，我们用数据分析库里的回归来做分析

注意Y值和X值输入区域，X值是自变量，Y是因变量。

四、线性回归方程的检验

评价回归拟合程度好坏（重要）：

1、 先看回归统计表，Multiple R即相关系数R的值，和我们之前做相关分析得到的值一样，大于0.8表示强正相关。

2、 回归统计表中的R Square是R平方值，R平方即R的平方，又可以叫判定系数、拟合优度，取值范围是[0,1],R平方值越大，表示模型拟合的越好。一般大于70%就算拟合的不错，60%以下的就需要修正模型了。这个案例里R平方0.9054，相当不错。

3、 Adjusted R是调整后的R方，这个值是用来修正因自变量个数增加而导致模型拟合效果过高的情况，多用于衡量多重线性回归。

4、 第二张表，方差分析表，df是自由度，SS是平方和，MS是均方，F是F统计量，Significance F是回归方程总体的显著性检验，其中我们主要关注F检验的结果，即Significance F值，F检验主要是检验因变量与自变量之间的线性关系是否显著，用线性模型来描述他们之间的关系是否恰当，越小越显著。这个案例里F值很小，说明因变量与自变量之间显著。

5、 残差是实际值与预测值之间的差，残差图用于回归诊断，回归模型在理想条件下的残差图是服从正态分布的。

6、 第三张表我们重点关注P-value，也就是P值，用来检验回归方程系数的显著性，又叫T检验，T检验看P值，是在显著性水平α（常用取值0.01或0.05）下F的临界值，一般以此来衡量检验结果是否具有显著性，如果P值>0.05，则结果不具有显著的统计学意义，如果0.01<P值<0.05，则结果具有显著的统计学意义，如果P<=0.01，则结果具有极其显著的统计学意义。T检验是看某一个自变量对于因变量的线性显著性，如果该自变量不显著，则可以从模型中剔除。

7、 从第三张表的第一列我们可以得到这个回归模型的方程：y=4361.486+1.198017x，此后对于每一个输入的自变量x,都可以根据这个回归方程来预测出因变量Y。

这里简单总结了一下什么是回归分析，如何用excel做线性回归分析，以及如何评价回归方程拟合程度的好坏。入门很简单，精通还很遥远，我们都在学习中。

如何采用Excel绘制简单散点图、分组散点图并拟合回归直线？

散点图用点的密集程度和趋势表示两个连续变量间的相关关系和变化趋势。经常用于相关、回归分析之前。

本讲目的：绘制如下的图形

简单散点图

分组散点图

1 简单散点图：两个连续变量之间的关系。

（1）数据及排列

（2）操作步骤

【做图】

1）选择上面的红色部分

2）插入→图表（点击右下角的"查看所有图表"）

3）点击"所有图表"→"XY散点图"→"散点图"→"确定"。

结果如下

4）在上图上面点右键，再点击"选择数据"。

5）点击"编辑"

删除系列名称；X轴系列值：选择年龄的数据；Y轴系列值：选择血压的数据。如下图：

点击"确定"。返回后再点击"确定"。结果如图：（很丑）

【修图】

6）添加坐标轴标题、修改坐标轴格式

点击图表任何1个部位，然后点击右上角的"＋"，勾选坐标轴标题。

把坐标轴标题修改为"血压下降（mmhg）"。如下图：

双击纵坐标轴上的数字，如下进行坐标轴格式设置。

同样的方法设置横坐标轴格式。

7）设置数据系列格式

点击（或双击）图中的散点设置数据系列格式。

8）添加趋势线

点击图表右侧的"＋"→"趋势线"→"线性"。

点击上图右下角的"更多选项"，然后勾选"设置趋势线格式"中的"显示公式"，即可添加回归方程。

2 分组散点图：分组别观察两个连续变量之间的关系。

（1）数据及排列

（2）操作步骤

1）选择上面的红色部分

2）插入→图表（点击右下角的"查看所有图表"）

3）点击"所有图表"→"XY散点图"→"散点图"→"确定"。

4）在图表上点击右键，点击"选择数据"。

删除所有的图例项，点击"添加"对第1个数据系列进行设置。

第1个数据系列的设置如下图（点击相应的单元格，或选择相应的范围）：

再点击"添加"分别进行第2个、第3个数据系列的设置。设置完成后如下图：

5）分别进行坐标轴、数据系列格式等方面的设置。方法与简单散点图里的一致。设置完成后即可生成最前面的"分组散点图"。