Excel多元回归方法

1、Excel在多元回归预测分析教学中的应用王斌会（暨南大学经济学院）Excel电子表格软件是微软办公软件组的核心应用程序之一，它功能强大，操作 简单，适用范围广，普遍应用于报表处理、数学运算、工程计算、财务处理、统 计分析、图表制作等各个方面。其数据分析模块简单直观，操作方便，是进行统 计学教学的首选软件。统计学中的回归预测分析具有普遍的实用意义， 但变量之间关系分析及计算 繁杂，而借助Excel可方便高效地研究其数量变动关系，完成其繁杂的计算分析 过程。根据回归预测中的实例，借助Excel进行相关分析，判断出其相关程度，并 在此基础上建立回归模型，最后用Excel完成计算分析、统计检验及预测，

2、使回 归预测分析的计算过程更简捷，统计预测方法更为实用。直线回归分析是研究一个应变量与一个自变量间呈直线趋势的数量关系。在实际中，常会遇到一个应变量与多个自变量数量关系的问题。一个应变量与多个自变量间的这种线性数量关系可以用多元线性回归方程来表示。式中bO相当于直线回归方程中的常数项 a，bi （i=1,2,m ）称为偏回归 系数，其意义为当其它自变量对应变量的线性影响固定时，bi反映了第i个自变量xi对应变量y线性影响的度量。例财政收入多因素分析在一定时期内，财政收入规模大小受许多因素的影响，如国民生产总值大小、 社会从业人员多少、税收规模大小、税率高低因素等。本例仅取四个变量作为解 释变量

3、，分析它们对财政收入的影响程度。t:年份，y:财政收入（亿元），xl:税收（亿元），x2:国民生产总值（亿元）， x3:其他收入（亿元），x4:社会从业人数（万人）。数据来自中国统计出版社出版的中国统计年鉴，数据时限为1978-1995年，见下图1所示。按下列步骤使用Excel “回归”分析工具：输数据：将数据输入A1:F19单元格。数据为19行6列，分别记每列变 量名为 t、y、x1、x2、x3、x4VPEFJd12x31132152SIB 2E41153ll«JI41S 2:刃il(13534D51四阳45?j371?ua»U7J79貼睿m迫1M2j.53011ai&g

4、t;jM出丽1MM阳»57lT»J925F14IW3473067M? 3>鬲冲J?P7300432WE9I2旧即JW.J3a si1EVM51!4>K211JBSPBJ335711ma 3Dftj|M3MJ®172I7T730*1iJE9sm胡:-田口中樹屋itmi関宼JUE3JT刿5“ ?US拠紗巧胡43阳3JO342J52IB曲iSOSE嗣4U329"曲83Ma回cai J?7T7J 493 M 33 IP 出跛晅町收删如财嗽陀叩庚跻頫期忸炷痕 味 t I 1 I L rr I I u I I I I L L I IH1213M1S16W

5、101. 相关分析：(1) 选择Excel “工具”菜单之“数据分析”选项,在分析工具框中选“相 关系数”。相关系数对话框将显示为图 2所示，它带输入输出的提示。1) 输入输入区域：$B$1:$F$19分组方式：。逐列选择标志位于第一行2) 输出选项。输出区域：$A$21(2) 单击“确定”，Excel将计算出结果显示在输出区域中2. 回归分析：选择Excel “工具”菜单之“数据分析”选项,在分析工具框中“回归”。回归对话 框将显示为图4所示，围4好晏量山口:折甘迄框围4好晏量山口:折甘迄框ss扎TM迈站r bwer i：-I- «l>Aa il_JM am. r塔已衍墾i|

6、k «图予务变址回卜I分莎结果围4好晏量山口:折甘迄框围4好晏量山口:折甘迄框1）输入丫值输入区域：$B$1:$B$19X值输入区域：$C$1:$F$19标志常数为零：只有当用户想强制使回归线通过原点时才选此框置信度:Excel自动包括了回归系数的95%置信区间。要使用其 他置信区间，选择该框并在“置信度”框中输入置信水平2）输出选项O输出区域：$H$13）残差残差（R）:选择此框可得到预测值和残差（Residual），见图7。残差图（D）:选择此框可得到残差和每一 x值的图表，见图6。标准残差（T）:选择此框可得到标准化的残差（每一残差被估计标准误差除） 这一输出可使曲线较容易分层

7、。线性拟合图（I）：选择此框可得到一含有y输入数据和与拟合的y值的散点图, 见图7。4）正态概率图：绘制应变量的正态概率图。单击“确定”，Excel将计算出结果显示在输出区域中。4 回归输出的解释（1）回归方程：参考图 5 所示单元 I17:I21 中的系数，回归方程为：y = 767.7742+0.0543x1+0.3680x2+1.1013x3-0.0037x4 在多元回归模型中，系数称为偏回归系数。例如，如果“税收”、“国民生 产总值”和“社会从业人数”为固定常数， 而“其他收入”允许变化， 那么单位 “其他收入”的变化引起“财政收入”的变化是 1.1013 亿元。（2）系数的显著水平“

8、税收”系数的 t 统计值大于 4，p<0.01, 表示拒绝在此模型中“税收”与 “财政收入”无关的原假设，而得出二者间有明显关系存在的结论。“国民生产总值”系数的 t 统计值大于 2，p<0.05, 说明“国民生产总值” 与“财政收入”间存在明显线性关系。而“其他收入”和“社会从业人数”系数的 t值较小，p>0.05,不能拒绝在 此模型中“其他收入”和“社会从业人数”与“财政收入”无关的原假设， 而得 出二者间无明显关系存在的结论。（3）回归统计值的解释参考图 5第7行，多元回归模型的标准误差是 138.86 亿元, 第5行的 R Square 值说明“财政收入”变化的 99

9、可以用“税收”、“国民生产总值”、 “其他收入”和“社会从业人数”为解释变量的线性回归模型来解释。（4）方差分析的解释图5中从第 10行到第 14行的方差分析输出是检验所有回归系数同时等于零 的原假设得出的结果。最终的结果是 P值，在输出中的标记为“ Significance F”。这里的P值约为6.04E-14，是从“财政收入”与解释变量之间无关系的总 体随机样本中得到这些结果的概率。这个P值说明要从这样总体的随机样本中看 到这些结果是极不可能的，从而得出至少有一个显著关系存在的结论。5残差分析 残差图对查看线性关系和常数方差的假设是否正确十分有用。 Excel 提供了 残差与每一解释变量间

10、关系的图形，如图 6所示。如果“财政收入”与“税收”间的关系是线性的 （ 考虑其它因素后 ） ，那么残 差图应是显随机图形。 另一方面， 如果能看到曲线或者其他对称图形， 那么就应 改变模型来加入非线性关系。大多数观测者会得出残差图基本上是随机的这一结论， 所以不需要附加的模 型化处理。因为所用的样本较小 （只有 18个观察对象） ，所以很难查出非线性模 式。观察图 6 所示的残数税收估值图， 该图显示基本， 且样本很小， 使检测非 线性模式十分困难。6拟合效果考察将财政收入原始值与估计值做线图， Excel 步骤为：（1）选数据：选 B1:B19,按 Ctrl 选 127:145（ 2）点击

11、“图表向导”，选择“折线图”，按“完成”按钮执行，结果见 图77预测：通过前面对回归方程及回归系数的检验知所建立的方程有意义， 同时通过上图也看到，该模型的拟合效果很好，今用该方程对1996、1997、和2000年财政收入进行预测，结果见下图 8。图践辖分折隆MNU1Jt1JLJ fiL1I4U.31!口上1那弹铲3 izri. v4 lilt *1 1 叶月 4 IMiVVMl. It» ff fFl#M-i禹J1"珂馆N>t1r 7HMTT-Jd, qH IzIP*1*ffr 1电p44 42 4JV.i i> I r -h ip i<i h m*斬.

12、M图7拟合效果图B 预测绪果药*=二!曲脚:腻凋肿皿边:隠1.£ B1 门 D E !G1,11T£迪£X劇就d mi m 朝戸此別i'阪血X处H JI3J3 mam飆ii觌卫瓏勺mj下面各步骤描述了如何用TREN函数预测“财政收入”：（1）按图1所示，在工作表（C28:F30）中输入解释变量的值（2）选取将要包含预测值的单元（B28）。（3）从Excel “插入”菜单中选择“函数”命令，选取函数分类列表框中的统计，并选取“函数名”列表柜中的TREND然后单击“确定”。TREN函数的语法为：TREND原 y' s,原 x' s,新 x' s,常数项）本例 B28 =TREND（$B$2:$B$19,$C$2:$F$19,C28:F28,1）（4） 将单元B28的拷贝到B29: B3