用Ecel作一元線性回歸分析

實驗四（1）用Excel作一元線性回歸分析實驗名稱：回歸分析實驗目的：學會應用軟件實驗一元線性回歸，多元線性回歸和非線性回歸模型的求解及應用模型解決相應地理問題。1利用Excel進行一元線性回歸分析第一步，錄入數(shù)據(jù)以連續(xù)10年最大積雪深度和灌溉面積關系數(shù)據(jù)為例予以說明。錄入結果見下圖（圖1）。第二步，作散點圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點擊“圖表向導”圖標；或者在“插入”菜單中打開“圖表（H）”。圖表向導的圖標為強。選中數(shù)據(jù)后，數(shù)據(jù)變?yōu)樗{色（圖2）（office2003）。插入-圖表（office2007）MicrosoftExcel-連續(xù)10年盤犬積雪探度和灌陽面積的數(shù)據(jù)b遡文件?編輯⑥視圖邊插入①格式辺工具①數(shù)據(jù)?窗口址）y（ri>&電魏|幻?cJ書壬搖斛詛MJ底B1dl最大積雪深度譏米）ABTCD1年份最大積雪洙度或米）灌溉面積鞏千田）2197115.228.63197210.419.34197321.240.55197418.6X>66197526.448.97197623.4458197713.529.29197816.734.11019792446.711198019.137.4.1圖2點擊“圖表向導”以后，彈出如下對話框（圖3）：圖恚商導-4步驟之1-圖表類型標準類型自定義類型|團表類型Q： 子團表糞型②:按下不放可查看示例⑷I叵 取消_|€上一步K）|「下一步〉|完成迪|圖3在左邊一欄中選中“XY散點圖”，點擊“完成”按鈕，立即出現(xiàn)散點圖的原始形式（圖4）：

觀察散點圖，判斷點列分布是否具有線性趨勢。只有當數(shù)據(jù)具有線性分布特征時，才能采用線性回歸分析方法。從圖中可以看出，本例數(shù)據(jù)具有線性分布趨勢，可以進行線性回歸。回歸的步驟如下：⑴首先，打開“工具”下拉菜單，可見數(shù)據(jù)分析選項(見圖5)(office2003)。數(shù)據(jù)-數(shù)據(jù)分析(office2007)用鼠標雙擊“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框(圖6)：

圖6⑵然后，選擇“回歸”，確定，彈出如下選項表（圖7）：回歸T圖6⑵然后，選擇“回歸”，確定，彈出如下選項表（圖7）：回歸T值輸入區(qū)域①■廠標志（!）廠置信度辺廠[95輸出刪C輸出區(qū)域?）：燈新工作表組巴:r新工作薄述殘善I~話差團①）廠I~話差團①）廠線性擬舍冒（X）廠標唯贖差⑴正態(tài)分布廠正譽慨率圖?圖7進行如下選擇：X、Y值的輸入區(qū)域（B1:B11,C1:C11），標志，置信度（95%），新工作表組，殘差，線性擬合圖（圖8-1）?；蛘撸篨、Y值的輸入區(qū)域（B2:B11，C2:C11），置信度（95%）,新工作表組，殘差，線性擬合圖（圖8-2）。注意：選中數(shù)據(jù)“標志”和不選“標志”，X、Y值的輸入區(qū)域是不一樣的：前者包括數(shù)據(jù)標志：最大積雪深度灌溉面積x（米） y（千畝）后者不包括。這一點務請注意（圖8）。圖8-1包括數(shù)據(jù)“標志”圖8-2不包括數(shù)據(jù)“標志”⑶再后，確定，取得回歸結果（圖9）。圖9線性回歸結果⑷最后，讀取回歸結果如下：截距：a二2.356；斜率：b二1.813；相關系數(shù)：R二0.989；測定系數(shù)：R2=0.979；F值：F=371.945；t值：t=19.286；標準離差（標準誤差）：s二1.419；回歸平方和：SSr=748.854；剩余平方和：SSe二16.107；y的誤差平方和即總平方和：SSt二764.961。⑸建立回歸模型，并對結果進行檢驗模型為：y=2.356+1.813x至于檢驗，R、R2、F值、t值等均可以直接從回歸結果中讀出。實際上，R二0.989416>0.632二R ，檢驗通過。有了R值，F(xiàn)值和t值均可計算出來。F值的0.05,80.98941620.98941621=371.945>5.32=Fn-k-1（1-R2）10-1n-k-1（1-R2）顯然與表中的結果一樣。t值的計算公式和結果為:=19.286>=19.286>2.306=t0.05,8t= ==:1-R2 |1-0.979416n—k—1 10一1-1

方&2=（yii準離差為于是標-y)2，然后求殘差平方和S=X10e2方&2=（yii準離差為于是標i=、呼=1.4198=、呼=1.4198s=\：n-k-iY(yi-yi)2=YvH i=1于是s1.419-= =0.0388<10~15%=0.1?0.15y36.53觀測值翟溉面積y殘差殘差平方129.91284-1.31283811.723544機準離差￡221.21082-1.9103173.6512221.413923905340.79036-0.29036450.084312436.07677-0.47676970.22730^Ry的均值550.21755-1.3175541.吒59490.038842702644.778790.221209160.043^33726.830872.36912775.612766332.632221.467780292.15437^945.S66540.833456520.694651036.983230.416769730.173697殘差平方和16.106762.013345圖10y的預測值及其相應的殘差等進而，可以計算DW值（參見圖11），計算公式及結果為工(&-&)2DW iI (-1.911+1.313)2+…+(0.417-0.833)2DW=宀_ = =0.751工&2i— (-1.313)2+(-1.911)2+…工&2i殘差19殘差210殘差之差殘差之差的平方-1.312838-1.910817-0.597978S890.357578752-1.910817-0.2903651.6204525012.625866307-0.290365-0.47677-0.1864052320.03474691-0.47677-1.317554-0.8407843050.706918248-1.31花540.22120921.5387631942.3677921680.22120922.36912772.1479185414.6135540592.36912771.4677S03-0.9013474070.8124271491.46778030.8334565-0.6343237730.4023666490.33345650.4167697-0.4166367330.173627375DW值0.4167697殘差之差的平方和12.094878120.750919i=1取a=0.05，k=1，n=10（顯然v=10-1-1=8），查表得d=0.94l=1.29。顯然，DW二＜d=0.94，可見有序列正相關，預測的結果令人懷疑。圖12⑵點擊“添加趨勢線（R）”，彈出如下選擇框（圖13泵加趨勢線類型趨勢預測/回歸分析類型選項皋項式②階數(shù)on：圖12⑵點擊“添加趨勢線（R）”，彈出如下選擇框（圖13泵加趨勢線類型趨勢預測/回歸分析類型選項皋項式②階數(shù)on：?匚周期（B）:指數(shù)?移動平均血選擇數(shù)據(jù)系列⑤:灌關面積譏千畝）取消|圖13圖11利用殘差計算DW值（DW取值范圍0<DW<4.其統(tǒng)計學意義：當DW值愈接近2時，殘差項間愈無相關。當DW值愈接近0時，殘差項間正相關愈強。當DW值愈接近4時，殘差項間負相關愈強。）最后給出利用Excel快速估計模型的方法：⑴用鼠標指向圖4中的數(shù)據(jù)點列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）曜溉面積譏千畝）

圖14⑷在選擇框中選中“顯示公式（E）”和“顯示R平方值（R）”（如圖14）,確定，立即得到回歸結果如下（圖15）：圖14圖表標題*灌溉面積y（千畝）—線性（灌溉面積y（千畝））圖15在圖15中，給出了回歸模型和相應的測定系數(shù)即擬合優(yōu)度。順便說明殘差分析：如果在圖8中選中“殘差圖（D）”，則可以自動生成殘差圖（圖12）。XVariable1ResidualPlot圖16回歸分析原則上要求殘差分布是無趨勢的，如果在圖中添加趨勢線，則趨勢線應該是與x軸平行的，且測定系數(shù)很小。事實上，添加趨勢線的結果如下（圖17）：XVariable1ResidualPloty=-9E-15x+2E-13

R2=1E-27-110152025-110152025-2-3XVariable1圖17可見殘差分布圖基本滿足回歸分析的要求。預測分析雖然DW檢驗似乎不能通過，但這里采用的變量相關分析，與純粹的時間序列分析不同（時間序列分析應該以時間為自變量）。從殘差圖看來，模型的序列似乎并非具有較強的自相關性，因為殘差分布相當隨機。因此，仍有可能進行預測分析?，F(xiàn)在假定：有人在1981年測得最大積雪深度為米，他怎樣預測當年的灌溉面積下面給出Excel2000的操作步驟：⑴在圖9所示的回歸結果中，復制回歸參數(shù)（包括截距和斜率），然后粘帖到圖1所示的原始數(shù)據(jù)附近；并將1981年觀測的最大積雪深度寫在1980年之后（圖18）。

[aCDEF1年份最丈積雪深度或米）灌漑面積y（千旬計第值Coefficients2197115.26I!lintereept2.3564379293197210.419.3最大積雪深度孟（米）1.8129210654197321.240.551974；18.635.66197526.44E.97197623.i45E197713.5翻.29197816.734.110197924：46.711198013.137.412198127.5圖18⑵將光標至于圖18所示的D2單元格中，按等于號“=”，點擊F2單元格（對應于截距a二…），按F4鍵，按加號“+”，點擊F3單元格（對應于斜率b二…），按F4鍵，按乘號“*”，點擊B2單元格（對應于自變量X?，于是得到表達式“二$F$2+$F$3*B2”圖19）,相當于表達式y(tǒng)1=a+b*X］，回車，立即得到y(tǒng)i=29.9128，即1971年灌溉面積的計算值。 1 1 1LaBCDE|F年份lm最大積雪深度潛）灌溉面積鞏千畝）. 15.5 2S.6計算值Coefficiente2.356437929310.419.3最丸積雪深度x〔米）1.812921065419殆21.240.5519恥18.635.66197526.44S.97197&23.445S19〃13.529.291^7316.734.11019旳2446.711198019.137.412158127.5圖19⑶將十字光標標至于D2單元格的右下角，當粗十字變成細十字以后，按住鼠標左鍵，往下一拉，各年份的灌溉面積的計算值立即出現(xiàn)，其中1981年對應的D12單元格的即我們所需要的預測數(shù)據(jù)，即有yi1=52.212千畝（圖20）。

LaLD|E1年份最大積雪深度龍米）灌概面積貳千畝）計算值匚oefficiente2197115.228.e29.913Intercept2.3564379293197210.419.321.211最丈積雪深度尤（米）1.B129210654197321.240.540.795197418.a35.636.0776197526.448.950.2107197623.44544.7798197713.529.226.S319197B1&.f34.132.6321019792446.745.8671119S019?137.436.9B31219B127.552.212圖20LaBCDEF1年份最大積雪深度臥米：）灌概面積貳千助計算值Coefficients2197115.22S.629.913In.tercept2.3564379293197210.419.321.211最大積雪深度n（米）1.8129210654157321.240.540.79519M18.635.636.077619陽26.44S.950.2187197623.44544.779S197713.529.226.8319197816.734.132.6321019792446