用Excel做線性回歸分析

用Excel進行一元線性回

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