用Excel做線性回歸分析

用Excel進行一元線性回

歸分析時間：2021.02.09創(chuàng)作人：歐陽歷Excel功能強大，利用它的分析工具和函數，可以進行各種試驗數據的多元線性回歸分析。本文就從最簡單的一元線性回歸入手.在數據分析中，對于成對成組數據的擬合是經常遇到的，涉及到的任務有線性描述，趨勢預測和殘差分析等等。很多專業(yè)讀者遇見此類問題時往往尋求專業(yè)軟件，比如在化工中經常用到的Origin和數學中常見的MATLAB等等。它們雖很專業(yè)，但其實使用Excel就完全夠用了。我們已經知道在Excel自帶的數據庫中已有線性擬合工具，但是它還稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對此類數據進行處理。文章使用的是2000版的軟件,我在其中的一些步驟也添加了2007版的注解.1利用Excel2000進行一元線性回歸分析首先錄入數據.以連續(xù)10年最大積雪深度和灌溉面積關系數據為例予以說明。錄入結果見下圖（圖1）O圖1第二步，作散點圖如圖2所示，選中數據（包括自變量和因變量），點擊圖表向導”圖標；或者在插入嗓單中打開圖表（H）（excel2007）圖表向導的圖標為璽。選中數據后，數據變?yōu)樗{色（圖2）。圖2點擊圖表向導'以后，彈出如下對話框（圖3）:圖3在左邊一欄中選中眷散點圖7點擊憲成按鈕，立即出現散點圖的原始形式（圖4）：圖4第三步，回歸觀察散點圖，判斷點列分布是否具有線性趨勢。只有當數據具有線性分布特征時，才能采用線性回歸分析方法。從圖中可以看岀，本例數據具有線性分布趨勢，可以進行線性回歸。回歸的步驟如下：（1） 首先，打開工具'下拉菜單，可見數據分析選項（見圖5） （2007為'數據右端的'數據分析J：圖5用鼠標雙擊數據分析'選項，彈岀數據分析'對話框（圖6） :圖6（2） 然后，選擇回歸7確定，彈出如下選項表：圖7進行如下選擇:X、Y值的輸入區(qū)域（Bl:BlbC1:C11）,標志，置信度（95%）,新工作表組，殘差，線性擬合圖?；蛘撸篨、Y值的輸入區(qū)域（B2:B11,C2:C11）,置信度（95%）,新工作表組，殘差，線性擬合圖。注意：選中數據標志'和不選林志X、Y值的輸入區(qū)域是不一樣的：前者包括數據標志：最大積雪深度x（米）灌溉面積y（千畝）后者不包括。這一點務請注意。圖包括數據標志”圖不包括數據標志”（3） 再后，確定，取得回歸結果（圖9）。圖9線性回歸結果⑷最后，讀取回歸結果如下：截距：g=2.356;斜率：b=1.813;相關系數：R=0.989;測定系數：R2=0.979;F值：F=371.945。（5）建立回歸模型，并對結果進行檢驗模型為：y=2.356+1.813x至于檢驗，R、R2和F值可以直接從回歸結果中讀出。實際上，R=0.989416>0.632=嘔.8，檢驗通過。有了R值，F值和t值均可計算出來。F值的計算公式和結果為：顯然與表中的結果一樣。（值的計算公式和結果為：回歸結果中給出了殘差（圖10），據此可以計算標準離差。首先求殘差的平方昇=?-刃尸，然后求殘差平方和=1.724+…+0.174=16.107,于是標準離差為/-I于是圖10y的預測值及其相應的殘差等進而，可以計算DW值（參見圖11），計算公式及結果為取a=0.05,k=\,/?=10（顯然v=10-l-l=8）,查表得dt=0.94,du=1.29o顯然，DW二0?751</=0.94,可見有序列正相關，預測的結果令人懷疑。圖11利用殘差計算DW值最后給岀利用Excel快速估計模型的方法：⑴用鼠標指向圖4中的數據點列，單擊右鍵，岀現如下選擇菜單（圖12）:圖12⑵點擊添加趨勢線（R）：彈出如下選擇框（圖13）:圖13⑶在份析類型'中選擇線性（L）：然后打開選項單（圖14）:圖14⑷在選擇框中選中顯示公式（E）'和顯示R平