利用Excel進(jìn)行線性回歸分析報(bào)告匯總情況

1、適用標(biāo)準(zhǔn)文檔內(nèi)容利用Excel進(jìn)行一元線性回歸分析利用Excel進(jìn)行多元線性回歸分析利用Excel進(jìn)行一元線性回歸分析第一步，錄入數(shù)據(jù)以連續(xù)10年最大積雪深度和澆灌面積關(guān)系數(shù)據(jù)為例予以說明。錄入結(jié)果見以下圖（圖1）。圖1第二步，作散點(diǎn)圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點(diǎn)擊“圖表導(dǎo)游”圖標(biāo)；或許在“插入”菜單中翻開“圖表（H）”。圖表導(dǎo)游的圖標(biāo)為。選中數(shù)據(jù)后，數(shù)據(jù)變成藍(lán)色（圖2）。文檔適用標(biāo)準(zhǔn)圖2點(diǎn)擊“圖表導(dǎo)游”此后，彈出以下對(duì)話框（圖3）：圖3在左邊一欄中選中“XY散點(diǎn)圖”，點(diǎn)擊“完成”按鈕，馬上出現(xiàn)散點(diǎn)圖的原始形式（圖4）：文檔適用標(biāo)準(zhǔn)澆灌面積y(千畝)60504030澆灌面積y

2、(千畝)201000102030圖4第三步，回歸觀察散點(diǎn)圖，判斷點(diǎn)列分布能否擁有線性趨勢(shì)。只有當(dāng)數(shù)據(jù)擁有線性分布特色時(shí)，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)擁有線性分布趨勢(shì)，可以進(jìn)行線性回歸?；貧w的步驟以下：1.第一，翻開“工具”下拉菜單，可見數(shù)據(jù)分析選項(xiàng)（見圖5）：圖5用鼠標(biāo)雙擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話框（圖6）：文檔適用標(biāo)準(zhǔn)圖62.此后，選擇“回歸”，確定，彈出以下選項(xiàng)表（圖7）：圖7進(jìn)行以下選擇：X、Y值的輸入地區(qū)（B1:B11，C1:C11），標(biāo)志，置信度（95%），新工作表組，殘差，線性擬合圖（圖8-1）?；蛟S：X、Y值的輸入地區(qū)（B2:B11，C2:C1

3、1），置信度（95%），新工作表組，殘差，線性擬合圖（圖8-2）。注意：選中數(shù)據(jù)“標(biāo)志”和不選“標(biāo)志”，X、Y值的輸入地區(qū)是不一樣樣的：前者包括數(shù)據(jù)標(biāo)志：最大積雪深度x(米)澆灌面積y(千畝)后者不包括。這一點(diǎn)務(wù)請(qǐng)注意（圖8）。文檔適用標(biāo)準(zhǔn)圖8-1包括數(shù)據(jù)“標(biāo)志”圖8-2不包括數(shù)據(jù)“標(biāo)志”3.再后，確定，獲取回歸納果（圖9）。文檔適用標(biāo)準(zhǔn)圖9線性回歸納果最后，讀取回歸納果以下：截距：a2.356；斜率：b1.813；有關(guān)系數(shù)：R0.989；測(cè)定系數(shù)：R20.979；F值：F371.945；t值：t19.286；標(biāo)準(zhǔn)離差（標(biāo)準(zhǔn)誤差）：s1.419；回歸平方和：SSr748.854；節(jié)余平方和：S

4、Se16.107；y的誤差平方和即總平方和：SSt764.961。成立回歸模型，并對(duì)結(jié)果進(jìn)行檢驗(yàn)?zāi)Ｐ蜑椋?2.3561.813xy至于檢驗(yàn)，R、R2、F值、t值等均可以直接從回歸納果中讀出。實(shí)質(zhì)上，R0.9894160.632R0.05,8，檢驗(yàn)經(jīng)過。有了R值，F(xiàn)值和t值均可計(jì)算出來。值的計(jì)算公式和結(jié)果為：FR20.9894162371.9455.32F0.05,81(1R2)1(10.9894162)nk11011明顯與表中的結(jié)果相同。值的計(jì)算公式和結(jié)果為：文檔適用標(biāo)準(zhǔn)tR0.979416R219.2862.306t0.05,8110.979416nk11011回歸納果中給出了殘差（圖10）

5、，據(jù)此可以計(jì)算標(biāo)準(zhǔn)離差。第一求殘差的平方2n10(yiy?i)2，此后求殘差平方和S2iii1離差為1n2?s(yiyi)nk1i11.7240.17416.107，于是標(biāo)準(zhǔn)1S16.1071.419v8于是s1.419y0.03881015%0.10.1536.53圖10y的展望值及其相應(yīng)的殘差等從而，可以計(jì)算DW值（拜會(huì)圖11），計(jì)算公式及結(jié)果為ni1)2DWi2(i(1.9111.313)2(0.4170.833)2n2(1.313)2(1.911)20.41720.751i1i取0.05，k1，n10（明顯v10118），查表得dl0.94，du1.29。明顯，DW=0.751dl0.

6、94，可見有序列正有關(guān)，展望的結(jié)果令人思疑。文檔適用標(biāo)準(zhǔn)圖11利用殘差計(jì)算DW值利用Excel迅速估計(jì)模型的方法：2.用鼠標(biāo)指向圖4中的數(shù)據(jù)點(diǎn)列，單擊右鍵，出現(xiàn)以下選擇菜單（圖12）：圖12點(diǎn)擊“增加趨勢(shì)線?”，彈出以下選擇框（圖13）：文檔適用標(biāo)準(zhǔn)圖133.在“分析種類”中選擇“線性(L)”，此后翻開選項(xiàng)單（圖14）：圖14在選擇框中選中“顯示公式(E)”和“顯示R平方值?”（如圖14），確定，馬上獲取回歸納果以下（圖15）：文檔適用標(biāo)準(zhǔn)圖表標(biāo)題60y=1.8129x+2.356450R2=0.978940澆灌面積y(千畝)30線性(澆灌面積20y(千畝)1000102030圖15在圖15中

7、，給出了回歸模型和相應(yīng)的測(cè)定系數(shù)即擬合優(yōu)度。順便說明殘差分析：假如在圖8中選中“殘差圖(D)”，則可以自動(dòng)生成殘差圖（圖）。XVariable1ResidualPlot321差殘0-1051015202530-2-3XVariable1圖16回歸分析原則上要求殘差分布是無趨勢(shì)的，假如在圖中增加趨勢(shì)線，則趨勢(shì)線應(yīng)當(dāng)是與x軸平行的，且測(cè)定系數(shù)很小。事實(shí)上，增加趨勢(shì)線的結(jié)果以下（圖17）：文檔適用標(biāo)準(zhǔn)XVariable1ResidualPlot3y=-9E-15x+2E-132R2=1E-27差10殘-1051015202530-2-3XVariable1圖17可見殘差分布圖基本滿足回歸分析的要求。

8、展望分析誠(chéng)然DW檢驗(yàn)憂如不可以經(jīng)過，但這里采用的變量有關(guān)分析，與純粹的時(shí)間序列分析不一樣（時(shí)間序列分析應(yīng)當(dāng)以時(shí)間為自變量）。從殘差圖看來，模型的序列憂如其實(shí)不是擁有較強(qiáng)的自有關(guān)性，由于殘差分布相當(dāng)隨機(jī)。因此，仍有可能進(jìn)行展望分析。此刻假設(shè)：有人在1981年測(cè)得最大積雪深度為27.5米，他如何展望當(dāng)年的澆灌面積？下面給出Excel2000的操作步驟：2.在圖9所示的回歸納果中，復(fù)制回歸參數(shù)（包括截距和斜率），此后粘帖到圖1所示的原始數(shù)據(jù)周邊；并將1981年觀察的最大積雪深度27.5寫在1980年此后（圖18）。文檔適用標(biāo)準(zhǔn)圖182.將光標(biāo)至于圖18所示的D2單元格中，按等于號(hào)“”，點(diǎn)擊F2單元格

9、（對(duì)應(yīng)于截距a=2.356），按F4鍵，按加號(hào)“”，點(diǎn)擊F3單元格（對(duì)應(yīng)于斜率b=1.812），按F4鍵，按乘號(hào)“*”，點(diǎn)擊B2單元格（對(duì)應(yīng)于自變量x1），于是獲取表達(dá)式“=$F$2+$F$3*B2”（圖19）相當(dāng)于表達(dá)式?ab*x1，回車，馬上獲取,y1y?129.9128，即1971年澆灌面積的計(jì)算值。圖19將十字光標(biāo)標(biāo)至于D2單元格的右下角，當(dāng)粗十字變成細(xì)十字此后，按住鼠標(biāo)左鍵，往下一拉，各年份的澆灌面積的計(jì)算值馬上出現(xiàn)，其中1981年對(duì)應(yīng)的D12單元格的52.212即我們所需要的展望數(shù)據(jù)，即有?52.212千畝（圖20）。y11文檔適用標(biāo)準(zhǔn)圖204.進(jìn)一步地，假如可以測(cè)得1982年及其

10、此后各年份的數(shù)據(jù)，輸入單元格B13及其下面的單元格中，在D13及其以下的單元格中，馬上出現(xiàn)展望數(shù)值。比方，假設(shè)1982年的最大積雪深度為x1223.7米，可以算得y?1245.323千畝；1983年的最大積雪深度為x1315.7，簡(jiǎn)單獲取?千畝（圖21）。y1331.819圖21展望結(jié)果（19811983）最后大家思慮一下為什么DW檢驗(yàn)對(duì)本例中的問題未必有效？文檔適用標(biāo)準(zhǔn)利用Excel進(jìn)行多元線性回歸分析【例】某省工業(yè)產(chǎn)值、農(nóng)業(yè)產(chǎn)值、固定財(cái)富投資對(duì)運(yùn)輸業(yè)產(chǎn)值的影響分析。Excel2000的操作方法與一元線性回歸分析截然相反：第一步，錄入數(shù)據(jù)（圖1）。圖1錄入的原始數(shù)據(jù)第二步，數(shù)據(jù)分析沿著主菜單

11、的“工具（T）”“數(shù)據(jù)分析（D）”路徑翻開“數(shù)據(jù)分析”對(duì)話框，選擇“回歸”，此后“確定”，彈出“回歸”分析對(duì)話框，對(duì)話框的各選項(xiàng)與一元線性回歸基真相同（圖2）。下面只說明x值的設(shè)置方法：第一，將光標(biāo)置于“X值輸入地區(qū)（X）”中（圖2）；文檔適用標(biāo)準(zhǔn)此后，從圖1所示的C1單元格起，至E19止，選頂用作自變量全部數(shù)據(jù)連同標(biāo)志，這時(shí)“X值輸入地區(qū)（X）”的空白欄中馬上出現(xiàn)“$C$1:$E$19”自然，也可以經(jīng)過直接在“X值輸入地區(qū)（X）”的空白欄中輸入“$C$1:$E$19”的方法實(shí)現(xiàn)這一步驟。注意：與一元線性回歸的設(shè)置相同，這里數(shù)據(jù)范圍包括數(shù)據(jù)標(biāo)志：工業(yè)產(chǎn)值農(nóng)業(yè)產(chǎn)值固定財(cái)富投資運(yùn)輸業(yè)產(chǎn)x1x2x3

12、值y故對(duì)話框中必定選中標(biāo)志項(xiàng)（圖3）。假如不設(shè)“標(biāo)志”項(xiàng)，則“X值輸入地區(qū)（X）”的空白欄中應(yīng)為“$C$2:$E$19”，“Y值輸入地區(qū)（Y）”的空白欄中則是“$F$2:$F$19”。否則，計(jì)算結(jié)果不會(huì)正確。圖2x值以外的各項(xiàng)設(shè)置文檔適用標(biāo)準(zhǔn)圖3設(shè)置完成后的對(duì)話框（包括數(shù)據(jù)標(biāo)志）完成上述設(shè)置此后，確定，馬上給出回歸納果。由于這里的“輸出選項(xiàng)”選中了“新工作表組（P）”（圖3），輸出結(jié)果在出此刻新建的工作表上（圖4）。從圖4的“輸出綱領(lǐng)（SUMMARYOUTPUT）”中可以讀出：a1.0044，b10.053326，b20.00402，b30.090694，R0.994296，R20.98862

13、5，s0.335426，F(xiàn)405.5799，tb12.940648，tb20.28629，tb33.489706。依照殘差數(shù)據(jù)，不難計(jì)算DW值，方法與一元線性回歸完好相同。依照回歸系數(shù)可以成立以下多元線性模型：?1.00440.55326x10.00402x20.090694x3y由于x2的回歸系數(shù)b2的符號(hào)與事理不符，b2的t檢驗(yàn)值為負(fù)，b2的絕對(duì)值很小，可以判斷，自變量之間可能存在多重共線性問題。文檔適用標(biāo)準(zhǔn)圖4第一次回歸納果3.剔除異樣變量x2（農(nóng)業(yè)產(chǎn)值），用節(jié)余的自變量x1、x3與y回歸（圖5），回歸步驟無非是重復(fù)上述過程（拜會(huì)圖6，注意這里沒設(shè)數(shù)據(jù)“標(biāo)志”），最后給出的回歸納果（圖7）。文檔適用標(biāo)準(zhǔn)圖5剔除異樣變量“農(nóng)業(yè)產(chǎn)值（x2）”圖6回歸對(duì)話框的設(shè)置（不包括數(shù)據(jù)標(biāo)志）從圖7中簡(jiǎn)單讀出回歸納果：文檔適用標(biāo)準(zhǔn)a0.89889，b10.051328，b30.091229，R0.994263，R20.988558，s0.324999，F(xiàn)647.973，tb14.200968，tb33.63228