第 33 卷第 5 期 2011 年 10 月 广 东 气 象 Guangdong Meteorology Vol． 33 October No． 5 2011 : doi 10． 3969 / j． issn． 1007 － 6190． 2011． 05． 015 用 Excel 作逐步回归分析 王飞凤，刘铸飘 ( 始兴县气象局，广东韶关 512500 ) 摘 要:结合始兴站 1965 ～ 1994 年前汛期部分观测要素实况数据对雨量的回归拟合个例，通俗说 明在 Excel 软件中进行逐步回归分析的详细过程，为中长期预报中应用逐步回归分析作参考。 关键词:应用气象学; 逐步回归; 方法; 拟合; 文献标识码: B 中图分类号: P49 Excel 软件 文章编号: 1007 － 6190(2011)05 － 0048 － 04 在各科学试验或管理工作的领域中，常常遇到将科 学数据的逐步回归分析问题，由于其中的一些内容计算 复杂，在计算过程中需要查统计用表，比较后再做出结 论，用起来较麻烦，尤其当自变量个数较多时，计算量惊 人，因此，逐步回归分析方法的广泛应用受到一定的限 ］，但此 制。当前较为常用的统计软件有 SPSS 和 SAS 等［ 类软件多为英文版，使用起来不太方便、易懂。常用办公 ］，为数据处理提供了 软件 Excel 为逐步回归分析方法［ 非常有效的工具。本文就基于 Excel 逐步回归分析方法 加以讨论，并举例说明利用 Excel 软件进行逐步回归分析 的详细过程。 1 2 1 逐步回归原理与相关 Excel 函数 1． 1 逐步回归原理 实际问题中，如果考虑的回归方程有 10 个影响因素 的 话，全 部 可 能 得 到 的 回 归 方 程 则 有 ∑ 10 Ci 10 = ］，但其中只可能有 i = 1 ! 10 10 － 1 ! ( ) ! = 210 － 1 = 1023 个之多［ 3 i 一个回归方程 是“最 优”回 归 方 程。怎 样 去 找 这 个“最 优”回归方程呢? 逐步回归分析方法就是要解决这个问 题。传统的方法和在一般的教科书中都是主张从第一个 自变量开始，按自变量对因变量的作用影响程度从大到 小依次引入回归方程，并且考虑到先引入的变量如果由 于后面变量的引入而变得不显著时，则随时将其剔除，以 保证每次在引入新的变量之前，回归方程中只含有显著 的变量，直到没有显著的变量为止，这种方法步骤繁多且 计算量大，比较繁琐。 而采用一种新的方法［ ］，即先建立所有自变量参加 的回归方程，然后将对因变量影响不显著的自变量即将 没有通过统计量检验的自变量剔除，最后剩下具有高显 著水平的自变量及其回归系数组成的回归方程即为“最 优”回归方程，这种方法与传统的方法的结果在理论上是 一致的。按照这一思路，这种方法可在 Excel 软件中轻松 实现。 1． 2 有关多元线性回归的 Excel 函数 1． 2． 1 函数 LINEST 4 ( ) 功能。 1 使用最小二乘法计算对已知数据( m 个自变量) 进行 最佳线性拟合，并返回描述此线性方程的自变量回归系 数的数组。因为此函数返回数值数组，故必须以数组公 式的形式输入，所得线性的方程为: y = bm + bm － 1 xm － 1 + … + b1 x1 + b0 bj ) 1 是常数，函数 LINEST 相对应的系数， 值是与 xj b0 返回的数组是{ ， ， ， } ，函数 LINEST 还可 bm － 1 bm b1 b0 返回附加回归统计值。 ， … ， b2 ( ( ) 语法。 2 ( 零) 如果 const 为 False ， { b b1 ， … bm ， b0 ， b0 ( ， known_xs ， const ， stats ) known_ys LINEST const 为 一 逻 辑 值，指 明 是 否 强 制 使 常 数 b0 。如果 const 为 True 或省略，则 b0 为 0 将被正常计算; ( 零) ，并 同 时 调 整 将 被 设 为 0 } 值以使 y = bx。 ， b2 ， bm － 1 stats 为一逻辑值，指明是否返回附加回归统计值。 ，函数 LINEST 返回附加回归统计值; 如 如果 stats 为 True 果 stats 为 False 或省略，则函数 LINEST 只返回系数和常 数项。 ( 3 函数 LINEST 返回的附加回归统计值存放位置的格 ) 线性回归输出。 式如下: bm bm － 1 … b2 Sm Sm － 1 … S2 R2 SE b1 S1 b0 S0 F f U QL ( ) 统计值说明。 4 ， S2 S1 ， Sm ， … 的标准误差值，这 是很有用的值，用它们可以算出每个自变量系数的 t 检 验值: 为系数 b1 ， bm ， … ， b2 量 xj ( ) 2 可以判断变 ，由 tj ( ， 2 ， … ， m ) j = 1 为常数项 b0 tj = bj / Sj t 检验值的自由度为 f = n － m － 1 的重要性。 S0 R2 为相关系数的平方， SE F 值为 F 统计值或观察值， U 为回归平方和， QL 的标准误差值。 为剩余平方和。 为剩余标准差。 f 为自由度。 收稿日期: 作者简介: 王飞凤( 2010 － 05 － 25 1978 年生) ，女，助理工程师，主要从事气象防雷工作。 

第 5 期 王飞凤等: 用 Excel 作逐步回归分析 94 1． 2． 2 函数 TREND ) 功能。 ( 1 返回一条线性回归拟合线的一组纵坐标值 ( y 值) 。 即找到适合已知数组 known_y's 和 known_x's 的直线( 用 最小二乘法) ，并返回指定数组 new_x' s 在直线上对应的 y 值，即可以按原有的线性趋势预测在新 xm 数据序列上 对应的 y 值。 ) 语法。 known_y’s ， new_x’s ， known_x’s ， TREND const const 为一逻辑值，指明是否强制常数项为 0 ) ( 零) 。 如果 const 为 TRUE 或省略，将按正常计算; 如果 const 为 ，将被设为 0 FALSE ( ) 说明。 3 对于返回结果为数组的公式，必须以数组公式的形 ( 零) ，值将被调整以使。 2 ( ( 明该自变量统计显著水平高。函数 FDIST 可用于返回获 得意外出现的较高 F 值的概率，若 FDIST 函数的返回值 低，则说明回归模型的可信度高。 2 逐步回归步骤与实施 2． 1 逐步回归步骤 ( ) 根据原始数据由 LINEST 函数得出多元线性回 1 归结果( 包括统计附加值) 。 ( 2 ) 计算各自变量的 t 检验 tj 值，在其中找到绝对值 最小的 tmin 。 tmin 3 ) ( 与临界值 tαf 对应 的变量，重新根据余下的自变量数据由 LINEST 函数再得 出多元线性回归结果，再返回( ) ，否则进入( 比较，如果 | tj | ＜ tαf ，剔除 tmin ) 2 4 。 ( ) 如果 | tj | ＞ tαf 4 ，则逐步回归结束，即剩下的自变量 式输入。 1． 2． 3 函数 TINV 和 FDIST 函数 TINV 用于返回检验 表中的临界值，测试自变量的统计显著水平，若是，则说 统计显著水平高。 根据上面步骤，可以简单绘出流程图如下: 2． 2 逐步回归的实施 ; ; ; ; ］ 5 － 7 下面通过举例来说明逐步回归在 Excel 软件中是如 何实施的，本例为求取当年前汛期( 4 ～ 6 月) 总降雨量与 当年相关实况数据的拟合回归模型，求得模型后便可通 过相关统计方法为来年作趋势预测参考［ 。见图 1 逐 步回归表 1 中 B 列为始兴气象观测站 1965 ～ 1994 年前 汛期设为因变量 y C 列为历年 4 ～ 6 月平均温度之和设 为自变量 x1 D 列为历年 4 ～ 6 月水汽压之和设为自变量 E 列为历年 4 ～ 6 月雨量≥0. 1 的雨日之和设为自变 x2 ， F 列为历年 4 ～ 6 月日照时数之和设为自变量 x4 量 x3 本例就是通过这 4 个因子、30 个样本数采用逐步回归方 法来求得历年前汛期总降雨量“最优”回归方程。 ，在 B2 F31 ， const F31 区域中输入样本数值后，在 B32 ， TRUE 单元 格 输 入 函 数 = LINEST TRUE 这 4 个 参 数 后 ( : known_x's = C2 B32 单元格中得到数值 0. 38 F36 : B31 B2 ， ) 并按图 2 设定好 known_y' s ， known_x' s stats : B31 即 为 y 值 区 域， known _ y' s = B2 F31 即为所有 x 值区域) ，点击确定将在 ( 见图 1 。 ，按 F2 键返回编辑状态，再 按 Ctrl + Shift + Enter 键，结 果 就 会 将 函 数 { = LINEST ( ) } 公式在 B32 F36 区域 B2 以数组公式输入，并在该区域返回多元线性回归附加回 归统计值( 见图 1 B32 ，保留 2 位小数) 然后选择区域 B32 F36 区域) ， TRUE ， TRUE 如图 1 ， C2 B31 F31 ， ， C2 。 ( : : : : : : : 至此便可根据 LINEST 函数返回的附加回归统计值 初步建立所有自变量参加的多元线性回归方程了，即为: ，然而 y = 0. 38x4 + 13. 86x3 + 68. 78x2 － 83. 80x1 + 651. 91 此方程所有变量的统计显著水平均未经过检验，模型是 ，则 函 数 不可 信 的。本 例 取 统 计 显 著 性 水 平 α = 0. 05 ( ( ， C35 ) 0. 05 = TINV TINV 0. 05 验表中的临界值 tαf = 2. 059 54 公式 2 可以求得 t 检验值 | tj 2. 98、3. 29 和 2. 66 先将日照时数因子 x4 剔除。 ( ) ( 见图 1 ， ) 将返回 t 检 25 ( 该值亦可查表求得) ，由 | 分别为 0. 70、 ，故首 ， 3 ， 2 ， 1 ) j = 4 ，由于 | t4 | = 0. 70 ＜ tαf = 2. 05954 ) ) ( ) ( ) ) ( ， 2 ， 1 j = 3 0. 05 ， v2 = TINV ， C35 ( ， 26 | 均大于 tαf = TINV 将剩下的 3 个因子按以上步骤再进行同样操作可以 得到剩下因子的新的多元线性回归结果。如图 3 所示， | 分别为 由公式 2 可以求得新的 t 检验值 | tj ， 2. 93、3. 26 和 2. 61 j = 3 ， 2 1 ，所有剩下的因子的 t 检验值 | tj 0. 05 = ，则说明所有剩下的因子自变量统计显著水平 ， 2. 055 53 高，均可进入“最优”回归方程的回归模型。而 FDIST = FDIST v1 ( F 为图 3 中 B35 单元格中 16. 95 ， 的数值，分子自由度 v1 = n – f – 1 n f 为自由度即图 3 中 C35 单元格中的数值) ，这 为样本数， 是一个极小的概率，说明意外出现的较高 F 值的概率很 低即回归模型是完全可信的，所以按本例中的方法求得 的“最优”回归方程为: 534. 39。 y = 13. 34x3 + 64. 55x2 － 75. 64x1 + ，分母自由度 v2 = f = FDIST ， ， 3 26 30 － C35 － 1 = 0. 000 002 6 B35 C35 ， ， F ( ( ) ) ( 利用函数 TREND 可返回一条线性回归拟合线的一 y 值) ，在 F2 单元格中输入公式 = TREND ( 见图 E31 ) 将得到 1965 年的模拟值 620. 4 组纵坐标值( ， ( : C2 B31 : B2 ) 3 。 : F31 然后选择区域 F2 ，按 F2 键返回编辑状态，再按 Ctrl + Shift + Enter 键，即可在该区域中得到所有 1965 ～ 1994 年的模拟值。该值亦可通过“最优”回归方程计算 得出，比如1965 年逐步回归模拟值为 y = 13. 34 × 59 + 

05 广东气象 第 33 卷 图 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 年模拟图，方程计算值为回归方程的计 ，但有 算结果，从图中看出，距平［ 2 年模拟效果不理想。因此如若要作中长期预报，则需 选取更具有物理意义的的因子，最好是选择能代表前期 气候特征的气象因子作外推才更具有意义。 20% 以内的为 8 /10 ］ 8 － 9 

第 5 期 王飞凤等: 用 Excel 作逐步回归分析 15 图 3 逐步回归表 2 图 4 后 10 年模拟图 3 结论 气象领域中在制作中长期天气预报时经常要用到逐 步回归分析，本文较为详细地介绍逐步回归在 Excel 软件 中是如何实现的。应用此方法，即使所选因子更多，样本 数更大，按此方法作逐步回归分析也能方便快捷地找出 “最优”回归方程，因此在不会编程的情况下，这不失为 一个较好的方法。 参考文献: ［ ］李明华，崔少萍，罗凤明，等． 统计软件 SPSS 在气象中的应用 1 ［ ］ ． 广东气象， J ［ ］左利芳，仇财兴． Excel 中的常用分布函数及其在气候统计中 2 ］ 的应用［ ． 广西气象， J 50 － 52． 27 － 28． ， 29 ， 28 2002 2007 ) : ) : 1 2 ( ( 2004． 2002． ， 27 ］ ． 2 版，北京: 气 ］ ． 3 版，北京: 气象出版 ［ ］黄嘉佑． 气象统计分析与预报方法［ M 3 社， ［ ］施能． 气象科研与预报中的多元分析方法［ M 4 象出版社， ［ ］彭端，黄天文，郭媚媚，等． 用逐步回归模型预测肇庆市汛期 5 降水［ ］ ． 广东气象， J ［ ］陈慧娴，黄露菁，陈创买． 用逐步回归方法预报番禺年降水量 6 ［ ］ ． 广东气象， J ［ ］黄彦彬，李天富，李春鸾，等． 2004 年春夏海南火箭人工增雨 7 效果检验［ ］ ． 广东气象， J ［ ］莫荣耀． 用逐步回归预报方程作冬季最低气温及 ≤5℃ 低温 8 的二级判别预报［ ］ ． 广东气象， J ［ ］李丽． 用深层地温资料建立多元非线性回归方程预报韶关站 9 ］ 前汛期降水量［ ． 广东气象， J 50 － 53． 17 － 18． 16 － 17． ， 25 ， 26 ， 23 ， 28 8 － 9． 7 － 9． ( ) : 1 2006 ( ) : 4 2001 ( ) : 2 2003 ( ) : 2 2004 2005 ( ) : 4