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

1、文档内容1 .利用excel进行一元线性回归分析2 .利用excel进行多元线性回归分析1.利用excel进行一元线性回归分析第一步，录入数据以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）abcd |1年傥最大积雪深度虬米）灌溉面积或千亩）2197115. 228. 63197210. 419. 34197321.240. 55197413. 635. 66197526. 448.97197623. 4458197713. 529.29197816. 734. 110 1979244g. 711198019.13r 4图1第二步，作散点图如图2所示，选中数据（包

2、括自变量和因变量），点击图表向导”图标；或者在 插入”菜单中打开 图表（h） 图表向导的图标为 的。选中数据后，数据变为蓝色（图 2）。mi crosoft eh cel-连维10年是大积雪评度和灌幽面积的数据匕.国）文件旧 捐羯d）视图 插入工）格式®j工具x）数据皿 菌口但 l今19后1号口甯亶一，嗫芝在外hue二=最大枳雪深度内米）abcd1年份最大积雪深度冗怵）灌溉面积y（千田）2197128. 63197210. 419. 345197319m2l21g. 640.56197e26.448. 97197623. 445s197713. e29. 29197816. 734

3、11019792446. 711198019.137. 41图2点击 图表向导”以后，弹出如下对话框（图 3）：图表向导- 4步骤之1 -图表类型标准类型i自定义类型i圉去类型©:子图表类型:做点图a比较成对的数值图的 理理谶图 在条折排ej图s囹 3环达面泡 腓雷曲气匹。囱tel?:按下不放可付着示例史i回取消 |上一步 |下一步：| 一成|图34):在左边一栏中选中 “xy散点图”，点击 完成”按钮，立即出现散点图的原始形式（图灌溉面积y（千亩）图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例

4、数据具有线性分布趋势，可以进行线性 回归。回归的步骤如下：1 .首先，打开 工具”下拉菜单，可见数据分析选项（ 见图5）:图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值的输入区域是不一样的：前者包括数据

5、标志:最大积雪深度x（米）灌溉面积y（千亩）后者不包括。这一点务请注意（ 图8）0图8-1包括数据标志图8-2不包括数据标志3.再后，确定，取得回归结果（图9）0|三餐尊 建田国曾志ra>i）由胃口南二华 后和却,|gimi|口启马占1（30 电x"工力射川d四二网r 丁，b j n五亘五/犬制闾±.k 二ju 1=_d c d efm i k 1i12 345& 7 e日asm m ct： ”.辛其丁*-：，1 f 二ftif.丁回归统计kultit-le rij兆乂1户r sam.sre0仃81口收mjuftc 1 r ;露空。0. 3112 料在误专1把

6、空可魏肯1:上差:才年9dfssnspslfinlfi-a'lce f10il ,->?1 741 9524t41咻空371. jfle?e. c j?2e-nn靴莅b1&1oe76o42.孙悟12sit9 7ew 9&1n14coeffi ci eir椁春至差t stup yilmlew 96*m，” 上、下隹 齐tn上双 阪小15intm圮口工2. w国二l能"携第l二弘的(j.1，2-l-h溺训4.67ls3-1-is工大码闰东上“白uflla.5.馅配：1 5w150796" ' ' ?11k也他,工郎'点r而s

7、eim! c1ttut13湖网-n1912.91284 -i. 3i2s3e12c:2l.21jr.i -i.sludit2l0凯簧tie外“452243fib0t6h -orcrs«r2?550.21ts5 -1.31756q;.丁司* 0.2212011625726.3:3087 1969u7t26992,63222 1.467730292?g至一 um时0期3m :2s10.368333.0.41676329淹主平下和h| | mbit 曜/anet i，回归4 匕pmk s /jjd2j1口旗后卬中、二|口四回植."言.图9线性回归结果4 .最后，读取回归结果如下：

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。5 .建立回归模型，并对结果进行检验模型为：? 2.356 1.813x至于检验，r、r(1 0.989416 )10 1 1显然与表中的结果一样。t值的计算公式和结果为:、f值、t值等均可以直接从回归结果中读出。实际上， r 0.989416 0.632 ro.o58 ,检

9、验通过。有了 r值，f值和t值均可计算出来。f值的计算公式和结果为:r20.9894162371.945 5.32f 0.05,8(1r2)1r20.9794161 0.97941619.286 2.306 t0.05,810 1 1回归结果中给出了残差（图10）据此可以计算标准离差。首先求残差的平方n 10(yi?i )2 ,然后求残差平方和s i2 1.724i 10.174 16.107,于是标准离差为n2(yi ?)2i 116.1071.419于是dwn(i i i)1 2i 2n2i 1_22(1.911 1.313)(0.417 0.833)(1.313)2( 1.911)20.

10、41720.751取显然,0.05, k1 , n 10 (显然 v 10 1 18 ）,查表得dl0.94, du 1.29dw=0.751 dl 0.94 ,可见有序列正相关，预测的结果令人怀疑观测值瞿溉面积y残差:残差平方129. 9您4-1. 31283811.723544标准离差s221, 21082-l 9108173. 651222l 418923905_340. 79036-0. 29036450.084312q36. 07677-0. 47676970. 22t30s峋的均值550.21755-1.317554l 7359490. 03s342702644. 77879q 2

11、21209165 0439331726, 830872. 36912775.612766832. 632221. 467780292.154379945,866540. 33345652。 694651036. 983230. 416769730.173697残差平方和16.106762. 013345sy1.41936.530.0388 10 15% 0.1 0.15图10y的预测值及其相应的残差等进而，可以计算dw值（参见图11）,计算公式及结果为好差l9残差2-1u残差之差残差之差的平方-1.312838 -l 910817 二。. 5979738893575f8752-l 910817

12、 元90克5l 620452501 2 625g6630t-0.290365 -0.476什7,186405232 0 03474691-0. 47677 -l 317554-0. 8407243050. 70691s24s-1.317554q.22120922. 36912770.22120921.5387631942. 3677921682. 36912771. 4677803-0. 9013474070. 8124271492.1479孺5414, 613554q59利用excel快速估计模型的方法:2 .用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（ 图12）:灌溉面积八千亩

13、）0102030图122.点击 添加趋势线？ "，弹出如下选择框（图13）:图133 .在分析类型”中选择线性（l）”，然后打开选项单（图14）:图144 .在选择框中选中 显示公式（e1和 显示r平方值？”（如图14）,确定，立即得到回 归结果如下（图15）:在图15中，给出了回归模型和相应的测定系数即拟合优度。顺便说明残差分析：如果在 图8中选中 残差图（d）”，则可以自动生成残差图（图12）x variable 1 residual plot32 -.1 -差 c1 0 '111 j1- 1 051015*2025.30- 2 - 3 lx variable 1图16回

14、归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与轴平行的，且测定系数很小。事实上，添加趋势线的结果如下（ 图17）:051015*2025 x variable 1 residual plot321差0残0-1-2-3y = -9e-15x + 2e-13r2 = 1e-275 10 15 * 20 25. 30x variable 1图17可见残差分布图基本满足回归分析的要求。预测分析虽然dw检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不 同（时间序列分析应该以时间为自变量）。从残差图看来，模型的序列似乎并非具有较强 的自相关性，因为残差分布相当

15、随机。因此，仍有可能进行预测分析。现在假定：有人在 1981年测得最大积雪深度为 27.5米，他怎样预测当年的灌溉面积？下面给出excel 2000的操作步骤：2. 在图9所示的回归结果中，复制回归参数（包括截距和斜率），然后粘帖到图1所示的原始数据附近；并将 1981年观测的最大积雪深度 27.5写在1980年之后 （图 18）。abcdef 11年份最大积雪*剽费咨）灌溉面积式千可计算值coefficient s2197115. 22w. 6|1llnrtercept2. 3564379293197210. 419. 8最大积雪深度x （米）l 8129210654197321, 240.

16、 5518. 6瓯66197326. 44s- 9715764458197713. 52丸2916. 7341102446. 71119801% 137. i121闻27, 3图182.将光标至于图18所示的d2单元格中，按等于号，”，点击f2单元格（对应于截距 a=2.356 ）,按f4键，按加号，点击f3单元格（对应于斜率b=1.812 ）,按f4键，按乘号“*'；点击b2单元格（对应于自变量 xi）,于是得到表达式 “二$f$2+$f$3*b2”（图19）,相当于表达式?i a b*xi,回车，立即得到? 29.9128,即1971年灌溉面 积的计算值。l abcdbf1年份最大

17、积雪深度密立灌溉面积近千西请算值coefficients21971：15-22.5=$f就书f$3由2|2. 3564379293197210. 419. 3最大积雪深度式米）1.8129210654197321. 240. 55197413. 635. 6619t526. 44氏97197623. 4458197713. 529. 29197816, 734. 110197rg2446.71119901瓦137. 412193127. 51-图193.将十字光标标至于d2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键，往下一拉，各年份的灌溉面积的计算值立即出现，其中 1981年对应的d

18、12单元格的52.212即我们所需要的预测数据，即有？11 52.212千亩（图20）1 abdef|1年份最大积雪深度始冏灌溉面积y（千直1计算值coef-ficient s2197115. 223, 629. 913inrt ercept2. 356437929319721。.今19. 321, 211最大积雪深度k休）1.8129210654197321. 240. 540. 795197418. 635. 636. 077619752s. 448. 950. 2167197623. %4544. 7798197713. 529. 226. s319197816. 73虫132. 632

19、1019792446. 745. s67n1980电137. 436. 98312198127. 552. 212图204.进一步地，如果可以测得 1982年及其以后各年份的数据，输入单元格 b13及其下面 的单元格中，在 d13及其以下的单元格中，立即出现预测数值。例如，假定 1982年的最 大积雪深度为 x12 23.7米，可以算得 ?12 45.323千亩；1983年的最大积雪深度为x13 15.7,容易得到？1331.819千亩（图21）1 abcdef 11年份最大积雪深度£米）灌溉面积yc千即计算值coefficients2197115.22s, 52913int exc

20、ept2. 356437929319tz10. 419. 321. 211最大积雪深度强（木）l 8129210654197321. 240. 540, 795197418. 635. 633. 0776197526. 448. 950. 2187197623. 445的.779s197713. e29. 22&. 931919t815. 734. 13z. 632101979244工745. 96711198019. 137, 436. 98312198127. 552. 21213193223. 745, 32314193315.730. 819图21预测结果（1981 1983）

21、最后大家思考一下为什么dw检验对本例中的问题未必有效?2.利用excel进行多元线性回归分析【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。excel 2000的操作方法与一元线性回归分析大同小异：第一步，录入数据（图1）abcdef1序号年份工业产值n农业产值kn固定黄产投资必运输业产值y21137057. 8227. 0514. 54工0332197158. os28. 89ib. 833.443197259.1533. 0212. 263. 8854197363. 8335, 2312. 873.9651974g5. 3g24. 9411. 653. 227619756

22、7. 2632. 9512. 87* 7627197666. 9230. 3510. s3. 59g8197767. 7938. 710. 934. 0310g19tb75, 6547. 9914. 71l 34：1110197g80. 5754. 1817. 564. 6512ii198079. 0250. 7320. 32也731312198180. 5259. 8518. 675. 0141319s2s6- 3s6l 5725. 345. 59151983近4870. 9725. 06&, 0116151984109, 71si, 5429, 697 . 031716198512

23、6.594. 014s. 8s10.03181713sc138. e9103. 2316. g10.83图1录入的原始数据第二步，数据分析1 .沿着主菜单的 工具（t） ” 一数据分析（d）”路径打开 数据分析”对话框，选择 回 归”，然后 确定"，弹出 回归”分析对话框，对话框的各选项与一元线性回归基本相同（图2）。下面只说明x值的设置方法：首先，将光标置于 “x值输入区域（x）"中（图2）;然后，从图1所示的c1单元格起，至 e19止，选中用作自变量全部数据连同标志，这时“x值输入区域（x） ”的空白栏中立即出现 “$c$1:$e$19 "t,也可以通过直接在

24、“x值输入区域（x） ”的空白栏中输入 “$c$1:$e$19的办法实现这一步骤。注意：与一元线性回归的设 置一样，这里数据范围包括数据标志：工业产值农业产值固定资产投资运输业产x1x2x3值 y故对话框中一定选中标志项（ 图3）。如果不设 标志”项，则“x值输入区域（x）”的空白 栏中应为“$c$2:$e$19， "y值输入区域（工）”的空白栏中则是“$f$2:$f$19：否则，计算结果不会准确。一图2x值以外的各项设置图3设置完毕后的对话框（包括数据标志）2 .完成上述设置以后，确定，立即给出回归结果。由于这里的输出选项”选中了 新工作表组（p） ”（图3）,输出结果在出现在新建

25、的工作表上（图4）。从图4的输出摘要（summary output ） ”中可以读出：a 1.0044, bi0.053326, b20.00402, 00.090694, r 0.994296,_2r 0.988625, s 0.335426, f 405.5799, tbi 2.940648, tb2 0.28629, tb3 3.489706 。根据残差数据，不难计算dw值，方法与一元线性回归完全一样。根据回归系数可以建立如下多元线性模型：?1.0044 0.55326x1 0.00402x2 0.090694x3由于x2的回归系数b2的符号与事理不符， b2的t检验值为负，b2的绝对值

26、很小, 可以判定，自变量之间可能存在多重共线性问题。abcdefghi ：l1suhmay output23回归统计4multiple0. 994296r square0. 988625eadjusted氏 9861877标港误差0. 335426s g观测值18id方差分析iidfss胞fjnificance? f12回口分析3136.895845. 6319240a 5f9gl 71e-1413用差141.575144011125114总计17138.470915 lbcoeffi cieit标准误差t si atrvaluelcwer 95%upper 9渊坪艮 95.的艮 95, 01

27、17intercept-l 00440.643156-l 56168i). 140679-2. 383840*3750312383840. 375031ib工业产值¥0. 0553260.018s14 9406480. 0107430. 0149730.09567s0,0149730. 09567819在北产值'-0.0u4u20. 014029 -0. 28629u. 778b46-0.c34110.026073-0.u3 乳二u 0260720固定小q,0906940,您9893. 4斯u6q, oo35u80. 0349530.146435d. 0349530.1464

28、352122232iresidual oljtputfrobfkbility outpjt2526观测值j运输业产理标性戏差e分比排值输业产道y2713. 40457-0. 31457-1, 033432. 7777783. 092323. 617595-0.2176-0.力兆5& 3333333, 222933. 2473930.6326072. 07825413.888893.4aa o z1r7 。011 malto1 fli 44q亡4m 4卜卜叵三绪里/京曲数据（sheer2/snr?et3/图4第一次回归结果3 .剔除异常变量x2 （农业产值），用剩余的自变量xi、x3与y

29、回归（图5）,回归步骤无非是重复上述过程（参见 图6,注意这里没设数据 标志”），最后给出的回归结果（ 图7）。abcde1序号年份匚业产值观固定资产投资如运输业产值产21197057. 8214, 543. 093219715b. 0516. 833.443197259. 1512, 263. 835417363. 8312. 87396519n65. 3611. 653. 227&197567. 2612. 873. 768t197666. 9210. 33. 59981*767. 7910. 93l 0310917875, 6514. 711,弘111。197rg80. 5717

30、. 56l 651211198079. 0220. 324, 79131z18180. 5218. 675. 011316286, s825. 345, 55l514：198395, 4825, 066. 0116151584=10s. 7129, 690317is185126. 543. 8610. 0318171加6138. 394即910. s319181987160. 5660. 9812. 9图5剔除异常变量农业产值（x2）图6回归对话框的设置（不包括数据标志）从图7中容易读出回归结果： 2 _a0.89889, bi 0.051328, ba 0.091229 , r 0.994263, r 0.988558,s 0.324999, f 647.973, tbi 4.200968 , 3 3.632285。显然，相对于第一次回归结果，回归系数的符号正常，检验参数f值提高了，标准误差 s值降低了，t值检验均可通过。相关系数r有所降低，这也比较正常 一一一般来说，增加变量数目通常提供复相关系数，减少变量则降低复相关系数。回归结果可以接受，建立二元回归模 型如下：y 0.051328x1 0