excel 多元线性回归

EXCEL在多元线性回归分析中的应用高󰀁平/文󰀁󰀁在一元线性回归分析中,重点放在了用模型中的一个自变量X来估计因变量Y。实际上,由于客观事物的联系错综复杂,一个因变量的变化往往受到两个或多个自变量的影响。为了全面揭示这种复杂的依存关系,准确地测定它们的数量变动,提高预测和控制的精确度,就要考虑更多的自变量,建立多元回归模型。多元回归分析的原理和方法同一元线性回归分析基本相同,但有两个不同点:1.不能用散点图来表示变量之间的关系。2.多元回归的计算难度要远大于简单线性回归,且变量越多,计算越复杂。但应用EXCEL来完成计算将变得简单和轻松。以下图中的数据为例:

󰀁󰀁多元线性回归的EXCEL数据分析操作方法首先单击工具栏,在弹出的菜单中选择󰀂数据分析 ,在数据分析工具的选项框中选中󰀂回归 ,然后在输入、输出选项以及有关的选项框中进行适当的选择,必须注意在进行自变量X的输入时要按照已经确定的各个自变量的顺序把所有自变量的单元格引用范围一起放在X值的输入区域内。见下图:!27!󰀁方法探讨󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁∀青海统计#2006年第12期

󰀁󰀁点击󰀂确定 按钮,即可得到线性回归分析的结果。见下图:!28!󰀁方法探讨󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁∀青海统计#2006年第12期󰀁󰀁根据上图中的显示结果,可直接写出二元线性回归方程:Yi=b0+b1X1i+b2X2i=-51.3127+1.4053x1i+6.3823x2ib1表示在促销费用固定时,商店的规模大小每增加1平方米,年销售额平均增加1.4053万元;b2表示在商店的规模大小固定时,促销费用每增加1万元,年销售额平均增加6.3823万元。这里b1即商店的规模大小的回归系数比一元线性回归方程中的回归系数b=1.6246小,是因为一元线性回归方程只考虑了商店的规模大小对年销售额的影响,忽略了促销费用这一很重要的因素,在商店的规模大小的影响中渗入了促销费用的影响。这里的截距b0=-51.3127万元,与一元线性回归方程中的截距+99.01万元有很大的不同,因为X1=0和X2=0都不在X1、X2的样本取值范围之内,因而对截距项的解释要非常谨慎。判定系数等于85.14%,表明在年销售额的变动中,有85.14%可由商店规模大小和促销费用多少这两个因素的变动来解释,只有14.86%的因素属于随机误差。引进了第二个自变量之后,回归方程的判定系数85.14%,比一元线性回归方程的判定系数77.68%提高了7.46个百分点。但需注意,在一般情况下,增加自变量,即使这个自变量在统计上并不显著,也会使判定系数的值增大。年平均销售额的估计标准误差为112.1015万元,引进了第二个自变量促销费用之后,回归方程的估计标准误差比一元线性回归方程的估计标准误差131.99万元有了下降,说明多元线性回归方程的代表性高于一元线性回归方程。设显著性水平󰀁=0.05,b1的检验统计量t=6.2817;b2的检验统计量t=2.4538,查t表知t0.05/2(15-3)=2.1788。因为6.2817>2.1788,2.4538>2.1788。因此拒绝H0:1=0、H0:2=0的假设,认为这两个回归系数在统计上都是显著的。需注意的是,若此例的显著性水平=0.01,不是0.05,则t0.01/2(15-3)=3.0545。虽然6.2817>3.0545,但是2.4538<3.0545,因此仍要拒绝H0:󰀂1=0的假设,但无法拒绝、H0:󰀂2=0的假设,所以第二个回归系数在统计上不是非常显著。设计显著性水平󰀁=0.05,查得F0.05(2,12)=3.89。F=34.38>F0.05(2,12)=3.8,所以拒绝原假设,表明样本的r2是显著的,由此推论已建立的二元线性回归模型有效。所谓复相关,是指一个因变量同多个自变量之间的相关关系。所有自变量共同变动时,因变量随之变动,其相关程度就可用复相关系数来测定。该例中商店规模大小、促销费用和年销售额三个变量的复相关系数为0.9227。计算结果表明,商店规模大小、促销费用作为一个整体影响因素同年销售额存在高度相关,其相关程度比一元回归中商店规模大小单个自变量同年销售额的相关系数更高。但需要强调是当我们研究的客观事物本质上属于多因素影响的变量时,用多元回归、复相关和偏相关分析,比一元回归和单相关分析更为真实和准确。(作者单位:省统计局)(下接第37页)󰀁󰀁3、加强普查队伍的建设与培训。农业普查不仅工作量大,而且专业性强,数据质量要求高。能否建立一支业务过硬、作风严谨、责任心强的高素质普查队伍,关系到普查的成败。因此,各级、各部门特别是县区政府一定要按照普查办法的要求,把好人员选聘和培训关,选调业务过硬、作风严谨、责任心强的人员充实到各级普查机构。与此同时,要认真做好普查培训和切实搞好普查试点工作,使所有普查人员明确普查指标的内容含义、要求及普查指标间的逻辑关系,准确把握普查的难点内容和问题,尤其要学会如何利用被调查对象的总体情况,现场分析评估被调查对象申报的数据,当场修改不实数据,确保各类普查数据的真实性。4、加强依法普查,确保普查质量。各级、各有关部门要以这次普查为契机,加大统计普法力度,使各级普查机构严格按照∀中华人民共和国统计法#的有关规定和普查的具体要求,克服困难,依法实事求是认真调查和填报,不弄虚作假,使各被调查单位和农户如实填报普查表,不虚报、瞒报。总之,要确保此次普查情况不失真,调查数字不含水,统计数据不掺假,经得起实践的检验、群众的检验和历史的检验,为更好地推进新农村建设,获取真实的󰀂三农 数据。做到这一要求,只要有好的方案,通过法制手段、宣传手段、培训手段和市场经济手段等多种措施,解决了人的问题,包括各级领导、各级普查人员、各被调查对象的认识问题、思想问题,普查的难点会迎刃而解。否则,别无他法。(作者单位:山东省沂南统计局󰀁文登统计局)!29!󰀁方法探讨󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁󰀁∀青海统计#2006年第12期

２０１１年９月　

第１４卷第１８期　中国管理信息化　

Ｃｈｉｎａ　Ｍａｎａｇｅｍｅｎｔ　Ｉｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ　Ｓｅｐ．，２０１１　

Ｖ０１．１４．Ｎｏ．１８　

基于Ｅｘｃｅｌ的最优子集多元线性回归预测模型设计　

杨桦　

（东北财经大学职业技术学院，辽宁大连１１６０２３）　

［摘要］回归分析预测法是一种具体的、行之有效的、实用价值很高的常用市场预测方法。统计学中的回归预测分析具有　

普遍的实用意义，但变量之间关系分析及计算繁杂，而借助Ｅｘｃｅ１可方便高效地研究其数量变动关系，完成其繁杂的计算　

分析过程。本文利用Ｅｘｃｅ１的图表及数据分析工具，采用最优子集法，判断各变量问的相关程度，并在此基础上建立回归模　

型进行预测．使回归预测分析的计算过程更加简单。统计预测方法更为实用　

［关键词］Ｅｘｃｅｌ：最优子集；多元线性回归；预测　

ｄｏｉ：１０．３９６９／ｉ．ｉｓｓｎ．１６７３—０１９４．２０１１．１８．０４６　

［中图分类号］Ｆ２７２．１；Ｆ２２４７［文献标识码］Ａ　［文章编号］１６７３—０１９４（２０１１）１８－００７０—０２　

１　引　言　

回归分析预测法有多种类型　依据相关关系中自变量的个　

数不同进行分类．可分为一元回归分析预测法和多元回归分析　

预测法。在一元回归分析预测法中，自变量只有一个．而在多元　

回归分析预测法中．自变量有两个以上。依据自变量和因变量之　

间的相关关系不同．可分为线性回归预测和非线性回归预测　

多元线性回归在诸多学科中有广泛应用　在多元线性回归　

的实际应用中，考虑的自变量Ｘ　（　ｌ，２，…，ｍ，ｍ为自变量数目　

或个数）经常包括所有可能影响因变量ｙ的因素。在众多的　，　

中．有的对ｙ有显著影响．有的影响很小甚至基本无影响　如果　

把对ｌ，影响小的　．保留在回归模型中．不仅增加收集数据和分　

析数据的负担，使得回归方程不稳定，而且会因　的数目过多而　

不便于使用　因此．自变量选择在理论上和应用上都十分重要　

实用标准文案

精彩文档 用Excel进行一元线性回归分析

Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归分析。本文就从最简单的一元线性回归入手.

在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。

文章使用的是2000版的软件,我在其中的一些步骤也添加了2007版的注解.

1 利用Excel2000进行一元线性回归分析

首先录入数据.

以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。

图1

第二步，作散点图

如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插入”菜单中打开“图表（H）(excel2007)”。图表向导的图标为。选中数据后，数据变为蓝色（图2）。 实用标准文案

精彩文档 图2

点击“图表向导”以后，弹出如下对话框（图3）：

图3

在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）： 实用标准文案

精彩文档 灌溉面积y(千亩)01020304050600102030灌溉面积y(千亩) 图4

第三步，回归

观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下：

⑴ 首先，打开“工具”下拉菜单，可见数据分析选项（见图5）(2007为”数据”右端的”数据分析”)：

图5

用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：

《计量地理学》实验指导

§2 运用EXCEL、SPSS进行相关分析和线性、非线性回归分析

回归分析是处理两个及两个以上变量间线性依存关系的统计方法。可以通过软件EXCEL

和SPSS实现。

一、利用EXCEL软件实现回归分析 以第4章习题2为例，运用EXCEL进行回归分析。

首先在菜单中选择工具==>加载宏，把“分析工具库”和“规划求解”加载上。然后在

“工具”菜单中将出现“数据分析”选项。点击“数据分析”中的“回归”，将出现对话框

如下图1所示。

图1 回归界面

【输入】

用以选择进行回归分析的自变量和因变量。

在“Y值输入区域”内输入B7:B11，在“X值输入区域”输入A7：A11，如果是多元线

性回归，则X值的输入区就是除Y变量以外的全部解释变量 “标志”；置信度水平为95％，输出结果选择在一张新的工作表中； “残差分析”，并绘制回归拟合图，点击“确定”即得到残差表。

【输出选项】

用于指定输出结果要显示的内容，包括是否需要残差表及图，参差的正态分布图等。

输出结果解释

图 2 回归结果显示

回归结果分为三部分：

（1）回归统计：包括R^2 及调整后的R^2、标准误差和观测值个数

（2）方差分析：包括回归平方和、残差平方和总离差平方和以及它们的自由度、均方

差和F通机量

（3）回归方程的截距、自变量的系数以及它们的t统计值、95%的上下限值 X Variable 1 Residual Plot

-0.100.1

01020304050

X Variable 1残差

图3 残差与子变量之间的散点图 X Variable 1 Line Fit Plot

2000200220042006

01020304050X Variable 1YY预测 Y

图4 预测值与实际值散点图

同样，如果在“数据分析”中点击“相关系数”，可以对多个变量进行相关系数的计

算。

二、.利用SPSS软件实现回归分析

在SPSS软件中，同样可以简单的实现回归分析，因为回归分析包含了线性回归与曲线