怎么用最新版excel做线性回归分析（统计学中的一元线性回归及其Excel实操）

线性回归分析是统计中的一种很重要的统计分析方法。方差分析研究分类型自变量与数值型因变量之间关系的分析方法，而线性回归分析主要研究数值型自变量和数值型因变量之间关系的分析方法。

从处理变量的多少来看，如果研究的是两个变量之间的关系，称为一元线性回归分析，如果研究的是两个以上变量之间的关系，称为多元线性回归分析。本文主要介绍一元线性回归分析。

一个例子

一个大型商业银行在多个地区设有分行，其业务主要是进行基础设施建设、国家重点项目建设、固定资产投资等项目的贷款。近年来，该银行的贷款额平稳增长，但不良贷款额也有较大比例的提高，这给银行业务的发展带来较大压力。为弄清楚不良贷款形成的原因，管理者希望利用银行业务的有关数据做些定量分析，以便找出控制不良贷款的办法。下表就是该银行所属的25家分行的有关业务数据。

管理者想知道，不良贷款是否与贷款余额、累计应收贷款、贷款项目的多少、固定资产投资额等因素有关？如果有关系，它们之间是一种什么样的关系？关系强度如何？试绘制散点图，并分析不良贷款与贷款余额、累计应收贷款、贷款项目个数、固定资产投资额之间的关系。

相关关系

说相关关系之前，先说下初高中数学学过的函数关系，这是是我们比较熟悉的关系。例如，函数y = f (x)，其中 x 称为自变量，y 称为因变量，x与y是一一对应的关系。例如，圆的面积S与半径R之间的关系可表示为S=πR^2，给定一个半径，就可以计算出一个面积，半径越大，面积也越大。

相关关系不同于函数关系，变量之间存在不确定的数量关系称为相关关系。例如，收入水平y与受教育程度x之间的关系，一般来说，受教育程度越高，收入水平也越高，但这并不绝对，只能说大部分情况下是，我们无法通过一个函数关系来定量描述收入水平y与受教育程度x之间的关系，只能说它们相关。

从上面可以看出，相关关系有以下2个特点。

• 变量间关系不能用函数关系精确表达。
• 一个变量的取值不能由另一个变量唯一确定，当变量 x 取某个值时，变量 y 的取值可能有几个。

描述相关关系的两种方式

1、散点图

针对前面的商业银行数据，用Excel绘制散点图，分别绘制不良贷款与贷款余额、累计应收贷款、贷款项目个数及固定资产投资额的散点图。

（1）不良贷款与贷款余额的散点图（这里添加了趋势线，下同）

（2）不良贷款与累计应收贷款的散点图

（3）不良贷款与贷款项目个数的散点图

（4）不良贷款与固定资产投资额的散点图

2、相关系数

通过散点图可以判断两个变量之间有无相关关系，并对变量间的关系形态作出大致的描述，但是散点图不能准确反映变量之间的关系强度。

为了准确度量两个变量之间的关系强度，需要计算相关系数。

相关系数用得较多的是皮尔逊相关系数（Pearson's correlation coefficient），计算公式如下：

幸运的是，我们不需要手动计算，在Excel中，可以通过分析工具库直接得出相关系数（这里加上了条件格式）。

通过相关系数看出，跟不良贷款线性关系最紧密的是各项贷款余额。

附：Excel分析工具库中的相关系数工具。

回归方程及最小二乘法

对于回归分析，关键是要求出回归方程。对于一元线性回归，估计的回归方程为：

需要求出该方程的常数项和回归系数，一般采用最小二乘法来求解。

最小二乘法的意思是，在下图中，找到一条直线，使得它到各个观测点的距离最近，即让这些点尽量均匀分布在直线两侧。

最小二乘法的数学原理及计算，如果不感兴趣，可以先不用管，因为我们一般是通过工具进行回归分析，如Excel、SPSS等，这些工具可以直接给出结果！

回归直线的拟合优度

拟合优度：表示回归直线与各观测点的接近程度，一般用判定系数来衡量回归方程的拟合优度。

回归分析的误差一般通过离差平方和（即误差）来表示，如下图所示。

• 总平方和SST：各观测点与均值的误差平方和平方和，也叫总误差。
• 回归平方和SSR：各观测点与回归直线的误差的平方和。
• 残差平方和SSE：其他因素造成的误差平方和。

各平方和的计算公式如下。

总平方和（SST）=回归平方和（SSR） 残差平方和（SSE）

回归直线的拟合优度取决于回归平方和SSR占总平方和SST的比例，即SSR/SST。

通过Excel进行回归分析，可以直接给出判定系数，不用担心计算的问题。

显著性检验

回归分析中的显著性检验主要包括两方面的内容：

1、线性关系检验

2、回归系数检验

线性关系检验主要是检验自变量x与因变量y之间的线性关系是否显著。

回归系数检验主要是检验自变量对因变量的影响是否显著。

线性关系检验主要关心F分布的P值，回归系数检验主要关心t分布的P值，具体理论这里略去，因为Excel可以直接给出对应的P值，判断的原则就是，P值越小越好，一般是小于0.05就符合要求。

实操：用Excel进行一元线性回归分析

用Excel进行一元线性回归分析分为以下三步：

1、计算相关系数，并进行显著性检验

2、选择合适的自变量x，与y进行线性回归

3、根据Excel给出的分析结果，写出回归方程并进行预测

具体操作步骤如下：

1、计算相关系数，并进行显著性检验

在Excel中，可以通过分析工具库直接得出相关系数，并加上条件格式。

通过相关系数矩阵看出，跟不良贷款线性关系最紧密的是各项贷款余额。

说明：显著性检验这里暂时略去。

2、选择合适的自变量x，与y进行线性回归

在“数据分析”中选择回归：

Y值输入区域，选择“不良贷款”，X值输入区域，选择“各项贷款余额”，置信度用默认的95%，残差部分，勾选“残差”、“残差图”、“线性拟合图”，确定即可。

得出以下分析结果：

在上方的结果中，判定系数、显著性检验的结果如标黄区域所示。

从上面可以看到，判定系数为0.71，说明两者有较强的线性关系。

线性关系检验的显著性水平远小于0.05，回归系数检验的显著性水平也是远小于0.05，说明都是可以通过检验的。

Excel也给出了残差图和线性拟合图（这里适当做了一些美化），如下所示。

对于残差图来说，如果残差图中的所有点都落在一条水平带中间，说明回归模型是合理的，上述残差图基本符合。

对于线性拟合图来说，蓝色点表示原始数据，红色点表示预测值，可以看出，由回归方程给出的预测值与原始值的拟合程度也是不错的。

你平时工作中是否用过线性回归？用什么工具进行呢？欢迎留言评论！

,