用Ecel做线性回归分析

1、用Excel进行一元线性回归分析Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归 分析。本文就从最简单的一元线性回归入手在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势 预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用 到的Origin和数学中常见的 MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在 Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝 试使用较为专业的拟合工具来对此类数据进行处理。文章使用的是2000版的软件，我在其中的一些步骤也添

2、加了2007版的注解.1利用Excel2000进行一元线性回归分析首先录入数据 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）AECD1年份最大积雪涕度或氷）灌漑面积只千亩）2r i97i15. 228. 63197210. 419. 34197321, 240. 55197418. 635. 66197B26. 442.07197623. 4458C 197713. 529. 29197816. 734. 1101979244E. 7|11198019. 137. 4图1第二步，作散点图如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“

3、插入”菜单中打开“图表（H） （excel2007） ”。图表向导的图标为 山。选中数据后, 数据变为蓝色（图2）。 MieroEdft.也2-疸续1D年揚丈积雪繰度和灌陽面視的數据b筍文件世）肩海迅）视團电）葩人 格式q_工旦q 数据迦 奮口也滂同石 a a m 八 ° 丁 & £ a n sf Bi EBl二I引最大积雪裸度陆点击“图表向导”以后，弹出如下对话框（图臼表向皂- < 步骤之1 -圉表类型1际唯英型I自定义粪型I圉恚类型:孑團恚凳型:3):E團閣形形线柱条折E)圏團囲雷曲散点匡k出较成对的数肯ABC1 D1年粉最大积雪袜度跃氷）灌溉面积y（千田

4、2197115. 228, 63197210. 40 3£197321.240.55197418. 66197526.448.97197623. 445S197713.529.29197816. ?32 11019792446. 711198019.137. 4图2按下丐血可萱看示例® I叵取消_ |上一步（<）|下一步j> I 完成Q |图3在左边一栏中选中“ XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）:60灌溉面积y（千亩）50*4030*灌溉面积y（千亩） 420101J00102030图4第三步，回归观察散点图，判断点列分布是否具有

5、线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性 回归。回归的步骤如下：首先，打开“工具”下拉菜单，可见数据分析选项（ 见图5）（2007为”数据”右 端的”数据分析”）：和灌襯面积的数据、式©工具数据1窗口逻）辛助H秒料写底）FT3自动保存0D:宋体灌溉共事工作漳QD 傑护垃联机协作E规划求辭迪自定文©选顶© -.-向导氓）卜!数据分析更新M载審营接1.V 1图56):用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图数据分析分析工貝匪消帮助备生少平 沟览百 半验叶图平数与.验 暫杭利

6、方动机位回样栓 JGr-傅直移隧排何抽1-图6 然后，选择“回归”，确定，弹出如下选项表:图7进行如下选择：X、Y值的输入区域（B1:B11 , C1:C11），标志，置信度（95%，新 工作表组，残差，线性拟合图。或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%，新工作表组，残 差，线性拟合图。注意：选中数据“标志"和不选“标志"，X、Y值的输入区域是不一样的：前者包括数据标志：灌溉面积y（千最大积雪深度x（米）亩）后者不包括。这一点务请注意。图8-1包括数据“标志”图8-2不包括数据“标志”再后，确定，取得回归结果（图 9）3回亡螃计JtuJl.

7、(iple R0l 9994155R 書 jLial ri.L 97B9446Al - Ji? led H Li 41alJe1.970312?L. 4LB9Q4S101 -JNAAEV QfTPin'2 1'EfG . H1J晨汰忸習深辰博米).inf miT Plot-腥麵界ly (千言貳趣1 MZ1RX¥w)LILLdfssPS.1 iTJufLLiSJLCs F12国EC萨1'也佥汀门748.葩 2m. 94&sS. 420： .11-0813g2. 0L33QiU怎计71. 9*1L&17Tntcreeps2.3554381.8273

8、761.289167d233363-.855424& 5Y153-1.858P5e.5T1531?19灵犬和査孔数侏0.09002.123?<42E-0?1+596150796 2.0296011.591E11.0296912i212：rESTDnAI OUT?P1T2? 西 2F卿恒库毗两枳y1旳%岀4-1. 3I2H42fi221.21082-1. =1 翊2731H. 79036-O.2U036盘4fifi. 07&77T：,育斤T90c50. W5-. 51755.1.1 7T'?7K.<J'卜R回归结見回E结駐£項薯數曙/反画數/

9、Eh泊打IdIfmficiw 杯准蛊年 t Si ar -ilrje Lwrr Urcr，綁 Fl?販锻.IT?匝加图9线性回归结果最后，读取回归结果如下：截距：a 2.356 ;斜率：b 1.813 ;相关系数：R 0.989 ;测定系数：R20.979 ； F值：F 371.945。 建立回归模型，并对结果进行检验模型为：?2.356 1.813x至于检验，R、f2和F值可以直接从回归结果中读出。实际上，R 0.989416 0.632R0.05,8，R1(1 R2)n k 1显然与表中的结果一样。检验通过。有了 R值，F值和t值均可计算出来。F值的计算公式和结果为:24 0.989416

10、 371.945 5.32F°.°5,8(10.9894162)10 1 1t值的计算公式和结果为：R0.979416t1 R2” n k 1回归结果中给出了残差（19.286 2.306 t005 81 0.979416:10 1 1图10），据此可以计算标准离差。首先求残差的平方n 10i2 (yi ?)2，然后求残差平方和S"1.724i 10.17416.107，于是标准离差为s16.107:81.419于是观测值瞿溉面积y残差'残差平方129. 1284-1. 31283811.723544标准离差£二221. 21082-1. 910

11、8173. 651222L 418923905340. 79036-0, 29036450.084312q弧07677-0.47676970.227305略的均值5g21755-1.3175541.7359490. 03S342702r644. 77879(1 221209160. 04S933726. 830872. 36912775 61276632- 63222L 467780292.154379945.866540. 333456520.694651036. 83230. 416769730.17367残差平方和16.106762. 013345sy丽 °.0388 10 1

12、5% o。15图10 y的预测值及其相应的残差等进而，可以计算DW值（参见图11），计算公式及结果为DWn(i i 1)2i 2n2ii 1(1.911 1.313)2(0.4170.833)22 2 2(1.313)( 1.911)0.4170.7510.05，k 1，n 10 (显然 v 10 1 18 )，查表得 di 0.94，du 1.29显然，DW= dl0.94，可见有序列正相关，预测的结果令人怀疑残差1-扌残差2 一 10残差之差残差之差的平方-1. 312838-1. 910817-0. 5979788890. 357578752-L 910817-0. 290365lt 6

13、204525012. 62586630707590365-Q, S7677-0.1864052320. 03474691-0.47677-L 317554-0. 8407843050. 706918248-1.3175540. 22120921.5387531942. 3677921680.22120922. 36912772.1479185414. 6135540592.36912771. 4677803-0. 9013474070. 8124271491.46778030. 8334565-0.6343237730. 4023666490.83345650. 4167697-0.4166867S30. 173627875DW值Q.4167697残差之差的平方和12. 094878120. 750919图11利用残差计算 DW®最后给出利用Excel快速估计模型的方法: 用鼠标指向图4中的数