1用Excel作一元线性回归分析

1、实验四1用Excel作一元线性回归分析实验名称:回归分析实验目的：学会应用软件实验一元线性回归，多元线性回归与非线性回归模型的求解 及应用模型解决相应地理问题。1。1利用Excel进行一元线性回归分析ABJC1年倚最大积雪深度製氷灌漑面积千田2197115. 228. 63197210. 419. 34197321.240. 5S197413. 635. 66197526. 4也97197623. 4458197713. 529.29197816. 734. 1101979244G. 711198019.137. 4第一步,录入数据以连续10年最大积雪深度与灌溉面积关系数据为例予以说明。录入结

2、果见以下图图图1第二步，作散点图如图2所示,选中数据包括自变量与因变量，点击“图表向导图标；或者在“插入菜 单中翻开“图表H。图表向导的图标为门！。选中数据后,数据变为蓝色图 2office2003。插入-图表office2007Bl司最大积書深度讥氷AB|CD 11年份最大积雪深度疏米權溉面积y 千田12匚 197115.228.63r 197210. 419.34L 19732L2也55197418.60661197526.44S. 97 197623,4458197713.529.29匚 197316.734. 11019792446. 711198019.137- 4.图義苛导- Q步

3、骤之1 -圉表类型割文件編辑視團插入格式迎工具数据匹|窗口®I。君口昌® a电倉卜 直£凄剋訂验匹图2点击“图表向导以后,弹出如下对话框图3:U2S1标堆类型|目定义类型酿类型Q：孑團表凳型型:散点團.出较成对的数值形我蟻圉 柱案掠饼鬻IME岀la吐潼Igl:Egl團 轿达面泡 圃雷腔气按下不股可直着於例.取消| V上一步下一步 |完成|图3在左边一栏中选中“ XY散点图，点击“完成按钮，立即出现散点图的原始形式图灌溉面积y千亩图4第三步，回归观察散点图，判断点列分布就是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以瞧出，本例数

4、据具有线性分布趋势，可以进行线性回归。回归的步骤如下：首先，翻开“工具下拉菜单,可见数据分析选项见图5office2003。数据-数 据分析office2007bx t灌漑秒拼写FT:自动保存迪:宋体英卓工作簿Qp. 保护电麼机协作diE III规划求辭世自定虫©选顶©商导但卜1斷据分析®更新加载宏聘接1.V3. V »!图5用鼠标双击“数据分析选项，弹出“数据分析对话框图6:图6然后,选择“回归，确定，弹出如下选项表图7：图7进行如下选择:X、Y值的输入区域B1:B11,C1:C11,标志,置信度95%,新工作表组，残 差,线性拟合图图8-1。或者:X

5、、Y值的输入区域B2:B11,C2:C11,置信度95%,新工作表组，残差，线性拟合图 图 8-2。注意:选中数据“标志"与不选“标志",X、Y值的输入区域就是不一样的：前者包括数据标志：最大积雪深度x米灌溉面积y千亩 后者不包括。这一点务请注意图8。图8-1包括数据“标志图8-2不包括数据“标志再后，确定，取得回归结果图9。12546&7E匸匚IF.丁旦归统计Kultit-leRiJ射詞卩R Sam-sreI榔8訓JMjuftc 1 R竺D 0. .3712杠往逞琴1狎舒勇M M c曰 as-Ir-l3Dt： H.- F ."*勺灯-电E沖間屮时1>

6、;杵ta ;i 却Eq=才1口J 足 ill左613(301殆门-s. p :IllT =A0CI DE |FG HI1 J9dfSSnsFSlfinlfi-a'lCE F10H '>?1 740. 3524T41 涮!I37L jflEJE. C：l?2E-£ 3nB1&.10E7604129肚 2 9&1n14t StuPvilijrLcer 9橄Lt per Z：EF-汽皿上題霭川15Lee址小2- 5W4岡1.82TS7W01.0.说：-L-H翻河；44.67LS31.必Y啊>-.5 戈:10工人或皆兌u.川勺H叫匸n .4：，.曲

7、:5.-0：1 5W1507962.哄11沁肛'W'?rRfETW. WTT13呱fl -n1912.91284 -I. 3I2S3E12C7 2L.21Jr.i -I.SlU-fIT2L0英 TH蚀 452243fiB0T6H -orcrsr2?550.21TS5 -1.3175：；6&.2212021625726.3:3087 1969U7T26992,63222 1.467730292?g心胡空M U-鬧;：茕.】2S10SLS833M0i41CrMT3129M| | 4v1、回Ois社/MWHSnan3/J餐谢电，k Cj 口ii* no西圍刍瞌E ,1 *图9

8、线性回归结果最后,读取回归结果如下：截距：a 2.356;斜率:b 1.813;相关系数：R 0.989;测定系数：R2 0.979 ；F值:F 371.945 ;t值：t 19.286 ;标准离差(标准误差):s 1.419 ;回归平方与:SSr 748.854;剩余平方与：SSe 16.107;y的误差平方与即总平方与：SSt 764.961。建立回归模型，并对结果进行检验模型为：？2.356 1.813x至于检验,R、R2、F值、t值等均可以直接从回归结果中读出。实际 上, R 0.989416 0.632 R0.05,8,检验通过。有了 R值,F值与t值均可计算出来。F值的 计算公式与

9、结果为：程一 371.9455.321 2(10.9894162)10 1 1F 0.05,8R21(1 R2)n k 1显然与表中的结果一样。t值的计算公式与结果为：0.979416 19.286 2.3061 0.97941610 1 110.05,8R1 R2n k 1差为回归结果中给出了残差(图10),据此可以计算标准离差。10i21.724(Yi ?)2,然后求残差平方与S0.174首先求残差的平方16.107，于就是标准离(yi ?)21S 攸071.419v .8于就是DW取 0.05 , k然,DW=Q 751n(ii1)2i 2n2i1n 10(显然 v 10 1i1,(1.

10、911 1.313)2(1.313)2(1.911)2(0-417 °833)20.7510.4172残差1-9残差2 一 10残差之差残差之差的平方-1. 312838-1. 910817-0. 5979788890. 357578752-L 910817-0. 2903651.6204525012. 625866307-0.290365-Q, 47677-0.1864052320. 03474691Ch 47677-L 317554-0. 3407843050. 706918248-1.3175540. 22120921.5387631942. 3677921680.221209

11、22. 36912772.1479185414. 6135540592.36912771. 4677803-0. 9013474070. 8124271491.46778030. 8334565-0.6343237730. 4023666490.83345650. 4167697-0.4166867S30. 173627875DW值丄 4167697残差之差的平方和12. 094878120. 750919dl18 ,查表得 dl 0.94 , du 1.29。,预测的结果令人疑心。0.94,可见有序列正相关图11利用残差计算 DW值DW取值范围0 vDW<4、其统计学意义： 当DW值愈

12、接近2时,残差项间愈无相关。 当DW值愈接近0时，残差项间正相关愈强。 当DW值愈接近4时，残差项间负相关愈强。观测值瞿溉面积y残差【残差平方129. 91284-1. 31283811.723544标淮离差。221.21082-1. 9108173. 6512221, 418923905340. 79036-0. 29036450- 084312P4-0.767697Q.227309略的均值521755-1.317554k7359490. 038842702644. 778790. 221209160.04893372反£30872. 36912775.612766832. 632

13、221. 467780252.154379945. 866540. 833456520.694651036. 83230. 416769730-17367残差平方和16.106762. 013345s 1.419y 36.530.038810 15%0.1 0.15图10 y的预测值及其相应的残差等进而，可以计算DW值参见图11,计算公式及结果为?最后给出利用Excel快速估计模型的方法：用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单图12:權溉面积y干西图12点击“添加趋势线R ,弹出如下选择框图13:图13在“分析类型中选择“线性 L ,然后翻开选项单图14：U2S1 类型 选顶|

14、图14 在选择框中选中“显示公式E 与“显示R平方值R 如图14,确定,立即得到回 归结果如下图15:图15在图15中，给出了回归模型与相应的测定系数即拟合优度。?顺便说明残差分析：如果在图8中选中“残差图D，那么可以自动生成残差图图 12。X Variable 1 Residual Plot21差0残0-1-2-31 1* 1 1 051015202530 «X Variable 1图16回归分析原那么上要求残差分布就是无趋势的，如果在图中添加趋势线，那么趋势线应该就是与X 轴平行的，且测定系数很小。事实上，添加趋势线的结果如下图17:X Variable 1 Residual P

15、lot1 差0 残0-2-3-1X Variable 1图17 可见残差分布图根本满足回归分析的要求。?预测分析虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不同 时间序列分析应该以时间为自变量。从残差图瞧来，模型的序列似乎并非具有较强的自相关性，因为残差分布相当随机。因此，仍有可能进行预测分析。现在假定：有人在1981年测得最大积雪深度为27、5米，她怎样预测当年的灌溉面积？下面给出Excel2000的操作步骤：在图9所示的回归结果中，复制回归参数包括截距与斜率,然后粘帖到图1所示的原 始数据附近；并将1981年观测的最大积雪深度 27、5写在1980年之后图18。

16、CDEF11年份最丈积雪泱廈聆冏權观面积応千閒计算荫Coefficient s2197115. 22& 6)| 1IlnrtercBpt2. 3564379293197210. 419. 8最大积雪深度珂米L. 8129210654197321, 240_ 5518. 6弧66197326. 44S- 97197623. 4458197713. 5血29197816. 734,11019792446- 711198019. 137. i12L盹.27, 3图18 将光标至于图18所示的D2单元格中,按等于号“=,点击F2单元格对应于截距 a=2、356，按F4键,按加号“ + ,点击F

17、3单元格对应于斜率b=1、812，按F4键, 按乘号“ * ，点击B2单元格对应于自变量X1,于就是得到表达式“ =$F$2+$F$3*B2'图 19,相当于表达式?1a b* X1,回车,立即得到？1 29.9128 ,即1971年灌溉面积的计算值。L AECD 1EF1年份最丈积雪深廣谜灌溉面积y千亩D115.?28.6计算值Co&ff icierit s21971-?F$2+1FP*B2|2, 3564379293197Z10. 419. 3最対只雪深度玖米1.8129210654197321. 2iO. 5513. 635. 66197526. 449. 9719T62

18、3. 445£197713, 529. 291978163L11019血2446. 711193019. 137, i12198127. 51图19 将十字光标标至于 D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下 一拉,各年份的灌溉面积的计算值立即出现,其中1981年对应的D12单元格的52、212即我们所需要的预测数据,即有?152.212千亩图20。L ABCDEI1年份最丈积雪深厦瓦杓灌溉面积y千旬计算值Coef-fi 匚 imrrts2197115. 228. 629. 913Irrt ercept2. 3564379293197210. 419. 321. 211最尢积雪深度珂散1. 8129210654197321. 240. 540. 795197418. 635. 636. 0776L9?52S. 448. 950. 2167L97623.电4544. 7798L97713. 529. 226. S31919781&a