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

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

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

3、為藍(lán)色（圖2）。0MicrosoftExcftl-連續(xù)1。年昆士積雪深度和灌溫閭枳的數(shù)據(jù)b回文件更）編輯口視圄5插入夏）格式也）工具數(shù)據(jù)窗口過(guò)）D昌昌國(guó)他喳匾工殘斛總數(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點(diǎn)擊“圖表向?qū)А币院?，彈出如下?duì)話框（圖3）:圖表向?qū)?4步驟之1-圖表類(lèi)型標(biāo)準(zhǔn)類(lèi)型I自定義

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

5、宋體Lp自動(dòng)保存中一,共享工作簿其一E?霍溉保護(hù)。聯(lián)機(jī)協(xié)作如視劃求解自定義©-選項(xiàng)W向?qū)?shù)據(jù)分析如更新加我宏堤接一圖5用鼠標(biāo)雙擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話框（圖6）:圖6然后，選擇“回歸”，確定，彈出如下選項(xiàng)表:圖7進(jìn)行如下選擇：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、點(diǎn)務(wù)請(qǐng)注意。圖8-1包括數(shù)據(jù)“標(biāo)志”圖8-2不包括數(shù)據(jù)“標(biāo)志”再后，確定，取得回歸結(jié)果（圖9）。麗大積雪深度米）LineFHPint3回歸統(tǒng)計(jì)4KultipleR0.9S94165RSquare0.978-446Adjusted.RSquareCl976312T標(biāo)唯誤1.418924S觀測(cè)值10SUJfllTAFYDITTPUT9方差分析10102030最大枳雪深度4米）灌港面枳孤千畝）預(yù)測(cè)灌溉面積四千畝）11盼差回我總df器暄FWiaifinanceFQ1748.8542畫(huà)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;測(cè)定系數(shù)：R2=0.979F值：F=371.945。建立回歸模型，并對(duì)結(jié)果進(jìn)行檢驗(yàn)?zāi)Ｐ蜑椋海?2.3561.813x至于檢驗(yàn)，R、R2和F值可以直接從回歸結(jié)果中讀出。實(shí)際上，R=0.989416a0.632=Ro.05,8,檢驗(yàn)通過(guò)。有了R值，F(xiàn)值和t值均可計(jì)算出來(lái)。F值的計(jì)算公式和結(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值的計(jì)算公式和結(jié)果為:0.9794161-0.97941610-1-1回歸結(jié)果中給出了殘差（圖10）,據(jù)此可以計(jì)算標(biāo)準(zhǔn)離差。首先求殘差的平方n卻鳥(niǎo)2=（yi貿(mào)）2，然后求殘差平方和S=£國(guó)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觀測(cè)值瞿溉面積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ù)測(cè)值及其相應(yīng)的殘差等進(jìn)而，可以計(jì)算DW值（參見(jiàn)圖11）,計(jì)算公式及結(jié)果為du=1.29取ot=0.05,k=1,n=10(顯然v=1011=8),查表得dl=0.94,顯然，DW=0.751Mdl=0.94,可見(jiàn)有序列正相關(guān)，預(yù)測(cè)的結(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利用殘差計(jì)算DW直最后給出利用Excel快速估計(jì)模型的方法：用鼠標(biāo)指向圖4中的數(shù)據(jù)點(diǎn)列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）:灌溉面積V（千畝）圖12點(diǎn)擊“添加趨勢(shì)線（R）”，彈出如下選擇框（圖13）:圖13在“分析類(lèi)型”中選擇“線性（L）”，然后打開(kāi)選項(xiàng)單（圖14）:圖14在選擇框中選中“顯示公式（»'和"顯示R平方值（R）”（如圖14）,確定，立即得到回歸結(jié)果如下（圖15）:圖表標(biāo)題灌溉面積y（千畝）一線性（灌溉面積y（千畝）圖15在圖15中，給出了

14、回歸模型和相應(yīng)的測(cè)定系數(shù)即擬合優(yōu)度。2007回歸分析使用Excel2007的“分析工具庫(kù)”做多元回歸分析首先，要求你的Excel2007是企業(yè)版或者專(zhuān)業(yè)版的（其他版本是否有此功能我不能確定），通常，在安裝Excel2007的時(shí)候，選擇”全部在硬盤(pán)中運(yùn)行”就會(huì)使你的Excel具有“分析工具庫(kù)”。如果你的Excel沒(méi)有這個(gè)功能，請(qǐng)按照下面的操作添加此功能：1 .打開(kāi)控制面板（在WindowsXP中，Win7的操作應(yīng)該與此類(lèi)似），找到Office點(diǎn)擊“更改”。當(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,要蝴!此槿序或招它從計(jì)蹣制除，卓擊"更球“眼“餐帆”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 .選中“添加或刪除功能”。然后點(diǎn)繼續(xù)。irr4st»£lOfficeZnterprist2007更己NMicrosoftOfficeEnt2007E、安裝衣靠加或刪除功能兇r修復(fù)如廣冊(cè)賒吵繼

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

18、使用時(shí)安裝(!)區(qū)不由用國(guó))MicrosoftOfficePowerPointMicrosoftOfficePublisherMicrosoft0££iceVisioViewerMicrosoftOffictWordOffice共享功能0££ice工具cX.使用MicrosoftOfficeExcel分析-共享和省理信息1上一步墟)繼建©第二步，激活Excel2007的分析工具庫(kù)。這才是我們的正事兒。1.打開(kāi)Excel2007點(diǎn)擊紅圈圈這個(gè)地方。會(huì)出現(xiàn)一個(gè)下拉菜單。在這個(gè)菜單的最底部，有“Exce選項(xiàng)”這個(gè)按鈕，點(diǎn)擊之。藪取外部數(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.一在隨后的界面中，點(diǎn)擊加載項(xiàng)；看到管理“Excel加載項(xiàng)”，點(diǎn)旁邊的“轉(zhuǎn)至按鈕。3.咬退耳存高班力瞳項(xiàng)信£口心在出現(xiàn)的界面中，勾選成功了。名稱1位置活動(dòng)應(yīng)用程序加我項(xiàng)AcrobatPDFMakerOfficeCOMAddinc,oChineseTranslationAddin6”。tGoogleDesktopOfficeAddinCz,

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

21、robatPDFMakerOfficeCOMAddin管理；目五贏三|“分析工具庫(kù)”，然后點(diǎn)確定。”分析工具庫(kù)”就安裝蕓亍似?.，iI4.5.打開(kāi)你要做回歸的Excel工作表。點(diǎn)菜單上的“數(shù)據(jù)”以及“數(shù)據(jù)分析”按鈕。選擇“回歸”6.分別輸入Y值區(qū)域和X值區(qū)域。輸出選項(xiàng)選擇“新工作表組”。然后點(diǎn)“確定"。Excel在一張新的工作表中就會(huì)給你做出回歸分析。7.yzHi3工qi5房?jī)?nèi)x82iisoK門(mén)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)負(fù)僵率y法人股所占比重n1saxtr隹川區(qū)域ilJ而誦刈什所占比重12I堂心區(qū)岸始總資產(chǎn)艱酰率»3Mi二刖國(guó)耳膾收益M書(shū)志4J犍力事酬-QP1流動(dòng)比率遙rHimF_*1聯(lián)皂資產(chǎn)）16總資產(chǎn)塔長(zhǎng)率I7廣金山區(qū)膩1工生營(yíng)業(yè)考收入增產(chǎn)率高稅率x9術(shù)特工華和IEJ廠黏工4titM非負(fù)債脫盾110資產(chǎn)可也保性illr時(shí)整如r效整的皿r