首页 用Excel作逐步回归分析

用Excel作逐步回归分析

举报
开通vip

用Excel作逐步回归分析 第 33 卷第 5 期 2011 年 10 月 广 东 气 象 Guangdong Meteorology Vol. 33 No. 5 October 2011 收稿日期:2010 - 05 - 25 作者简介:王飞凤(1978 年生) ,女,助理工程师,主要从事气象防雷工作。 doi:10. 3969 / j. issn. 1007 - 6190. 2011. 05. 015 用 Excel作逐步回归分析 王飞凤,刘铸飘 (始兴县气象局,广东韶关 512500) 摘 要:结合始兴站 1965 ~ ...

用Excel作逐步回归分析
第 33 卷第 5 期 2011 年 10 月 广 东 气 象 Guangdong Meteorology Vol. 33 No. 5 October 2011 收稿日期:2010 - 05 - 25 作者简介:王飞凤(1978 年生) ,女,助理工程师,主要从事气象防雷工作。 doi:10. 3969 / j. issn. 1007 - 6190. 2011. 05. 015 用 Excel作逐步回归 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 王飞凤,刘铸飘 (始兴县气象局,广东韶关 512500) 摘 要:结合始兴站 1965 ~ 1994 年前汛期部分观测要素实况数据对雨量的回归拟合个例,通俗说 明在 Excel软件中进行逐步回归分析的详细过程,为中长期预报中应用逐步回归分析作参考。 关键词:应用气象学;逐步回归; 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ;拟合;Excel软件 中图分类号:P49 文献标识码:B 文章编号:1007 - 6190(2011)05 - 0048 - 04 在各科学试验或管理工作的领域中,常常遇到将科 学数据的逐步回归分析问题,由于其中的一些 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 计算 复杂,在计算过程中需要查统计用 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ,比较后再做出结 论,用起来较麻烦,尤其当自变量个数较多时,计算量惊 人,因此,逐步回归分析方法的广泛应用受到一定的限 制。当前较为常用的统计软件有 SPSS和 SAS等[1],但此 类软件多为英文版,使用起来不太方便、易懂。常用办公 软件 Excel为逐步回归分析方法[2],为数据处理提供了 非常有效的工具。本文就基于 Excel 逐步回归分析方法 加以讨论,并举例说明利用 Excel软件进行逐步回归分析 的详细过程。 1 逐步回归原理与相关 Excel函数 1. 1 逐步回归原理 实际问题中,如果考虑的回归方程有 10 个影响因素 的话,全 部 可 能 得 到 的 回 归 方 程 则 有 ∑ 10 i = 1 Ci10 = 10! i! (10 - 1) != 2 10 - 1 = 1023 个之多[3],但其中只可能有 一个回归方程是“最优”回归方程。怎样去找这个“最 优”回归方程呢?逐步回归分析方法就是要解决这个问 题。传统的方法和在一般的教科书中都是主张从第一个 自变量开始,按自变量对因变量的作用影响程度从大到 小依次引入回归方程,并且考虑到先引入的变量如果由 于后面变量的引入而变得不显著时,则随时将其剔除,以 保证每次在引入新的变量之前,回归方程中只含有显著 的变量,直到没有显著的变量为止,这种方法步骤繁多且 计算量大,比较繁琐。 而采用一种新的方法[4],即先建立所有自变量参加 的回归方程,然后将对因变量影响不显著的自变量即将 没有通过统计量检验的自变量剔除,最后剩下具有高显 著水平的自变量及其回归系数组成的回归方程即为“最 优”回归方程,这种方法与传统的方法的结果在理论上是 一致的。按照这一思路,这种方法可在 Excel软件中轻松 实现。 1. 2 有关多元线性回归的 Excel函数 1. 2. 1 函数 LINEST (1)功能。 使用最小二乘法计算对已知数据(m个自变量)进行 最佳线性拟合,并返回描述此线性方程的自变量回归系 数的数组。因为此函数返回数值数组,故必须以数组公 式的形式输入,所得线性的方程为: y = bm + bm - 1xm - 1 +… + b1x1 + b0 (1) bj 值是与 xj 相对应的系数,b0 是常数,函数 LINEST 返回的数组是{bm,bm - 1,…,b2,b1,b0},函数 LINEST还可 返回附加回归统计值。 (2)语法。 LINEST(known_ys,known_xs,const,stats) const为一逻辑值,指明是否强制使常数 b0 为 0 (零)。如果 const 为 True 或省略,则 b0 将被正常计算; 如果 const 为 False,b0 将被设为 0(零) ,并同时调整 b{bm,bm - 1,…,b2,b1,b0}值以使 y = bx。 stats为一逻辑值,指明是否返回附加回归统计值。 如果 stats为 True,函数 LINEST返回附加回归统计值;如 果 stats为 False或省略,则函数 LINEST只返回系数和常 数项。 (3)线性回归输出。 函数 LINEST返回的附加回归统计值存放位置的格 式如下: bm bm -1 … b2 b1 b0 Sm Sm -1 … S2 S1 S0 R2 SE F f U QL (4)统计值说明。 S1,S2,…,Sm 为系数 b1,b2,…,bm 的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 误差值,这 是很有用的值,用它们可以算出每个自变量系数的 t 检 验值: tj = bj /Sj (j = 1,2,…,m) (2) t检验值的自由度为 f = n - m - 1,由 tj 可以判断变 量 xj 的重要性。 S0 为常数项 b0 的标准误差值。 R2 为相关系数的平方,SE 为剩余标准差。 F值为 F统计值或观察值,f为自由度。 U为回归平方和,QL 为剩余平方和。 1. 2. 2 函数 TREND (1)功能。 返回一条线性回归拟合线的一组纵坐标值(y 值)。 即找到适合已知数组 known_y's和 known_x's的直线(用 最小二乘法) ,并返回指定数组 new_x's 在直线上对应的 y值,即可以按原有的线性趋势预测在新 xm 数据序列上 对应的 y值。 (2)语法。 TREND(known_y’s,known_x’s,new_x’s,const) const为一逻辑值,指明是否强制常数项为 0(零)。 如果 const为 TRUE 或省略,将按正常计算;如果 const为 FALSE,将被设为 0(零) ,值将被调整以使。 (3)说明。 对于返回结果为数组的公式,必须以数组公式的形 式输入。 1. 2. 3 函数 TINV 和 FDIST 函数 TINV 用于返回检验 表中的临界值,测试自变量的统计显著水平,若是,则说 明该自变量统计显著水平高。函数 FDIST可用于返回获 得意外出现的较高 F 值的概率,若 FDIST 函数的返回值 低,则说明回归模型的可信度高。 2 逐步回归步骤与实施 2. 1 逐步回归步骤 (1)根据原始数据由 LINEST 函数得出多元线性回 归结果(包括统计附加值)。 (2)计算各自变量的 t检验 tj 值,在其中找到绝对值 最小的 tmin。 (3)tmin与临界值 tαf比较,如果 | tj | < tαf,剔除 tmin对应 的变量,重新根据余下的自变量数据由 LINEST函数再得 出多元线性回归结果,再返回(2) ,否则进入(4)。 (4)如果 | tj | > tαf,则逐步回归结束,即剩下的自变量 统计显著水平高。 根据上面步骤,可以简单绘出流程图如下: 2. 2 逐步回归的实施 下面通过举例来说明逐步回归在 Excel 软件中是如 何实施的,本例为求取当年前汛期(4 ~ 6 月)总降雨量与 当年相关实况数据的拟合回归模型,求得模型后便可通 过相关统计方法为来年作趋势预测参考[5 - 7]。见图 1 逐 步回归表 1 中 B 列为始兴气象观测站 1965 ~ 1994 年前 汛期设为因变量 y;C 列为历年 4 ~ 6 月平均温度之和设 为自变量 x1;D列为历年 4 ~ 6 月水汽压之和设为自变量 x2;E列为历年 4 ~ 6 月雨量≥0. 1 的雨日之和设为自变 量 x3;F列为历年 4 ~ 6 月日照时数之和设为自变量 x4, 本例就是通过这 4 个因子、30 个样本数采用逐步回归方 法来求得历年前汛期总降雨量“最优”回归方程。 如图 1,在 B2:F31 区域中输入样本数值后,在 B32 单元格输入函数 = LINEST(B2:B31,C2:F31,TRUE, TRUE)并按图 2 设定好 known_y's,known_x's,const,stats 这 4 个参数后(known _ y' s = B2:B31 即为 y 值区域, known_x's = C2:F31 即为所有 x值区域) ,点击确定将在 B32 单元格中得到数值 0. 38(见图 1,保留 2 位小数)。 然后选择区域 B32:F36,按 F2 键返回编辑状态,再 按 Ctrl + Shift + Enter 键,结果就会将函数{= LINEST (B2:B31,C2:F31,TRUE,TRUE) }公式在 B32:F36 区域 以数组公式输入,并在该区域返回多元线性回归附加回 归统计值(见图 1 B32:F36 区域)。 至此便可根据 LINEST 函数返回的附加回归统计值 初步建立所有自变量参加的多元线性回归方程了,即为: y = 0. 38x4 + 13. 86x3 + 68. 78x2 - 83. 80x1 + 651. 91,然而 此方程所有变量的统计显著水平均未经过检验,模型是 不可信的。本例取统计显著性水平 α = 0. 05,则函数 TINV(0. 05,C35)= TINV(0. 05,25) (见图 1)将返回 t检 验表中的临界值 tαf = 2. 059 54(该值亦可查表求得) ,由 公式 2 可以求得 t检验值 | tj(j = 4,3,2,1)|分别为 0. 70、 2. 98、3. 29 和 2. 66,由于 | t4 | = 0. 70 < tαf = 2. 05954,故首 先将日照时数因子 x4 剔除。 将剩下的 3 个因子按以上步骤再进行同样操作可以 得到剩下因子的新的多元线性回归结果。如图 3 所示, 由公式 2 可以求得新的 t 检验值 | tj(j = 3,2,1)|分别为 2. 93、3. 26 和 2. 61,所有剩下的因子的 t检验值 | tj(j = 3, 2,1)|均大于 tαf = TINV(0. 05,C35)= TINV(0. 05,26)= 2. 055 53,则说明所有剩下的因子自变量统计显著水平 高,均可进入“最优”回归方程的回归模型。而 FDIST(F, v1,v2)= FDIST(B35,30 - C35 - 1,C35)= FDIST (16. 95,3,26)= 0. 000 002 6(F 为图 3 中 B35 单元格中 的数值,分子自由度 v1 = n– f– 1,分母自由度 v2 = f,n 为样本数,f为自由度即图 3 中 C35 单元格中的数值) ,这 是一个极小的概率,说明意外出现的较高 F 值的概率很 低即回归模型是完全可信的,所以按本例中的方法求得 的“最优”回归方程为:y = 13. 34x3 + 64. 55x2 - 75. 64x1 + 534. 39。 利用函数 TREND可返回一条线性回归拟合线的一 组纵坐标值(y 值) ,在 F2 单元格中输入公式 = TREND (B2:B31,C2:E31)将得到 1965 年的模拟值 620. 4(见图 3)。 然后选择区域 F2:F31,按 F2 键返回编辑状态,再按 Ctrl + Shift + Enter 键,即可在该区域中得到所有 1965 ~ 1994 年的模拟值。该值亦可通过“最优”回归方程计算 得出,比如1965年逐步回归模拟值为y = 13. 34 × 59 + 94第 5 期 王飞凤等:用 Excel作逐步回归分析 图 1 逐步回归表 1 图 2 LINEST函数使用 64. 55 × 70. 7 - 75. 64 × 69. 6 + 534. 39 = 620. 4,结果是一 样的。最后不难求得模拟值和原始数据之间的距平百分 比(见图 3 中的 G列) ,最大的距平百分比仅为 1980 年的 22%,距平百分比在 ± 15%以内的占了 22 /30,说明模拟 效果还可以,求得的逐步回归方程回归模拟效果还是比 较显著的。 图 4 为后 10 年模拟图,方程计算值为回归方程的计 算结果,从图中看出,距平[8 - 9]20%以内的为 8 /10,但有 2 年模拟效果不理想。因此如若要作中长期预报,则需 选取更具有物理意义的的因子,最好是选择能代表前期 气候特征的气象因子作外推才更具有意义。 05 广东气象 第 33 卷 图 3 逐步回归表 2 图 4 后 10 年模拟图 3 结论 气象领域中在制作中长期天气预报时经常要用到逐 步回归分析,本文较为详细地介绍逐步回归在 Excel软件 中是如何实现的。应用此方法,即使所选因子更多,样本 数更大,按此方法作逐步回归分析也能方便快捷地找出 “最优”回归方程,因此在不会编程的情况下,这不失为 一个较好的方法。 参考文献: [1]李明华,崔少萍,罗凤明,等.统计软件 SPSS在气象中的应用 [J].广东气象,2007,29(1) :50 - 52. [2]左利芳,仇财兴. Excel中的常用分布函数及其在气候统计中 的应用[J].广西气象,2002,28(2) :27 - 28. [3]黄嘉佑.气象统计分析与预报方法[M]. 3 版,北京:气象出版 社,2004. [4]施能.气象科研与预报中的多元分析方法[M]. 2 版,北京:气 象出版社,2002. [5]彭端,黄天文,郭媚媚,等.用逐步回归模型预测肇庆市汛期 降水[J].广东气象,2005,27(2) :16 - 17. [6]陈慧娴,黄露菁,陈创买.用逐步回归方法预报番禺年降水量 [J].广东气象,2004,26(4) :7 - 9. [7]黄彦彬,李天富,李春鸾,等. 2004 年春夏海南火箭人工增雨 效果检验[J].广东气象,2006,28(1) :50 - 53. [8]莫荣耀.用逐步回归预报方程作冬季最低气温及≤5℃低温 的二级判别预报[J].广东气象,2001,23(4) :17 - 18. [9]李丽.用深层地温资料建立多元非线性回归方程预报韶关站 前汛期降水量[J].广东气象,2003,25(2) :8 - 9. 15第 5 期 王飞凤等:用 Excel作逐步回归分析
本文档为【用Excel作逐步回归分析】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
下载需要: 免费 已有0 人下载
最新资料
资料动态
专题动态
is_722727
暂无简介~
格式:pdf
大小:1MB
软件:PDF阅读器
页数:4
分类:
上传时间:2012-12-26
浏览量:156