用EXCEL做線性回歸分析(2000與)

1、用excel進行一元線性回歸分析excel功能強大，利用它的分析工具和函數(shù)，可以進行各種試驗數(shù)據(jù)的多元線性回歸分析。本文就從最簡單的一元線性回歸入手.在數(shù)據(jù)分析中，對于成對成組數(shù)據(jù)的擬合是經(jīng)常遇到的，涉及到的任務有線性描述，趨勢預測和殘差分析等等。很多專業(yè)讀者遇見此類問題時往往尋求專業(yè)軟件，比如在化工中經(jīng)常用到的origin和數(shù)學中常見的matlab等等。它們雖很專業(yè)，但其實使用excel就完全夠用了。我們已經(jīng)知道在excel自帶的數(shù)據(jù)庫中已有線性擬合工具，但是它還稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對此類數(shù)據(jù)進行處理。文章使用的是2000版的軟件,我在其中的一些步驟也添加了200

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

3、趨勢，可以進行線性回歸?；貧w的步驟如下： 首先，打開“工具”下拉菜單，可見數(shù)據(jù)分析選項（見圖5）(2007為”數(shù)據(jù)”右端的”數(shù)據(jù)分析”)：圖5用鼠標雙擊“數(shù)據(jù)分析”選項，彈出“數(shù)據(jù)分析”對話框（圖6）：圖6 然后，選擇“回歸”，確定，彈出如下選項表：圖7進行如下選擇：x、y值的輸入?yún)^(qū)域（b1:b11，c1:c11），標志，置信度（95%），新工作表組，殘差，線性擬合圖?；蛘撸簒、y值的輸入?yún)^(qū)域（b2:b11，c2:c11），置信度（95%）， 新工作表組，殘差，線性擬合圖。注意：選中數(shù)據(jù)“標志”和不選“標志”，x、y值的輸入?yún)^(qū)域是不一樣的：前者包括數(shù)據(jù)標志：最大積雪深度x(米)灌溉面積y(千畝

4、)后者不包括。這一點務請注意。圖8-1 包括數(shù)據(jù)“標志”圖8-2 不包括數(shù)據(jù)“標志” 再后，確定，取得回歸結果（圖9）。圖9 線性回歸結果 最后，讀取回歸結果如下：截距：；斜率：；相關系數(shù)：；測定系數(shù)：；f值：。 建立回歸模型，并對結果進行檢驗 模型為：至于檢驗，r、r2和f值可以直接從回歸結果中讀出。實際上，檢驗通過。有了r值，f值和t值均可計算出來。f值的計算公式和結果為：顯然與表中的結果一樣。t值的計算公式和結果為： 回歸結果中給出了殘差（圖10），據(jù)此可以計算標準離差。首先求殘差的平方，然后求殘差平方和，于是標準離差為于是圖10 y的預測值及其相應的殘差等進而，可以計算dw值（參見圖1

5、1），計算公式及結果為取，（顯然），查表得，。顯然，dw=0.751，可見有序列正相關，預測的結果令人懷疑。圖11 利用殘差計算dw值最后給出利用excel快速估計模型的方法： 用鼠標指向圖4中的數(shù)據(jù)點列，單擊右鍵，出現(xiàn)如下選擇菜單（圖12）：圖12 點擊“添加趨勢線(r)”，彈出如下選擇框（圖13）：圖13 在“分析類型”中選擇“線性(l)”，然后打開選項單（圖14）：圖14 在選擇框中選中“顯示公式(e)”和“顯示r平方值(r)”（如圖14），確定，立即得到回歸結果如下（圖15）：圖15在圖15中，給出了回歸模型和相應的測定系數(shù)即擬合優(yōu)度。2007回歸分析使用excel2007的“分析工具

6、庫”做多元回歸分析首先，要求你的excel2007是企業(yè)版或者專業(yè)版的（其他版本是否有此功能我不能確定），通常，在安裝excel2007的時候，選擇“全部在硬盤中運行”就會使你的excel具有“分析工具庫”。如果你的excel沒有這個功能，請按照下面的操作添加此功能：1. 打開控制面板（在windowsxp中，win7的操作應該與此類似），找到office。點擊“更改”。2. 選中“添加或刪除功能”。然后點繼續(xù)。3. 在隨后出現(xiàn)的界面中，點開excel選項，選擇“在本機運行全部程序”。office此時會進入安裝界面，所以此時你必須插入office的安裝盤，或者幫助計算機找到office的源文件。裝好之后，你的excel2007應該具備“分析工具庫”了。第二步，激活excel2007的分析工具庫。這才是我們的正事兒。1. 打開excel2007。點擊紅圈圈這個地方。會出現(xiàn)一個下拉菜單。在這個菜單的最底部，有“excel選項”這個按鈕，點擊之。2. 在隨后的界面中，點擊加載項；看到管理“excel加載項”，點旁邊的“轉到”按鈕。3. 在出現(xiàn)的界面中，勾選“分析工具庫”，然后點確定?！胺治龉ぞ邘臁本桶惭b成功了。4. 打開你要做回歸的excel工作表。點菜單上的“數(shù)據(jù)”以及“數(shù)據(jù)分析”按鈕。5. 選擇“回歸”6. 分別輸入y值區(qū)域和x值區(qū)域