Excel数据分析工具进行多元回归分析

1、使用Excel数据分析工具进行多元回归分析Excel2010版使用Excel数据分析工具进行多元回归分析与简单的回归估算分析方法基本相同。但是由于有些电脑在安装办公软件时并未加载数据分析工具，所以从加载开始说起（以©粘贴凄入芟面布局公式数据审阅视图为例，其余版本都可以在相应界面找到）点击“文件”，如下图：11营塞建在弹出的菜单中选择“选项”，如下图所示:交件点另百为学关闭信息最近所用文件新建保存并发送帮助在弹出的“选项”菜单中选择“加载项”，在“加载项”多行文本框中使用滚动条找到并选中“分析工具库”，然后点击最下方的“转到”，如下图所示：在弹出的“加载宏”菜单中选择“分析工具库”，然

2、后点击“确定”，如下图所示:加载完毕，在“数据”工具栏中就出现“数据分析”工具库，如下图所示:（同.，房1E2,_力近丫.褰思因分圜唱嘲ffi主的。寸F11Wm>ftflMtS*自产厂算一分审翻船a含+部:怔；*'v®«立身出国性-，-珊smsfefiE5J4JrtUtT.m匕二：，一一二：-;_一二一一:给出原始数据，自变量的值在A2:I21单元格区间中，因变量的值在J2:J21中，如下图所示：1AECDEFGHI,_JL1k1k2mek!s2工1湛x2sLEk1,2i2"2x3£261.056.ISsr.3sUFULI.7454.61c

3、'f27.14b.t63S1L9103KS.35&.753k打5T9B.S6-823S.89J40745.比312U1611910461.05*G.M34.6533X3312115,447S.喻-186.32128.6l：43sm5._ua3513.C5r4至J615.57筑宜卫2331i.QQ74312"。6St.T10.25LXI.91你4.7520.26043.1101,0436011210789.710,251914,M4574.7520.28046.110L04;国（H12210SBl,Q56,7531.65412gWILE4233.893727.145.尔

4、120?.6ILUib9i:a,3s5.75fiT.3STR.RfiTWIX942,61UODT45=F3i53fi121S0-161.。川13,fiJifi7,而83X33靠11.791*33FT2E11MS153611775fu1出3513.65注651615.54130.842旷HOOT135.321200.6121c4jn.L日10.25191k914574.752C.2eotu.110k0423011210513日SF10.251nLMqb儿7520.2£OtS.1WLOt2S02120i0El41.110.3SL422.2821L141320.2I71<1UL口*2

5、5011085015138Ki.25114".67038520.21904410k04250113035LB1384.3516217038229.519。4420.252501ItSEO17138IS.95L21M.S7030810.91904422,61EuOl13430.LB13310,223.551407.63292+D,211融电104,0+554.61L775L&13810.273.451407.610026BOD.19190家IM.0+6154.4124E02013010.25L1407,67038520.219clM104.042S011213S2113811J

6、.2LL14U7.6ZU3Sb组21904104.0t3601119g90假设回归估算表达式为：y=b。+biXi+b2x3+b3x3+bAxrx2+院、1刈+bQX2X3+b/J+bax22+bgx32试使用Excel数据分析工具库中的回归分析工具对其回归系数进行估算并进行回归分析:点击“数据”工具栏中中的“数据分析”工具库，如下图所示：在弹出的“数据分析”-“分析工具”多行文本框中选择“回归”，然后点击“确定”，如下图所示：模塔底蛉-1yi雪居sm平H平质观IA播髻磐处弹出“回归”对话框并作如下图的选择:ic.F0"£J_I_L_l_1X1x3峭白ri-：12-2工3N

7、rr261.1工行ST.4112.1UlhT4B4.出3727.3电60%?Ll露4El肘!L_HA民TJ孤丁?cit.ifiittitaa.1、u4工1.，上il'filj5osH心r'j,黑门43”：1&8L叫M4川tMxem心L'i"k-.1-12HE一J-7-,二乳孔T?14SOTw：u一13(t#白的13.2sijl-b.!J4!：74.T1E20.2£七_，二j。乜&农工i*!(an%iH>iT例1工：SI914,945：t7£第工,901曲一M的:二住堂仁6.3九？2S3.»值凯二1201UU7

8、9,.-ftl*J)庭19lit花67.4丁口瑞9%7U.tIF."1HWT佟兴手附话修即id12as2.ZS2：Uil.7出立均irriTuiABA.32IBUlit*511Lit1369闻841'：-o口471.Mlit»L201l打精*stmws3HH1JXj1口外L加三；£产二4752Q2jrdKfi&l【？】K讨一EEHJ田时",d制*£1=747:第司必.；1隈%速Kt沟国azfrwcsiXiL.iWIum2111.t马沁£iruio4lm1L-11阂我IFio.l5111阂70Xf20.219M4tH.o

9、<wl双料蹲不中殛圣阳期Ife138超E时印：7G38同城5!1*H4映拜L13SIJ标.*健皿d口期为HDisi骗n.»融91*41winflu4IKUI11.即工i匹St?，g3薪6l«fl321%1HQ,以Ju第K|Q.CHbM-ILT8r£trr的/T*5到:ic'raua2£1*总；口”IMfll709Fsn.141中*ED4L£4EL14*ll'*犯M2婀24l现*q1画一屯才IO.1，笛上52ft.2|t>4414u411!¥5119&5上述选择的具体方法是:在“Y值输入区域”，点击右

10、侧折叠按钮，选取函数丫数据所在单元格区域J2J21,选完后再单击折叠按钮返回；这过程也可以直接在Y值输入区域”文本框中输入J2:J21;在“X值输入区域”，点击右侧折叠按钮，选取自变量数据所在单元格区域A2:I21,选完后再单击折叠按钮返回；这过程也可以直接在X值输入区域”文本框中输入A2:I21;置信度可选默认的95%。在“输出区域”如选“新工作表”，就将统计分析结果输出到在新表内。为了比较对照，我选本表内的空白区域，左上角起始单元格为K10.点击确定后，输出结果如下：r,KLitJi一下一Ii!IIAAYDJTfUTirM62THI3A776?1”S口Ce.更£»勺卜甘

11、门,9加工运数，SMtIP-nH6370.436357l9号L60613C66B1,C6571414tfES昨FF旧：分听Q2E.5»55KL?一性专上4Ol913114796也S343265fi9礼物以g19E&T2S2MEKyUlpletRSyuueAjjujledI领皿*解泡磁主费需他L”一处InftrcXTirlivbLtIXTtriablr2工Tiri.ibL-：>xtutljrijl*Xfariablit3XTtrlatoLrbxTtrianiift£TirlaUL*8&6,74JT014iid573象9谢21137571105.7WTrt

12、.56n353W.OTinQTfel弭3221L.273九T必军叫M1W3m时&也2注KJF,UHT36；5r3副，上H-。,盟113-Q.KTW0S61.8UC43WM»-QLT1那4m工e两卓i”工,；IE弓当七4的-Q.444k.ti668dLMMbi口MJaidC.507K9C.ZJ17O7o-siMzaC-571W12731.miB.092221.即10918.»:幻沏2l“9100.2642Ll2S132100.2042-芋帕971别飞51J柳1-MI67251RTT.Mg钻33ISF.hWHh7ZSl；7.6B»bHHCdM。£*普

13、二毋肾AUTtUb啦。血髓HaZ17&89f.T223Z0124g能r.mWSHaZtH613、用切工23解3E.q7?1HII；E4F赧”1-M4?TO1一乱5】*工-l®t<rri5W1212.07234-IB.Uli12-OT2S4P£汾用”付*28F.82MR,*气割第一张表是“回归统计表”（K12:L17）:其中:MultipleR:（复相关系数R）R2的平方根，又称相关系数，用来衡量自变量x与y之间的相关程度的大小。本例R=0.9134表明它们之间的关系为高度正相关。（Multiple:复合、多种）RSquare：复测定系数，上述复相关系数R的平方

14、。用来说明自变量解释因变量y变差的程度，以测定因变量y的拟合效果。此案例中的复测定系数为0.8343,表明用用自变量可解释因变量变差的83.43%AdjustedRSquare：调整后的复测定系数R2,该值为0.6852,说明自变量能说明因变量y的68.52%,因变量y的31.48%要由其他因素来解释。（Adjusted:调整后的）标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其它统计量，此值越小，说明拟合程度越好观察值：用于估计回归方程的数据的观察值个数。第二张表是“方差分析表”：主要作用是通过F检验来判定回归模型的回归效果。该案例中的SignificanceF（F显著性统计量）的

15、P值为0.00636,小于显著性水平0.05,所以说该回归方程回归效果显著，方程中至少有一个回归系数显著不为0.（Significance:显著）第三张表是“回归参数表”：K26:K35为常数项和b1b9的排序默认标示。L26:L35为常数项和b1b9的值，据此可得出估算的回归方程为：y=6570.136+56,7127/+104.1138x2+50.07101x.40,£81642巧次-0.0873633-0.089749K沟-0,23871X：22.999601045xaa-0,171408684x32该表中重要的是。列，该列的O26:O35中的P-value为回归系数t统计量的

16、P值。值得注意的是：其中b1、b7的t统计量的P值为0.0156和0.0175,远小于显著性水平0.05,因此该两项的自变量与y相关。而其他各项的t统计量的P值远大于b1、b7的t统计量的P值，但如此大的P值说明这些项的自变量与因变量不存在相关性，因此这些项的回归系数不显著。回归分析是一种应用很广的数量分析方法，用于分析事物间的统计关系，侧重数量关系变化。回归分析在数据分析中占有比较重要的位置。一元线性回归模型：指只有一个解释变量的线性回归模型，用来揭示被解释变量与另一个解释变量的线性关系。多元线性回归模型：指含有多个揭示变量的线性回归模型，用来揭示被解释变量与多个解释变量的线性关系。此篇文章

17、主要讲述多元线性回归分析。方法/步骤线性回归分析的内容比较多，比如回归方程的拟合优度检验、回归方程的显著性检验、回归系数的显著性检验、残差分析、变量的筛选问题、变量的多重共线性问题。操作见图。回归分析通常需要多次试验操作才可以得出较好的模型。“方法”中选择“进入”，表示所有的自变量都进入模型，目前还没有考虑到变量的多重共线问题，要先观察初步的结果分析，才会考虑发哦变量的多重共线问题。-W-f*5/WM-»MD喇»RM西CU£画一皓圜，国品空屋S3由穗幅fii日i蔺琳KiM7CC150n?o丁f.旗1七餐王.UUKhJi餐胤SB日曰心一4RAAT-P.I.率MAEW

18、WARltOI我善史了MA.注*内百MJ：礼II凡二匚一一玷匐一M0WVVV1TJTW10««0僚。刘。尊J1T0tMUC喻。阴9#。q15-&-v-ii；ci-i-ja-.aun-n*。，I1. 3通过观察调整后的判定系数0.924,拟合优度较高，不被解释的变量较少。由回归方程显著性检验的概率为0,小于显著性水平0.05,则认为系数不同时为0,被解释变量与解释变量全体的线性关系是显著的，可建立线性方程。由系数表知，观察回归系数显著性检验中的概率值，如果显著性水平为0.05,除去“投入人年数”外，其他变量均大于显著性水平，这些变量保留在方程中是不正确的。所以该模型不

19、可用，应重新建模。柳型汇整"督教RR方i腺黎启方标超ft计的情塞1.939犯42315255耀鼎盛鞅麒娥斛爵怆平方和面恂方FSig1回目1790312075立由m况tso61532OOCr1蒲裱1JS6497131J463634047忌讳2107B810.0Q303期巩陵更（常蛹L技离我投入同阴事业货百元，.曲文功.等并购.投入入隼数,盛融融如菜/,VII能怖准化系他裙石欧1$ig0标准用差范用版11虐期门53137G.5BQ*401E49投入八年翻,ege20S1.361335?003投人职栉的人年效467626*.464了仃463费又自助事业费百元.003.002.237kGOl

20、132.022川4.05U断3论文驾*064053*.2521198243衰莫朝713503J19；IF17：H曰英堂更思独2. 4重新建模操作见图片，采用的是“向后筛选”方法，依次剔除的变量是专著数、投入高级职称的人年数、投入科研事业费、获奖数、论文数。最后的模型结果是“立项课题数=-94.524+0.492x投入人年数”*u机Zbaamm，在二昱:fIHM；八ayu?：wr>m»fwr«,UtilKIIWB0。WDMOD咄勃湖尊Mbmi1时中外或勺vq模型汇总gc.国量）.荻奖效.投入科研事业赞（百元），论捏型RR方调整R方标准鳖十的误1.969；.939,924

21、231.52552,969b.939.927226.86443968c.937.927226.58204.965d.931.923232.08335.963e.927.921234.86946.9591.919,917241.9582a.预测变量:（富量）系奖致连人科科事业费（百元）论d.预恻变量:（常坦:）,获奖数，论文数渗入人年数。e预测变势:集量）.论文数，投入人定数。f预测至是:隔量、投入人年数。g因变量:课题思数搞髭:',1北寮拉tTi.0t挥4年忒再玩1口期-35313Ttt.500-4C13g投入人早屹.9S,20B1.S613.362,003他人高桀&林的人年救

22、.467,526764-J47,463投入料研事地费E售元1,003Q02J371601.122甘鬻敏.022,377E14.059.953论文数、口54.网二252-1.198J43薪奖故J12,503.1191.416.1702傀?W-3624673442p.49426投入人年整.6921Z51.34-83.932OOI戮人高孤日防的人不担-.443养a7刊-967口43投入靠解事贵.003M2J401.77SLcsa论文教.阿心52-.2531230230狭斐数,701,453,1171,5-18J343（常R-2979173047-J08,687投入Z金.553.1021.0795.411.DOfr投科研事业费（百元）皿0011521525J翦论文数-.0掘.M534B-1用34Q酎获北皎7164521201586.125-4储守）-63.3057U40.晚投L人年於.64498512557.527.D论文效3。