用Excel作逐步回归分析.pdf

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

1、第 33 卷第 5 期2011 年 10 月广东气象Guangdong MeteorologyVol 33No 5October2011收稿日期:2010 05 25作者简介:王飞凤(1978 年生)，女，助理工程师，主要从事气象防雷工作。doi:103969/j issn 1007 6190 201105 015用 Excel 作逐步回归分析王飞凤，刘铸飘(始兴县气象局，广东韶关512500)摘要:结合始兴站 1965 1994 年前汛期部分观测要素实况数据对雨量的回归拟合个例，通俗说明在 Excel 软件中进行逐步回归分析的详细过程，为中长期预报中应用逐步回归分析作参考。关键词:应用气象学

2、;逐步回归;方法;拟合;Excel 软件中图分类号:P49文献标识码:B文章编号:1007 6190(2011)05 0048 04在各科学试验或管理工作的领域中，常常遇到将科学数据的逐步回归分析问题，由于其中的一些内容计算复杂，在计算过程中需要查统计用表，比较后再做出结论，用起来较麻烦，尤其当自变量个数较多时，计算量惊人，因此，逐步回归分析方法的广泛应用受到一定的限制。当前较为常用的统计软件有 SPSS 和 SAS 等1，但此类软件多为英文版，使用起来不太方便、易懂。常用办公软件 Excel 为逐步回归分析方法2，为数据处理提供了非常有效的工具。本文就基于 Excel 逐步回归分析方法加以讨

3、论，并举例说明利用 Excel 软件进行逐步回归分析的详细过程。1逐步回归原理与相关 Excel 函数1 1逐步回归原理实际问题中，如果考虑的回归方程有 10 个影响因素的 话，全 部 可 能 得 到 的 回 归 方 程 则 有 10i=1Ci10=10!i!(10 1)!=2101=1023 个之多3，但其中只可能有一个回归方程是“最优”回归方程。怎样去找这个“最优”回归方程呢?逐步回归分析方法就是要解决这个问题。传统的方法和在一般的教科书中都是主张从第一个自变量开始，按自变量对因变量的作用影响程度从大到小依次引入回归方程，并且考虑到先引入的变量如果由于后面变量的引入而变得不显著时，则随时将

4、其剔除，以保证每次在引入新的变量之前，回归方程中只含有显著的变量，直到没有显著的变量为止，这种方法步骤繁多且计算量大，比较繁琐。而采用一种新的方法4，即先建立所有自变量参加的回归方程，然后将对因变量影响不显著的自变量即将没有通过统计量检验的自变量剔除，最后剩下具有高显著水平的自变量及其回归系数组成的回归方程即为“最优”回归方程，这种方法与传统的方法的结果在理论上是一致的。按照这一思路，这种方法可在 Excel 软件中轻松实现。1 2有关多元线性回归的 Excel 函数1 2 1函数 LINEST(1)功能。使用最小二乘法计算对已知数据(m 个自变量)进行最佳线性拟合，并返回描述此线性方程的自变

5、量回归系数的数组。因为此函数返回数值数组，故必须以数组公式的形式输入，所得线性的方程为: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，b

6、0 值以使 y=bx。stats 为一逻辑值，指明是否返回附加回归统计值。如果 stats 为 True，函数 LINEST 返回附加回归统计值;如果 stats 为 False 或省略，则函数 LINEST 只返回系数和常数项。(3)线性回归输出。函数 LINEST 返回的附加回归统计值存放位置的格式如下:bmbm1b2b1b0SmSm1S2S1S0R2SEFfUQL(4)统计值说明。S1，S2，Sm为系数 b1，b2，bm的标准误差值，这是很有用的值，用它们可以算出每个自变量系数的 t 检验值:tj=bj/Sj(j=1，2，m)(2)t 检验值的自由度为 f=n m 1，由 tj可以判断变

7、量 xj的重要性。S0为常数项 b0的标准误差值。R2为相关系数的平方，SE为剩余标准差。F 值为 F 统计值或观察值，f 为自由度。U 为回归平方和，QL为剩余平方和。1 2 2函数 TREND(1)功能。返回一条线性回归拟合线的一组纵坐标值(y 值)。即找到适合已知数组 known_ys 和 known_xs 的直线(用最小二乘法)，并返回指定数组 new_xs 在直线上对应的y 值，即可以按原有的线性趋势预测在新 xm数据序列上对应的 y 值。(2)语法。TREND(known_y s，known_x s，new_x s，const)const 为一逻辑值，指明是否强制常数项为 0(零)

8、。如果 const 为 TRUE 或省略，将按正常计算;如果 const 为FALSE，将被设为 0(零)，值将被调整以使。(3)说明。对于返回结果为数组的公式，必须以数组公式的形式输入。1 2 3函数 TINV 和 FDIST函数 TINV 用于返回检验表中的临界值，测试自变量的统计显著水平，若是，则说明该自变量统计显著水平高。函数 FDIST 可用于返回获得意外出现的较高 F 值的概率，若 FDIST 函数的返回值低，则说明回归模型的可信度高。2逐步回归步骤与实施2 1逐步回归步骤(1)根据原始数据由 LINEST 函数得出多元线性回归结果(包括统计附加值)。(2)计算各自变量的 t 检验

9、 tj值，在其中找到绝对值最小的 tmin。(3)tmin与临界值 tf比较，如果|tj|tf，剔除 tmin对应的变量，重新根据余下的自变量数据由 LINEST 函数再得出多元线性回归结果，再返回(2)，否则进入(4)。(4)如果|tj|tf，则逐步回归结束，即剩下的自变量统计显著水平高。根据上面步骤，可以简单绘出流程图如下:2 2逐步回归的实施下面通过举例来说明逐步回归在 Excel 软件中是如何实施的，本例为求取当年前汛期(4 6 月)总降雨量与当年相关实况数据的拟合回归模型，求得模型后便可通过相关统计方法为来年作趋势预测参考5 7。见图 1 逐步回归表 1 中 B 列为始兴气象观测站

10、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_ys，known_xs，const，stats这 4 个参数后(

11、known_y s=B2:B31 即为 y 值区域，known_xs=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.38x

12、4+13.86x3+68.78x2 83.80 x1+651.91，然而此方程所有变量的统计显著水平均未经过检验，模型是不可信的。本例取统计显著性水平 =0.05，则函数TINV(0.05，C35)=TINV(0.05，25)(见图 1)将返回 t 检验表中的临界值 tf=2.059 54(该值亦可查表求得)，由公式 2 可以求得 t 检验值|tj(j=4，3，2，1)|分别为 0.70、2.98、3.29 和 2.66，由于|t4|=0.70 tf=2.05954，故首先将日照时数因子 x4剔除。将剩下的 3 个因子按以上步骤再进行同样操作可以得到剩下因子的新的多元线性回归结果。如图 3 所

13、示，由公式 2 可以求得新的 t 检验值|tj(j=3，2，1)|分别为2.93、3.26 和 2.61，所有剩下的因子的 t 检验值|tj(j=3，2，1)|均大于 tf=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 中

14、 C35 单元格中的数值)，这是一个极小的概率，说明意外出现的较高 F 值的概率很低即回归模型是完全可信的，所以按本例中的方法求得的“最优”回归方程为:y=13.34x3+64.55x275.64x1+534.39。利用函数 TREND 可返回一条线性回归拟合线的一组纵坐标值(y 值)，在 F2 单元格中输入公式=TREND(B2:B31，C2:E31)将得到 1965 年的模拟值 620.4(见图3)。然后选择区域 F2:F31，按 F2 键返回编辑状态，再按Ctrl+Shift+Enter 键，即可在该区域中得到所有 1965 1994 年的模拟值。该值亦可通过“最优”回归方程计算得出，比

15、如1965年逐步回归模拟值为y=13.34 59+94第 5 期王飞凤等:用 Excel 作逐步回归分析图 1逐步回归表 1图 2LINEST 函数使用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 年模拟效果不理想。因此

16、如若要作中长期预报，则需选取更具有物理意义的的因子，最好是选择能代表前期气候特征的气象因子作外推才更具有意义。05广东气象第 33 卷图 3逐步回归表 2图 4后 10 年模拟图3结论气象领域中在制作中长期天气预报时经常要用到逐步回归分析，本文较为详细地介绍逐步回归在 Excel 软件中是如何实现的。应用此方法，即使所选因子更多，样本数更大，按此方法作逐步回归分析也能方便快捷地找出“最优”回归方程，因此在不会编程的情况下，这不失为一个较好的方法。参考文献:1 李明华，崔少萍，罗凤明，等 统计软件 SPSS 在气象中的应用J 广东气象，2007，29(1):50 52 2 左利芳，仇财兴 Exc

17、el 中的常用分布函数及其在气候统计中的应用 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 915第 5 期王飞凤等:用 Excel 作逐步回归分析