利用Excel进行线性回归分析

1、文档内容1. 利用Excel进展一元线性回归分析2. 利用Excel进展多元线性回归分析1. 利用Excel进展一元线性回归分析第一步，录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下列图图 1。ABJCD 11年粉最大积雪浑度或朮）灌漑面积千田）2197115. 228. 63197210. 419. 34197321.240. 5S197413. 635. 66197526. 4也97197623. 4458197713. 529.29197816. 734. 11019792446. 711198019.137. 4图1第二步，作散点图如图2所示，选中数据包括

2、自变量和因变量，点击 图表向导图标；或者在 入'菜单中翻开 图表H。图表向导的图标为 曲“。选中数据后，数据变为蓝色 2。-疸续ID年摄丈积雪滦度和谨陽面親餡數据!筍文件®）鋼论迅）视国®貳人格貳©工具d）数弭匹i奮口H I® (S直甬(fio tt |ft £ A zl51tai EB1=j昜大积雪裸度讥米）ABCJ1年併最丈积雪屏贯三;米灌溉闻积y（千田）2197115. 228. 631972IQ. 40 3吕197321.24CL5519218. 66197E26.448.97197623. 445S197?13.529.231

3、97816. r34.11019792446. 711198019.137.11图2点击图表向导以后，弹出如下对话框图3：图3在左边一栏中选中“ XY散点图，点击完成按钮，立即出现散点图的原始形式图 4：灌溉面积y（千亩）第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进展线性 回归。回归的步骤如下：1. 首先，翻开 工具'下拉菜单，可见数据分析选项 见图5：式0） TACT3数据 裔口遁 帮助亠丫秒拼写FT :宋体屮自咙保存英車工怅簿Qp.E II灌溉保护曠机协作直）规划求解

4、电）自定璽口1选顶）一.商导址）埶据分折叨更劭M载宏卷接1.图5用鼠标双击 数据分析'选项，弹出 数据分析对话框图6：图62. 然后，选择 回归，确定，弹出如下选项表 图7：图7进展如下选择：X、Y值的输入区域B1:B11 , C1:C11，标志，置信度95%， 新工作表组，残差，线性拟合图图8-1。或者：X、Y值的输入区域B2:B11，C2:C11，置信度95%，新工作表组，残 差，线性拟合图图8-2。注意：选中数据 标志"和不选 标志"，X、Y值的输入区域是不一样的：前者包括 数据标志：最大积雪深度x（米）灌溉面积y（千亩）后者不包括。这一点务请注意图8。图8-

5、1包括数据标志图8-2不包括数据标志3.再后，确定，取得回归结果图9。"*勺灯-电嚥古£VI时A杵ta ;i 却Eq=才1口J 足 ill左613(301殆门-s. p :IllT =k0CI DE |FG HI1 J12546&7Er-l3DM M c曰 as-I匸匚IF.丁旦归统计Kultit-leRiJ射詞卩R Sam-sreI榔8訓JMjuftc 1 R竺D 0. .3712杠往逞琴1狎舒勇9dfSSnspSlfinlfi-a'lCE F10H '>?1 740. 3524T41 涮!I37L jflEJE. C：l?2E-£

6、 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：，.曲: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.3

7、175：；6&.2212011625726.3:3087 1969U7T26992,63222 1.467730292?g心胡空M U-鬧;：茕.】2S10SLS833M0i41CrMT329M| | 4v m1、回Ois社/MWHSnan3/J川餐谢电，k Cj 口ii* no西圍”刍瞌E ,1 *图9线性回归结果4. 最后，读取回归结果如下：截距：a 2.356 ;斜率：b 1.813 ;相关系数：R 0.989 ;测定系数： R20.979 ； F 值：F 371.945 ； t 值：t 19.286 ；标准离差标准误差：s 1.419 ；回归平方和： SSr 748.854 ；

8、剩余平方和：SSe 16.107 ； y的误差平方和 即总平方和：SSt 764.961。5. 建立回归模型，并对结果进展检验模型为：?2.356 1.813x至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。实际上， R 0.9894160.632 凤.。5,8，检验通过。有了 R值，F值和t值均可计算出来。F值的计算公式和结果为：R21(1 R2)n k 1显然与表中的结果一样。T值的计算公式和结果为：R1 R20.98941622我"0.989416 )371.9455.32F 0.05,81 0.979416 皿86 2306 t00510 1 1回归结果中给出了残

9、差图10,据此可以计算标准离差。首先求残差的平方 n 10i2(yi ?)2，然后求残差平方和S " 1.7240.174 16.107,于是标准i 1离差为n2？) k 1 i 116-1071.419于是进而,可以计算DW值n(ii 2n2ii 1k 1，n 10参见图11，计算公式及结果为i 1)2DW2(1.911 1.313)(1.313)2( 1.911)22(O.417 0.833)0.7510.41720.05，显然 v 10 1 1 8，查表得 dl0.94，du 1.29。观测值瞿溉面积y残差残差平方129. 91284-1. 31283811.723544杯准离

10、差£ 221. 21082-1. 9108173. 651222L 418923905340. 79036-0, 29036450.084312q36. 07677-0. 47676970. 22T30S胎的均值5g21755-1.3175541.7359490. 03S342702644. 77879(1 221209160. 043933726. 830872. 36912775.612766832. 632221. 467780292.154379945 那6540. 333456520.694651036.驱230. 416769730.17367残差平方和16.106762

11、. 013345sy丽 00388 10 15% o。15图10y的预测值及其相应的残差等显然,dl0.94，可见有序列正相关，预测的结果令人疑心残差1-9残差2 10残差之差残差之差的平方-1.312838-1. 910817-0. 5979788890. 357578752-1. 910E17-0. 2903651.6204&25012. 625866307-0,290365-0, 47677-0.1864052320. 03474631-0+ 47677-1. 317554-0.8407843050. 706918248-1.3175540. 22120921.538763194

12、2. 367792168C 2212Q922. 36912772.1479185414. 6135540592. 36912771. 4677803-0. 9013474070. 812427149L 46778030. 83別565-Q.6343237730. 4023666490.83345650. 4167697-0. 4166867830. 173627875DW值0, 4167697残差之差的平方和12. 094878120. 750919图11利用残差计算 DW值利用Excel快速估计模型的方法:2. 用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单 图12：權溉面积只千亩)

13、图122点击添加趋势线？"，弹出如下选择框图13：图133. 在 分析类型中选择 线性(L)",然后翻开选项单图14：图144. 在选择框中选中 显示公式(E) 和 显示R平方值？ 如图14，确定，立即得到 回归结果如下图15：图表标题60504030201.8129X + 2.3564*灌溉面积y(千亩)线性(灌溉面积 y(千亩)-100300 10 20图15在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在 图8中选中 残差图(D) ，那么可以自动生成残差图图 12。X Variable 1 Residual Plot-1-2-31 1*

14、1 1 051015202530 «X Variable 1图16回归分析原那么上要求残差分布是无趋势的，如果在图中添加趋势线，那么趋势线应该是 与X轴平行的，且测定系数很小。事实上，添加趋势线的结果如下 图17：X Variable 1 Residual Plot1 差0 残0-2-3-1X Variable 1图17 可见残差分布图根本满足回归分析的要求。预测分析虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不 同时间序列分析应该以时间为自变量。从残差图看来，模型的序列似乎并非具有较强 的自相关性，因为残差分布相当随机。因此，仍有可能进展预测分析。现在假

15、定：有人在 1981年测得最大积雪深度为，他怎样预测当年的灌溉面积？下面给出Excel2000的操作步骤：2. 在图9所示的回归结果中，复制回归参数包括截距和斜率，然后粘帖到图1所示的原始数据附近；并将1981年观测的最大积雪深度写在1980年之后图18。ACDEF11年份最丈积雪泱廈聆冏權观面积y（千閒计算荫Coefficient s2197115. 22& 6)| 1IlnrtercBpt2. 3564379293197210. 419. 8最大积雪深度珂米）L. 8129210654197321, 240_ 5518. 6弧66197326. 44S- 97197623. 445

16、8197713. 5血29197816. 734,11019792446- 711198019. 137. i12L盹.27, 3图182.将光标至于图18所示的D2单元格中，按等于号 丄，点击F2单元格对应于截 距a=2.356，按F4键，按加号 牟"，点击F3单元格对应于斜率 b=1.812，按 F4键，按乘号“* ，点击B2单元格对应于自变量X1,于是得到表达式“ =$F$2+$F$3*B2 ”图19,相当于表达式？1 a b*捲,回车，立即得到 ?129.9128，即1971年灌溉面积的计算值。L ABcD 1EE1年份最大积雲深度炙米）灌溉面积肌千朋it算值Coeffici

17、ent s1971L15.2：28. 5-WSMFB*B2|2 3564379293197210. 419. 3孟丸积雪采度黑（料1.8129210654197321. 240. 551974：13. 635. 6619T926. 44S. 97197623. 4458197713. 529. 29197816, 734. 1101979244：6. 711199019. 127. 412193127.1图193. 将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的即我们所需要的预测数据，即有

18、52.212千亩图20。L ABDEI1年份最丈积雪深厦瓦杓灌溉面积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&. 73b 132. 63210197924蚯745. S67n198019,

19、 137. 435. 98312198127. 552. 212图204. 进一步地，如果可以测得 1982年及其以后各年份的数据，输入单元格 B13及其下面 的单元格中，在 D13及其以下的单元格中，立即出现预测数值。例如，假定1982年的最大积雪深度为 x12 23.7米，可以算得 ?12 45.323千亩；1983年的最大积雪深度为X1315.7，容易得到 31.819千亩图21L ABCDEF11年份最丈积誓深度反米）灌漑面积yC千助计算值Coefficients2197115,22S, 529. 913Irtt except2. 356437929319T210. 419. 321.

20、 211最大积雷深度u侏）1.8129210654197321. 240. 540, 7951974=18. 635. 633. 0776197526. 448. 950. 2187197623. 44544. 779S197713. E29. 226. 831919T815. 734+ 132. 5321019792446. 745. 96711198019. 137. 436. 98312198127. 552. 21213193223. 745. 32314198315. 730. 819图21预测结果1981 - 1983最后大家思考一下为什么 DW检验对本例中的问题未必有效？2. 利

21、用Excel进展多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。Excel 2000的操作方法与一元线性回归分析大同小异：第一步，录入数据图1。ABCDEF1序号年馅工业产值敢农业产值蛇固定資产按资菇运输业产值卩21137057. 8227. 0514. 543. 093219715 5- 0520. 6916. 833.443197259.1533. 0212. 263. 8854197363. 8335. 2312. 873.95197465- 3624. 9411. 653. 2276197567. 2632. 9512. 873. 762719张66.

22、 9230,35 -10.83. 59g8197767. 7988. 710, 93L 03109137875. 6547. 9914. 71L 3i11101975E0- 5754. 1917. 56L &5L2L119807 9- 0258. 7320. 324. 781312198180. 5259, 6518. 675. X14L3190256- 9064. 5725. 345. 5915li198395. 4870. 9725. 06&. 0115151934109. 7101. 5429( 697. 0317L61985126.594. 0148. 8610.031

23、8171986138. 89103. 23电& 910.83图1录入的原始数据第二步，数据分析1.沿着主菜单的 工具T" T数据分析D路径翻开 数据分析对话框，选择 回归，然后 确定，弹出回归'分析对话框，对话框的各选项与一元线性回归根本一样图2。下面只说明x值的设置方法：首先，将光标置于 “ X值输入区域X中图2；然后，从图1所示的C1单元格起，屋 E19止，选中用作自变量全部数据连同标志，这时“X值输入区域X"的空白栏中立即出现“ $C$1:$E$19 ” 然也可以通过直接在“X值输入区域X的空白栏中输入“$C$1:$E$19'的方法实现这一步骤

24、。注意：与一元线性回归的设置一样，这里数据范围包括数据标志：工业产值农业产值固定资产投资运输业产x1x2x3值 y故对话框中一定选中标志项 图3。如果不设 标志'项，那么 “X值输入区域X的 空白栏中应为 “$C$2:$E$19', “Y值输入区域Y的空白栏中那么是 “$F$2:$F$19”否那 么，计算结果不会准确。图2x值以外的各项设置图3设置完毕后的对话框包括数据标志新工作2.完成上述设置以后，确定，立即给出回归结果。由于这里的输出选项选中了表组P"图3，输出结果在出现在新建的工作表上 图4。从图4的输出摘要SUMMARY OUTPUT "中可以读出：

25、a 1.0044, bi 0.053326 , b?0.00402 , d 0.090694 , R 0.994296,2R 0.988625, s 0.335426, F 405.5799, tbi 2.940648 , tb20.28629,tb3 3.489706。根据残差数据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：?1.00440.55326 x10.00402 x20.090694 x3由于X2的回归系数b2的符号与事理不符， b2的t检验值为负，b2的绝对值很小，可 以判定，自变量之间可能存在多重共线性问题。ABcDEFGHIL1OUTP

26、UT23回归统计gMultiple0, 994296R Square0. 988625eAdjusted0 9861877标准俣差0. 335426r sg观测值18ID方差分析1112dfssMSFjnificancE回归分析3136.895845. 6319240&. 5F997. 71E-1413141.5751440.1125114总计17138.470915lbCoeffi cieir祢巻谣差t SlatPalueLcwer 95Wper 9碍F限 95,出上艰y17Intercept-L 00440.643156-L 56168i). 140679-2. 383840.37

27、5031-2. 383840. 375031IB工业产值丫0. 0553260.018S14£ 9406480. 0107430. 0149730.09567S0,0149730. 09567819-0.QU4U20. 014029 -0.换9U. 778B46-0. 24110.026073-0,(J3411U 0260712021固定茨产扌0, 090旳40.U259B93.48706'X 006030. 0349530.146435D. 0349530.146 必 5222iRESIDUALOLTTPUTFRObfkBILITY OUTPJT2526观孤!值J运输辻产醴

28、标堆残差s分比轴伍输业产渣甘2713. 40457-0. 31457-L 033432. 7777783* Q92323. 617595-0.2H6-0. 71485& 3333333, 222933. 2473930.6326072. 07825413.88833.4A T z* b 廿 C b1 11 fli丿詞卅M耳匸書八回/Sheet2/Sheet3/I图4第一次回归结果3剔除异常变量X2农业产值，用剩余的自变量 Xi、X3与y回归图5，回归步骤无 非是重复上述过程参见 图6，注意这里没设数据 标志，最后给出的回归结果图7。ABcDE1序号年份工业产值好固足资产投资汩运输业产值

29、卩p157. 8214, 543. 093219715B. 05L6. 833.443197259. 1512, 263. 835417363. 8312. 87396519T465. 3611. 653. 227&17567. 2612. 87E 768T176&6 9210. 33. 5998177B7. 7910. 93L 0310917875, 6514. 711.11101洛80. 5717. 56L 651211ieo79. 0220. 324, 79131Z18180- 5218. 675. 01141316286, S825. 345, 551514：18395. 4825. 066. 0116151584=10S. 7129, 697 0317IS1985126. 543. 8610. 0318171曲6138. 394乩910. S319181987160. 5660. 9812.莎图5剔除异常变量 农业产值X2图6回归对话框的设置不包括数据标志从图7中容易读出回归结果：2a0.89889, bi 0.051328，ba 0.091229，R 0.994263，R 0.988558，s 0.324999，F 647.973，tbi 4.200968，"3.632285。显然，相对于第一次回归结果，回