楊雄 曾智

（婁底職業(yè)技術(shù)學院，湖南 婁底 417000）

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

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

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

1.1 回歸分析的建立過程

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

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

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

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

定義2當給定x一組不完全相同的值x1,x2,…,xn時，對Y分別在x1,x2,…,xn處進行獨立觀測，其觀察結(jié)果記為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）式中的未知參數(shù)a,b的估計值，再代入（1）式中，進而可得回歸方程。

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

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

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

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

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

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

圖1 某企業(yè)2010年-2019年的產(chǎn)量與成本圖

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

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

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

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

表1 相關程度

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

2.3 判斷產(chǎn)量與成本是否存在線性關系

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

圖2 原始數(shù)據(jù)散點圖

圖3 相關系數(shù)計算結(jié)果

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

3.1 應用Excel中的函數(shù)計算回歸方程

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

y=139.493+0.0264x

圖4 Linest函數(shù)操作圖

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

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

圖5 建立回歸模型

以下對在Excel中數(shù)據(jù)分析結(jié)果的參數(shù)及圖進行詳細解讀。

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

表2 回歸統(tǒng)計表

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

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

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

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

表3 方差分析表（ANOVA）

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

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

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

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

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

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

表4 回歸參數(shù)表

表5 P值分析表

表4中最后幾列給出的回歸系數(shù)以95%為置信區(qū)間的上限和下限?？梢宰x出，在α=0.05的顯著水平上，a的取值范圍是131.2526＜a＜147.7342，b的取值范圍是0.02159＜b＜0.03117。

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

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

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

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

表6 殘差輸出結(jié)果

1）預測值可由回歸模型=139.4934+0.0264xi求解，式中xi是圖1中的數(shù)據(jù)，從圖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）標準殘差是由殘差的數(shù)據(jù)標準化后的數(shù)值，應用均值命令average及標準差命令stdev容易計算出結(jié)果，殘差的算術(shù)平均值為0，標準差為1.71218，利用標準化命令standardize（殘差，均值，標準差）立即算出表6中的結(jié)果。當然，也可以利用數(shù)據(jù)標準化公式進行逐一計算。將殘差平方再求和，便可得殘差平方和（也稱剩余平方和），則有

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

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

圖6 殘差圖

圖7 線性擬合圖

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

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

4 多元線性回歸分析

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

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

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

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

分析：

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

2）填寫如圖5“回歸”對話框，其中$D$2:$D$8輸入到“Y值輸入?yún)^(qū)域”，$B$2:$C$8輸入到“X值輸入?yún)^(qū)域”。同一元回歸分析一樣，可得到一系列分析數(shù)據(jù)及回歸分析正態(tài)分布圖，這不給出分析圖及表，只給出結(jié)果，a=?5.6259,b1=0.1275,b2=3.5407。

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

5 總結(jié)

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