用Excel做線性回歸分析(2000與2007)

1、用Excel進行一元線性回歸分析Excel功能強大，利用它的分析工具和函數(shù)，可以進行各種試驗數(shù)據(jù)的多元線性回歸分析。本文就從最簡單的一元線性回歸入手.在數(shù)據(jù)分析中，對于成對成組數(shù)據(jù)的擬合是經(jīng)常遇到的，涉及到的任務(wù)有線性描述，趨勢預(yù)測和殘差分析等等。很多專業(yè)讀者遇見此類問題時往往尋求專業(yè)軟件，比如在化工中經(jīng)常用到的Origin和數(shù)學(xué)中常見的MATLAB等等。它們雖很專業(yè)，但其實使用Excel就完全夠用了。我們已經(jīng)知道在Excel自帶的數(shù)據(jù)庫中已有線性擬合工具，但是它還稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對此類數(shù)據(jù)進行處理。文章使用的是2000版的軟件，我在其中的一些步驟也添加了200

2、7版的注解.首先錄入數(shù)據(jù).以連續(xù)10年最大積雪深度和灌溉面積關(guān)系數(shù)據(jù)為例予以說明。錄入結(jié)果見下圖（圖1）。ABCD1年份最大積雪深度芯冰）灌溉面積y（千畝）2197115.228.63197210.419.34197321.240.55197418.635.66197526.448,07197623.4458197713.529.29197816.734.11019792446.7I11198019.137.4圖1第二步，作散點圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點擊“圖表向?qū)А眻D標(biāo)；或者在“插入”菜單中打開“圖表（H）（excel2007）”。圖表向?qū)У膱D標(biāo)為呢。選中數(shù)據(jù)后，數(shù)據(jù)變

3、為藍色（圖2）。0MicrosoftExcftl-連續(xù)1。年昆士積雪深度和灌溫閭枳的數(shù)據(jù)b回文件更）編輯口視圄5插入夏）格式也）工具數(shù)據(jù)窗口過）D昌昌國他喳匾工殘斛總數(shù)因bi二=r最大枳雪深度工穌）AB|CD1年份最大積雪深度式米）灌溉面積貫千畝）2197115,228.63197210.419.34197321.240.5_5_197418.63QJ66197526.448.97197623*4458197713.529.29197816.734.11019792446.711198019.1迎4圖2點擊“圖表向?qū)А币院?，彈出如下對話框（圖3）:圖表向?qū)?4步驟之1-圖表類型標(biāo)準(zhǔn)類型I自定義

4、類型I圉去類型©:匹。囪tel?:圖的理理讖圖在條折排EJ圖S囹3環(huán)達面泡腓雷曲氣按下不放用查看示例回取一I上步|產(chǎn)二步）I一成。I圖3在左邊一欄中選中“XY散點圖”，點擊“完成”按鈕，立即出現(xiàn)散點圖的原始形式（圖4）:灌溉面積y（千畝）圖4第三步，回歸觀察散點圖，判斷點列分布是否具有線性趨勢。只有當(dāng)數(shù)據(jù)具有線性分布特征時，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)具有線性分布趨勢，可以進行線性回歸?；貧w的步驟如下：首先，打開“工具”下拉菜單，可見數(shù)據(jù)分析選項（見圖5）（2007為“數(shù)據(jù)”右端的“數(shù)據(jù)分析"）：濯溉面程的數(shù)據(jù)匕式工具數(shù)據(jù)衡口世）幫助國）等拚寫）FT7

5、宋體Lp自動保存中一,共享工作簿其一E?霍溉保護。聯(lián)機協(xié)作如視劃求解自定義©-選項W向?qū)?shù)據(jù)分析如更新加我宏堤接一圖5用鼠標(biāo)雙擊“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框（圖6）:圖6然后，選擇“回歸”，確定，彈出如下選項表:圖7進行如下選擇：X、丫值的輸入?yún)^(qū)域（B1:B11,C1:C11）,標(biāo)志，置信度（95%）,新工作表組，殘差，線性擬合圖?；蛘撸篨、Y值的輸入?yún)^(qū)域（B2:B11,C2:C11）,置信度（95%）,新工作表組，殘差，線性擬合圖。注意：選中數(shù)據(jù)“標(biāo)志”和不選“標(biāo)志”，X、Y值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標(biāo)志：最大積雪深度x（米）灌溉面積y（千畝）后者不包括。這一

6、點務(wù)請注意。圖8-1包括數(shù)據(jù)“標(biāo)志”圖8-2不包括數(shù)據(jù)“標(biāo)志”再后，確定，取得回歸結(jié)果（圖9）。麗大積雪深度米）LineFHPint3回歸統(tǒng)計4KultipleR0.9S94165RSquare0.978-446Adjusted.RSquareCl976312T標(biāo)唯誤1.418924S觀測值10SUJfllTAFYDITTPUT9方差分析10102030最大枳雪深度4米）灌港面枳孤千畝）預(yù)測灌溉面積四千畝）11盼差回我總df器暄FWiaifinanceFQ1748.8542畫S54237L澗/5.42032E-08816.106TS2.01334597日一口引1516CDefficierL1標(biāo)

7、推誤差tSlatPf211LIULowtr則Dpt.tr兆%F限非.此限如（fi617IntEt亡e；0t2.35643S1.8278761.2891670.233363-LS58654246.S71S3-LSSS6S6.5715318最大枳雪深度K1米）1.3129210.09400219.205805.42E-081.5961507%2.0296911.5S61512.02969119202122RESIDUALOUTPUT2324觀惻侑雷低面積T殘差251Z9.91284-1.3128426221,21082-1.9118927340,79036-0,290362Eq36,07677-0

8、.47677£9550.21755-1,31755£Ml*制人回歸結(jié)果/Sh把11%回歸緒果2/原始戰(zhàn)據(jù)/反函基/Sheet3/圖9線性回歸結(jié)果最后，讀取回歸結(jié)果如下：截距：a=2.356;斜率：b=1.813;相關(guān)系數(shù)：R=0.989;測定系數(shù)：R2=0.979F值：F=371.945。建立回歸模型，并對結(jié)果進行檢驗?zāi)Ｐ蜑椋海?2.3561.813x至于檢驗，R、R2和F值可以直接從回歸結(jié)果中讀出。實際上，R=0.989416a0.632=Ro.05,8,檢驗通過。有了R值，F(xiàn)值和t值均可計算出來。F值的計算公式和結(jié)果為:R2f(i2)_20.9894162(1-0.98

9、94162)10-1-1=371.945a5.32=F0.05,8R1-R2n-k-1=19.286a2.306=t0.05,8顯然與表中的結(jié)果一樣。t值的計算公式和結(jié)果為:0.9794161-0.97941610-1-1回歸結(jié)果中給出了殘差（圖10）,據(jù)此可以計算標(biāo)準(zhǔn)離差。首先求殘差的平方n卻鳥2=（yi貿(mào)）2，然后求殘差平方和S=£國2=1.724+0.174=16.107，于是標(biāo)準(zhǔn)i1離差為s1.n-k-1(y-？)2=.1s=i1.v16.107.1.4198DWn2(-)i=2n2；ii1_2_2(-1.9111.313)+(0.417-0.833)ZT2.：Z2.'

10、;2(-1.313)(-1.911)0.417=0.751于是s1.419-0.0388：二1015%=0.10.15y36.53觀測值瞿溉面積F殘差.殘差平方129.91284-1.31283811.723544標(biāo)準(zhǔn)離差£221.21082-1.91?；?3.6512221.418923905340,79036-CL29036450.084312436.07677-0.47676970.227309s/y的均值550.21755-L317554L7359490.038842702644.778790.221209160.048933726.830872.36912775.61276

11、6832.632221.467780292.154379945.866540.833456520.694651036.983230.416769730.173697殘差平方和16,106762.013345圖10y的預(yù)測值及其相應(yīng)的殘差等進而，可以計算DW值（參見圖11）,計算公式及結(jié)果為du=1.29取ot=0.05,k=1,n=10(顯然v=1011=8),查表得dl=0.94,顯然，DW=0.751Mdl=0.94,可見有序列正相關(guān)，預(yù)測的結(jié)果令人懷疑殘差1-9殘差2-10殘差之差二殘差之差的平方-L312838-1.910817-0.5979788890.357578752-L9108

12、17-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.6135540592.36912771.4677803-0.9013474070,8124271491.46778030.8334565-0.634323773Q.4023666490.83345650.4167697-0.41668

13、67830.173627875DW值0.4167697殘差之差的平方和12,094878120,750919圖11利用殘差計算DW直最后給出利用Excel快速估計模型的方法：用鼠標(biāo)指向圖4中的數(shù)據(jù)點列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）:灌溉面積V（千畝）圖12點擊“添加趨勢線（R）”，彈出如下選擇框（圖13）:圖13在“分析類型”中選擇“線性（L）”，然后打開選項單（圖14）:圖14在選擇框中選中“顯示公式（»'和"顯示R平方值（R）”（如圖14）,確定，立即得到回歸結(jié)果如下（圖15）:圖表標(biāo)題灌溉面積y（千畝）一線性（灌溉面積y（千畝）圖15在圖15中，給出了

14、回歸模型和相應(yīng)的測定系數(shù)即擬合優(yōu)度。2007回歸分析使用Excel2007的“分析工具庫”做多元回歸分析首先，要求你的Excel2007是企業(yè)版或者專業(yè)版的（其他版本是否有此功能我不能確定），通常，在安裝Excel2007的時候，選擇”全部在硬盤中運行”就會使你的Excel具有“分析工具庫”。如果你的Excel沒有這個功能，請按照下面的操作添加此功能：1 .打開控制面板（在WindowsXP中，Win7的操作應(yīng)該與此類似），找到Office點擊“更改”。當(dāng)前叟量砌序glicramEt融11CTMDft.liCTETtBftMETFruvncrkZ.0Servicehek£NETFrt

15、Mncrk3.口SkFiric*F«ck2NETFt*酢*原35SF10Hi.eraEdftAt-iv*SjTCNicr4iChftGBL3O3OSq*附t11licrPTO'ftiikt«n4IarjTTSYzstlicrnsiifLQffici-I>t«r>ri.3E«2)007單恚士麗相支掙值Ji,要蝴!此槿序或招它從計蹣制除，卓擊"更球“眼“餐帆”C)liersT»ftOF西aFej*1Pr«EiKiiuxti20垃MieroiaftOfficv-Va130FfdEhxioitlilI2007Is

16、iw.Hicrofa-hSaIvtrli.cjht3NicroKi»ftSQLS«rvir2005Co«pk.clEdiliamr顯示奧事®排序方式（5）：頤3大小1B3.DHB大小大小1a9.wn大小瑞捌a大小1827»大小I2.IIM大小如潮已使用鈿上次嬲日照的吟哼更.|好大小大小7W.QHB式小isaw»大小1.7UI2 .選中“添加或刪除功能”。然后點繼續(xù)。irr4st»£lOfficeZnterprist2007更己NMicrosoftOfficeEnt2007E、安裝衣靠加或刪除功能兇r修復(fù)如廣冊賒吵繼

17、綾©3.在隨后出現(xiàn)的界面中，點開Excel選項，選擇“在本機運行全部程序”。Office此時會進入安裝界面，所以此時你必須插入office的安裝盤，或者幫助計算機找到office的源文件。裝好之后，你的Excel2007應(yīng)該具備“分析工具庫”了。安裝選項Q!)所需馳的器空間總大?。?522MB可用驅(qū)動器空間：能冊配自定義MicrosoftOffice程序的運行方式3:/MicrosoftOffice國+困國+國+田一Micro-softOfficeAccexx,MicrosoftOfficeExcel6L從本機運行通)TO06從本機運行全部程序如InfoPathlutlook71首次

18、使用時安裝(!)區(qū)不由用國)MicrosoftOfficePowerPointMicrosoftOfficePublisherMicrosoft0££iceVisioViewerMicrosoftOffictWordOffice共享功能0££ice工具cX.使用MicrosoftOfficeExcel分析-共享和省理信息1上一步墟)繼建©第二步，激活Excel2007的分析工具庫。這才是我們的正事兒。1.打開Excel2007點擊紅圈圈這個地方。會出現(xiàn)一個下拉菜單。在這個菜單的最底部，有“Exce選項”這個按鈕，點擊之。藪取外部數(shù)據(jù)曲C8-AA

19、BCD2120060.65670.08030.45463 ,20070.64270.05820.51764 20080.65270.05750.56055 20090.67070.03330.73106 20100.69200.03330.82352.一在隨后的界面中，點擊加載項；看到管理“Excel加載項”，點旁邊的“轉(zhuǎn)至按鈕。3.咬退耳存高班力瞳項信£口心在出現(xiàn)的界面中，勾選成功了。名稱1位置活動應(yīng)用程序加我項AcrobatPDFMakerOfficeCOMAddinc,oChineseTranslationAddin6”。tGoogleDesktopOfficeAddinCz,

20、esOfficeSpecialSymbolinputAdd-inc：.”0分析二具章c：.,c非活動應(yīng)用程序加殂頁ChineseCon.-ersiorAddinInternetA5sifitant7BAC：.5OMlkr與口牝Actions0云簽打印向?qū)薘"內(nèi)容登閱向?qū)Х治鯤至-VBA靛力求蜓紅羲工C»C63缶加載項士匚走攻AcrobatPCFMakerOfficeCOMAddin爰不者：AdobeSystem?;,Incorporaied1.工置：C:'ProgramFiles''Adobe'Acrobat7.C=DFM<然明：Ac

21、robatPDFMakerOfficeCOMAddin管理；目五贏三|“分析工具庫”，然后點確定?！狈治龉ぞ邘臁本桶惭b蕓亍似?.，iI4.5.打開你要做回歸的Excel工作表。點菜單上的“數(shù)據(jù)”以及“數(shù)據(jù)分析”按鈕。選擇“回歸”6.分別輸入Y值區(qū)域和X值區(qū)域。輸出選項選擇“新工作表組”。然后點“確定"。Excel在一張新的工作表中就會給你做出回歸分析。7.yzHi3工qi5房內(nèi)x82iisoK門20060.6S&7；S00030.4E46LL02730.2B131.561022.74929式釬0.29410.275a0536o.髭gg2007。.杷2T；0.05820.517

22、60.03270-42431.S75023.07520-®1O0,B24S0.27920.0465以總解20080.652T：O.O5750.56050.02)7m蜀02.073723.65090,浪30.2S240.23710.0262電鈍712000.6707：Q03330.73100.0393&施梵2.090723.909S>XS&S00.5£bl0.27020.01090.63202010奧四0.0333u82350.0367O-EEBO1.941724.41211132070.5093U.27130.0070值65T1河加Jj司豆資產(chǎn)負僵率y法人股所占比重n1saxtr隹川區(qū)域ilJ而誦刈什所占比重12I堂心區(qū)岸始總資產(chǎn)艱酰率»3Mi二刖國耳膾收益M書志4J犍力事酬-QP1流動比率遙rHimF_*1聯(lián)皂資產(chǎn)）16總資產(chǎn)塔長率I7廣金山區(qū)膩1工生營業(yè)考收入增產(chǎn)率高稅率x9術(shù)特工華和IEJ廠黏工4titM非負債脫盾110資產(chǎn)可也保性illr時整如r效整的皿r