下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 excel在回归分析中的应用

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

excel在回归分析中的应用

hes001
2010-11-10 0人阅读 举报 0 0 暂无简介

简介:本文档为《excel在回归分析中的应用pdf》,可适用于其他资料领域

Excel在回归分析中的应用涂 虬(武钢职工大学土建系 武汉:)摘 要 回归分析方法是数量统计常用的一种方法。本文详细介绍了如何使用EXCEL进行回归分析的二种方法:“回归”分析工具以及工作表函数,并以实例进行了讲解。关键词 Excel 回归分析 工程表函数 显著性检验中图分类号:   文献标识码:A来稿日期:作者简介:涂虬,男,年生,武钢职工大学副教授。 前言回归分析方法是处理多个变量之间相互关系的一种数学方法,是数理统计常用方法之一。从分析测试的观点来看,回归分析的任务就是找出响应值y(因变量)与影响它的诸因素xi(自变量,i=,,,⋯n)之间的统计关系(回归模型),利用这种统计关系在一定置信度下由各因素的取值去预测响应值的范围,在众多的预报变量中,判断哪些变量对自变量的影响是显著的,哪些变量的影响是不显著的根据预报变量的给定值来估计和预测精度。常用的回归模型包括线性回归、非线性回归,前者又可分为一元线性回归、多元线性回归,后者分为可化为一元线性方程的回归方程,如冥函数、指数函数、对数函数等,以及可化为多元线性方程的回归方程,如多项式方程。传统的回归分析方法是对线性回归模型采用最小二乘法来拟合回归方程,然后计算相关系数进行显著性检验,而对非线性方程,还得对自变量和因变量作适当的变换,把非线性方程转化为线性方程,然后再用线性回归的方法处理。这种传统的回归计算方法,尤其对于多元非线性方程,求解过程非常繁琐,计算复杂。Excel在回归分析中能避开复杂的计算,把复杂的计算过程交给计算机处理,使用起来非常简单。本文将探讨如何使用Excel进行回归分析。 Excel回归分析过程与方法介绍 回归分析过程回归分析过程就是通过样本观测数据对模型中的因变量f(x)进行估计,分析随机误差项的分布特征,使用模型进行预测预报,任何一个回归分析过程包括以下内容:() 建立回归模型,确定自变量和因变量,判定因变量f(x)的形式。() 用样本数据来拟合模型中的未知参数。() 确定随机误差项和估计量的分布性质。() 拟合优度检验,验证用X预测Y的拟合程度。() 利用模型进行预测和控制。预测问题是对固定的X值预测相应的Y值,而控制问题可以看成是预测问题的反问题,通过控制X值以便把Y的值控制在指定的范围内。第卷第期年月     武钢职工大学学报JournalofUniversityforStaffandWorkersofWISGCO       VolNoJune采用Excel以取代传统的数学方法,计算(),(),(),(),这正是Excel用以回归分析的方便所在。 回归分析方法介绍Excel为用户提供了二种回归分析工具:“回归”分析工具、工作表函数。 “回归”分析工具使用方法介绍()在Excel工作区单元格中录入样本观测数据。()建立回归模型,确定因变量f(x)的形式。取样本观测数据,录入Excel中的图表向导,选用“XY散点图”,把样本数据连成曲线,观察曲线形状,建立回归方程。()启动“回归分析”分析工具从“工具”菜单中选择“数据分析”子菜单,打开“数据分析”对话框,从中选择“回归”分析工具。如果“工具”菜单中没有“数据分析”,从“工具”中选定“加载宏”,在“加载宏”中选定“分析工具库”即可在“工具”菜单中加载“数据分析”。()填写“回归分析”对话框,如图所示。“回归”对话框()回归结果分析 在回归“对话框中按“确定”按钮,可以得到回归结果。 工作表函数使用说明Excel提供了个函数用于建立回归模型和回归预测,其中个函数用于一元回归分析,个函数用于多元回归分析,这些函数列于表。表 用于回归分析的工作表函数函数名定   义语法格式INTERCEPT一元线性回归模型α的估计值(截距)INTERCFPT(Knowny’s,Knownx’s)SLOPE一元线性回归模型β的估计值(斜率)SLOPE(Knowny’s,Knownx’s)RSQ一元线性回归模型的决定系数(r)SRQ(Knowny’s,Knownx’s)FORECAST依照一元线性回归模型的预测值FORECAST(newx’s,Knowny’s,Knownx’s)STEYX依照一元线性回归模型的标准误差STEYX(Knowny’s,Knownx’s)TREND依照多元线性回归模型的预测值TREND(Knowny’s,Knownx’s,newx’s,const)GROWTH依照多元指数回归模型的预测值GROWTH(Knowny’s,Knownx’s,newx’s,const)LINST估计多元线性回归模型的未知函数LINST(Knowny’s,Knownx’s,const,stats)LOGEST估计多元指数回归模型的未知函数LOGEST(Knowny’s,Knownx’s,const,stats)对以上九个函数说明如下:()所有函数必须要求自变量与因变量的数据个数相同,否则将返回错误值。涂虬:Excel在回归分析中的应用()工作表函数参数分别说明如下:Knowny’s,knownx’s分别表示因变量、自变量的数据区域引用newx’s表示待预测的自变量值。Const:用于指定是否要设定常数为的逻辑值。如果Const设定为TRUE,则常数项将通过计算求得。如果Const设定为FALSE,则常数项将设为,调整回归参数以满足公式y=f(x)。Stats:为逻辑值,用以指出是否返回除回归系数以外的其它统计量值,如果为TRUE,则返回这些值,如果为FALSE,不返回这些值。这些统计量包括:回归系数的标准误差(抽样误差)、决定系数(r)、估计量的标准估计误差(抽样误差)、自由度、回归平方和、残差平方和。以上工作表函数使用非常简单,只要理解各工作表函数定义及语法格式,使用起来非常方便。 Excel“回归”分析工具的使用以下以具体实例讲解如何使用Excel“回归分析”分析工具。 一元线性回归模型一元线性回归方程可表示为y=αβxε,式中α,β是要求解的回归参数,ε是一个随机变量,即因变量Y的随机误差项,它反映了除X变量以外其它变量对Y的影响程度和方式。“回归”分析对话框见图,需要说明的是:自变量X、因变量Y在单元格中必须以单列数据形式输入。例:炼铝厂测得铝硬度X与抗张强度Y的数据如下:xiyi求X对Y的回归方程。解:()工作区单元格中录入数据:xi录入$A$B:$A$单元格,yi录入在$B$$B$单元格。()建立回归模型,确定因变量f(x)的形式。按()中介绍的方法,确定回归方程y=αβxε()启动“回归分析”分析工具,从“工具”菜单中选择“数据分析”子菜单,打开“数据分析”对话框,从中选择“回归”。()填写“回归分析”对话框。在“Y”值输入区域”输入:$B$:$B$,在“X”值输入区域”输入:$A$:$A$。“标志”:本例题未选“置信度”:省略,表示“常数为零:”不强制回归通过原点,可不选。“残差”:如果不需要以残差形式出现,不选此复选框。()分析结果:如下表所示(由于篇幅有限,只给出部分结果):SUMMARYOUTPUT回归统计MuItipleRRSquareAdjustedRSquare标准误差观测值方差分析dfSSMSFSignificanceF回归分析残差总计                     武钢职工大学学报                    ()Coefficients标准误差tStatPvalueLowerUpper下限上限InterceptXVariable回归分析正态分布图从表中“回归统计”可以看到R的倍数、R的平方、调整后的R平方、标准误差。从“方差分析”可以看到残差的方差、平均残差的方差以及F下的显著水平。回归分析出的截距α=  斜率β= 则回归方程为y=xε 多元线性回归方程多元线性回归方程可表示为y=αβxβx⋯βnxn⋯ε,式中α,β,β,βn是要求解的回归参数,ε意义同上。同样地,自变量X、因变量Y在单元格中必须以单列数据形式输入。例 铝合金化学铣切工艺中,为便于操作,需要对腐蚀速度进行控制,因此要考察腐蚀液温度X(C)、碱浓度X(gc)、腐蚀液含铝量x(g)对腐蚀速度Y(mmmin)的影响,一共做了次,所得数据如下:XiXiXiyiXiXiXiyiXiyi求y对x,x,x的线性回归方程。解:()在工作区单元格中录入数据(x,x,x值分别输入在$A$:$A$,$B$:$B$,$C$:$C$yi值输入在$D$:$D$)。(注意该区域必须由单列数据组成)()建立回归模型y=αβxβxβxε()填写如图所示“回归分析”对话框在“Y值输入区域”输入:$D$:$D$在“X值输入区域”输入:$A$:$C$涂虬:Excel在回归分析中的应用其余同图例()结果分析:如下表所示(由于篇幅有限,只给出部分结果)SUMMARYOUTPUT回归统计MuItipleRRSquareAdjustedRSquare标准误差观测值方差分析dfSSMSFSignificanceF回归分析E残差EE总计Coefficients标准误差tStatPvalueLowerUpper下限上限InterceptEXVariableEEXVariableEEXVariableEEEEEE回归分析正态分布图截距α=  ,x的斜率β=,x的斜率β=,x的斜率β=×回归方程为y=xx×xε 非线性回归方程回归模型是非线性回归方程,其方程形式不定,大体上可分为两类,一类是可化为一元线性方程的非线性回归方程,如冥函数y=αxβε,双曲线方程y=αβxε,指数曲线方程y=αβxε,对数曲线y=αβlnxε。另一类是可以化为多元线性方程的非线性回归方程,如多项方程y=αβxβx⋯βnxnε,以上各式中α,β,β,βn是要求解的回归参数。以下以多项式方程为例。例:某种半成品在生产过程中废品率Y与它所含的某种化学成份X有关,现将试验得到的数据记录如下:xy求回归方程                     武钢职工大学学报                    ()解:()在工作单元格录入数据()按()中介绍的方法,画出散点图形状,可以考虑拟合抛物线。设确定的回归方程为y=αβxβxε。()把多项式方程化为多元线性回归方程,令x=x,x=x,则回归方程为y=αβxβxεxxY()在工作表单元格中录入数据:X值输入在单元格$A$:$A$,X值输入在单元格$B$:$B$,Y值输入在$C$:$C$()填写如图所示“回归分析”对话框在“X值输入区域”输入:$A$:$B$,在“Y值输入区域”输入$C$:$C$其余同例。()结果分析如下:截距b=,x的斜率β=,x的斜率β=,回归方程为:y=xxε 用Excel工作表进行回归分析 FORECAST的函数:一元线性回归模型预测值函数根据给定的数据计算或预测未来值。此测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后,返回基于x的线性回归预测值y=αβxε。其语法格式见表。例:见例,试推算铝硬度为时的抗张强度。解:()在工作单元格式中录入$B$:$K$中录入铝硬度xi数值在单元格$B$:$K$中录入抗张强度yi数值。()在单元格$C$内键入“=FORECAST(,B:K,B:K)”()输出结果:单击鼠标左键,即可得到结果为表示铝硬度为时的抗张强度为。也可以采用菜单,在“插入”菜单中选择“函数”命令中的“统计”选项的“FORECAST函数”,出现FORECAST函数对话框,填入相应的内容即可。 TREND函数:多元性线回归模型预测值函数返回一条线性回归拟合线的一组纵坐标值(Y值),即用最小二乘法找出最适合给定的数组Knowny’s,Knownx’s的直线,并返回指定数值组newx’s值在直线上对应的Y值。其语法格式见表。使用TREND函数可以计算同一变量的不同乘方的回归值来拟合多项曲线。例如,假设A列包括Y值,B列包含有X值。可以在C列输入x,在D列输入x等等,然后根据B列到D列对A列进行回归计算。当在参数中输入Knownx’s这样的数组常数时,可以用逗号分开同一列中数值,用分号分开数值行。例:见例,试推算铝硬度分别为,,的张抗力。解:在单元格中$B$:$K$范围内键入铝的硬度。在单元格中$B$:$K$范围内键入铝的抗张力。涂虬:Excel在回归分析中的应用在单元格中$B$:$K$范围内键入铝的硬度(,,)启动TREND函数,在Knowny’s,Knownx’s,newx’s的文本框内键入相应的数据小数点。在Const的文本框内键入“TRUE”,按CTRLSHIFTENTER键。计算结果为,,,表示铝硬度分别为,,时的抗张力为,,。 LOGEST函数:在回归分析中,计算最符合观测数据数组的指数回归曲线方程y=αβxε或y=αβxβx⋯βnxnε(如果有多个X值),并返回描述该曲线的数组。LOGEST是数组函数,以数组表格的形式返回参数估计结果,结果表的形式如下:系数估计值βnβn⋯ββb系数估计值的抽样误差sensen⋯sesese回归模型性质r决定系数sey估计值y的抽样误差方差分析表F统计量ssreg回归平方和ssresid残差平方和语法格式见表是否返回附加的回归统计,由逻辑值stats来判定,如果Stats设为TRUE,函数LOGEST将返回附加的指数曲线回归统计值,返回的数组为(βn,βn⋯,β,bsen,sen,⋯se,seb,r,sey,F,df,ssreg,ssresid)。如果Stats设为FALSE或省略,则LOGEST只返回系数βn,βn,⋯,β和常数项α。需要说明的是:当需要输入一个数组常数,如Knownx’s作为参数时,以逗号作为同一行中数据的分隔符,以分号作为不同行数据的分隔符。例:经过个月的经济不景气,某公司发现在市场上推出新产品后,销售呈指数增长,此后个月中,每个月的销售量分别增加为,,,。,单位。试建立指数回归模型。解:将这些数据存入一个命名为“销售量”的区域内。在选定的区域的第个单元(如DI单元)输入数据公式。“=LOGEST(销售量,(),TRUE,TURE,输入结果为:        P   由此建立起指数回归方程为:y=x以上介绍了LINST,LOGEST,GOWTH工作表函数的使用,三个函数区别是:LINEST,LOGEST都返回一组描述数值的相互关系的数值数组,但函数LINEST是用直线来拟回数据的,而函数LOGEST是用指数曲线来拟合数据,而且LOGEST所返回的额外的回归统计值是基于下面的线性模型计算得出的:Iny=Xlnβ⋯xnlnβnlnα GROWTH函数:指数预测值函数根据给定的数据预测指数增长值。根据已知的X值和Y值,函数GROWTH返回一组新的X值对应的Y,以使GROWTH工作表函数来拟合满足给定X值和Y值的指定的曲线y=αβxε。语法格式见表                     武钢职工大学学报                    ()对于返回结果为数组的公式,在选定适当数目的单元格后,必须以数组公式的形式输入。数组公式可以同时进行多重计算并返回一种或多种结果。生成数值公式的方法与生成基本的单值公式相同。先选定要用来包含公式的一个或多个单元格,输入公式内容,再按CTRLSHIFTENTER组合键完成公式输入。当为参数输入数组常数时,应当使用逗号分开同一行中的数据,用分号分开不同行的数据。例:采用与LOGEST示例中相同的数据,第到第月的销售量分别为,,,,,。预测第,个月的销售量。解:将第到第月的销售输入到名为“UNTFOLD”的单元格中。然后,选定某单元格输入“=GROWTH(UNITSOLD,)”。输出结果为(),表示第月和第月的销售量分别为,个单位。 LINST函数:计算线性回归方程参数的函数使用最小二乘法计算对已知数据进行最佳直线拟合,直线方程为:y=αβxε,或y=αβxβx⋯βnxnε(如果X值是多重的),并返回描述此直线的数组。LINST返回的数值组是(βn,βn,⋯β,α),因此此函数返回数组值,故必须以数组公式的形式输入。语法格式见表函数LINEST还可返回附加回归统计值,由语法格式(表)中的Stats逻辑值来判断。如果Stats为TRUE,则函数LINST返回附加回归统计值,这时返回的数组为(βn,βn,⋯,β,bsen,sen,⋯,se,sebF,dfssreg,ssresid)。如果Stats为FALSE或省略,函数LINST只返回系数βn,βn,⋯,β和常数项α。回归统计结果表的形式与LOGEST的回归统计结果表的形式相同。例:一个商务开发者正考虑在已有商业区中购买一群小办公楼。根据以下变量来估计给定地区内的办公楼的价值Y办公楼的评估值,反映该地区办公楼的价值。X以平方英尺计算的楼层面积X办公室数目X进入办公室的人口数目X办公楼已经使用的年限开发者从个办公楼中,随机抽取个办公楼作为样本,获取一组经验数据列于表。解:在工作中单元格$A$:$E$中录入数据,见上表。在$A$单元格中键入“=LINST(E:E,A:D,TRUE,TRUE)返回结果列在表后部,因此得到一个多元回归模型:Y=XXXX在返回的结果中,决定系数r=,显示出自变量与销售价格之间有很强的关系。进一步可以用F统计量来检验这种关系是否可靠。如果取临界水平为α=,个自由度为:v=K=  v=n(k)=()=其中,k为回归分析中自变量个数,n为样本点个数,查表得F临界值为,说明回归模型是可用的。以上是用LINST函数拟回多元线性回归方程,至于一元线性回归方程,则更简单,这里不再举例。 其它工作表函数的使用介绍其它工作表函数(见表)INTERCEPT,SLOPE,RSQ,STEYX,只要理解其语法格式,在掌握本文前面介绍的内容基础上,使用起来非常简单。在此不再举例。 结束语回归分析方法是数理统计常用的一种方法,在科研生产实践中获得了广泛的应用。但在应用它解决实涂虬:Excel在回归分析中的应用际问题时,传统的回归分析方法求解过程繁琐,计算量大,为其应用带来了困难。本文提出应用EXCEL进行回归分析以取代传统的方法,将复杂的计算过程交给计算机处理,这给回归分析法的应用带来了广阔的应用前景。表 办公楼价值的评估数据ABCDE楼层面积办公室数目人口数目已使用年限评估值$$$$$$$$$$$NANA#NA#NA#NA#NA#NA#NA#NA参 考 文 献 余秉勤编著EXCEL在工程分析中的应用M,武钢职工大学教材科,年月。 邓勃编著分析测试数据的统计处理方法M,清华大学出版社,年月。ApplicationofExcelinRegressionAnalysisTuQiuAbstract:RegressionanalysisisausualmethodusedinmathematicsThispaperintroducestwomethodstomakeregressionanalysis,whichare“reqression”analysistoolandworktablefunctionSomeexamplesareadoptedtoexplainregressionanalysisusedbyExcelKeywords:Excel regressionanalysis worktablefunction testofsignificance                     武钢职工大学学报                    ()

VIP免券下载文档

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/9

excel在回归分析中的应用

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利