利用Excel进行线性回归分析实例

1、如有帮助，欢迎下载。 1利用Excel2000进行一元线性回归分析 第一步，录入数据 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图 1）。 A B cr 1 年份 最大积雪深度或氷） 灌溉面积y（干眄 2 1971 15.2 28. 6 g 1972 10. 4 19. 3 4 1973 21.2 40. 5ZZ 5 1974 18. 6 35. 6 6 r 1975 26.4 48.0n 1 1976 23. 4 Q5 8 1977 13.5 29. 2 9 1978 15. 7 34, 1 10 1979 24 46.7| 11 1980 19. 1 37.

2、q 图1 第二步，作散点图 如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入”菜单中打开“图表（H）”。图表向导的图标为 I和。选中数据后，数据变为蓝 色（图2）。 图2 点击“图表向导”以后，弹出如下对话框（图3）: 9 图表简导- Q步骤之1 -圉表类型 标唯尖型II自定义裘型 圉複类型: 孑團表宴型: 阳国0 世形线EJ 狂条折常 曬r空 0吐!?; EJI)gm 5环达ffi泡 EJ咅曲气 空阳 敌点图.出较成对的葩信 取消 按下不放可萱看示例I 上T（0|下TI 恶I 在左边一栏中选中“ XY散点图 （图 4）: 图3 点击“完成”按钮，立即出现散点图

3、的原始形式 灌溉面积y（千亩） *灌溉面积y（千亩） 图4 第三步，回归 观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性 回归。回归的步骤如下： 首先，打开“工具”下拉菜单，可见数据分析选项（见图5）： 和灌溉面积的数据b 式Q） 工具数据）窗口观帮助 4常 尊拼写FT 2自动画W :宋体 灌溉 共亨工作薄. 保护 联机协作（2） E 迪 自定乂 ） 选项- 向导 1 数据分析 更新加我宏卷接（L）- 睪 图5 用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图 6） 数据分析 分析

4、工員匾I 方 主分 均煖百 平验叶圉平数与 ii 图6 然后，选择“回归”，确定，弹出如下选项表: 图7 进行如下选择：X、Y值的输入区域（B1:B11 , C1:C11 ），标志，置信度（95%）, 新工作表组，残差，线性拟合图。 或者：X、Y值的输入区域（B2:B11，C2:C11 ），置信度（95%），新工作表组，残 差，线性拟合图。 注意：选中数据“标志和不选“标志，X、Y值的输入区域是不一样的：前者包 括数据标志： 最大积雪深度x（米）灌溉面积y（千亩） 后者不包括。这一点务请注意。 图8-1包括数据“标志” 图8-2 不包括数据“标志” 再后，确定，取得回归结果（图9）。 A E

5、c D Hf5 H I J 最大积雪祥房讥米】Lirr Fit ft 2 Q w 型统计 q Hui tlple ft C. F 值：F -371.945。 建立回归模型，并对结果进行检验 模型为：?-2.356 1.813x 至于检验，R、R2和F值可以直接从回归结果中读出。实际上， R =0.989416 0.632 =Ro.O5,8，检验通过。有了 R值，F值和t值均可计算出来。F值的 计算公式和结果为： R2 0.9894162 1 n -k -1 2 (1 _R ) 2 E(1989416 ) = 371.9455.32 二 F 0.05,8 显然与表中的结果一样。t值的计算公式和结

6、果为: 1RR2 n - k -1 回归结果中给出了残差（ 0.979416 =,=19.2862.306 = t005 8 1 -0.979416 .10-1-1 图10），据此可以计算标准离差。首先求残差的平方 22、心 i （yi 审），然后求残差平方和 s =二 i吕 i2 =1.7240.174 =16.107，于是标准 离差为 16.107“419 8 于是 s 1 419 0.0388 ：1015% =0.10.15 y 36.53 观测值 瞿漑面积y残差 残差平方 1 29. 91264 -1. 3128381 1.723544 标准离差s 2 21.21082 -1. 910

7、817 3. 651222 L 418923905 3 40. 79036 -0. 2903645 0.084312 P4 36. 07677 -0. 4767697 0.22T309 跆的均值 r5 50.21755 -1.317554 1.735949 0. 038842702 P6 44. 77879 0. 22120916 0. 048933 厂? 26.83087 2. 3691277 5.612766 8 32. 63222 1. 46778029 2.154379 9 45 86654 0. 33345652 0.69 加 5 10 36. 98323 0. 41676973 0

8、.17367 残差平方和 16.10676 2. 013345 图10 y的预测值及其相应的残差等 进而，可以计算DW值（参见图11），计算公式及结果为 显然，DW=0.751： di = 0.94，可见有序列正相关，预测的结果令人怀疑 残差1-9 残差2 - 10 残差之差 残差之差的平方 -L 312838 -L 910817 -0. 597978889 0. 357578752 -1.910817 -0. 20365 1. 620452501 2. 625866307 -0. 29036F -0.47677 -0.186405232 0. 03474691 -0. 4T6T7 -L 31

9、7554 -0. 840784305 0. 706918248 -L 317554 Q. 2212092 1.538763194 2. 367792168 0. K12092 2. 3631277 2.14T918541 4. 613554059 2. 3691277 1. 4677803 -0. 901347407 0. 812427149 1.4677803 6 8334565 -6 &34323773 0. 402366649 0. 83345&5 0. 4167697 -0. 416&86783 0. 173627875 DW值 0. 416697 残差之差的平方和 12. 09487

10、812 6 750919 图11利用残差计算 DW值 最后给出利用Excel快速估计模型的方法： 二(；i - ；i J ) DW 取：=0.05, 2 2 (1.911 1.313)2(0.417 0.833)2 (-1.313)2 (-1.911)20.4172 = 0.751 k = 1 , n =10 (显然 v =10-1-1 = 8 )，查表得 d| = 0.94 , du = 1.29 灌溉面积讥千亩） 图12 用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图 12）： 点击“添加趋势线（R） ”，弹出如下选择框（图13）： 趙玛预測/回归分折类型 钱性 时数0 乘需 指数 选择數据系列: 腥爲面抚賢（千宙） 图13 在“分析