excel在回归分析中的应用.pdf

《excel在回归分析中的应用.pdf》由会员分享，可在线阅读，更多相关《excel在回归分析中的应用.pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel在回归分析中的应用涂 虬(武钢职工大学土建系 武汉:43008)摘 要 回归分析方法是数量统计常用的一种方法。本文详细介绍了如何使用EXCEL进行回归分析的二种方法:“回归”分析工具以及工作表函数,并以实例进行了讲解。关键词 Excel 回归分析 工程表函数 显著性检验中图分类号:文献标识码:A来稿日期:2001.02.05作者简介:涂虬,男,1970年生,武钢职工大学副教授。1 前言回归分析方法是处理多个变量之间相互关系的一种数学方法,是数理统计常用方法之一。从分析测试的观点来看,回归分析的任务就是找出响应值y(因变量)与影响它的诸因素xi(自变量,i=1,2,3,n)之间的统计关

2、系(回归模型),利用这种统计关系在一定置信度下由各因素的取值去预测响应值的范围,在众多的预报变量中,判断哪些变量对自变量的影响是显著的,哪些变量的影响是不显著的;根据预报变量的给定值来估计和预测精度。常用的回归模型包括线性回归、非线性回归,前者又可分为一元线性回归、多元线性回归,后者分为可化为一元线性方程的回归方程,如冥函数、指数函数、对数函数等,以及可化为多元线性方程的回归方程,如多项式方程。传统的回归分析方法是对线性回归模型采用最小二乘法来拟合回归方程,然后计算相关系数进行显著性检验,而对非线性方程,还得对自变量和因变量作适当的变换,把非线性方程转化为线性方程,然后再用线性回归的方法处理。

3、这种传统的回归计算方法,尤其对于多元非线性方程,求解过程非常繁琐,计算复杂。Excel在回归分析中能避开复杂的计算,把复杂的计算过程交给计算机处理,使用起来非常简单。本文将探讨如何使用Excel进行回归分析。2Excel回归分析过程与方法介绍2.1 回归分析过程回归分析过程就是通过样本观测数据对模型中的因变量f(x)进行估计,分析随机误差项的分布特征,使用模型进行预测预报,任何一个回归分析过程包括以下内容:(1)建立回归模型,确定自变量和因变量,判定因变量f(x)的形式。(2)用样本数据来拟合模型中的未知参数。(3)确定随机误差项和估计量的分布性质。(4)拟合优度检验,验证用X预测Y的拟合程度

4、。(5)利用模型进行预测和控制。预测问题是对固定的X值预测相应的Y值,而控制问题可以看成是预测问题的反问题,通过控制X值以便把Y的值控制在指定的范围内。第13卷第2期2001年6月武钢职工大学学报Journal of University for Staff and Workers of WISGCOVol.13.No.2June.2001采用Excel以取代传统的数学方法,计算(2),(3),(4),(5),这正是Excel用以回归分析的方便所在。2.2 回归分析方法介绍Excel为用户提供了二种回归分析工具:“回归”分析工具、工作表函数。2.2.1“回归”分析工具使用方法介绍(1)在Exc

5、el工作区单元格中录入样本观测数据。(2)建立回归模型,确定因变量f(x)的形式。取样本观测数据,录入Excel中的图表向导,选用“XY散点图”,把样本数据连成曲线,观察曲线形状,建立回归方程。(3)启动“回归分析”分析工具从“工具”菜单中选择“数据分析”子菜单,打开“数据分析”对话框,从中选择“回归”分析工具。如果“工具”菜单中没有“数据分析”,从“工具”中选定“加载宏”,在“加载宏”中选定“分析工具库”即可在“工具”菜单中加载“数据分析”。(4)填写“回归分析”对话框,如图1所示。“回归”对话框(5)回归结果分析 在回归“对话框中按“确定”按钮,可以得到回归结果。2.2.2 工作表函数使用

6、说明Excel提供了9个函数用于建立回归模型和回归预测,其中5个函数用于一元回归分析,4个函数用于多元回归分析,这些函数列于表1。表1 用于回归分析的工作表函数函 数 名定 义语 法 格 式INTERCEPT一元线性回归模型的估计值(截距)INTERCFPT(Known-ys,Known-xs)SLOPE一元线性回归模型的估计值(斜率)SLOPE(Known-ys,Known-xs)RSQ一元线性回归模型的决定系数(r2)SRQ(Known-ys,Known-xs)FORECAST依照一元线性回归模型的预测值FORECAST(new-xs,Known-ys,Known-xs)STEYX依照一元

7、线性回归模型的标准误差STEYX(Known-ys,Known-xs)TREND依照多元线性回归模型的预测值TREND(Known-ys,Known-xs,new-xs,const)GROWTH依照多元指数回归模型的预测值GROWTH(Known-ys,Known-xs,new-xs,const)LINST估计多元线性回归模型的未知函数LINST(Known-ys,Known-xs,const,stats)LOGEST估计多元指数回归模型的未知函数LOGEST(Known-ys,Known-xs,const,stats)对以上九个函数说明如下:(1)所有函数必须要求自变量与因变量的数据个数相同

8、,否则将返回错误值。52涂虬:Excel在回归分析中的应用(2)工作表函数参数分别说明如下:Known-ys,known-xs分别表示因变量、自变量的数据区域引用;new-xs表示待预测的自变量值。Const:用于指定是否要设定常数为0的逻辑值。如果Const设定为TRUE,则常数项将通过计算求得。如果Const设定为FALSE,则常数项将设为0,调整回归参数以满足公式y=f(x)。Stats:为逻辑值,用以指出是否返回除回归系数以外的其它统计量值,如果为TRUE,则返回这些值,如果为FALSE,不返回这些值。这些统计量包括:回归系数的标准误差(抽样误差)、决定系数(r2)、估计量的标准估计误

9、差(抽样误差)、自由度、回归平方和、残差平方和。以上工作表函数使用非常简单,只要理解各工作表函数定义及语法格式,使用起来非常方便。3Excel“回归”分析工具的使用以下以具体实例讲解如何使用Excel“回归分析”分析工具。3.1一元线性回归模型一元线性回归方程可表示为y=+x+,式中,是要求解的回归参数,是一个随机变量,即因变量Y的随机误差项,它反映了除X变量以外其它变量对Y的影响程度和方式。“回归”分析对话框见图1,需要说明的是:自变量X、因变量Y在单元格中必须以单列数据形式输入。例1:炼铝厂测得铝硬度X与抗张强度Y的数据如下:xi68537084607251837064yi28829834

10、9343290354283324340286求X对Y的回归方程。解:(1)工作区单元格中录入数据:xi录入 AB:A11单元格,yi录入在 B2;B11单元格。(2)建立回归模型,确定因变量f(x)的形式。按2.2.1(2)中介绍的方法,确定回归方程y=+x+(3)启动“回归分析”分析工具,从“工具”菜单中选择“数据分析”子菜单,打开“数据分析”对话框,从中选择“回归”。(4)填写“回归分析”对话框。在“Y”值输入区域”输入:B2:B11,在“X”值输入区域”输入:A2:A11。“标志”:本例题未选“置信度”:省略,表示95%“常数为零:”不强制回归通过原点,可不选。“残差”:如果不需要以残差

11、形式出现,不选此复选框。(6)分析结果:如下表所示(由于篇幅有限,只给出部分结果):SUMMARY OUTPUT回归统计MuItiple R0.680744861R Square0.463413566Adjusted R Square0.396340262标准误差22.68523588观测值10方差分析dfSSMSFSignificance F回归分析13555.5405843555.5405846.90906120.03024811残差84116.959416514.619927总计97672.562 武钢职工大学学报 2001.6(2)Coefficients标准误差t StatP-val

12、ueLower 95%Upper 95%下限95%上限95.0%Intercept193.9507524 46.795780464.144620530.003232886.03941937301.86208586.0394194 301.862085X Variable 1 1.8007295940.685076362.6285093160.03024810.2209396543.380519530.22093965 3.38051953回归分析正态分布图从表中“回归统计”可以看到R的倍数、R的平方、调整后的R平方、标准误差。从“方差分析”可以看到残差的方差、平均残差的方差以及F下的显著水平。

13、回归分析出的截距=193.95 斜率=1.80073 则回归方程为y=1.80073x+193.95+3.2 多元线性回归方程多元线性回归方程可表示为y=+1x2+2x2+nxn+,式中,1,2,n是要求解的回归参数,意义同上。同样地,自变量X、因变量Y在单元格中必须以单列数据形式输入。例2 铝合金化学铣切工艺中,为便于操作,需要对腐蚀速度进行控制,因此要考察腐蚀液温度X1(.C)、碱浓度X2(g/c)、腐蚀液含铝量x3(g/1)对腐蚀速度Y(mm/min)的影响,一共做了20次,所得数据如下:Xi173737575757579797979Xi212213042364812213036Xi32

14、00200175175200200200200225225yi0.02400.02350.02400.01900.02450.01850.03200.03000.02900.0275Xi179798383838383838787Xi242481221303642481221Xi3200200200225175175200200200225yi0.02500.02500.03700.03600.03550.03250.03050.02700.04400.0425Xi3200200200225175175200200200225yi0.02500.02500.03700.03600.03550.0

15、3250.03050.02700.04400.0425求y对x1,x2,x3的线性回归方程。解:(1)在工作区单元格中录入数据(x1,x2,x3值分别输入在 A2:A21,B2:B21,C2:C21;yi值输入在 D2:D21)。(注意该区域必须由单列数据组成)(2)建立回归模型y=+1x1+2x2+3x3+(3)填写如图1所示“回归分析”对话框在“Y值输入区域”输入:D2:D21;在“X值输入区域”输入:A2:C2172涂虬:Excel在回归分析中的应用其余同图例1(4)结果分析:如下表所示(由于篇幅有限,只给出部分结果)SUMMARY OUTPUT回归统计MuItiple R0.98590

16、8501R Square0.972015572Adjusted R Square0.966768492标准误差0.001277812观测值20方差分析dfSSMSFSignificance F回归分析30.00090740.0003025185.24891.23996E-12残差162.612E-051.633E-06总计190.0009336Coefficients标准误差t StatP-valueLower 95%Upper 95%下限95%上限95.0%Intercept-0.068806790.0062661-10.980817.36E-09-0.082090315-0.0555232

17、7-0.08209032-0.05552327X Variable10.0013145727.031E-0518.6960442.7E-120.0011655160.0014636290.0011655160.001463629X Variable2-0.000245142.341E-05-10.470111.44E-08-0.000294772-0.0001955-0.00029477-0.0001955X Variable3 4.35492E-061.879E-050.23170860.819702-3.54883E-05 4.41981E-05-3.5488E-05 4.41981E-0

18、5回归分析正态分布图截距=-0.068807,x1的斜率1=0.001315315,x2的斜率2=-0.000245,x3的斜率3=4.3549210-6回归方程为y=0.001315315x1-0.000245x2+4.3549210-6x3-0.068807+3.3非线性回归方程回归模型是非线性回归方程,其方程形式不定,大体上可分为两类,一类是可化为一元线性方程的非线性回归方程,如冥函数y=x+,双曲线方程1y=+x+,指数曲线方程y=x+,对数曲线y=+lnx+。另一类是可以化为多元线性方程的非线性回归方程,如多项方程y=+1x+2x2+nxn+,以上各式中,1,2,n是要求解的回归参数

19、。以下以多项式方程为例。例3:某种半成品在生产过程中废品率Y与它所含的某种化学成份X有关,现将试验得到的数据记录如下:x34363738393939404041424345464748y1.301.000.730.900.810.700.600.500.440.560.300.420.350.400.410.60求回归方程82 武钢职工大学学报 2001.6(2)解:(1)在工作单元格录入数据(2)按2.2.1(2)中介绍的方法,画出散点图形状,可以考虑拟合抛物线。设确定的回归方程为y=+1x+2x2+。(3)把多项式方程化为多元线性回归方程,令x1=x,x2=x2,则回归方程为y=+1x1+

20、2x2+x34363738393939404041424343454748x21156 1296 1369 1444 1521 1521 1521 1600 1600 1984 1764 1849 1849 2025 2209 2304Y1.30 1.00 0.73 0.90 0.81 0.70 0.60 0.50 0.44 0.56 0.30 0.42 0.35 0.40 0.41 0.60(4)在工作表单元格中录入数据:X值输入在单元格 A2:A17,X2值输入在单元格 B2:B17,Y值输入在 C2:C17(5)填写如图1所示“回归分析”对话框在“X值输入区域”输入:A2:B17,在“Y

21、值输入区域”输入;C2:C17其余同例1。(6)结果分析如下:截距b=18.0679,x1的斜率1=0.8308,x2的斜率2=-0.009435,回归方程为:y=-8308x+0.009435x2+18.6798+3.4用Excel工作表进行回归分析3.4.1FORECAS T的函数:一元线性回归模型预测值函数根据给定的数据计算或预测未来值。此测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后,返回基于x的线性回归预测值y=+x+。其语法格式见表1。例4:见例1,试推算铝硬度为100时的抗张强度。解:(1)在工作单元格式中录入 B1:K1中录入铝硬度xi数值;在单

22、元格 B2:K2中录入抗张强度yi数值。(2)在单元格 C3内键入“=FORECAS T(100,B2:K2,B1:K1)”(3)输出结果:单击鼠标左键,即可得到结果为374.0237表示铝硬度为100时的抗张强度为374.0237。也可以采用菜单,在“插入”菜单中选择“函数”命令中的“统计”选项的“FORECAS T函数”,出现FORECAS T函数对话框,填入相应的内容即可。3.4.2TREND函数:多元性线回归模型预测值函数返回一条线性回归拟合线的一组纵坐标值(Y值),即用最小二乘法找出最适合给定的数组Know n-ys,Know n-xs的直线,并返回指定数值组new-xs值在直线上对

23、应的Y值。其语法格式见表1。使用TREND函数可以计算同一变量的不同乘方的回归值来拟合多项曲线。例如,假设A列包括Y值,B列包含有X值。可以在C列输入x2,在D列输入x3等等,然后根据B列到D列对A列进行回归计算。当在参数中输入Know n-xs这样的数组常数时,可以用逗号分开同一列中数值,用分号分开数值行。例5:见例1,试推算铝硬度分别为85,90,100的张抗力。解:在单元格中 B1:K1范围内键入铝的硬度。在单元格中 B2:K2范围内键入铝的抗张力。92涂虬:Excel在回归分析中的应用在单元格中 B3:K3范围内键入铝的硬度(86,90,100)启动TREND函数,在Know n-ys

24、,Know n-xs,new-xs的文本框内键入相应的数据小数点。在Const的文本框内键入“TRU E”,按CTRL+S HIFT+EN TER键。计算结果为347.0128,356.0164,347.0227,表示铝硬度分别为85,90,100时的抗张力为347.0128,356.0164,374.0237。3.4.3LOGES T函数:在回归分析中,计算最符合观测数据数组的指数回归曲线方程y=x+或y=1x12x2 nxn+(如果有多个X值),并返回描述该曲线的数组。LOGES T是数组函数,以数组表格的形式返回参数估计结果,结果表的形式如下:系数估计值nn-122b系数估计值的抽样误差

25、sensen-1se2se1se0回归模型性质r2决定系数sey估计值y的抽样误差方差分析表F统计量ssreg回归平方和ssresid残差平方和语法格式见表1是否返回附加的回归统计,由逻辑值stats来判定,如果Stats设为TRUE,函数LOGEST将返回附加的指数曲线回归统计值,返回的数组为(n,n-1,-,b;sen,sen-1,se1,seb,r2,sey,F,df,ssreg,ssresid)。如果Stats设为FALSE或省略,则LOGEST只返回系数n,n-1,1和常数项。需要说明的是:当需要输入一个数组常数,如Known-xs作为参数时,以逗号作为同一行中数据的分隔符,以分号作

26、为不同行数据的分隔符。例6:经过10个月的经济不景气,某公司发现在市场上推出新产品后,销售呈指数增长,此后6个月中,每个月的销售量分别增加为33100,47300,6900,102000。150000,220000单位。试建立指数回归模型。解:将这些数据存入一个命名为“销售量”的区域内。在选定的区域的第1个单元(如DI单元)输入数据公式。“=LOGEST(销售量,(11;12;13;14;15;16),TRUE,TURE,输入结果为:1.46327563495.309770.00263340.035834280.999808620.0110163120896.801142.53601883P0

27、.00048504 由此建立起指数回归方程为:y=495.314633x以上介绍了LINST,LOGEST,GOWTH工作表函数的使用,三个函数区别是:LINEST,LOGEST都返回一组描述数值的相互关系的数值数组,但函数LINEST是用直线来拟回数据的,而函数LOGEST是用指数曲线来拟合数据,而且LOGEST所返回的额外的回归统计值是基于下面的线性模型计算得出的:Iny=X1ln1+xnlnn+ln3.4.4GROWTH函数:指数预测值函数根据给定的数据预测指数增长值。根据已知的X值和Y值,函数GROWTH返回一组新的X值对应的Y,以使GROWTH工作表函数来拟合满足给定X值和Y值的指定

28、的曲线y=x+。语法格式见表103 武钢职工大学学报 2001.6(2)对于返回结果为数组的公式,在选定适当数目的单元格后,必须以数组公式的形式输入。数组公式可以同时进行多重计算并返回一种或多种结果。生成数值公式的方法与生成基本的单值公式相同。先选定要用来包含公式的一个或多个单元格,输入公式内容,再按CTRL+SHIFT+ENTER组合键完成公式输入。当为参数输入数组常数时,应当使用逗号分开同一行中的数据,用分号分开不同行的数据。例7:采用与LOGEST示例中相同的数据,第11到第16月的销售量分别为33100,47300,69000,102000,150000,220000。预测第17,18

29、个月的销售量。解:将第11到第16月的销售输入到名为“UNTFOLD”的单元格中。然后,选定某单元格输入“=GROWTH(UNITSOLD,11;12;13;14;15;16;17;18)”。输出结果为(320197;468536),表示第17月和第18月的销售量分别为320197,468536个单位。3.4.5LINST函数:计算线性回归方程参数的函数使用最小二乘法计算对已知数据进行最佳直线拟合,直线方程为:y=+x+,或y=+1x+2x2+nxn+(如果X值是多重的),并返回描述此直线的数组。LINST返回的数值组是(n,n-1,1,),因此此函数返回数组值,故必须以数组公式的形式输入。语

30、法格式见表1函数LINEST还可返回附加回归统计值,由语法格式(表1)中的Stats逻辑值来判断。如果Stats为TRUE,则函数LINST返回附加回归统计值,这时返回的数组为(n,n-1,1,b;sen,sen-1,se1,seb;F,df;ssreg,ssresid)。如果Stats为FALSE或省略,函数LINST只返回系数n,n-1,1和常数项。回归统计结果表的形式与LOGEST的回归统计结果表的形式相同。例8:一个商务开发者正考虑在已有商业区中购买一群小办公楼。根据以下变量来估计给定地区内的办公楼的价值;Y办公楼的评估值,反映该地区办公楼的价值。X1以平方英尺计算的楼层面积X2办公室

31、数目X3进入办公室的人口数目X4办公楼已经使用的年限开发者从1500个办公楼中,随机抽取11个办公楼作为样本,获取一组经验数据列于表3。解:在工作中单元格 A12:E12中录入数据,见上表。在 A14单元格中键入“=LINST(E2:E12,A2:D12,TRUE,TRUE)返回结果列在表3后部,因此得到一个多元回归模型:Y=52318+27.64X1+12530X2+2553X3-234.24X4在返回的结果中,决定系数r2=0.99675,显示出自变量与销售价格之间有很强的关系。进一步可以用F统计量来检验这种关系是否可靠。如果取临界水平为=0.052,2个自由度为:v1=K=4v2=n-(

32、k+1)=11-(4+1)=6其中,k为回归分析中自变量个数,n为样本点个数,查表得F临界值为4.53,说明回归模型是可用的。以上是用LINST函数拟回多元线性回归方程,至于一元线性回归方程,则更简单,这里不再举例。3.4.6 其它工作表函数的使用介绍其它工作表函数(见表1)INTERCEPT,SLOPE,RSQ,STEYX,只要理解其语法格式,在掌握本文前面介绍的内容基础上,使用起来非常简单。在此不再举例。4 结束语回归分析方法是数理统计常用的一种方法,在科研生产实践中获得了广泛的应用。但在应用它解决实13涂虬:Excel在回归分析中的应用际问题时,传统的回归分析方法求解过程繁琐,计算量大,

33、为其应用带来了困难。本文提出应用EXCEL进行回归分析以取代传统的方法,将复杂的计算过程交给计算机处理,这给回归分析法的应用带来了广阔的应用前景。表3 办公楼价值的评估数据ABCDE1楼层面积办公室数目人口数目已使用年限评估值2231022201420003233333121440004235631.5331510005237932431500006240223531390007242542231690008244821.599126000924712234142900102494332316300011251744251690001225402322149000131423423716452

34、55321066125297682276413873752317830511513268011485306691519400066868385429374042122373616160.9967479939705784629N/AN/A#N/A1745975367426#N/A#N/A#N/A1817323933195652135316#N/A#N/A#N/A参 考 文 献1 余秉勤编著.EXCEL在工程分析中的应用M,武钢职工大学教材科,2000年4月。2 邓勃编著.分析测试数据的统计处理方法M,清华大学出版社,1993年12月。Application of Excel in Regress

35、ion AnalysisTu QiuAbstract:Regression analysis is a usual method used in mathematics.Thispaper introduces two methods to makeregression analysis,which are“reqression”analysis tool and work-table function.Some examples are adopted toexplain regression analysis used by Excel.Keywords:Excelregression analysiswork-table functiontest of significance23 武钢职工大学学报 2001.6(2)