利用Excel进行线性回归分析

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

2、2007版的注解.首先录入数据.以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下列图图1。ABCD1年份最大积雪深度盟冰灌溉面积y千亩2197115.228.63197210.419.34197321.240.55197418.635.66197526.448,07197623.4458197713.529.29197816.734. 11019792446. 7I11198019137. 4图1第二步，作散点图如图2所示，选中数据包括自变量和因变量，点击“图表向导图标；或者在“插入菜单中翻开“图表H excel2007。图表向导的图标为丄。选中数据后，数 据变为蓝色图2

3、。0 Mi croioft Excftl -连续10年:B共积雪葆度旳灌咼袒的数齬1轡 文件 編辐勁 视图世插g 榕式 工具 数据窗口D L& 19自S EiA zlB1d=|最大积雪探度莖&I A 1BCD1年份最大积雪深度忑米灌溉面积y千亩2197115. 228. 63197210. 419. 34197321. 240. 55197418. 63Qj66197526. 448. 97197623. 4458197713. 529. 29197816. 734.11019792446.711198019.137. 41J图2点击“图表向导以后，弹出如下对话框图3：图3在左边一栏中选中“

4、XY散点图，点击“完成按钮，立即出现散点图的原始形式 图 4:灌溉面积y千亩图4第三步，回归观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才 能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性 回归。回归的步骤如下：首先，翻开“工具下拉菜单，可见数据分析选项 见图52007为数据右端 的数据分析：肝哩咼面积的数菇b式1工具数据窗口观秒拼写FT2自动保存迪共享工作薄E權溉保护联机协作他规划求解自定义1选项W向导世数据分析Q-更新加義宏聘接心图5用鼠标双击“数据分析选项，弹出“数据分析对话框图 6：图6然后，选择“回归，确定，弹出如下选项表:图

5、7进行如下选择：X、Y值的输入区域B1:B11 , C1:C11 ，标志，置信度95%, 新工作表组，残差，线性拟合图。或者：X、Y值的输入区域B2:B11，C2:C11 ，置信度95%，新工作表组，残 差，线性拟合图。注意：选中数据“标志和不选“标志，X、Y值的输入区域是不一样的：前者包括数据标志：最大积雪深度x米灌溉面积y千亩后者不包括。这一点务请注意。图8-1包括数据“标志图8-2 不包括数据“标志再后，确定，取得回归结果图9。ABCDEFGHTJOUTPUT最大积雪谏度k米Line Fit Hlt234567a9回归统计KuLtiple RR SquareAdjuated R Squa

6、re标准误差 视测值0. 9294160. 5785440. 3T6312L 418924106040200+隍溉面积y干亩0102030最大积雪探度M米-预测灌溉面积 叫千亩11dfSSJISFSi EJiificance F1213回归分析1748.8542748, 8542371. 94535.42032E-08ft呂16. 106762.013345149T64.S61151617Coefficien1标准误差t SlatPyalu&Lowtr 95Upt.tr 9勢邱艮95.址限阪W6Inter亡匕pt2. 35643S1.8278761. 2S91670.233363T. S586

7、54246. 51X53-1. 858656.5715313最犬积雪需度讥米1. 3129210. 09400219. 205885. 42E-081.5961507%2. 0296911.5S61512. 029691方差井祈10192021222324252627赴-1, 31284-1, 91082-0, 29036-0. 47677-1. 317552829RESIDUAL OUTPUT观测佰29. 9128421. 2108240.T9036浙 Q76T750,21755图9线性回归结果最后，读取回归结果如下：截距：a =2.356 ;斜率：b =1.813 ;相关系数：R = 0.

8、989 ;测定系数： R2 =0.9 7 9 F 值：F =371.945。建立回归模型，并对结果进行检验模型为：$ =2.356 1.813x至于检验，R、R2和F值可以直接从回归结果中读出。实际上，R =0.989416 0.632 =R.o5,8，检验通过。有了 R值，F值和t值均可计算出来。F值的 计算公式和结果为:R220.989416显然与表中的结果一样。= 371.945 5.32 = F.05,8 1 2(1 -0.9894162)10 1 1t值的计算公式和结果为：tJ 1 - R2O.979416= 19.2862.306 = t.05 81 -0.979416n k -1

9、10-1 一1回归结果中给出了残差图10，据此可以计算标准离差。首先求残差的平方n =10；i2 =yi -克2，然后求残差平方和S7 ；i2 =1.724 0.174 =16.107，于是标准i=1离差为f n k1y(% - ？)2= 1.419于是DWn( ；i - ；i) i =2n寸2；ii A2 2(-1.911 1.313):;川(0.417 - 0.833)2 2 2(-1.313)(-1.911)0.417-0.751观测值翟溉面积y残差残差平方129. 91284-1. 31283811.723544标准离差221.210823. 6512221.418923905340,

10、790367.29036450. 084312436. 07677-0, 47676970. 227309旳的均值550. 21755-L 3175541.7359490.038842702644.778790. 221209160. 048933726. 830S72. 36912775. 612766832. 632221.467780292.154379945. 866540. 833456520. 694651036. 983230. 416769730.173697残差平方和16.106762. 013345s 1 4190.0388 ：1015% =0.10.15y 36.53图1

11、0 y的预测值及其相应的残差等进而，可以计算DW值参见图11,计算公式及结果为残差残差2- 1。残差之差1残差之差的平方-L 3128387. 910S17-0.5979788890. 3575787527. 910817-0.2903651.6204525012.625866307-0.290365-0. 47677-0.1864052320. 03474691-0. 47677-1. 317554-0.8407843050. 706918248-1.3175540. 22120921.5387631942. 3677921680. 22120922. 36912772.1479185414

12、. 6135540592. 36912771,4677803-0.9013474070, 8124271491. 46778030. 8334565-0.6343237730. 4023666490. 83345650. 4167697-0.4166867830.173627875DW值0. 4167697残差之差的平方和12, 094878120, 750919k -1，n -10 (显然 v=10-1-1=8 )，查表得 d 0.94，du =1.29显然，DW=0.751：dl =0.94，可见有序列正相关，预测的结果令人疑心图11利用残差计算 DW值最后给出利用Excel快速估计模型的

13、方法：用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单图 12：取：0.05，灌溉面积讥千亩图12点击“添加趋势线R ，弹出如下选择框图13:图13在“分析类型中选择“线性 L ，然后翻开选项单图14:图14 在选择框中选中“显示公式E 和“显示R平方值R 如图14，确定，立即得 到回归结果如下图15:图表标题60504030201.8129X + 2.3564+灌溉面积y千亩线性灌溉面积 y千亩-10020300 10图15在图15中，给出了回归模型和相应的测定系数即拟合优度。2007回归分析使用Excel2007的“分析工具库做多元回归分析首先，要求你的Excel2007是企业版或者

14、专业版的其他版本是否有此功能我不能 确定，通常，在安装Excel2007的时候，选择“全部在硬盘中运行就会使你的 Excel具有“分析工具库。如果你的 Excel没有这个功能，请按照下面的操作添 加此功能：1.翻开控制面板在 WindowsXP中，Win7的操作应该与此类似，找到 Office点击“更改。当罰安寮跚序厂样序方式gjfTzl制 Hi fratQft MET2. 0 Sif-flcc Fvck 21B3.DHB RiNtl Frwnerl: 2 0 酉喜包-质萍中文滋 B4JIBHi cr ova ft NET FrtMncirk 3 0 StrvLc：* Fck E16S. 00

15、U囲MET3 5 Sfl枷 Ml era Ed ftV*STLC大水18. Z7Ulicrosft GB L3O3O Support Ftdctft?. I1B闊 licrPT&ft Hiki 也id lury TTS 畑 umicTBft ofFicaatrr大水加 l.DME上扶飙日裡2021-3-5更更戏辩序車胃它JU计机田龄单击“更裁魚胞厦谀|躺.1pj li crTft Offict Pr#j*cl Pr:titi ul 2007691.0MBMi cr oiaft 0fi c* Va 13-d Pro fast i otl ilI 2007何.(XK* Hi crofa-h Sil

16、vurlidit| 气 Hi era so ft SQL Satwif 2006 Coepk.cl Edi lion ISHf1.7UI备 Syne-2.选中“添加或删除功能。然后点继续。 i rr4stl Office Znterpr ist 200T更三; Microsoft Office Ent2007 安婪匚否忝加或删除功能r修复l册際叫继续3.在随后出现的界面中，点开 Excel选项，选择“在本机运行全部程序。Office此时会进入安装界面，所以此时你必须插入office的安装盘，或者帮助计算机找到office的源文件。装好之后，你的Excel2007应该具备“分析工具库了。安装选项

17、Q!)自定义Microsoft Office程序的运行方式 Microsoft Office.+iEH+ll+.+* Micro-soft Office* Microsoft Office Excel从i机运疔旺化从本机运行堂部程序(W-1首次使用时安装(X)空不可用堪)nfoFath卜口曾Not令lutlookMicrosoftMicrosoftMli crosoftOffice PowerFointOffi ce PublisherOffice Vi si o Vi ewerMicrosoft Office Wordffl共拿功能Office 工員使用Microsoft Office Ex

18、cel分折-共拿和暮理信息所需哑对器空间总大小：152Z MB 可用呃动器空间：92SS I1B越上一歩広)城续第二步，激活Excel2007的分析工具库。这才是我们的正事儿。1.翻开Excel2007点击红圈圈这个地方。会出现一个下拉菜单。在这个菜单的 最底部，有“ Exce选项这个按钮，点击之。C8- 0AABCD1yxlx2220060. 65670. 08030, 45463r2007CL 64270. 05820,420210. 65270. 05750. 5605520210. 67070. Q3330. 7310620210. 69200. 0333Q. 82352.在随后的界面

19、中，点击加载项；看到管理“ Excel加载项，点旁边的“转 至按钮。3.在出现的界面中，勾选成功了。加载项舍称| 3活动应用程常加靈项Acrobat PDFMaker Office COM Addinc,oChinese Translation AddinG.QGoogle Desktop Office AddinCz,esOffice Special Symbol input Add-inc：. 0分析二具卒c：.,c非活动应用程凉加载项Chinese Con-.-ersior AddinInternet Asifitant 7BAC： .5Oh/linr勺Actions标签打印向导人习工内

20、菩C：V*I莹阅向导分析二具克 VBACC址覺反；Acrobat PCFMaker Office COM Addin疸弔着：Adobe System?;, Incorporaied 恃書:C:Program Files-Adobe Acrobat 7.C=DFM说明： Acrobat PDFMaker Office COM Addin勰 | Excel iU_ 转到“ |“分析工具库，然后点确定。“分析工具库就安装)4.翻开你要做回归的 Excel工作表。点菜单上的“数据以及“数据分析按 钮。Qai飼 砸贩冋tm二:：5.汎册球萌L=Wl曰fr 重舉沁-选择“回归分别输入Y值区域和X值区域。输

21、出选项选择“新工作表组。然后点“确 定。Excel在一张新的工作表中就会给你做出回归分析。200620072021200$2021 xi:0?6567； 0. OB03 J Q 05B20.凹27：0.6637 : 0.0&7BI： 0.6707 : a 0333也劇魚OM常30. 51760. 56050, 7310 8235总冨4冨5廉油胡訓xlD xii0.02730.2S131.5G1U22.7492良打刖0.29410.2750.053&0 桃的0.03270- 42431.875023.07520-S5100.S2400.27920.02170.28702.078723.65090, &6330.2S240.23H0.02620,69710.0393仇 4E3D2,090723.9093SSfiSO0s5S140.27020.01090,63200.03670-E5BO1.941724.41210.32070.50930.27130.00700-65T1O- 42431.875023. 07520-85100. S24S思资产负借率 y 法人股所占比重讨 怖通/殳所占比重12 宜资产报酬率曲 每股收益74流咖怦応1址总资产16包资