Excel在一元线性回归预测分析中的应用
Vol119 No14 JournalofHandanPolytechnicCollege Dec12006
Excel在一元线性回归预测分析中的应用
赵丽娟1 冯韶华2
(1邯郸职业技术学院经济系,河北邯郸 056001;2,河北)
摘要:预测是企业决策的前提与基础,Excel。我们可以运用Excel进行回归分析预测,,Excel函数与数据分析工具两种。
关键词:Excel;;文献标识码:A 文章编号:1009-5462(2006)04-0066-06
现代企业经营管理离不开决策,决策的正确与否关系到企业的生存与发展。而正确的决策要依据正确的预测,预测分析是决策的前提与基础。预测分析的方法种类繁多,随分析对象和预测期限不同而有差异,但基本方法可分为定量分析法和定性分析法。
回归分析法是根据事物的因果关系对变量的预测方法,它是定量预测方法的一种。因果关系普遍存在,比如,收入对消费支出的影响预测、产量对生产成本的影响预测、销量的预测、资金需要量的预测等,都可以运用回归分析法建立数学模型,进行预测分析。Excel是一个功能强大的数据管理与分析软件,我们可以运用Excel函数与数据分析进行回归分析预测。一、一元线性回归预测法的基本原理
该方法是指影响市场变化的因素虽然是多方面的,但存在一个因素是最基本的、起决定作用的,而且自变量与因变量之间的数据分布成线性(直线)趋势,那么就可以运用一元线性回归方程y=a+bx进行预测。这里,y是因变量,x
是自变量,a,b均为参数,其中b为回归系数,表示当x每增加一个单位时,y的平均增加数量。
例如要对城镇居民消费支出的发展趋势进行预测,首先要找到影响城镇居民消费支出的影响因素。通过分析我们发现城镇居民消费支出的多少与城镇居民人均可支配收入密切相关,收入高,其消费支出就大,反之消费支出就小。但城镇居民人均可支配收入并不能完全决定消费支出,城镇居民消费支出的多少还受到市场环境、居民储蓄额、消费习惯、消费心理等诸多因素的影响,因此,城镇居民消费支出与城镇居民人均可支配收入是一种非确定性关系(即相关关系)。
当因变量、自变量间变化呈现线性关系时,我们就可以用一条直线来表示它们的线性关系,以便于预测。但对于这种线性关系,我们可以用很多条的直线来描述,每条线对变量之间关系的拟合程度是不相同的
,我们要从所有直线中找到一条最具有“代表性”的直线。常用的确定直线方程的方法是最小二乘法。
一元线性回归预测法就是要依据一定数量的观察值(xi,yi),i=1,2,...,n,找出回归方程y=a+bx,确定方程参数a,b,即:
∑y∑yb= a=nn∑xi2-(∑xi)2
收稿日期:2006-08-20
作者简介:赵丽娟(1974-),女,河北曲周人,邯郸职业技术学院经济系讲师。
・66・
第19卷第4期 邯郸职业技术学院学报 2006年12月这样,把求出的a,b代入
y=a+bx中,就得到回归直线,只要给定xi值,就可以用
yi作因变量yi的预测值。
二、一元线性回归预测实例以上我们介绍了一元线性回归预测的基本原理,下面我们将通过实例说明一元线性回归预测的方法应用,以及如何利用办公软件Excel2000来分析处理数据。成数据的计算。
案例:
表1为我国1991-2001与消费支出之间的关系,并根据年2003。
1990
19911992
1993
1994
1995
1996
1997
1998
1999
2000
20011278.891453.811671.732110.812851.343537.573919.54185.64331.64615.949985309.01城镇居民人均可支配收入1510.21700.62026.62577.43496.242834838.95160.35425.[1**********]59.6
预测步骤
:
第一种方法:运用数据分析工具
1.将1990-2001年城镇居民人均可支配收入与消费支出的原始数据按照如图1的格式输入到Excel中,并绘制散点图如图2。
2.通过目测发现其散点图呈直线趋势。通过相关分析判断变量间相关的程度。
选择工具菜单中的数据分析命令,弹出数据分析对话框。(若没有,需要通过安装Excel2000的加载宏之后,对它们进行调用,然后才可以在编辑窗口的菜单中使用相关的命令)
在分析工具列表框中,选择相关系数工具,出现相关系数对话框,如图3所示。输入各项参数后得到相关分析结果,如图4所示。从计算出的相关系数可以看出,城镇居民人均可支配收入与消费支出之间存在较强的相关性。因此,需要利用回归分析工具进一步建立回归方程。
・67・
3.建立回归模型
选择工具菜单中的数据分析命令,弹出数据分析对话框。
在分析工具列表框中选择回归工具,弹出回归对话框。
指定输入参数。在输入Y区域、输入X区域指定相应数据所在的单元格区域,本例分别指定为B2:B14和C2:C14,并选定标志复选框,在置信水平框内键入95%。对于一些特殊的回归模型,可以根据需要指定常数为0。
・68・
指定输出选项。这里选择输出到新工作表组,并指定工作表的名称为“回归模型”。选定残差(即随机误差项)、线性拟合图和正态分布中的所有输出选项,以观察相应的结果。
单击确定按钮。如图5所示
。
最后得到回归分析的结果。图6是有关回归分析的统计量、方差分析表和回归系数及t检验、预测区间等数据。
分析图6的计算结果,可得下述检验结论(本例样本个数n=12,解释变量个数m=1)。
拟合程度检验:在回归统计区域A3:B8中给出的R2为0.998504,调整后的R2为0.998354,均很接近1,说明x与y的关系很密切。
F检验:在方差分析区域A10:F14中给出的F检验值为6672.716,
F0.05(1,12-2)=4.96,F检验值远远大于F0.05(1,12-2),说明x与y的回归方程显著。
t检验:在回归模型区域A16:I18中给出了回归系数a、b的估计值极其标准误差、t检验值和回归系数估计区间的上下限等。a=151.60、b=0.77
回归系数b的t检验值为81.6867,t2(12-2)=2.228,t检验值大于t2(12-2),故拒绝原假设,即可以断言,城镇居民人均可支配收入对消费支出有显著影响。
综合上述计算结果和检验结果,确定回归模型如下:
y=151.60+0.77x R2=0.9985 n=12 F=6672.716 S=58.2346
这是一个较为优良的回归模型。现在利用该回归模型,就可以根据预测期的城镇居民人均可支配收入预测消费支出。
假定2002年城镇居民人均可支配收入为7300元,则2002年消费支出的预测值为:
y=151.60+0.77x=151.60+0.77×7300=5772.6(元)
因标准误差S=58.2346、样本个数n=12、解释变量个数m=1,则在显著水平α=0.05下,2001年消费支出的预测区间为:
μt(n-m)・μ2.228×μ129.7467yS=5772.658.2346=5772.62
即:当2001年的城镇居民人均可支配收入为7300元时,在显著水平α=0.05下,城镇居民消费支出预测区间在5642.8533～5902.3467元之间。
第二种方法:运用函数法
1.将1990-2001年城镇居民人均可支配收入与消费支出的原始数据按照如图1的格式输入到Excel中,并绘制散点图。
2.通过目测发现其散点图呈直线趋势。通过相关分析判断变量间相关的程度。
运用函数CORREL求出相关系数,CORREL函数的语法格式如下:
CORREL(array1,array2)
Array1第一组数值单元格区域。
Array2第二组数值单元格区域。
输入参数后得到计算结果,如图7所示。
・69・
Vol119 No14 JournalofHandanPolytechnicCollege Dec12006
3.利用,LINEST函数的语法格式如下:
LINx’s,const,stats)
其中:
Known-y’s是关系表达式y=a+bx中已知的y值集合。Known-x’s是关系表达式y=a+bx中已知的可选x值集合。
Const为一逻辑值,指明是否强制使常数a为0
。
如果const为TRUE或省略,a将被正常计算。
如果const为FALSE,a将被设为0,并同时调整b值使y=bx。
Stats为一逻辑值,指明是否返回附加回归统计值
。
如果stats为FALSE或省略,函数LINEST只返回系数b和常数项a。
如果stats为TRUE,函数LINEST返回各个回归系数及附加回归统计值,函数返回的数据格式如表2所示。
表2 函数返回的数据格式
计算结果
ββΛ ββm m-1 1 0
Λ SE1 SEm SEm-1 SE0
R2 S
F df
S回 S残 数据含义回归系数回归系数的标准误差复可决系数 R2、因变量标准误差F统计量、自由度 df回归平方和 S回、残差平方和 S残
具体步骤如下:
为输出数据指定足够的存储区域;
在单元格E4中输入公式,并输入函数参数,如图8所示;
按下组合键Ctrl+Shift键后,再按回车键,系统输出如图9所示。
・70・
第19卷第4期 邯郸职业技术学院学报 2006年12月对照上表可知,回归系数分别为a=151.60、b=0.77,复可决系数R2=0.9985,因变量的标准误差S=58.2347,F统计量=6672.7159,残差平方和S残=33912.77、回归平方和S回=22629031.11
将这些计算结果与由回归工具所得的结果(图6)比较可以看出,使用LINEST函数与利用回归工具进行回归分析可得到一致的结论。
参考文献
[1](美)拉格斯代尔著,杜学孔,崔鑫生译.电子表格建模与决策分析[M].北京:电子工业出版社[2](美)罗伯特・S・平狄克,丹尼尔・L・鲁宾费尔德著,钱小军译.:机械工业出版社,
1999.11
[3]刘利主编.中文版Excel2003教程[M].北京:[责任编校:张勇]
(上接第65页)
3结论
1)实验结果显示冷凝温度的变化主要受循环水流量和水箱水温的影响,而与蒸发温度的变化关系不大。且随着流量的增大,流量对冷凝温度的影响越小。2)
从实验结果可以看出冷凝回收利用家用空调的冷凝热是可行的。
参考文献
[1]张华俊,董晓俊,项卫中.制冷系统余热回收的研究[J].低温工程,1997,99(5):37-43.
[2]季杰,裴刚等.空调-热水器一体机制冷兼制热水模式的性能模拟和实验分析[J].暖通空调2003,33(2):19-23.
[3]季杰,裴刚等.空调-热水器一体机单独制热水模式的性能模拟和实验分析[J].暖通空调,2004,34(12):96-98.
[4]江辉民,王洋,马最良等.带热水供应的家用空调器的探讨[J].建筑热能通风空调.2004,23(1):48-51.
[5]江辉民.带热水供应的节能型空调器的实验研究[D].哈尔滨:哈尔滨工业大学,2003.
[责任编校:张勇]
・71・