Excel多元回歸方法

1、Excel在多元回歸預(yù)測(cè)分析教學(xué)中的應(yīng)用王斌會(huì)（暨南大學(xué)經(jīng)濟(jì)學(xué)院）Excel電子表格軟件是微軟辦公軟件組的核心應(yīng)用程序之一，它功能強(qiáng)大，操作簡(jiǎn)單，適用范圍廣，普遍應(yīng)用于報(bào)表處理、數(shù)學(xué)運(yùn)算、工程計(jì)算、財(cái)務(wù)處理、統(tǒng)計(jì)分析、圖表制作等各個(gè)方面。其數(shù)據(jù)分析模塊簡(jiǎn)單直觀，操作方便，是進(jìn)行統(tǒng)計(jì)學(xué)教學(xué)的首選軟件。統(tǒng)計(jì)學(xué)中的回歸預(yù)測(cè)分析具有普遍的實(shí)用意義，但變量之間關(guān)系分析及計(jì)算繁雜，而借助Excel可方便高效地研究其數(shù)量變動(dòng)關(guān)系，完成其繁雜的計(jì)算分析過(guò)程。根據(jù)回歸預(yù)測(cè)中的實(shí)例，借助Excel進(jìn)行相關(guān)分析，判斷出其相關(guān)程度，并在此基礎(chǔ)上建立回歸模型，最后用Excel完成計(jì)算分析、統(tǒng)計(jì)檢驗(yàn)及預(yù)測(cè)，使回歸預(yù)測(cè)分析

2、的計(jì)算過(guò)程更簡(jiǎn)捷，統(tǒng)計(jì)預(yù)測(cè)方法更為實(shí)用。直線回歸分析是研究一個(gè)應(yīng)變量與一個(gè)自變量間呈直線趨勢(shì)的數(shù)量關(guān)系。在實(shí)際中，常會(huì)遇到一個(gè)應(yīng)變量與多個(gè)自變量數(shù)量關(guān)系的問(wèn)題。一個(gè)應(yīng)變量與多個(gè)自變量間的這種線性數(shù)量關(guān)系可以用多元線性回歸方程來(lái)表示。式中b0相當(dāng)于直線回歸方程中的常數(shù)項(xiàng)a,bi（i=1,2,m）稱為偏回歸系數(shù)，其意義為當(dāng)其它自變量對(duì)應(yīng)變量的線性影響固定時(shí)，bi反映了第i個(gè)自變量xi對(duì)應(yīng)變量y線性影響的度量。例財(cái)政收入多因素分析在一定時(shí)期內(nèi)，財(cái)政收入規(guī)模大小受許多因素的影響，如國(guó)民生產(chǎn)總值大小、社會(huì)從業(yè)人員多少、稅收規(guī)模大小、稅率高低因素等。本例僅取四個(gè)變量作為解釋變量，分析它們對(duì)財(cái)政收入的影響程

3、度。t:年份，y:財(cái)政收入（億元），xl:稅收（億元），x2:國(guó)民生產(chǎn)總值（億元），x3:其他收入（億元），x4:社會(huì)從業(yè)人數(shù)（萬(wàn)人）。數(shù)據(jù)來(lái)自中國(guó)統(tǒng)計(jì)出版社出版的中國(guó)統(tǒng)計(jì)年鑒，數(shù)據(jù)時(shí)限為1978-1995年，見(jiàn)下圖1所示。按下列步驟使用Excel“回歸”分析工具：1.輸數(shù)據(jù)：將數(shù)據(jù)輸入A1:F19單元格。數(shù)據(jù)為19行6列，分別記每列變量名為t、y、x1、x2、x3、x4EC>IFydax3M1132IS$ISRlUftJEJUS2巧iltiul4D551g犯，妁X371?UTJW匕苫蛇1史號(hào)gL21233期031泗3梆71T外胃25F144M眥IWU73067M?3J,第KJr?加mW

4、E9IMl弗E耽足«fL.T圖1盟政業(yè)人國(guó)也就提1516】潭里5JIJJEIsnsj產(chǎn)中ao&o1砌54亍送5SJ720*1iJE9就八點(diǎn)川陰色MYm屣曳題3E3S睜虬M3：陽(yáng)般口的43必30542J52跑M：611Z2ESISI置冷同出E|HECT7J331P2.相關(guān)分析：(1)選才¥Excel“工具”菜單之“數(shù)據(jù)分析”選項(xiàng),在分析工具框中選“相關(guān)系數(shù)”。相關(guān)系數(shù)對(duì)話框?qū)@示為圖2所示，它帶輸入輸出的提示。1)輸入輸入?yún)^(qū)域：$B$1:$F$19分組方式：。逐列日選擇標(biāo)志位于第一行2)輸出選項(xiàng)。輸出區(qū)域：$A$21(2)單擊“確定”，Excel將計(jì)算出結(jié)果顯示在輸出

5、區(qū)域中3 .回歸分析：回歸”。回歸對(duì)話(1)選才¥Excel“工具”菜單之“數(shù)據(jù)分析”選項(xiàng)，在分析工具框中框?qū)@示為圖4所示,iM-<1>.<il|M0Ur耳n.MUj1%-IP蛀回二te把同篤空1圖5參變量回舊分析結(jié)果引4敗度量回一口分析時(shí)諾愜exTgMR口前G囊亡.后設(shè)立面r1nMufl&）胃工性鼻s17蛇口PfyftlSR藏所F?4Eu1）輸入Y值輸入?yún)^(qū)域：$B$1:$B$19X值輸入?yún)^(qū)域：$C$1:$F$19回標(biāo)志回常數(shù)為零：只有當(dāng)用戶想強(qiáng)制使回歸線通過(guò)原點(diǎn)時(shí)才選此框日置信度：Excel自動(dòng)包括了回歸系數(shù)的95%置信區(qū)間。要使用其他置信區(qū)間，選擇該框

6、并在“置信度”框中輸入置信水平2）輸出選項(xiàng)O輸出區(qū)域：$H$13）殘差殘差（R）：選擇此框可得到預(yù)測(cè)值和殘差（Residual）,見(jiàn)圖7。殘差圖（D）:選擇此框可得到殘差和每一x值的圖表，見(jiàn)圖6。標(biāo)準(zhǔn)殘差（T）:選擇此框可得到標(biāo)準(zhǔn)化的殘差（每一殘差被估計(jì)標(biāo)準(zhǔn)誤差除）這一輸出可使曲線較容易分層。線性擬合圖（I）:選擇此框可得到一含有y輸入數(shù)據(jù)和與擬合的y值的散點(diǎn)圖，見(jiàn)圖7。4）正態(tài)概率圖：繪制應(yīng)變量的正態(tài)概率圖。（2） .單擊“確定”，Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。4 .回歸輸出的解釋(1)回歸方程：參考圖5所示單元117:121中的系數(shù)，回歸方程為：y=767.7742+0.0543

7、x1+0.3680x2+1.1013x3-0.0037x4在多元回歸模型中，系數(shù)稱為偏回歸系數(shù)。例如，如果“稅收”、“國(guó)民生產(chǎn)總值”和“社會(huì)從業(yè)人數(shù)”為固定常數(shù)，而“其他收入”允許變化，那么單位“其他收入”的變化引起“財(cái)政收入”的變化是1.1013億元。(2)系數(shù)的顯著水平“稅收”系數(shù)的t統(tǒng)計(jì)值大于4,p<0.01,表示拒絕在此模型中“稅收”與“財(cái)政收入”無(wú)關(guān)的原假設(shè)，而得出二者間有明顯關(guān)系存在的結(jié)論?！皣?guó)民生產(chǎn)總值”系數(shù)的t統(tǒng)計(jì)值大于2,p<0.05,說(shuō)明“國(guó)民生產(chǎn)總值”與“財(cái)政收入”間存在明顯線性關(guān)系。而“其他收入”和“社會(huì)從業(yè)人數(shù)”系數(shù)的t值較小，p>0.05,不能拒絕

8、在此模型中“其他收入”和“社會(huì)從業(yè)人數(shù)”與“財(cái)政收入”無(wú)關(guān)的原假設(shè)，而得出二者間無(wú)明顯關(guān)系存在的結(jié)論。(3)回歸統(tǒng)計(jì)值的解釋參考圖5第7行，多元回歸模型的標(biāo)準(zhǔn)誤差是138.86億元，第5行的RSquare值說(shuō)明“財(cái)政收入”變化的99%可以用“稅收”、“國(guó)民生產(chǎn)總值”、“其他收入”和“社會(huì)從業(yè)人數(shù)”為解釋變量的線性回歸模型來(lái)解釋。(4)方差分析的解釋圖5中從第10行到第14行的方差分析輸出是檢驗(yàn)所有回歸系數(shù)同時(shí)等于零的原假設(shè)得出的結(jié)果。最終的結(jié)果是P值，在輸出中的標(biāo)記為“SignificanceF”。這里的P值約為6.04E-14,是從“財(cái)政收入”與解釋變量之間無(wú)關(guān)系的總體隨機(jī)樣本中得到這些結(jié)果

9、的概率。這個(gè)P值說(shuō)明要從這樣總體的隨機(jī)樣本中看到這些結(jié)果是極不可能的，從而得出至少有一個(gè)顯著關(guān)系存在的結(jié)論。5 .殘差分析殘差圖對(duì)查看線性關(guān)系和常數(shù)方差的假設(shè)是否正確十分有用。Excel提供了殘差與每一解釋變量間關(guān)系的圖形，如圖6所示。如果“財(cái)政收入”與“稅收”間的關(guān)系是線性的(考慮其它因素后)，那么殘差圖應(yīng)是顯隨機(jī)圖形。另一方面，如果能看到曲線或者其他對(duì)稱圖形，那么就應(yīng)改變模型來(lái)加入非線性關(guān)系。大多數(shù)觀測(cè)者會(huì)得出殘差圖基本上是隨機(jī)的這一結(jié)論，所以不需要附加的模型化處理。因?yàn)樗玫臉颖据^小(只有18個(gè)觀察對(duì)象)，所以很難查出非線性模式。觀察圖6所示的殘數(shù)一稅收估值圖，該圖顯示基本，且樣本很小，

10、使檢測(cè)非線性模式十分困難。6 .擬合效果考察將財(cái)政收入原始值與估計(jì)值做線圖，Excel步驟為：(1)選數(shù)據(jù)：選B1:B19,按Ctrl選I27:I45(2)點(diǎn)擊“圖表向?qū)А?，選擇“折線圖”，按“完成”按鈕執(zhí)行，結(jié)果見(jiàn)圖77 .預(yù)測(cè)：通過(guò)前面對(duì)回歸方程及回歸系數(shù)的檢驗(yàn)知所建立的方程有意義，同時(shí)通過(guò)上圖也看到，該模型的擬合效果很好，今用該方程對(duì)1996、1997、和2000年財(cái)政收入進(jìn)行預(yù)測(cè)，結(jié)果見(jiàn)下圖8。下面各步驟描述了如何用TRENtB數(shù)預(yù)測(cè)“財(cái)政收入”：(1)按圖1所示，在工作表(C28:F30)中輸入解釋變量的值(2)選取將要包含預(yù)測(cè)值的單元(B28)o(3)從Excel“插入”菜單中選擇“函數(shù)”命令，選取函數(shù)分類列表框中的統(tǒng)計(jì)，并選取“函數(shù)名”列表柜中的TREND然后單擊“確定”。TREN函數(shù)的語(yǔ)法為：TRENDSy's,原x's,新x's,常數(shù)項(xiàng))本例B28=TREND($B$2:$B$19,$C$2:$F$19,C28:F28,1)(4)將單元B28的拷貝到B29:B30中，得結(jié)果見(jiàn)上表(5)預(yù)測(cè)的解釋X12000