(1)用Excel作一元線性回歸分析

1、 實(shí)驗(yàn)四（1） 用Excel作一元線性回歸分析實(shí)驗(yàn)名稱：回歸分析實(shí)驗(yàn)?zāi)康模簩W(xué)會(huì)應(yīng)用軟件實(shí)驗(yàn)一元線性回歸，多元線性回歸和非線性回歸模型的求解及應(yīng)用模型解決相應(yīng)地理問(wèn)題。1 利用Excel進(jìn)行一元線性回歸分析 第一步，錄入數(shù)據(jù) 以連續(xù)10年最大積雪深度和灌溉面積關(guān)系數(shù)據(jù)為例予以說(shuō)明。錄入結(jié)果見(jiàn)下圖（圖1）。圖1第二步，作散點(diǎn)圖如圖2所示，選中數(shù)據(jù)（包括自變量和因變量），點(diǎn)擊“圖表向?qū)А眻D標(biāo)；或者在“插入”菜單中打開(kāi)“圖表（H）”。圖表向?qū)У膱D標(biāo)為。選中數(shù)據(jù)后，數(shù)據(jù)變?yōu)樗{(lán)色（圖2）（office2003）。插入-圖表（office2007)圖2點(diǎn)擊“圖表向?qū)А币院?，彈出如下?duì)話框（圖3）：圖3 在左

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

3、），新工作表組，殘差，線性擬合圖（圖8-1）?；蛘撸篨、Y值的輸入?yún)^(qū)域（B2:B11，C2:C11），置信度（95%）， 新工作表組，殘差，線性擬合圖（圖8-2）。注意：選中數(shù)據(jù)“標(biāo)志”和不選“標(biāo)志”，X、Y值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標(biāo)志：最大積雪深度x(米)灌溉面積y(千畝)后者不包括。這一點(diǎn)務(wù)請(qǐng)注意（圖8）。圖8-1 包括數(shù)據(jù)“標(biāo)志”圖8-2 不包括數(shù)據(jù)“標(biāo)志” 再后，確定，取得回歸結(jié)果（圖9）。圖9 線性回歸結(jié)果 最后，讀取回歸結(jié)果如下：截距：；斜率：；相關(guān)系數(shù)：；測(cè)定系數(shù)：；F值：；t值：；標(biāo)準(zhǔn)離差（標(biāo)準(zhǔn)誤差）：；回歸平方和：；剩余平方和：；y的誤差平方和即總平方和：。 建立

4、回歸模型，并對(duì)結(jié)果進(jìn)行檢驗(yàn) 模型為：至于檢驗(yàn)，R、R2、F值、t值等均可以直接從回歸結(jié)果中讀出。實(shí)際上，檢驗(yàn)通過(guò)。有了R值，F(xiàn)值和t值均可計(jì)算出來(lái)。F值的計(jì)算公式和結(jié)果為： 顯然與表中的結(jié)果一樣。t值的計(jì)算公式和結(jié)果為： 回歸結(jié)果中給出了殘差（圖10），據(jù)此可以計(jì)算標(biāo)準(zhǔn)離差。首先求殘差的平方，然后求殘差平方和，于是標(biāo)準(zhǔn)離差為于是圖10 y的預(yù)測(cè)值及其相應(yīng)的殘差等進(jìn)而，可以計(jì)算DW值（參見(jiàn)圖11），計(jì)算公式及結(jié)果為取，（顯然），查表得，。顯然，DW=0.751，可見(jiàn)有序列正相關(guān)，預(yù)測(cè)的結(jié)果令人懷疑。圖11 利用殘差計(jì)算DW值 （DW取值范圍<DW<4.其統(tǒng)計(jì)學(xué)意義：當(dāng)DW值愈接近2時(shí)

5、，殘差項(xiàng)間愈無(wú)相關(guān)。當(dāng)DW值愈接近0時(shí)，殘差項(xiàng)間正相關(guān)愈強(qiáng)。當(dāng)DW值愈接近4時(shí)，殘差項(xiàng)間負(fù)相關(guān)愈強(qiáng)。） 最后給出利用Excel快速估計(jì)模型的方法： 用鼠標(biāo)指向圖4中的數(shù)據(jù)點(diǎn)列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）：圖12 點(diǎn)擊“添加趨勢(shì)線(R)”，彈出如下選擇框（圖13）：圖13 在“分析類型”中選擇“線性(L)”，然后打開(kāi)選項(xiàng)單（圖14）：圖14 在選擇框中選中“顯示公式(E)”和“顯示R平方值(R)”（如圖14），確定，立即得到回歸結(jié)果如下（圖15）：圖15 在圖15中，給出了回歸模型和相應(yīng)的測(cè)定系數(shù)即擬合優(yōu)度。 順便說(shuō)明殘差分析：如果在圖8中選中“殘差圖(D)”，則可以自動(dòng)生成殘差圖（圖

6、12）。圖16回歸分析原則上要求殘差分布是無(wú)趨勢(shì)的，如果在圖中添加趨勢(shì)線，則趨勢(shì)線應(yīng)該是與x軸平行的，且測(cè)定系數(shù)很小。事實(shí)上，添加趨勢(shì)線的結(jié)果如下（圖17）：圖17可見(jiàn)殘差分布圖基本滿足回歸分析的要求。 預(yù)測(cè)分析 雖然DW檢驗(yàn)似乎不能通過(guò)，但這里采用的變量相關(guān)分析，與純粹的時(shí)間序列分析不同（時(shí)間序列分析應(yīng)該以時(shí)間為自變量）。從殘差圖看來(lái)，模型的序列似乎并非具有較強(qiáng)的自相關(guān)性，因?yàn)闅埐罘植枷喈?dāng)隨機(jī)。因此，仍有可能進(jìn)行預(yù)測(cè)分析?，F(xiàn)在假定：有人在1981年測(cè)得最大積雪深度為27.5米，他怎樣預(yù)測(cè)當(dāng)年的灌溉面積？ 下面給出Excel2000的操作步驟： 在圖9所示的回歸結(jié)果中，復(fù)制回歸參數(shù)（包括截距和

7、斜率），然后粘帖到圖1所示的原始數(shù)據(jù)附近；并將1981年觀測(cè)的最大積雪深度27.5寫在1980年之后（圖18）。圖18 將光標(biāo)至于圖18所示的D2單元格中，按等于號(hào)“”，點(diǎn)擊F2單元格（對(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á)式，回車，立即得到，即1971年灌溉面積的計(jì)算值。圖19 將十字光標(biāo)標(biāo)至于D2單元格的右下角，當(dāng)粗十字變成細(xì)十字以后，按住鼠標(biāo)左鍵，往下一拉，各年份的灌溉面積的計(jì)算值立即出現(xiàn)，其中1981年對(duì)應(yīng)的D12單元格的52.212即我們所需要的預(yù)測(cè)數(shù)據(jù)，即有千畝（圖20）。圖20 進(jìn)一步地，如果可以測(cè)得1982年及其以后各年份的數(shù)據(jù)，