Excel在一元線性回歸分析中的應用

楊雄 曾智

（婁底職業技術學院，湖南 婁底 417000）

回歸分析是在研究現象之間相關分析的基礎上，對自變量x和因變量y的變動趨勢擬合數學模型進行數量推算的一種統計分析方法[1]。在客觀世界中，尋找變量之間的關系，大致可以分為兩種類型：一是反映變量之間的確定性的關系，稱為函數關系；二是變量之間存在著關系，但不是確切的函數關系，可是變量之間又存在某種密切關系，然而又不能由一個（或一組）變量的值精確地求出另一個變量的值，稱這種非確定性關系為相關關系。在相關關系中，假設x,y是兩個變量，其中x是自變量，y是因變量，而自變量x的取值是非隨機的普通變量，它是人為的可控制的變量，稱為可控量，因變量y由于隨機誤差等因素的影響，取值是隨機的，稱為隨機變量，但服從一定的概率分布。進而當自變量x是非隨機的可控變量時，自變量x與因變量y關系的分析稱為回歸分析。

回歸分析法屬于因素分析法的一種，在掌握大量觀察數據或歷史數據的基礎上，利用數理統計方法建立因變量y與自變量x之間的回歸關系函數表達式。在有些專業中，開設了經濟數學課，包含一元回歸分析內容，其中會計專業課會講到成本預測，成本預測需要建立回歸方程，但在成本預測的計算中面對復雜的數據，同時涉及要素也繁多，此項工作任務繁重，因此需要借助相應工具來簡化計算提高工作效率。而運用Excel軟件能夠把煩瑣、主觀的核算與分析內容簡單化、客觀化、圖表化，這無疑是一種較好的方法。而且使工作更加便利、快捷，并能有效減少錯誤發生的概率。因此以成本預測為案例，對回歸分析內容及應用Excel進行回歸分析的實際操作進行研究。

1 一元線性回歸模型建立過程及定義

1.1 回歸分析的建立過程

回歸分析是利用歷史數據或觀察數據對模型中的函數值f(x)進行估算，探討隨機誤差項的分布特征，進而應用模型進行預算，一般建立一個回歸分析方程包括以下幾個過程：通過樣本數據，判定因變量x與自變量y的關系，確定回歸模型的f(x)的函數形式；利用樣本數據擬合回歸模型的f(x)中的未知參數；確定估計量與隨機誤差的分布特征；進行擬合優度檢驗，驗證是用歷史數據或觀察數據x值對預測y值的擬合程度；利用回歸模型進行未來預測或控制。預測是通過回歸方程，對已知的值進行相應值計算，而在回歸方程中參數的計算、分析及預測值的計算運用傳統的數學方法實踐起來困難，可運用Excel進行替代，這正是運用Excel進行回歸分析的意義所在。

1.2 一元線性回歸模型的定義

定義1設x是可控變量，Y是依賴于x的隨機變量，假定

其中未知參數a,b及σ2都不依賴于x，則（1）式稱為一元線性回歸模型[2]。

定義2當給定x一組不完全相同的值x1,x2,…,xn時，對Y分別在x1,x2,…,xn處進行獨立觀測，其觀察結果記為Y1,Y2,…,Yn，則Y1,Y2,…,Yn是相互獨立的隨機變量，則稱(x1,Y1),(x2,Y2),…,(xn,Yn)是模型（1）的一個樣本，相應的樣本值記為(x1,y1),(x2,y2),…,(xn,yn)。

此時，由模型（1）式有

則（2）式稱為一元線性回歸模型（1）式的樣本形式，也稱為一元線性回歸模型。由(xi,yi)(i=1,2,…,n)可求得（1）式中的未知參數a,b的估計值，再代入（1）式中，進而可得回歸方程。

1.3 a,b值的具體求解過程

在一元線性回歸分析中，y的值是隨著x的值變化而變化，事實上一個實際的x值會對應一個實際y值（稱y實際），若x與y存在直線關系，想求出此條直線方程，每一個實際x值有一個直線預測值（稱預測）與之對應，進而進行線性回歸分析的目的就是要求y實際與預測之差的平方和最小，即下式的和最小。

Q分別對a,b求偏導數，并令偏導數等于0，解方程組可得：，把a,b值代入直線方程，即得回歸方程[3]。

2 模型的提出及判定是否存在線性相關性

2.1 回歸分析成本預測模型的提出

在回歸分析模型建立之前，先探討一個成本預測模型，若已知某企業2010年—2019年的產量與實際成本數據如圖1（產量單位萬件，成本單位萬元），根據成本與產量的數據，是否可以發現什么內在的規律，假設存在規律，是否可以根據規律及假設2021年的產量估算成本。不防假設預測模型如下（也有可能是其他函數模型，這是只分析簡單的一次函數模型）：Yt=a+bxt+ε。

圖1 某企業2010年-2019年的產量與成本圖

其中：Yt是總成本，a固定成本，b是單位業務量所需的變動成本，ε為殘差，xt是產量。若能利用2010年?2019年的數據確定預測模型中的a、b數值，則就可以預測一定產量x所需發生的總成本Y。接下來的問題就是如何求出a、b數值，若求出a、b的值，且假設2021年生產產品3 000萬件時，就可以預測其成本。

2.2 相關系數的定義及其判斷變量之間線性關系程度

以上提出的問題，并不一定保證產量與實際成本是線性關系，即兩個變量之間是否存在一元直線回歸方程的形式，首先應判斷怎樣的兩個變量之間才有可能存在一元直線回歸關系，只有存在線性關系的兩個變量，求出的一元線性回歸方程，在實際應用中才有意義。進而要對變量之間的線性相關的緊密程度進行判斷，其中相關系數R或R2就是判斷兩個變量之間線性相關的密切程度的[4]。并且R和R2分別由定義3和定義4給出。

R的取值范圍是[?1,1]，R的絕對值越接近于1，x與y兩個變量之間的線性相關性越強，R的絕對值越接近于0，x與y兩個變間之間的線性相關性越弱。相關系數R在Excel中有三種計算方法：Correl函數；Pearson函數；使用數據分析工具，即點擊數據選項卡下數據分析功能，在彈出的對話框中的相應輸入區域選擇數據范圍，選擇輸出區域為期望放置結果的位置，回車后輸出結果。具體判斷變量之間的性線關系程度見表1。

表1 相關程度

若SSE=0，則R2=1，即y的變化完全由x的變化引起，沒有其他的因素影響y，因此可由x完全解釋y的變化，也就是變成了一次函數關系。若R2靠近0，則x與y之間可能不存在線性關系。

2.3 判斷產量與成本是否存在線性關系

在Excel工作表中輸入數據圖1，并作出散點圖圖2，從散點圖2上可以看出產量與實際成本呈現出直線趨勢。這只是定性的判斷了產量與成本具有線性關系，還需要進行定量的計算。Ex?cel軟件中需有“數據分析”工具，若沒有，需要先安裝。安裝方法：在“Excel選項”中選中左側欄的“加載項”，再單擊右側欄最下面的“轉到”按鈕，在彈出的“加載宏”窗口中選中“分析工具庫”選項，單擊“確定”按鈕，按系統提示自動安裝，安裝完成后，重啟Excel系統，再打開，則在“數據”菜單下出現“數據分析”工具[6]。然后進行后面二步操作，點擊數據工具中相關系數，選擇數據以及輸出區域，點擊確定，即可出現圖3的結果。從圖3可知相關系數R=0.976054，說明產量與實際成本具有高度線性相關性。既然產量與成本具有高度的線性關系，則可以用Excel求解一元線性回歸方程。

圖2 原始數據散點圖

圖3 相關系數計算結果

3 運用Excel進行一元回歸分析實例操作

3.1 應用Excel中的函數計算回歸方程

用函數Slope（y值數列，x值數列），返回線性回歸直線的斜率a，用函數Intercept（y值數列，x值數列），返回截距b，或用函數Linest（y值數列，x值數列，邏輯值（常數），邏輯值（統計）），可以直接求出回歸方程的參數。這里例舉Linest函數的應用，如圖4選定E1：F5，在Excel地址欄中輸入=Linest(C2:C11,B2:B11,true,true)，然后同時按下組合鍵ctrl+shift+enter，進而得到圖4的結果，可以讀出a=139.493,b=0.0264，即回歸方程為

y=139.493+0.0264x

圖4 Linest函數操作圖

3.2 在Excel中應用數據分析工具求解回歸方程及其輸出數據的解釋

根據圖1的數據，在Excel中，應用數據分析工具中的回歸分析，可以求出相關系數和回歸直線方程。在數據分析工具中打開回歸對話框，如圖5，在Y值輸入區域輸入$C$2:$C$11（可以直接選取區域），在X值輸入區域輸入$B$2:$B$11（可以直接選取區域），并指定輸出區域，勾選殘差、線性擬合圖，然后確定，進而得到表2、3、5、6及圖6、7分析結果。從表2中可以看出相關系數R2=0.952682，調整后值為0.946767，兩者數據都接近于1，則可知產量和成本具有高度線性關系。也可以讀取F的檢驗值為161.07，而F0.05(1,10?2)=5.32＜161.07，則可知產量x與成本y的線性回歸方程顯著。同時可讀取a=139.493,b=0.0264，進而可得回歸方程為y=139.493+0.0264x。

圖5 建立回歸模型

以下對在Excel中數據分析結果的參數及圖進行詳細解讀。

從表2可以讀出相關系數、測定系數、校正測定系數、標準誤差和樣本數目。相關系數是Multiple對應的值，即R=0.976054399；測定系數（或稱擬合優度）是R Square對應值，即R2=0.9760543992=0.952682189；校正測定系數是Adjusted對應值，即Ra=0.946767463，標準誤差（standard error）的值是S=1.816038761；樣本數目是觀測值，即n=10。當然其中有些值可直接由公式計算。

表2 回歸統計表

1）校正測定系數可以用公式（3）計算。

其中n是樣本數，m是變量數，R2是測定系數，對于本回歸模型，n=10,m=1,R2=0.952682189，將這些值代入（3）式即可得校正測定系數Ra值。

2）標準誤差可以用公式（4）式計算。

其中SSE是殘差平方和，從表3（方差分析表）中讀出SSE=26.38397并與n=10,m=1，代入（4）式可得S值。

表3 方差分析表（ANOVA）

表3可以讀出自由度、誤差平方和、均方差、F值、P值等。自由度（degree of freedom）是df對應的值，其中第一個數是變量數目，即dfr=m=1，第二個數是殘差自由度dfe=n?m?1=8，第三個數是總自由度dft=n?1=9；誤差平方和（或稱變差）是SS對應的值，其中第一個數是回歸平方（或稱回歸變差）SSR=531.2068，第二個數值殘差平方和（或稱剩余變差）SSE=26.38397，第三個數值是總偏差平方和（或稱總變差）SST=557.5908；均方差是MS對應的值，第一個數是回歸均方差MS=531.2068，第二個數是剩余均方差MSE=3.297997；F=161.0695；P=1.39748*10?6。其中有些值可以直接由公式計算。

1）回歸平方和，它表征是因變量的預測對其平均值的總偏差。

2）殘差平方和，它表征的是因變量對其預測值的總偏差，數值越大，擬合的效果越差，y的標準誤差即由SSE值求出。

3）總偏差平方和，它表示的是因變量對其平均值的總偏差。

4）測定系數，它表示的是回歸平方和占總偏差平方和的比重，數值越大，擬合效果越好。

5）均方差，它是誤差平方和除以相應的自由度得到的商，有回歸均方差MSR和剩余均方差MSE，MSE的值越小，擬合效果越好。

6）F值，它是用于線性關系的判定，一元線性回歸中F的計算公式（5），將R2=0.952682，dfe=10?1?1=8，代入（5）式中，即可得F=161.0695。

7）P值，Significance F對應的值是在顯著性水平下的Fα臨界值，也就是P值，也為棄真概率，所建模型為假的概率，則1?P是所建模型為真的概率，當然P值越小越好，本模型中P=0.00000139748＜0.0001，故置信度達到99.99%以上。

表4中可以讀出回歸模型的截距、斜率及其有關的檢驗參數。回歸系數是Coefficients對應的值，即截距a=139.493398和斜率b=0.026378669，因此建立的模型是=139.4934+0.0264xi或=139.4934+0.0264xi+εi；標準誤差，a=3.573629，=0.002078，其值越小，參數的精確度越高；統計量t值是t Stat對應的值，用于對模型參數的檢驗，需要查表才能決定；參數p值（雙側）是p value對應的值，對P值的分析如表5，對于本模型P=0.0000014＜0.0001，即可以認為在α=0.0001的水平上顯著，或者置信度達到99.99%。P值檢驗與t值檢驗是等價的，但p值不用查表，顯然要方便得多。

表4 回歸參數表

表5 P值分析表

表4中最后幾列給出的回歸系數以95%為置信區間的上限和下限。可以讀出，在α=0.05的顯著水平上，a的取值范圍是131.2526＜a＜147.7342，b的取值范圍是0.02159＜b＜0.03117。

其中有些量可以直接用公式計算。

1）t值在一元線性回歸分析中，F值、t值、相關系數R是等價，在相關系數檢驗中已有這部分信息，但是在多元線性回歸分析中，t檢驗是不可能缺省的。其中回歸系數與其標準誤差的比值就是t值。

一元線性回歸分析中的t值可以用相關系數值或測定系數值進行計算，如公式（6），本模型中，將R=0.976054，n=10，m=1代入（6）式可得t=12.691317

表6是選擇輸出內容，若選擇殘差項則有表6內容，輸出結果包括，第一列觀測值序號（用i表示），第二列因變量的預測值（用表示），第三列殘差（用ei表示）以及第四列標準殘差值。

表6 殘差輸出結果

1）預測值可由回歸模型=139.4934+0.0264xi求解，式中xi是圖1中的數據，從圖1可知x1=1200，代入模型中可得=139.4934+0.0264xi=139.4934+0.0264*1200=171.1478。

其他預測值都可以用同樣方法求解。

2）殘差ei的計算公式為ei=yi?。

從圖1可知y1=1700，代入上式可得e1=y1?1=1700 ? 171.1478= ?1.1478。

其他殘值可以用同樣方法求解。

3）標準殘差是由殘差的數據標準化后的數值，應用均值命令average及標準差命令stdev容易計算出結果，殘差的算術平均值為0，標準差為1.71218，利用標準化命令standardize（殘差，均值，標準差）立即算出表6中的結果。當然，也可以利用數據標準化公式進行逐一計算。將殘差平方再求和，便可得殘差平方和（也稱剩余平方和），則有

應用Excel中的命令sumsq（求平方和函數）容易求出以上結果。

圖6與圖7是以產量xi為自變量，以殘差ei為因變量，作散點圖，可得殘差圖（圖6）。殘差點列的分布越沒有趨勢（沒有規則，即越是隨機），得到的回歸結果就越是可靠。以產量xi為自變量，用實際成本yi及其預測值為因變量，作散點圖，可得線性擬合圖（圖7），實際成本與預測成本越重疊在一起，說明回歸方程越可靠。

圖6 殘差圖

圖7 線性擬合圖

3.3 在Excel中應用所求回歸方程進行預測

前面通過多種參數分析說明預測模型中的產量與成本構成線性關系，應用Excel中的函數計算或數據分析工具都得到回歸方程為y=139.493+0.0264x，并應用數據分析工具可以得到殘差數據表，說明預測值與真實值有一點差距，但殘差值滿足回歸檢驗范圍，滿足預測的要求，所以可以應用前面計算得到的回歸方程進行預測計算。從回歸方程y=139.493+0.0264x可知該產品的固定成本為139.493萬元，單位變動成本為0.026 4萬元，因此假設2021年生產3 000萬件，則該產品的總成本為139.493+0.0264*3000=218.693萬元。

4 多元線性回歸分析

多元線性回歸分析是研究因變量和多個自變量的線性關系，這種線性關系可用數學模型來表示，設因變量為yc，因變量yc與自變量x1,x2,x3,…xn之間存在線性關系，可用多元線性回歸方程來表示這種關系。設多元線性回歸方程為：yc=a+b1x1+b2x2+b3x3+…+bnxn+ε。

式中a、b1、b2、b3、…、bn為線性回歸方程和參數，ε為殘差。具體的案例及參數與以上的一元回歸分析一樣可在Excel中進行。

案例1某地區2014年到2020年一種太陽能熱水器銷售額，廣告費和利潤資料見表7。

表7 某地太陽能熱水器銷售資料（單位：百萬元）

分析：

1）A1：D8區域內輸入表7數據，即在單元格B2：B8中輸入x1值（銷售額），在單元格C2：C8中輸入x2值（廣告費），在D2：D8中輸入y值（利潤額），其中要求區域由列數據組成。

2）填寫如圖5“回歸”對話框，其中$D$2:$D$8輸入到“Y值輸入區域”，$B$2:$C$8輸入到“X值輸入區域”。同一元回歸分析一樣，可得到一系列分析數據及回歸分析正態分布圖，這不給出分析圖及表，只給出結果，a=?5.6259,b1=0.1275,b2=3.5407。

3）建立回歸模型y=a+b1x1+b2x2+ε，將通過Excel回歸分析得到數據代入即可得到回歸方程：y=?5.6259+0.1275x1+3.5407x2+ε，進而可用該模型對未來的利潤進行預算。這里只例舉了兩個自變量的回歸模型在Excel中進行計算，實質三個以上的自變量都可以用同樣的操作方法解決。

5 總結

在Excel中，進行多元回歸分析的操作過程類似于一元線性回歸分析，并且分析所得結果相似，變量數m≠1，t值和F值等統計量不等價于R值，進而不能應用相關系數求解。若用軟件Spss進行回歸分析，分析結果與Excel分析結果大同小異，只是Spss分析結果中出現更多的統計量及顯示方法上有差異。因此若能讀懂Excel的回歸分析中各參數的意思，則就可以讀懂Spss回歸輸出結果的大部分內容。采用回歸分析法進行成本預測的定量分析，運用Excel軟件實現預測總成本的計算，在操作過程中，有助于理解數學知識，提高動手能力。