基于Excel的一元線性回歸方程的建立

黃基廷　趙麗棉

摘要：通過實例說明在Excel平臺上建立一元線性回歸方程常用的方法和步驟，說明了借助Excel，幾乎可以完成所有統(tǒng)計數(shù)據(jù)分析與處理，且利用不同的方法建立的一元線性回歸方程都是一樣的，我們可以根據(jù)需要選擇不同的方法。

關鍵詞：Excel平臺；一元線性；回歸方程

中圖分類號：0212.4；G642.0 文獻標志碼：A 文章編號：1674-9324（2015）05-0203-02

在社會經濟現(xiàn)象中，數(shù)量之間的變化常常是按比例變化，而且許多現(xiàn)象非線性的變化在較短的時間內也近似于線性變化，可以利用線性方法分析，這給數(shù)學運算帶來很大方便，所以通過建立簡單的一元線性回歸方程進行回歸分析是統(tǒng)計學中常用的方法。本文將通過實例說明如何在Excel平臺上利用不同的方法建立一元線性回歸方程。

1 一元線性回歸方程的測定

測定兩組變量和是否存在線性關系，可以通過二條途徑：

一是畫散點圖，看數(shù)據(jù)點是否是大致沿直線分布；

二是計算兩組變量的相關系數(shù)：

一般地，當0.8≤|r|<1時，認為x與y之間存在高度線性相關，可以建立一元線性回歸方程。

2 未知參數(shù)的確定

未知參數(shù)的確定最常用的方法是最小二乘法，即實際值y與相應估計值的離差平方和最小，設一元線性回歸方程為=a+bx，則Q=∑（y-a-bx）2，分別令Q對a，b的偏導數(shù)等于0得：

∑y=na+b∑x∑xy=a∑x+b∑x2 （2）

解此方程組即可得a，b的估計值。

3 基于Excel的一元線性回歸方程的建立

例 為研究某一化學反應過程中，溫度x（℃）對產品得率Y（%）的影響，測得數(shù)據(jù)并輸入到Excel工作表中（圖1）。

這里x是普通變量，Y是隨機變量，求Y關于x的回歸方程。

3.1 回歸方程的測定

3.1.1 畫散點圖

第一步，拖動鼠標選定數(shù)值區(qū)域A2：B11，不包括數(shù)據(jù)上面的標志項。

第二步，選擇“插入”菜單的“圖表”子菜單，進入圖表向導。

第三步，選擇“圖表類型”為“散點圖”，然后單擊“下一步”。

第四步，繼續(xù)單擊“下一步”，選擇“標題”下的子項“圖表標題”，在其中輸入“產品得率與溫度”，在“數(shù)值（X）軸（A）”子項中輸入“溫度”，在“數(shù)值（Y）軸（V）”子項中輸入“溫度”。單擊“完成”，即生成圖2所示結果。

從散點圖可以看出，產品得率Y與溫度之間存在著正的線性相關關系，可以計算它們的相關系數(shù)來確定兩者相關的密切程度。

3.1.2 計算相關系數(shù)

第一步，用鼠標單擊C2單元，輸入“=A2^2”，回車得第一個x的平方值，然后將鼠標指針移至C2單元格右下角的小方塊（填充柄）上，當指針變成+形時按住鼠標左鍵往下拖拽，至C11單元格放開鼠標，得所有x的平方值，類似地求出所有y的平方值。

第二步，在E2單元中輸入“=A2*B2”，回車得第一個x與y的積，類似求出所有x與y的積。

第三步，利用工具欄中的“∑”，求得∑x=1450，∑y=670，∑xy=101570，∑x2=218500，∑y2=47225。

第四步，將數(shù)據(jù)代入公式（1）求得r=0.998129

結果表明產品得率與溫度之間存在高度線性正相關關系，可以建立一元線性回歸方程。

3.2 回歸方程的建立

3.2.1 用常規(guī)方法建立一元線性回歸方程

利用3.1.2算出的數(shù)據(jù)和公式（2），求得：b=0.4830，a=-2.7394即可確定回歸方程為=-2.7394+0.483x

3.2.2 用“添加線性趨勢線”建立一元線性回歸方程

接3.1.1中的第四步，在圖2中，用鼠標對準任一數(shù)據(jù)點，單擊右鍵，選擇“添加趨勢線”，在“類型”選項卡中選擇“線性”，在“選項”選項卡中單擊“顯示公式”和“顯示R平方”復選框，再單擊“確定”即可得如圖3結果：

結果表明所求的回歸方程也是：

=-2.7394+0.483x，R2=0.9963

3.2.3 運用數(shù)據(jù)分析工具建立一元線性回歸方程

在Excel的數(shù)據(jù)分析工具中，有一個專用于進行回歸分析的工具。使用此工具，可以更加方便快捷準確地進行回歸分析，并能提供更多的數(shù)據(jù)信息。

第一步，用鼠標點擊工作表中待分析數(shù)據(jù)的任一單元格，選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，用鼠標雙擊“回歸”選項，進入回歸對話框。

第二步，在回歸對話框中，在“y值輸入?yún)^(qū)域”框中輸入B2：B11，在“x值輸入?yún)^(qū)域”框中輸入A2：A11，選中“標志”復選框，在“輸出區(qū)域”中輸入D2，選中“殘差”、“標準殘差”、“線性擬合圖”等復選框。

第三步，單擊“確定”按鈕，即在以D2為起點的右邊空白區(qū)域給出結果（圖4）。

結果表明，趨勢方程為：=-2.7394+0.483x

從判定系數(shù)看，在溫度對產品得率的影響中，有99%可由該回歸方程解釋。從檢驗看，回歸系數(shù)的p值遠遠小于0.05，表明是顯著的。從F檢驗看，Significance F為5.35E-11，表明該模型通過了5%的顯著性檢驗，模型整體也是顯著的。所以回歸方程是合適的。

從以上過程可看到，借助Excel，幾乎可以完成所有統(tǒng)計數(shù)據(jù)分析與處理，且利用不同的方法建立的一元線性回歸方程都是一樣的，我們可以根據(jù)需要選擇不同的方法。

參考文獻：

[1]盛驟，等.概率論與數(shù)理統(tǒng)計[M].北京：高等教育出版社，2001：294-313.

[2]范秀榮，等.統(tǒng)計學原理[M].北京：高等教育出版社，2010：197-203.

[3]王劍武，等.統(tǒng)計學[M].長沙：湖南師范大學出版社，2014：192-198.

[4]黃應繪，等.統(tǒng)計學實驗[M].成都：西南財經大學出版社，2009：111-121.

[5]馬軍.excel統(tǒng)計分析典型實例[M].北京：清華大學出版社，2009：221-244.