利用Excel进行线性回归分析汇总

1、文档内容利用Excel进行一元线性回归分析利用Excel进行多元线性回归分析1.利用Excel进行一元线性回归分析第一步，录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。C ABCD1年份最大积雪探度或米）罹溉面积千亩）2197115. 228. 63197210. 419. 34197321. 240. 55197413. 6萸.G6197526. 448. 97197623. 4458197713. 529. 29197316. 734. 11019792446. 71119801队137. 4图1第二步，作散点图如图2所示，选中数据（包括自变量和因

2、变量），点击“图表向导”图标；或者在“插 入菜单中打开“图表（H）”。图表向导的图标为皿。选中数据后，数据变为蓝色（图 2）。OMi cros oft ExcelM.U.I.I.U：-逐续1U年最大积雪源度和谜格面和的数据h亶|文件F）编辑田）视图（V）插A（I）格式 工真。数冤如 菌口芽O习15! toSBlf=最大积雪深度H米）ABC|_ D1年份最大积雪滓度或米）罹溉面积丸二r田;|2197115. 228.610. 419.321. 240.51S. 6363197241973519746197526. 448. 923. 445719768197713. S29.216. 734.1

3、2446.719. 137.4919781019791119801图2点击“图表向导”以后，弹出如下对话框（图3）：图表茴导- 4步骤之1 -图表类型标卷类型自定义类型1子图表类型:散点图n比较成对的数值囹图图图ts图图形形姓图,9环达面泡 柱条折饼圆雷曲气 MM 也 s/，lalwlgl!4）莅下不放可登看示例QD回职消 |上一步-）|下一步| 完成|图3在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图灌溉面积y（千亩）图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例数据具有

4、线性分布趋势，可以进行线性 回归。回归的步骤如下：用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：分析工且因七-检验：平均值的成对二律本分祈数据分析图6指数平滑?|x|2.然后，选择“回归”，确定，弹出如下选项表（图7）：F-检验双样本方差 傅利叶始析 宜方图 穆劫平均 随机数逅生器 排位与百公比排位图7进行如下选择：X、Y值的输入区域（B1:B11, C1:C11），标志，置信度（95%）, 新工作表组，残差，线性拟合图（图8-1）。或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%），新工作表组，残 差，线性拟合图（图8-2）。注意：选中数据“标志”和不选“

5、标志”，X、Y值的输入区域是不一样的：前者包括数据 标志：最大积雪深度x（米）灌溉面积y（千亩）后者不包括。这一点务请注意（图8）。图8-1包括数据“标志”图8-2不包括数据“标志”3.再后，确定，取得回归结果（图9）。|曰，：-：-代1. 413沌 47-.i! i10B/T尊理*1固根m千宙）、割i邮曲血JT汗如102Q3D3dfssnsFSiEnLfSctjuE F10百1 F1748.职洲T4B.器彼371- 9453E_2CG2L-0BL_11:忙EIB. 10676&42. 01335123花土 id14”-9. :! :5.-42E-0B1.5961507962 1296911

6、S96150E：2 029691317HESWIJAL OUTPUT13J:顶me19129.912E4-1.31233212022L.21OE2-L9L0317213如.792i3&-0.29036-4522436.0T6TT-CL目花F龄T:-i!50,21T-L3L?5财叫 7?8?&0r 221216:-i26,8308?A 3691打T26:32,63222 L46773t)22?-8邮Dr 83的S6旎23I36,983% 0.416773网:yiwiH 1 1 h 回蜡罪ZaEEt i 回归珀集2 ZU始富酎咨jj骐醐况t3/ -blIHI髭国如，i.：卤|自it国帝顼 、口 C

7、?三LJ 41回* M K 三 .L图9线性回归结果4,最后，读取回归结果如下：截距：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。5.建立回归模型，并对结果进行检验模型为：y = 2.356 +1.813尤至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。实际

8、上，R = 0.989416 0.632 = R，检验通过。有了 R值，F值和t值均可计算出来。09894162 一-=371.945 5.32 = F10.05,8(1 - 0.9894162)10 -1 -1F值的计算公式和结果第：f = r-（1 - R2） n k 1显然与表中的结果一样。0.979416=19.286 2.306 = t：1 - 0.979416如8T值的计算公式和结果为：R10 -1 -1回归结果中给出了残差(图10 ),据此可以计算标准离差。首先求残差的平方 2 = (y - )2，然后求残差平方和S =罗 2 = 1.724 +A+ 0.174 = 16.107

9、，于是标准ii iii =1v16.107= 1.4198问左s =，U (y - y )2 =V n - k -1 i i 、i=1于是s1.419-= -=0.0388 10 15% = 0.10.15y 36.53观测值薯溉面积y残差残差平方129.91284-1.31283811.723544标准窗差m221.21082-1.9103173.6512221.413923905340.79036-0.29036450.084312436.07677-0.47676970.22730的均值550.21755-1.317554L 7359490.038842702644.778790. 22

10、1209160.04333726.830872. 36912775.612766332.632221.467780292.15437945.S66540.833456520.694651036.983230. 416769730.173697残差平方和16.106762. 013345图10y的预测值及其相应的残差等进而，可以计算DW值(参见图11)，计算公式及结果为c (i -i-1)2(-1.911 +1.313)2 +A + (0.417-0.833)2DW = -i=2= 0.751* 。(-1.313)2 + (-1.911)2 +A + 0.41728 2 ii=1取a= 0.05

11、，k = 1，n = 10 (显然 v = 10 -1 -1 = 8)，查表得 d = 0.94，d = 1.29。显然，DW=0.751 d = 0.94，可见有序列正相关，预测的结果令人怀疑。l9S2 10残差之差残差之差的平方-1.312838-1. 910817-0.5979788890.357578752-1.910817-0. 2903651.6204525012.625866307-0.290365-0. 47677-0.1864052320.03474691-0.47677-1. 317554-0.S407S43050.706918248-1.3175540. 22120921

12、.5387631942.3677921680.22120922. 36912772.1479185414.6135540592.36912771.4677803-0.9013474070. 8124271491.4677803Q. 8334565-0.6343237730.4023666490.83345650. 4167697-0.4166S67830.173627875DWffi0.4167697残差之差的平方和12. 094878120.750919图11利用残差计算DW值利用Excel快速估计模型的方法:2用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图12）罹溉面积试千亩）

13、图122.点击“添加趋势线”，弹出如下选择框（图13）：图133,在“分析类型”中选择“线性（L）”，然后打开选项单（图14）：图144,在选择框中选中“显示公式（E）”和“显示R平方值”（如图14），确定，立即得到回 归结果如下（图15）：图表标题灌溉面积y（千亩）线性（灌溉面积 y（千亩）图15在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在图8中选中“残差图（D）”，则可以自动生成残差图（图12）。回归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与X 轴平行的，且测定系数很小。事实上，添加趋势线的结果如下（图17）：可见残差分布图基

14、本满足回归分析的要求。预测分析虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不 同（时间序列分析应该以时间为自变量）。从残差图看来，模型的序列似乎并非具有较强 的自相关性，因为残差分布相当随机。因此，仍有可能进行预测分析。现在假定：有人在 1981年测得最大积雪深度为27.5米，他怎样预测当年的灌溉面积？下面给出Excel 2000的操作步骤：2. 在图9所示的回归结果中，复制回归参数（包括截距和斜率），然后粘帖到图1 所示的原始数据附近；并将1981年观测的最大积雪深度27.5写在1980年之后 （图 18）。aBCDEF1年份最大粒雪深度忒米）灌溉面积y（千旬）计

15、算值C o efficient s2197115. 228. 6I !lint ere ept2.3564379293197210. 419. 3最大积雪深度x （米）1.8129210654197321. 240. 55197418. 635. 66197526. 44E. 97197623. 445E197713. 5现29197816. 734. 1101979翌46. 7111980137. 412198127. 5图182,将光标至于图18所示的D2单元格中，按等于号“ = ”，点击F2单元格（对应于截距 a=2.356.），按F4键，按加号“+”，点击F3单元格（对应于斜率b=1.

16、812.），按F4 键，按乘号“*”，点击B2单元格（对应于自变量x1），于是得到表达式“=$F$2+$F$3*B2” （图19）,相当于表达式宁=a + b * x，回车，立即得到宁=29.9128，即1971年灌溉面积的计算值。l_ABCDE1年份最大枳雪深度昭灌溉面积y（千瓦计算值Coefficient s2L也22S. 6=$F$2-f$F$3+B2|2.356437929317210. 419. 3-最大积雪深度变米）1.8129210654137321. 24(1 55197418. 635. 66197526. 440. 971抓23. 4450137713. 529. 2919

17、7816. 734. 11013732446. 711198019. 137. 412198127. 5图193.将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左 键，往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的52.212即我们所需要的预测数据，即有宁=52.212千亩（图20LaL D|_ E1年份最大枳雪深度K案）灌溉面积戒千瓦）计算值Coefficient e2197115. 228. e29. 913Int ercept2.3564379293197210. 419. 321.211最大积雪深度N*）1.B12921065419

18、7321. 240. 540. 795197418. a35. 636.0776197526. 448. 950. 2107197623. 44544.7798197713. 529. 226.S319197B16. T34. 132.6321019792446. 745.8671119S019.137. 436.9B31219B127. 552. 212图20ABCDEF1年份最大积雪深度或米）灌溉面积觅千瓦计算值Coefficient s217115. 22S. 629. 913Int ercept2.3564379293197210. 419. 321. 211最大积雪深度x （米）1.

19、812921065419731974175197619771978197919801981198219S321. 240. 540. 79518. 635. 636. 077626. 418. 950. 218723. 44544. 779813. 529. 226. 031g16. 734. 132. 632102446. 745. 8671119. 137. 436. 9B31227. 552. 2121323. 745. 3231415. 730. B194.进一步地，如果可以测得1982年及其以后各年份的数据，输入单元格B13及其下面 的单元格中，在D13及其以下的单元格中，立即出现预

20、测数值。例如，假定1982年的最 大积雪深度为气2= 23.7米，可以算得R2 = 45.323千亩；1983年的最大积雪深度为 x = 15.7，容易得到y = 31.819千亩（图21）。1313图21预测结果（19811983）最后大家思考一下为什么DW检验对本例中的问题未必有效?2.利用Excel进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。Excel 2000的操作方法与一元线性回归分析大同小异: 第一步，录入数据（图1）。ACDEF1序号年份工业产值Q农业产值蛇固定资产投资兢运输业产值y21197057. 8227. 0514. 543. 0

21、932157158.此20. 0916. S33.443197259.1533. 0212. 263. 8854197363. 8335. 2312. S73. 96519743624. 9411. 653. 227S197567. 2632. 9512. S73. 7SE7197666.沮30. 3510. 83. 59g8157767. 73S. 710. 934.0310g197875. 547. 9914. 714. 341110197980. 5754. 1B17. 564. &51211198079. 0250. 7320. 324. 781312198180. 5259. 851

22、0. 675.眼1413198286. 8864. 5725. 345. 591514：198395. 4870. 9725. 06&. 0116151984109. 7181. 5429. 697. 0317161985126. 594. 0143. 8610. 0318171986138. 89103. 2348. 910. 83图1录入的原始数据第二步，数据分析1.沿着主菜单的“工具（T）”一“数据分析（D）.”路径打开“数据分析”对话框，选择“回 归”，然后“确定”，弹出“回归”分析对话框，对话框的各选项与一元线性回归基本相同（图 2）。下面只说明x值的设置方法：首先，将光标置于“X值

23、输入区域（X）”中（图2）；然后，从图1所示的C1单元格起，至E19止，选中用作自变量全部数据连同标志，这时 “X值输入区域（X）”的空白栏中立即出现“$C$1:$E$19”当然，也可以通过直接在“X值输 入区域（X）”的空白栏中输入“$C$1:$E$19”的办法实现这一步骤。注意：与一元线性回归的设 置一样，这里数据范围包括数据标志：工业产值 农业产值 固定资产投资 运输业产x1x2x3值 y故对话框中一定选中标志项（图3）。如果不设“标志”项，则“X值输入区域（X）”的空白 栏中应为“$C$2:$E$19”，“Y值输入区域（Y）”的空白栏中则是“$F$2:$F$19”。否则，计算结 果不会

24、准确。图2 x值以外的各项设置图3设置完毕后的对话框（包括数据标志）2,完成上述设置以后，确定，立即给出回归结果。由于这里的，输出选项”选中了“新工作表 组（P）”（图3），输出结果在出现在新建的工作表上（图4）。从图4的“输出摘要（SUMMARY OUTPUT）”中可以读出：a = 1.0044, b = 0.053326 , b =0.00402 , b = 0.090694 , R = 0.994296,R2 = 0.988625 , s = 0.335426 , F = 405.5799, t = 2.940648 , t = 0.28629 , t = 3.489706。庭根据残差数

25、据，不难计算DW值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：宁=1.0044 + 0.55326x 0.00402x + 0.090694x123由于x2的回归系数b2的符号与事理不符，b2的t检验值为负，b2的绝对值很小， 可以判定，自变量之间可能存在多重共线性问题。ABCDEFGHI1SUKMAY OUTPUT23回归统计4Multiple0.9942965W Square0.9886256AdJ ust已d0. 9861877标准误差0.3354263观测值1E910方差分析11dfESMSFnificance F12回归分析3136.895845. 63192

26、405. 57997. 71E-1413践差E1.5751440.1125114总计17138.47091516Coeffic i 已 it标推误差t StatP-v日1 u已Lower 95%Jpper 95%Tf艮 95. 0上限95. O17I nt 已r。已口 t-1. 00440. 643156-1. 56168i). 140679-2. 383840.375031-2.383840. 3750311E_L业产值0.0553260. 0188142.940648i). 010M30. 0149730.095678).0149730. 09567819农业产值，-0.004020.01

27、4029-0. 286290.778E46-J. Ub4110.026073-0.034110. 02607320固定资产才0. 0906940.0259E93.4897060. 0036080. 0349530.146435D. 0349530.1464352122237AOUTPUTPROBABILITY OUTFLJT2526视测值J延输业广残差怀唯残差百分l匕排仓W输业产堂2T13.40457-U. 31457-1. 033432. 7777783. Cy2B23. 617595-0.2176-0. 714858. 3333333. 222953.2473930. 6-26)72.07

28、S25413.888893.4-1Q CT CT QJT a Qi -! Q Z1 CQQC-11 .-1 | 口 A O1 fi.-1 .-1.-1.-1Q 匚CiM 1卜I 回曰结果/原始 W/Sheet 2/Sheet 3/14 D图4第一次回归结果3,剔除异常变量x2 （农业产值），用剩余的自变量、x3与y回归（图5），回归步骤无 非是重复上述过程（参见图6,注意这里没设数据“标志”），最后给出的回归结果（图7）。ABCDE1序号1年份|r业产值乂1|固定资产投资蕊1运输业产值2117057. 3214. 543.拥32197158. 0516. 833. 443197259. 151

29、2. 263. 8S54197363. 0312. 873.965197465. 3611. 653. 2216197567. 2612. 873. 7687197&66. 9210. 83. 5998197767. 7910. 934. 031091&7S75. 6514. 714.弛11101浒980. 5717. 564：. 651211198079. 0220. 324. 7S1312198180. 521B. 675. 04：1413198286. 8825. 345. 591514158395. 4825. 066. 0116151984：109. 7129. 697. 03171S1985126. 543. 8610. 031817198613S. S940. 910. 8319181987160. 5660. 9S12. 9图5剔除异常变量“农业产值（x2） ”图6回归对话框的设置（不包括数据标志）从图7中容易读出回归结果：a = -0.89889，b1 = 0.051328，b = 0.091229，R = 0.994263，R2 = 0.988558， s = 0.324999，F = 647.973，、= 4.200968，t33 = 3.632285。显然，相对于第一次回归结果，回归系数的符号正常，检验参数F值提高了，