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

黃基廷　趙麗棉

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

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

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

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

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

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

一是畫散點圖，看數據點是否是大致沿直線分布；

二是計算兩組變量的相關系數：

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

2 未知參數的確定

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

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

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

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

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

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

3.1 回歸方程的測定

3.1.1 畫散點圖

第一步，拖動鼠標選定數值區域A2：B11，不包括數據上面的標志項。

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

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

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

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

3.1.2 計算相關系數

第一步，用鼠標單擊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。

第四步，將數據代入公式（1）求得r=0.998129

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

3.2 回歸方程的建立

3.2.1 用常規方法建立一元線性回歸方程

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

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

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

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

=-2.7394+0.483x，R2=0.9963

3.2.3 運用數據分析工具建立一元線性回歸方程

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

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

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

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

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

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

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

參考文獻：

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

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

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

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

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