Excel多元回歸方法

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

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

3、，分析它們對財(cái)政收入的影響程度。t:年份，y:財(cái)政收入（億元），xl:稅收（億元），x2:國民生產(chǎn)總值（億元）， x3:其他收入（億元），x4:社會(huì)從業(yè)人數(shù)（萬人）。數(shù)據(jù)來自中國統(tǒng)計(jì)出版社出版的中國統(tǒng)計(jì)年鑒，數(shù)據(jù)時(shí)限為1978-1995年，見下圖1所示。按下列步驟使用Excel “回歸”分析工具：輸數(shù)據(jù)：將數(shù)據(jù)輸入A1:F19單元格。數(shù)據(jù)為19行6列，分別記每列變 量名為 t、y、x1、x2、x3、x4VPEFJd12x31132152SIB 2E41153ll«JI41S 2:刃il(13534D51四陽45?j371?ua»U7J79貼睿m迫1M2j.53011ai&g

4、t;jM出麗1MM陽»57lT»J925F14IW3473067M? 3>鬲沖J?P7300432WE9I2舊即JW.J3a si1EVM51!4>K211JBSPBJ335711ma 3Dftj|M3MJ®172I7T730*1iJE9sm胡:-田口中樹屋itmi関宼JUE3JT劌5“ ?US拠紗巧胡43陽3JO342J52IB曲iSOSE嗣4U329"曲83Ma回cai J?7T7J 493 M 33 IP 出跛晅町收刪如財(cái)嗽陀叩庚躋頫期忸炷痕 味 t I 1 I L rr I I u I I I I L L I IH1213M1S16W

5、101. 相關(guān)分析：(1) 選擇Excel “工具”菜單之“數(shù)據(jù)分析”選項(xiàng),在分析工具框中選“相 關(guān)系數(shù)”。相關(guān)系數(shù)對話框?qū)@示為圖 2所示，它帶輸入輸出的提示。1) 輸入輸入?yún)^(qū)域：$B$1:$F$19分組方式：。逐列選擇標(biāo)志位于第一行2) 輸出選項(xiàng)。輸出區(qū)域：$A$21(2) 單擊“確定”，Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中2. 回歸分析：選擇Excel “工具”菜單之“數(shù)據(jù)分析”選項(xiàng),在分析工具框中“回歸”?；貧w對話 框?qū)@示為圖4所示，圍4好晏量山口:折甘迄框圍4好晏量山口:折甘迄框ss扎TM邁站r bwer i：-I- «l>Aa il_JM am. r塔已衍墾i|

6、k «圖予務(wù)變址回卜I分莎結(jié)果圍4好晏量山口:折甘迄框圍4好晏量山口:折甘迄框1）輸入丫值輸入?yún)^(qū)域：$B$1:$B$19X值輸入?yún)^(qū)域：$C$1:$F$19標(biāo)志常數(shù)為零：只有當(dāng)用戶想強(qiáng)制使回歸線通過原點(diǎn)時(shí)才選此框置信度:Excel自動(dòng)包括了回歸系數(shù)的95%置信區(qū)間。要使用其 他置信區(qū)間，選擇該框并在“置信度”框中輸入置信水平2）輸出選項(xiàng)O輸出區(qū)域：$H$13）殘差殘差（R）:選擇此框可得到預(yù)測值和殘差（Residual），見圖7。殘差圖（D）:選擇此框可得到殘差和每一 x值的圖表，見圖6。標(biāo)準(zhǔn)殘差（T）:選擇此框可得到標(biāo)準(zhǔn)化的殘差（每一殘差被估計(jì)標(biāo)準(zhǔn)誤差除） 這一輸出可使曲線較容易分層

7、。線性擬合圖（I）：選擇此框可得到一含有y輸入數(shù)據(jù)和與擬合的y值的散點(diǎn)圖, 見圖7。4）正態(tài)概率圖：繪制應(yīng)變量的正態(tài)概率圖。單擊“確定”，Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。4 回歸輸出的解釋（1）回歸方程：參考圖 5 所示單元 I17:I21 中的系數(shù)，回歸方程為：y = 767.7742+0.0543x1+0.3680x2+1.1013x3-0.0037x4 在多元回歸模型中，系數(shù)稱為偏回歸系數(shù)。例如，如果“稅收”、“國民生 產(chǎn)總值”和“社會(huì)從業(yè)人數(shù)”為固定常數(shù)， 而“其他收入”允許變化， 那么單位 “其他收入”的變化引起“財(cái)政收入”的變化是 1.1013 億元。（2）系數(shù)的顯著水平“

8、稅收”系數(shù)的 t 統(tǒng)計(jì)值大于 4，p<0.01, 表示拒絕在此模型中“稅收”與 “財(cái)政收入”無關(guān)的原假設(shè)，而得出二者間有明顯關(guān)系存在的結(jié)論。“國民生產(chǎn)總值”系數(shù)的 t 統(tǒng)計(jì)值大于 2，p<0.05, 說明“國民生產(chǎn)總值” 與“財(cái)政收入”間存在明顯線性關(guān)系。而“其他收入”和“社會(huì)從業(yè)人數(shù)”系數(shù)的 t值較小，p>0.05,不能拒絕在 此模型中“其他收入”和“社會(huì)從業(yè)人數(shù)”與“財(cái)政收入”無關(guān)的原假設(shè)， 而得 出二者間無明顯關(guān)系存在的結(jié)論。（3）回歸統(tǒng)計(jì)值的解釋參考圖 5第7行，多元回歸模型的標(biāo)準(zhǔn)誤差是 138.86 億元, 第5行的 R Square 值說明“財(cái)政收入”變化的 99

9、可以用“稅收”、“國民生產(chǎn)總值”、 “其他收入”和“社會(huì)從業(yè)人數(shù)”為解釋變量的線性回歸模型來解釋。（4）方差分析的解釋圖5中從第 10行到第 14行的方差分析輸出是檢驗(yàn)所有回歸系數(shù)同時(shí)等于零 的原假設(shè)得出的結(jié)果。最終的結(jié)果是 P值，在輸出中的標(biāo)記為“ Significance F”。這里的P值約為6.04E-14，是從“財(cái)政收入”與解釋變量之間無關(guān)系的總 體隨機(jī)樣本中得到這些結(jié)果的概率。這個(gè)P值說明要從這樣總體的隨機(jī)樣本中看 到這些結(jié)果是極不可能的，從而得出至少有一個(gè)顯著關(guān)系存在的結(jié)論。5殘差分析 殘差圖對查看線性關(guān)系和常數(shù)方差的假設(shè)是否正確十分有用。 Excel 提供了 殘差與每一解釋變量間

10、關(guān)系的圖形，如圖 6所示。如果“財(cái)政收入”與“稅收”間的關(guān)系是線性的 （ 考慮其它因素后 ） ，那么殘 差圖應(yīng)是顯隨機(jī)圖形。 另一方面， 如果能看到曲線或者其他對稱圖形， 那么就應(yīng) 改變模型來加入非線性關(guān)系。大多數(shù)觀測者會(huì)得出殘差圖基本上是隨機(jī)的這一結(jié)論， 所以不需要附加的模 型化處理。因?yàn)樗玫臉颖据^小 （只有 18個(gè)觀察對象） ，所以很難查出非線性模 式。觀察圖 6 所示的殘數(shù)稅收估值圖， 該圖顯示基本， 且樣本很小， 使檢測非 線性模式十分困難。6擬合效果考察將財(cái)政收入原始值與估計(jì)值做線圖， Excel 步驟為：（1）選數(shù)據(jù)：選 B1:B19,按 Ctrl 選 127:145（ 2）點(diǎn)擊

11、“圖表向?qū)А保x擇“折線圖”，按“完成”按鈕執(zhí)行，結(jié)果見 圖77預(yù)測：通過前面對回歸方程及回歸系數(shù)的檢驗(yàn)知所建立的方程有意義， 同時(shí)通過上圖也看到，該模型的擬合效果很好，今用該方程對1996、1997、和2000年財(cái)政收入進(jìn)行預(yù)測，結(jié)果見下圖 8。圖踐轄分折隆MNU1Jt1JLJ fiL1I4U.31!口上1那彈鏟3 izri. v4 lilt *1 1 葉月 4 IMiVVMl. It» ff fFl#M-i禹J1"珂館N>t1r 7HMTT-Jd, qH IzIP*1*ffr 1電p44 42 4JV.i i> I r -h ip i<i h m*斬.

12、M圖7擬合效果圖B 預(yù)測緒果藥*=二!曲腳:膩凋腫皿邊:隠1.£ B1 門 D E !G1,11T£迪£X劇就d mi m 朝戸此別i'阪血X處H JI3J3 mam飆ii覿衛(wèi)瓏勺mj下面各步驟描述了如何用TREN函數(shù)預(yù)測“財(cái)政收入”：（1）按圖1所示，在工作表（C28:F30）中輸入解釋變量的值（2）選取將要包含預(yù)測值的單元（B28）。（3）從Excel “插入”菜單中選擇“函數(shù)”命令，選取函數(shù)分類列表框中的統(tǒng)計(jì)，并選取“函數(shù)名”列表柜中的TREND然后單擊“確定”。TREN函數(shù)的語法為：TREND原 y' s,原 x' s,新 x' s,常數(shù)項(xiàng)）本例 B28 =TREND（$B$2:$B$19,$C$2:$F$19,C28:F28,1）（4） 將單元B28的拷貝到B29: B3