黃基廷　趙麗棉

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

關(guān)鍵詞：Excel平臺；一元線性；回歸方程

中圖分類號：0212.4；G642.0 文獻(xiàn)標(biāo)志碼：A 文章編號：1674-9324（2015）05-0203-02

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

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

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

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

二是計(jì)算兩組變量的相關(guān)系數(shù)：

一般地，當(dāng)0.8≤|r|<1時(shí)，認(rèn)為x與y之間存在高度線性相關(guān)，可以建立一元線性回歸方程。

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

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

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

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

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

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

這里x是普通變量，Y是隨機(jī)變量，求Y關(guān)于x的回歸方程。

3.1 回歸方程的測定

3.1.1 畫散點(diǎn)圖

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

第二步，選擇“插入”菜單的“圖表”子菜單，進(jìn)入圖表向?qū)А?/p>

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

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

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

3.1.2 計(jì)算相關(guān)系數(shù)

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

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

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

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

結(jié)果表明產(chǎn)品得率與溫度之間存在高度線性正相關(guān)關(guān)系，可以建立一元線性回歸方程。

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中，用鼠標(biāo)對準(zhǔn)任一數(shù)據(jù)點(diǎn)，單擊右鍵，選擇“添加趨勢線”，在“類型”選項(xiàng)卡中選擇“線性”，在“選項(xiàng)”選項(xiàng)卡中單擊“顯示公式”和“顯示R平方”復(fù)選框，再單擊“確定”即可得如圖3結(jié)果：

結(jié)果表明所求的回歸方程也是：

=-2.7394+0.483x，R2=0.9963

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

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

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

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

[4]黃應(yīng)繪，等.統(tǒng)計(jì)學(xué)實(shí)驗(yàn)[M].成都：西南財(cái)經(jīng)大學(xué)出版社，2009：111-121.

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