Excel多元回归方法

Excel在多元回归预测分析教学中的应用

王斌会（暨南大学经济学院）

Excel电子表格软件是微软办公软件组的核心应用程序之一，它功能强大，操作简单，适用范围广，普遍应用于报表处理、数学运算、工程计算、财务处理、统计分析、图表制作等各个方面。其数据分析模块简单直观，操作方便，是进行统计学教学的首选软件。

统计学中的回归预测分析具有普遍的实用意义，但变量之间关系分析及计算繁杂，而借助Excel可方便高效地研究其数量变动关系，完成其繁杂的计算分析过程。

根据回归预测中的实例，借助Excel进行相关分析，判断出其相关程度，并在此基础上建立回归模型，最后用Excel完成计算分析、统计检验及预测，使回归预测分析的计算过程更简捷，统计预测方法更为实用。

直线回归分析是研究一个应变量与一个自变量间呈直线趋势的数量关系。在实际中，常会遇到一个应变量与多个自变量数量关系的问题。一个应变量与多个自变量间的这种线性数量关系可以用多元线性回归方程来表示。

式中b0相当于直线回归方程中的常数项a，bi（i=1,2,……m）称为偏回归系数，其意义为当其它自变量对应变量的线性影响固定时，bi反映了第i个自变量xi对应变量y线性影响的度量。

〔例〕财政收入多因素分析

在一定时期内，财政收入规模大小受许多因素的影响，如国民生产总值大小、社会从业人员多少、税收规模大小、税率高低因素等。本例仅取四个变量作为解释变量，分析它们对财政收入的影响程度。

t:年份，y:财政收入(亿元)，xl:税收(亿元)，x2:国民生产总值(亿元)，x3:其他收入(亿元)，x4:社会从业人数(万人)。

数据来自中国统计出版社出版的《中国统计年鉴》，数据时限为1978-1995年，见下图1所示。按下列步骤使用Excel“回归”分析工具：

1.输数据:将数据输入A1:F19单元格。数据为19行6列，分别记每列变量名为t、y、x1、x2、x3、x4

该工具正常情况在“工具/数据分析”。

★如果你的Excel里没有，请点“工具/加载宏”，勾选“分析工具库”和“分析数据库—VBA函数”。

★如果加载宏里也没有，请你重新安装Office,并选择完全安装即可。

2.相关分析：

(1)选择Excel“工具”菜单之“数据分析”选项,在分析工具框中选“相

关系数”。相关系数对话框将显示为图2所示，它带输入输出的提示。

1)输入

输入区域：$B$1:$F$19

分组方式：⊙逐列

选择标志位于第一行

2)输出选项

⊙输出区域:$A$21

(2)单击“确定”，Excel将计算出结果显示在输出区域中。

3.回归分析:

(1)选择Excel“工具”菜单之“数据分析”选项,在分析工具框中“回归”。回归对话框将显示为图4所示，

1)输入

Y值输入区域：$B$1:$B$19

X值输入区域：$C$1:$F$19

标志

常数为零:只有当用户想强制使回归线通过原点时才选此框

置信度:Excel自动包括了回归系数的95％置信区间。要使用其

他置信区间，选择该框并在“置信度”框中输入置信水平

2)输出选项

⊙输出区域:$H$1

3)残差

残差(R)：选择此框可得到预测值和残差(Residual)，见图7。

残差图(D):选择此框可得到残差和每一x值的图表，见图6。

标准残差(T)：选择此框可得到标准化的残差(每一残差被估计标准误差除)。这一输出可使曲线较容易分层。

线性拟合图(I)：选择此框可得到一含有y输入数据和与拟合的y值的散点图,见图7。

4)正态概率图:绘制应变量的正态概率图。

(2)．单击“确定”，Excel将计算出结果显示在输出区域中。

4．回归输出的解释

（1）回归方程：

参考图5所示单元I17:I21中的系数，回归方程为：

y＝767.7742+0.0543x1

+0.3680x2+1.1013x3-0.0037x4

在多元回归模型中，系数称为偏回归系数。例如，如果“税收”、“国民生产总值”和“社会从业人数”为固定常数，而“其他收入”允许变化，那么单位“其他收入”的变化引起“财政收入”的变化是1.1013亿元。

（2）系数的显著水平

“税收”系数的t统计值大于4，p<0.01,表示拒绝在此模型中“税收”与“财政收入”无关的原假设，而得出二者间有明显关系存在的结论。

“国民生产总值”系数的t统计值大于2，p<0.05,说明“国民生产总值”与“财政收入”间存在明显线性关系。

而“其他收入”和“社会从业人数”系数的t值较小，p>0.05,不能拒绝在此模型中“其他收入”和“社会从业人数”与“财政收入”无关的原假设，而得出二者间无明显关系存在的结论。

（3）回归统计值的解释

参考图5第7行，多元回归模型的标准误差是138.86亿元,第5行的R Square值说明“财政收入”变化的99％可以用“税收”、“国民生产总值”、“其他收入”和“社会从业人数”为解释变量的线性回归模型来解释。

（4）方差分析的解释

图5中从第10行到第14行的方差分析输出是检验所有回归系数同时等于零的原假设得出的结果。最终的结果是P值，在输出中的标记为“Significance F”。这里的P值约为6.04E-14，是从“财政收入”与解释变量之间无关系的总体随机样本中得到这些结果的概率。这个P值说明要从这样总体的随机样本中看到这些结果是极不可能的，从而得出至少有一个显著关系存在的结论。

5．残差分析

残差图对查看线性关系和常数方差的假设是否正确十分有用。Excel提供了残差与每一解释变量间关系的图形，如图6所示。

如果“财政收入”与“税收”间的关系是线性的(考虑其它因素后)，那么残差图应是显随机图形。另一方面，如果能看到曲线或者其他对称图形，那么就应改变模型来加入非线性关系。

大多数观测者会得出残差图基本上是随机的这一结论，所以不需要附加的模型化处理。因为所用的样本较小(只有18个观察对象)，所以很难查出非线性模式。

观察图6所示的残数—税收估值图，该图显示基本，且样本很小，使检测非线性模式十分困难。

6．拟合效果考察

将财政收入原始值与估计值做线图，Excel步骤为：

（1）选数据：选B1:B19，按Ctrl选I27:I45

（2）点击“图表向导”，选择“折线图”，按“完成”按钮执行，结果见图7

7．预测：通过前面对回归方程及回归系数的检验知所建立的方程有意义，同时通过上图也看到，该模型的拟合效果很好，今用该方程对1996、1997、和2000年财政收入进行预测，结果见下图8。