回歸分析問題及多元線性回歸分析中Excel的應用

【摘 要】如果回歸分析中存在兩個及兩個以上的自變量就是多元回歸，只有不同自變量的最優(yōu)自合才能對因變量進行預測或者估計，一個自變量X對因變量Y進行估計是線性回歸分析中的重點，與利用一個自變量展開估計及預測相比，這種方法的真實性與可靠性要更高，具有更大的實用意義。鑒于此，筆者首先對多元線性回歸進行了概述，帶著對多元線性回歸的簡單了解，引領大家從不同角度對多元線性回歸分析中Excel的應用進行了進一步的探討和分析。

【關鍵詞】Excel 多元線性回歸分析 應用

在線性回歸分析中，用模型中一個自變量X對因變量Y進行估計是其中的重點，因為不同事物之間的聯(lián)系是錯綜復雜的，如果一個因變量發(fā)生變化，往往是因為單個或者多個自變量發(fā)生變化而引起的連鎖變化，為了明確線性回歸分析中這種復雜的相互關系，實現(xiàn)提高控制及預測的精確度，需要對更多的自變量進行考慮，并結合實際情況建立起多元回歸模型。事實上，利用多元回歸分析的方法和其原理在同一元線性回歸方法分析中大體上是相同的，但其中也存在一些不同點，比方說不可以利用散點圖表示不同變量之間存在的關系，與簡單線性回歸相比，多元回歸計算的難度要大很多，計算的難度隨著變量的增多而越來越復雜，但是利用Excel可以順利的將計算變得輕松且簡單。下面我們就從不同角度針對Excel在多元線性回歸分析中的應用展開進一步分析。

一、多元線性回歸概述

如果回歸分析中存在兩個及兩個以上的自變量，那么這就是多元回歸，實際上很多情況下一個現(xiàn)象都伴隨著多個現(xiàn)象，與不同因素發(fā)生著聯(lián)系，只有不同自變量的最優(yōu)自合才能對因變量進行預測或者估計，這種分析方法往往比一個利用一個自變量展開估計及預測更加有效，同時也更加與實際情況相符合，所以，多元線性回歸往往比一元線性回歸具有更大的實用意義。

實際上多元線性回歸的基本原理及計算過程與一元線性回歸基本上是相同的，但是因為自變量的個數(shù)多，計算起來免不了會麻煩，通常在實際應用過程中需要借助統(tǒng)計學軟件來完成。因為不同自變量的單位可能會存在差異，比方說在一個消費水平關系式中，職業(yè)、工資水平及地區(qū)等因素都會對消費水平產生影響，但是這些消費因素的單位很明顯是不同的，所以自變量前系數(shù)的大小并不能直接說明該因素是否重要，簡單一點來說，同樣的工資收入，如果利用元作為單位，與以百元作為單位相比得到的回歸系數(shù)要小很多，但是工資水平對消費水平的影響情況卻并沒有發(fā)生改變，因此，需要想辦法將不同自變量量化到統(tǒng)一的單位上來。具體來說就是將包括因變量的所有變量都轉化成標準分，然后進行線性回歸，這時得到的回歸系數(shù)可以將自變量的重要程度直接反映出來，這時我們得到的回歸方程就是標準回歸方程，其回歸系數(shù)被稱作是標準回歸系數(shù)。

二、常見的回歸分析問題

我們經常會在現(xiàn)實世界中遇到一些相互制約、相互依賴的變量，這些變量之間往往存在著一定的關系，大致上可以將這種關系分成兩個種類：一種是確定性的關系，這種確定性關系可以利用函數(shù)來表示，另一種是不確定關系，一旦確定了一個變量之后，另外一個變量的值是不能得到確定的，這種情況下，針對自變量之間存在的非確定性關系展開研究和分析具有十分重要的現(xiàn)實意義。

對兩個變量間的關系進行研究時，其中只有一個是隨機變量，另外一個通常為一般變量，假設建立兩個變量，他們分別為x和y，其中，x為一般的變量，這個變量可以實現(xiàn)精確的測量或者控制；y為隨機變量，這一隨機變量為x取某個值以后才經過計算得到的，并不能在事先進行確定，通過試驗觀察我們可以發(fā)現(xiàn)，具有關系的兩個變量之間存在很多對實測數(shù)據(jù)，在坐標中可以利用這些數(shù)據(jù)描繪出來一個散點圖（如圖1）。

從上圖中可以看出，與實測數(shù)據(jù)相對應的各點并沒有在一條直線上，但是從位置上來看這些點都是趨向一條直線的，因此我們說這兩個變量之間存在著線性關系，而這條直線就是y對X的回歸直線，雖然這兩個變量之間存在著非確定性函數(shù)關系，但是我們始終可以利用直線形式對兩個變量的規(guī)律性展開研究。下面我們以某商店為例，對其商店的規(guī)模大小、促銷費用及年銷售額之間的關系展開分析。

三、Excel數(shù)據(jù)分析在多元線性回歸中的應用

利用Excel數(shù)據(jù)分析這項功能對多元線性回歸進行分析的時候，首先需要單擊頁面上方的工具欄，并在出現(xiàn)的菜單中選擇“數(shù)據(jù)分析”這項，然后在數(shù)據(jù)分析工具選項框中選擇“回歸”，并對輸入和輸出等選項進行相關的設置，選擇過程中需要對自變量X的輸入加以注意，在輸入X的過程中應按照已經確定好的不同自變量的順序，將自變量的單元格引用范圍共同放在X值的輸入?yún)^(qū)域中。然后點擊界面上的“確定”按鈕，這樣就能得到線性回歸分析結果了（如下圖2）。

從以上兩張截圖得到的顯示結果，我們可以寫出關于二元線性回歸方程：

Yi=b0+b1X1i+b2X2i=-51.3127+1.4053X1i+6.3823X2i

式中，b1表示如果在促銷費用固定的時候，那么商店的規(guī)模將會增加1平方米，從而商店的年銷售額將會增加1.4053萬元；b2表示如果商店規(guī)模大小是固定的，那么商店促銷費用如果增加1萬元，其年銷售額將會增加6.3823萬元。從這里我們可以看出，與一元線性回歸方程中的回歸系數(shù)b=1.6246相比，b1也就是商店規(guī)模大小的回歸系數(shù)，將會小很多，之所以會出現(xiàn)這種結果，其原因在于一元線性回歸方程中只對商店規(guī)模大小與年銷售額之間的關系進行了考慮，并沒有考慮到促銷費用這一要素，這一重要因素受到了嚴重忽略，將促銷費用的影響摻進了商店規(guī)模大小影響之中。b0表示截距，b0=-51.3127萬元，這一數(shù)值和一元線性回歸方程中的截距（b0=+99.01）相比存在很大的不同，這主要是因為X1=0與X2=0二者都不再其樣本的取值范圍中，所以可以看出，對截距項進行解釋需要非常謹慎。

如果判定系數(shù)=85.14%，則說明在年銷售額變動中，可以由商店規(guī)模大小及促銷費用的多少二者來對有85.14%這樣的判定系數(shù)進行解釋，14.86%這一因素是隨機誤差，當?shù)诙€自變量引進來以后，回歸方程判定系數(shù)85.14%與一元線性回歸方程判定系數(shù)77.68%相比，明顯提高了7.46個百分點。這里我們尤其要注意，通常自變量增加，在統(tǒng)計上效果并不明顯，其判定系數(shù)的值也將得到增大。

商店年平均銷售額的估計標準也是存在誤差的，利用商店的估計標準誤差為112.1015萬元，利用Excel進行數(shù)據(jù)分析時，當引進第二個自變量促銷費用，與一元線性回歸方程的估計標準誤差相比，回歸方程的估計標準差有了一些下降，這就說明與一元線性回歸方程相比，回歸方程的代表性要高出很多。

如果假設顯著性水平α是0.05，b1的檢驗統(tǒng)計量為6.2817，b2的檢驗統(tǒng)計量為2.4538，通過查表可以知道，t0.05/2（15-3）=2.1788。因為6.2817比2.1788大，而2.4538比2.1788大，以這些為依據(jù)我們可以拒絕H0：1=0以及H0：2=0的相關假設，判定在統(tǒng)計上這兩個回歸系數(shù)都是顯著的。值得注意的是，如果這一例的顯著性水平為0.01，而不是0.05，那么t0.01/2（15-3）=3.0545。雖然這時6.2817比3.0545大，但是2.4538始終比3.0535小，這種情況下如果拒絕H0：β1=0的假設，也不能拒絕H0：β2=0的假設，因此我們可以判定在統(tǒng)計上第二個回歸系數(shù)是不顯著的。

設計顯著性水平α為0.05，經過查表可以了解F0.05（2，12）=3.89，F(xiàn)=34.38比F0.05（2，12）=3.89要大，這種情況下可以拒絕原來的假設，這時樣本的r2是顯著的，由這些依據(jù)可以推論出已經建立起來的二元線性回歸模型是有效的，之所以說存在復相關的關系，主要是指一個因變量和不同自變量之間存在的相關性，一旦自變量出現(xiàn)了共同的變動，那么因變量將會隨之發(fā)生變動，同時其相關程度也需要利用復相關系對其進行測定。本文中所舉出的實際例子，商店規(guī)模大小、促銷費用以及年銷售額三者之間的復相相關系數(shù)是0.9227，經過計算可以看出，商店規(guī)模和促銷費用二者可以作為一個整體因素存在，這個整體因素與年銷售額之間存在的相關性非常高，同時與一元回歸中商店規(guī)模單個自變量相比，其相關程度與年銷售額之間存在較高的相關系數(shù)。這里值得一提的是，在我們對客觀事物本質上為多因素影響的變量時，可以利用復相關、多元回歸及偏相關展開進一步分析，這種分析方法與單相關和一元回歸分析相比，準確性和真實性要更強一些。

四、結束語

綜上所述，我們經常會在現(xiàn)實世界中遇到一些相互制約、相互依賴的變量，這些變量之間往往存在著一定的關系，回歸分析就是對客觀事物數(shù)量之間依存關系而進行的一種分析，在數(shù)理統(tǒng)計中是十分常用的一種分析方法，從本文中一元線性回歸和多元線性回歸兩種方法的比較分析中可以看出，利用Excel對線性回歸問題進行分析時，可以與相關圖表相配合，這樣就可以將數(shù)據(jù)清晰的顯示出來，從而大大提高辦公效率。本文結合筆者的實際工作經驗，首先針對線性回歸分析進行了簡單概述，然后從不同角度針對多元線性回歸分析中Excel的應用展開了分析，供大家參考。

參考文獻

[1]李傳偉.基于Excel的向前增選法多元線性回歸在體育教學中的應用[J].中國管理信息化，2012，（5）：89-90

[2]程冬梅，劉志峰，王琳琳.EXCEL在水文學線性回歸分析中的應用[J].黑龍江水利科技，2012，（12）：96-98

[3]付勇，汪立今，柴鳳梅，鄧剛，陳勇，陳俊華.多元線性回歸和逐步回歸分析在白石泉Cu-Ni硫化物礦床研究中的應用[J].地學前緣，2009，（1）：373-380

[4]張海峰.Excel在計量地理學教學中的應用——以簡單線性回歸分析為例[J].電腦開發(fā)與應用，2009，（8）：45-48+51