利用Excel進行線性回歸分析匯總

1、1. 利用Excel進行一元線性回歸分析2. 利用Excel進行多元線性回歸分析1. 利用Excel進行一元線性回歸分析第一步，錄入數(shù)據(jù)以連續(xù)10年最大積雪深度和灌溉面積關系數(shù)據(jù)為例予以說明。錄入結果見下圖（圖1）。圖1第二步，作散點圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點擊“圖表向導”圖標；或者在“插入”菜單中打開“圖表（H）”。圖表向導的圖標為。選中數(shù)據(jù)后，數(shù)據(jù)變?yōu)樗{色（圖2）。圖2點擊“圖表向導”以后，彈出如下對話框（圖3）：圖3在左邊一欄中選中“XY散點圖”，點擊“完成”按鈕，立即出現(xiàn)散點圖的原始形式（圖4）：圖4第三步，回歸觀察散點圖，判斷點列分布是否具有線性趨勢。只有當數(shù)據(jù)

2、具有線性分布特征時，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)具有線性分布趨勢，可以進行線性回歸?；貧w的步驟如下：1. 首先，打開“工具”下拉菜單，可見數(shù)據(jù)分析選項（見圖5）：圖5用鼠標雙擊“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框（圖6）：圖62. 然后，選擇“回歸”，確定，彈出如下選項表（圖7）：圖7進行如下選擇：X、Y值的輸入?yún)^(qū)域（B1:B11，C1:C11），標志，置信度（95%），新工作表組，殘差，線性擬合圖（圖8-1）?；蛘撸篨、Y值的輸入?yún)^(qū)域（B2:B11，C2:C11），置信度（95%），新工作表組，殘差，線性擬合圖（圖8-2）。注意：選中數(shù)據(jù)“標志”和不選“標志”，X、

3、Y值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標志：最大積雪深度x(米)灌溉面積y(千畝)后者不包括。這一點務請注意（圖8）。圖8-1包括數(shù)據(jù)“標志”圖8-2不包括數(shù)據(jù)“標志”3. 再后，確定，取得回歸結果（圖9）。圖9線性回歸結果4. 最后，讀取回歸結果如下：截距：；斜率：；相關系數(shù)：；測定系數(shù)：；F值：；t值：；標準離差（標準誤差）：；回歸平方和：；剩余平方和：；y的誤差平方和即總平方和：。5. 建立回歸模型，并對結果進行檢驗模型為：至于檢驗，R、R2、F值、t值等均可以直接從回歸結果中讀出。實際上，檢驗通過。有了R值，F(xiàn)值和t值均可計算出來。F值的計算公式和結果為：顯然與表中的結果一樣。T值的計

4、算公式和結果為：回歸結果中給出了殘差（圖10），據(jù)此可以計算標準離差。首先求殘差的平方，然后求殘差平方和，于是標準離差為于是圖10y的預測值及其相應的殘差等進而，可以計算DW值（參見圖11），計算公式及結果為取，（顯然），查表得，。顯然，可見有序列正相關，預測的結果令人懷疑。圖11利用殘差計算DW值利用Excel快速估計模型的方法：2. 用鼠標指向圖4中的數(shù)據(jù)點列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）：圖122. 點擊“添加趨勢線®”，彈出如下選擇框（圖13）：圖133. 在“分析類型”中選擇“線性(L)”，然后打開選項單（圖14）：圖144. 在選擇框中選中“顯示公式(E)”和“顯

5、示R平方值®”（如圖14），確定，立即得到回歸結果如下（圖15）：圖15在圖15中，給出了回歸模型和相應的測定系數(shù)即擬合優(yōu)度。順便說明殘差分析：如果在圖8中選中“殘差圖(D)”，則可以自動生成殘差圖（圖12）。圖16回歸分析原則上要求殘差分布是無趨勢的，如果在圖中添加趨勢線，則趨勢線應該是與x軸平行的，且測定系數(shù)很小。事實上，添加趨勢線的結果如下（圖17）：圖17可見殘差分布圖基本滿足回歸分析的要求。預測分析雖然DW檢驗似乎不能通過，但這里采用的變量相關分析，與純粹的時間序列分析不同（時間序列分析應該以時間為自變量）。從殘差圖看來，模型的序列似乎并非具有較強的自相關性，因為殘差分布相

6、當隨機。因此，仍有可能進行預測分析?，F(xiàn)在假定：有人在1981年測得最大積雪深度為，他怎樣預測當年的灌溉面積？下面給出Excel2000的操作步驟：2. 在圖9所示的回歸結果中，復制回歸參數(shù)（包括截距和斜率），然后粘帖到圖1所示的原始數(shù)據(jù)附近；并將1981年觀測的最大積雪深度寫在1980年之后（圖18）。圖182. 將光標至于圖18所示的D2單元格中，按等于號“”，點擊F2單元格（對應于截距a=2.356），按F4鍵，按加號“”，點擊F3單元格（對應于斜率b=1.812），按F4鍵，按乘號“*”，點擊B2單元格（對應于自變量x1），于是得到表達式“=$F$2+$F$3*B2”（圖19）,相當于表

7、達式，回車，立即得到，即1971年灌溉面積的計算值。圖193. 將十字光標標至于D2單元格的右下角，當粗十字變成細十字以后，按住鼠標左鍵，往下一拉，各年份的灌溉面積的計算值立即出現(xiàn)，其中1981年對應的D12單元格的即我們所需要的預測數(shù)據(jù)，即有千畝（圖20）。圖204. 進一步地，如果可以測得1982年及其以后各年份的數(shù)據(jù)，輸入單元格B13及其下面的單元格中，在D13及其以下的單元格中，立即出現(xiàn)預測數(shù)值。例如，假定1982年的最大積雪深度為米，可以算得千畝；1983年的最大積雪深度為，容易得到千畝（圖21）。圖21預測結果（19811983）最后大家思考一下為什么DW檢驗對本例中的問題未必有效

8、？2. 利用Excel進行多元線性回歸分析【例】某省工業(yè)產值、農業(yè)產值、固定資產投資對運輸業(yè)產值的影響分析。Excel 2000的操作方法與一元線性回歸分析大同小異：第一步，錄入數(shù)據(jù)（圖1）。圖1 錄入的原始數(shù)據(jù)第二步，數(shù)據(jù)分析1. 沿著主菜單的“工具（T）”“數(shù)據(jù)分析（D）” 路徑打開“數(shù)據(jù)分析”對話框，選擇“回歸”，然后“確定”，彈出“回歸”分析對話框，對話框的各選項與一元線性回歸基本相同（圖2）。下面只說明x值的設置方法：首先，將光標置于“X值輸入?yún)^(qū)域（X）”中（圖2）；然后，從圖1所示的C1單元格起，至E19止，選中用作自變量全部數(shù)據(jù)連同標志，這時“X值輸入?yún)^(qū)域（X）”的空白欄中立即出

9、現(xiàn)“$C$1:$E$19”當然，也可以通過直接在“X值輸入?yún)^(qū)域（X）”的空白欄中輸入“$C$1:$E$19”的辦法實現(xiàn)這一步驟。注意：與一元線性回歸的設置一樣，這里數(shù)據(jù)范圍包括數(shù)據(jù)標志：工業(yè)產值x1農業(yè)產值x2固定資產投資x3運輸業(yè)產值y故對話框中一定選中標志項（圖3）。如果不設“標志”項，則“X值輸入?yún)^(qū)域（X）”的空白欄中應為“$C$2:$E$19”，“Y值輸入?yún)^(qū)域（Y）”的空白欄中則是“$F$2:$F$19”。否則，計算結果不會準確。圖2 x值以外的各項設置圖3 設置完畢后的對話框（包括數(shù)據(jù)標志）2. 完成上述設置以后，確定，立即給出回歸結果。由于這里的“輸出選項”選中了“新工作表組（P）

10、”（圖3），輸出結果在出現(xiàn)在新建的工作表上（圖4）。從圖4的“輸出摘要（SUMMARY OUTPUT）”中可以讀出：，。根據(jù)殘差數(shù)據(jù)，不難計算DW值，方法與一元線性回歸完全一樣。根據(jù)回歸系數(shù)可以建立如下多元線性模型：由于x2的回歸系數(shù)b2的符號與事理不符，b2的t檢驗值為負，b2的絕對值很小，可以判定，自變量之間可能存在多重共線性問題。圖4 第一次回歸結果3. 剔除異常變量x2（農業(yè)產值），用剩余的自變量x1、x3與y回歸（圖5），回歸步驟無非是重復上述過程（參見圖6，注意這里沒設數(shù)據(jù)“標志”），最后給出的回歸結果（圖7）。圖5 剔除異常變量“農業(yè)產值（x2）”圖6 回歸對話框的設置（不包括數(shù)據(jù)標志