1、 實驗四（1） 用Excel作一元線性回歸分析實驗名稱：回歸分析實驗目的：學會應用軟件實驗一元線性回歸，多元線性回歸和非線性回歸模型的求解及應用模型解決相應地理問題。1 利用Excel進行一元線性回歸分析 第一步，錄入數據 以連續10年最大積雪深度和灌溉面積關系數據為例予以說明。錄入結果見下圖（圖1）。圖1第二步，作散點圖如圖2所示，選中數據（包括自變量和因變量），點擊“圖表向導”圖標；或者在“插入”菜單中打開“圖表（H）”。圖表向導的圖標為。選中數據后，數據變為藍色（圖2）（office2003）。插入-圖表（office2007)圖2點擊“圖表向導”以后，彈出如下對話框（圖3）：圖3 在左

2、邊一欄中選中“XY散點圖”，點擊“完成”按鈕，立即出現散點圖的原始形式（圖4）：圖4第三步，回歸觀察散點圖，判斷點列分布是否具有線性趨勢。只有當數據具有線性分布特征時，才能采用線性回歸分析方法。從圖中可以看出，本例數據具有線性分布趨勢，可以進行線性回歸。回歸的步驟如下： 首先，打開“工具”下拉菜單，可見數據分析選項（見圖5）（office2003）。數據-數據分析（office2007)：圖5用鼠標雙擊“數據分析”選項，彈出“數據分析”對話框（圖6）：圖6 然后，選擇“回歸”，確定，彈出如下選項表（圖7）：圖7進行如下選擇：X、Y值的輸入區域（B1:B11，C1:C11），標志，置信度（95%

3、），新工作表組，殘差，線性擬合圖（圖8-1）。或者：X、Y值的輸入區域（B2:B11，C2:C11），置信度（95%）， 新工作表組，殘差，線性擬合圖（圖8-2）。注意：選中數據“標志”和不選“標志”，X、Y值的輸入區域是不一樣的：前者包括數據標志：最大積雪深度x(米)灌溉面積y(千畝)后者不包括。這一點務請注意（圖8）。圖8-1 包括數據“標志”圖8-2 不包括數據“標志” 再后，確定，取得回歸結果（圖9）。圖9 線性回歸結果 最后，讀取回歸結果如下：截距：；斜率：；相關系數：；測定系數：；F值：；t值：；標準離差（標準誤差）：；回歸平方和：；剩余平方和：；y的誤差平方和即總平方和：。 建立

4、回歸模型，并對結果進行檢驗 模型為：至于檢驗，R、R2、F值、t值等均可以直接從回歸結果中讀出。實際上，檢驗通過。有了R值，F值和t值均可計算出來。F值的計算公式和結果為： 顯然與表中的結果一樣。t值的計算公式和結果為： 回歸結果中給出了殘差（圖10），據此可以計算標準離差。首先求殘差的平方，然后求殘差平方和，于是標準離差為于是圖10 y的預測值及其相應的殘差等進而，可以計算DW值（參見圖11），計算公式及結果為取，（顯然），查表得，。顯然，DW=0.751，可見有序列正相關，預測的結果令人懷疑。圖11 利用殘差計算DW值 （DW取值范圍<DW<4.其統計學意義：當DW值愈接近2時

5、，殘差項間愈無相關。當DW值愈接近0時，殘差項間正相關愈強。當DW值愈接近4時，殘差項間負相關愈強。） 最后給出利用Excel快速估計模型的方法： 用鼠標指向圖4中的數據點列，單擊右鍵，出現如下選擇菜單（圖12）：圖12 點擊“添加趨勢線(R)”，彈出如下選擇框（圖13）：圖13 在“分析類型”中選擇“線性(L)”，然后打開選項單（圖14）：圖14 在選擇框中選中“顯示公式(E)”和“顯示R平方值(R)”（如圖14），確定，立即得到回歸結果如下（圖15）：圖15 在圖15中，給出了回歸模型和相應的測定系數即擬合優度。 順便說明殘差分析：如果在圖8中選中“殘差圖(D)”，則可以自動生成殘差圖（圖

6、12）。圖16回歸分析原則上要求殘差分布是無趨勢的，如果在圖中添加趨勢線，則趨勢線應該是與x軸平行的，且測定系數很小。事實上，添加趨勢線的結果如下（圖17）：圖17可見殘差分布圖基本滿足回歸分析的要求。 預測分析 雖然DW檢驗似乎不能通過，但這里采用的變量相關分析，與純粹的時間序列分析不同（時間序列分析應該以時間為自變量）。從殘差圖看來，模型的序列似乎并非具有較強的自相關性，因為殘差分布相當隨機。因此，仍有可能進行預測分析。現在假定：有人在1981年測得最大積雪深度為27.5米，他怎樣預測當年的灌溉面積？ 下面給出Excel2000的操作步驟： 在圖9所示的回歸結果中，復制回歸參數（包括截距和

7、斜率），然后粘帖到圖1所示的原始數據附近；并將1981年觀測的最大積雪深度27.5寫在1980年之后（圖18）。圖18 將光標至于圖18所示的D2單元格中，按等于號“”，點擊F2單元格（對應于截距a=2.356），按F4鍵，按加號“”，點擊F3單元格（對應于斜率b=1.812），按F4鍵，按乘號“*”，點擊B2單元格（對應于自變量x1），于是得到表達式“=$F$2+$F$3*B2”（圖19）,相當于表達式，回車，立即得到，即1971年灌溉面積的計算值。圖19 將十字光標標至于D2單元格的右下角，當粗十字變成細十字以后，按住鼠標左鍵，往下一拉，各年份的灌溉面積的計算值立即出現，其中1981年對應的D12單元格的52.212即我們所需要的預測數據，即有千畝（圖20）。圖20 進一步地，如果可以測得1982年及其以后各年份的數據，