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

“国民生产总值”系数的t统计值大于2,p

而“其他收入”和“社会从业人数”系数的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。

下面各步骤描述了如何用TREND函数预测“财政收入”:

(1)按图1所示,在工作表(C28:F30)中输入解释变量的值

(2)选取将要包含预测值的单元(B28)。

(3)从Excel“插入”菜单中选择“函数”命令,选取函数分类列表框中的统计,并选取“函数名”列表柜中的TREND,然后单击“确定”。TREND函数的语法为:

TREND(原y’s,原x’s,新x’s, 常数项)

本例 B28 =TREND($B$2:$B$19,$C$2:$F$19,C28:F28,1)

(4)将单元B28的拷贝到B29:B30中, 得结果见上表

(5)预测的解释

对2000情况来说,对于“税收”为68043.28、“国民生产总值”为8571.70,“其他收入”为652.99,“社会从业人数”为72361.00的财政收入的最佳预测值是8069.13,与实际值的8075.38相差很小。

从上述的分析计算过程中可以看出,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

“国民生产总值”系数的t统计值大于2,p

而“其他收入”和“社会从业人数”系数的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。

下面各步骤描述了如何用TREND函数预测“财政收入”:

(1)按图1所示,在工作表(C28:F30)中输入解释变量的值

(2)选取将要包含预测值的单元(B28)。

(3)从Excel“插入”菜单中选择“函数”命令,选取函数分类列表框中的统计,并选取“函数名”列表柜中的TREND,然后单击“确定”。TREND函数的语法为:

TREND(原y’s,原x’s,新x’s, 常数项)

本例 B28 =TREND($B$2:$B$19,$C$2:$F$19,C28:F28,1)

(4)将单元B28的拷贝到B29:B30中, 得结果见上表

(5)预测的解释

对2000情况来说,对于“税收”为68043.28、“国民生产总值”为8571.70,“其他收入”为652.99,“社会从业人数”为72361.00的财政收入的最佳预测值是8069.13,与实际值的8075.38相差很小。

从上述的分析计算过程中可以看出,Excel在回归预测应用中,其分析计算简捷,检验结果准确方便,使统计预测分析更具实用价值。


相关内容

  • 巧用Excel解决多元非线性回归分析
  • ·研究与开发· 农业网络信息 AGRICULTURENETWORKINFORMATION 2011年第1期 巧用Excel解决多元非线性回归分析 龚江,石培春,李春燕 (石河子大学农学院,石河子832003) 摘 要:非线性回归是回归分析的重要内容和难点,而多元非线性回归在农业生产中有重要的应用.应 ...

  • 神经网络与回归方法分析(数学建模)
  • 多元回归与神经网络的应用 摘要 本文主要是通过整理分析数据, 以得出题目中所给出的x i 与y j 的函数关系. 由于数据并不是很充足,我们选择了所有数据为样本数据和部分数据为验证数据.我们首先采用了多元回归方法,由于数据之间并没有明显的线性或者其它函数关系,模型很难选择,得到的结论对于y 1来说残 ...

  • 经济预测与决策实验报告-副本
  • 重 庆 交 通 大 学 学 生 实 验 报 告 实验课程名称 经济预测与决策上机实验报告 开课实验室 学 院 管理学院 年级 09级 专业班 工商管理2班 学 生 姓 名 杨乐晨 学 号 09040229 开 课 时 间 经济预测与决策实验报告 实验一 实验名称:一元线性回归预测上机实验. 实验目的 ...

  • Excel多元非线性回归分析
  • 四个自变量(x1,x2,x3,x4)一个因变量(t)的非线性回归分析,具体数据如下: x1=[50 50 50 50 60 60 60 60 70 70 70 70 80 80 80 80]' x2=[15 20 25 30 15 20 25 30 15 20 25 30 15 20 25 30]' ...

  • 我国城市垃圾产量预测
  • 我国城市垃圾产量预测 孟繁柱 我国城市垃圾产量预测 ForecastingtheAmountofMunicipalSolidWasteinChina 孟繁柱 金志英 王荣森 (沈阳市环境卫生工程设计研究院 沈阳 110013)张 弓也 (沈阳惠天热电股份有限公司) 曹 权 (沈阳皇姑热电有限公司) ...

  • 多元回归分析的应用研究
  • 目 录 摘要: .............................................................................................................................................. ...

  • 基于杭州出租车保有量的预测模型
  • 基于杭州出租车保有量的预测模型 摘要 本文主要讨论了如何确定合理的城市出租车的需求量的问题.以杭州为例,通过该市2001年到2008年的各项数据进行分析,建立多元统计回归模型,对未来几年的出租车保有量进行了预测. 首先,我们通过Excel2003软件画出城市出租车保有量和各解释变量之间的关系,然后建 ...

  • 最优投资组合实验
  • <证券投资分析>上机实验 上机实验要求: 第6,8,10,12周星期三1,2节实验课,共分为四项上机实验项目,上机完成实验内容: 具体内容与步骤: (一)数据收集:3-5项股票的价格,上证指数(至少1年时间跨度),K线图,上市公司财务数据 中国股市股票组合的适宜规模为5-10种股票 为了 ...

  • 多元线性回归在国内生产总值(GDP)增长率预测中的应用
  • 多元线性回归在国内生产总值(GDP)增长率预测中的应用 摘要:产业结构变动与经济增长是分不开的.经济增长率的高低体现了一个国家或地区在一定时期内经济总量的增长速度,也是衡量一个国家或地区总体经济实力增长速度的标志.为了更好的了解我国各产业增长率对我国国内生产总值增长率的影响,从<中国统计年鉴& ...