(完整word版)利用Excel进行线性回归分析实例

1、1利用Excel2000进行一元线性回归分析第一步,录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明.录入结果见下列图图1.ABC1D|1年份最大积雪深度式米灌溉面积y千田»2197115.22&63F197210.419.34r197321.240.5518.635.66r197526.4婴Q1197623.4458197713.529.29197815.734,11019792446.7111198019.137Y图1第二步,作散点图如图2所示,选中数据包括自变量和因变量,点击“图表向导图标；或者在“插入菜单中翻开“图表H.图表向导的图标为曲.选中数据后,数

2、据变为蓝色图2.图2点击“图表向导以后,弹出如下对话框图3):图表向导-4步骤之I-图表类型标推美罂|自定义类型困蓑类型.：拉F不放可苴看例如W子图表类型：取消|上学|"下一步I片成|EJ雷曲气的m面泡组图3环比mglE形搭域图柱条折饵Mr底3昌.遨T:图3在左边一栏中选中“XY散点图,点击“完成按钮,立即出现散点图的原始形式图4:灌溉面积y千亩灌溉面积y千亩第三步,回归观察散点图,判断点列分布是否具有线性趋势.只有当数据具有线性分布特征时,才能采用线性回归分析方法.从图中可以看出,本例数据具有线性分布趋势,可以进行线性回归.回归的步骤如下：首先,翻开“工具下拉菜单,可见数据分析选项

3、见图5:图5用鼠标双击“数据分析选项,弹出“数据分析对话框图6:图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.Li11UI!iAK¥OtTTPUT£li-E

4、lBW4JLiltipleR|0l8EM16S2(jquait|Pl91的4鼻RSquare0.叮631d1_际漱误差L«.BBM£掰测】QLd.方差分析最大枳臂深度“米.LirrFit由8至4口茴帼20g00102030祀大根三探度*冰:灌就而肺汗匐-花沼串港什讯,千亩r计£常京df空F挚gn田3=FL1宜自“2748.E542讦1.3产5.42032E-08316.106762.OLSS459FM.蜘167ucf£iciEA标准退整tSlatf13加Lw口35%UdEiXF限部.5二限嚣.5i17Itiler-BE,|.tL.L64.iJ1.8E7C

5、761.2E?Lt71.2337&：1.8l：G：4246.57153-l.K-SCS6.57K3LS最大科亏深噎一米1.CL2?2；ftQ9400219.285835.42E'QE1,59GL51IT962.0296911.5XL!：L_2.lUL192d21欧亚WMJALCJT?UT23羽状?节*浙H帜影羊C3Jg9181-：t3l2262?l.31082-L引吃产3和.79O36-0.?丁海28436,0767T-0.47577鬻EE(l.If55T.31755c_由7TQ7CIAGEnfl凸.J.H*卜*飞回!要与卬ZUhectl三绪甲"三*产回/尸网土S/S

6、hEe-tS/图9线性回归结果最后,读取回归结果如下:截距：a2.356；斜率：b1.813；相关系数：R0.989；测定系数：R20.979;F值：F371.945.建立回归模型,并对结果进行检验模型为：?2.3561.813x至于检验,R、R2和F值可以直接从回归结果中读出.实际上,R0.9894160.632R0.05b,检验通过.有了R值,F值和t值均可计算出来.F值的计算公式和结果为:R20.9894162(1R2)12(10.989416)1011371.9455.32F0.05,8显然与表中的结果一样.t值的计算公式和结果为:t10.9794160.97941619.2862.3

7、06t0.05,8回归结果中给出了残差10图10,据此可以计算标准离差.首先求残差的平方n10(yiW)2,然后求残差平方和Si21.724i10.17416.107,于是标准离差为16.10781.419于是观测值瞿溉面积y残差残差平方129.91284-1.31283811.723544标准离差s-221.21082-1.9108173.6512221.418923905340.79036-0.29036450.084312P436.07677-0.47676970.22T309遇的均值r550.21755-1.317554L7359490.038842702n_644.778790.22

8、1209160.04E933726.830872.36912775.512766-832.63222L467780292.1543丫9g45,66540,833456520.694651036.983230.416769730.173697残差平方和16.106762.013345sy1.41936.530.03881015%0.10.15图10y的预测值及其相应的残差等进而,可以计算DW值参见图11,计算公式及结果为(iil)2DW2i10.05,k1,n10_22(1.9111.313)2(0.4170.833)2(1.313)2(1.911)20.41720.7511.29残差1-9残差

9、2-10残差之差残差之差的平方-L312838-L910817-0.5979788890.357578752-1.910817-0.2903651.6204525012.625866307-0.29U365-0.47677-0.1864052320.03474691-0.4T6T7-L311554-0.8407843050.706918248-1.3175540.22120921.5387631942.3677921680.221Z09Z2.36312772.14T9185414.6135540592.36912771.4677803-0.9013474070.8124271491.46778030.8334565T.6343237730.4023666490.83345&B0.4167697-0.41&6867830.173627875DW值S416T69T残差之差的平方和12.094878120.750919(显然v10118),查表得dl0.94,du显然,DW=0.751dl0.94,可见有序列正相关,预测的结果令人疑心图11利用残差计算DW直最后给出利用Excel快速估计模型的方法：用鼠标指向图4中的数据点列,单击右键,出现如下选择菜单图12:灌溉面积y