介紹統(tǒng)計學中的一元和多元線性回歸，并通過EXCEL自帶的統(tǒng)計函數(shù)LINEST、INDEX進行手工計算，再通過EXCEL數(shù)據(jù)分析工具包進行自動計算。

由于很多復雜的EXCEL自動化程序，需要用到自動化計算，EXCEL數(shù)據(jù)分析工具并不適用自動計算，反而EXCEL統(tǒng)計函數(shù)是很容易實現(xiàn)批量自動計算。

所以本文重點介紹EXCEL統(tǒng)計函數(shù)的使用。

統(tǒng)計學上的線性回歸原理簡介

回歸是研究一個隨機變量y對另一個(x)或一組(x1，x2，…，xn)變量的相依關(guān)系的統(tǒng)計分析方法。其中y又叫因變量，x叫自變量。

簡單的記憶方法：x是自身可以變動的，y是因為x的變化而變化的，就不會把自變量和因變量的意義搞亂。

線性回歸是自變量與因變量之間是線性關(guān)系的回歸。

一般來說，因變量只有一個，自變量會有一個或多個。下面就按因變量的數(shù)量及類別為分：一元線性回歸、多元線性回歸。

一元線性回歸

一元線性回歸是指一個因變量y只與一個自變量x有相關(guān)關(guān)系，通過公式可以表示為如下圖：

其中a稱為斜率，b稱為截距。

它的意思是當x增減一個單位時，y會同樣增減a個單位的x，如a＝2時，x增加一個單位，y就增加2個單位x。

通過EXCEL統(tǒng)計函數(shù)LINEST來實現(xiàn)一元線性回歸分析，在EXCEL的A1到B10輸入如下數(shù)據(jù)：

使用LINEST線性回歸函數(shù)進行手工計算。

LINEST函數(shù)可通過使用最小二乘法計算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，來計算某直線的統(tǒng)計值，然后返回描述此直線的數(shù)組。

也可以將 LINEST 與其他函數(shù)結(jié)合使用來計算未知參數(shù)中其他類型的線性模型的統(tǒng)計值，包括多項式、對數(shù)、指數(shù)和冪級數(shù)。因為此函數(shù)返回數(shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。

LINEST語法

LINEST(known_y’s, [known_x's], [const], [stats])

• Known_y’s 必需。關(guān)系表達式 y = mx + b 中已知的 y 值集合。如果 known_y’s 對應(yīng)的單元格區(qū)域在單獨一列中，則 known_x’s 的每一列被視為一個獨立的變量。如果 known_y’s 對應(yīng)的單元格區(qū)域在單獨一行中，則 known_x’s 的每一行被視為一個獨立的變量。
• Known_x’s 可選。關(guān)系表達式 y = mx + b 中已知的 x 值集合。known_x’s 對應(yīng)的單元格區(qū)域可以包含一組或多組變量。如果僅使用一個變量，那么只要 known_y’s 和 known_x’s 具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果使用多個變量，則 known_y’s 必須為向量（即必須為一行或一列）。如果省略 known_x’s，則假設(shè)該數(shù)組為 {1,2,3,…}， 其大小與 known_y’s 相同。
• const 可選。一個邏輯值，用于指定是否將常量 b 強制設(shè)為 0。如果 const 為 TRUE 或被省略，b 將按通常方式計算。如果 const 為 FALSE，b 將被設(shè)為 0，并同時調(diào)整 m 值使 y = mx。
• stats 可選。一個邏輯值，用于指定是否返回附加回歸統(tǒng)計值。如果 stats 為 TRUE，則 LINEST 函數(shù)返回附加回歸統(tǒng)計值，這時返回的數(shù)組為 {mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果 stats 為 FALSE 或被省略，LINEST 函數(shù)只返回系數(shù) m 和常量 b。

附加回歸統(tǒng)計值如下：

下面的圖示顯示了附加回歸統(tǒng)計值返回的順序。

在任意單元格中輸入=LINEST(B2:B10,A2:A10,TRUE,TRUE)，計算得出來的結(jié)果為94.33。Linest函數(shù)直接計算，返回的是第一個自變量的系數(shù)，LINEST返回的是一個數(shù)組，即上述的圖表。

如果要通過EXCEL數(shù)組來實現(xiàn)這種功能。選定A14:B18,在EXCEL地址欄輸入=LINEST(B2:B10,A2:A10,TRUE,TRUE)，然后同時按CTRL+SHIFT+ENTER，返回一個表格，表格中的每個單元格的公式顯示為：{=LINEST(B2:B10,A2:A10,TRUE,TRUE)}

對應(yīng)上表及上述的圖，解釋上表的各個參數(shù)的意義。

• 94.34就是x的系數(shù)a，
• 93.92為截距b，
• 6.2為x的系數(shù)a的標準誤差值，
• 25.89為截距（常量）b的標準誤差值，
• 0.97為相關(guān)系數(shù)，
• 37.62為y估計值的標準誤差，
• 231.78為F統(tǒng)計值
• 7為自由度，
• 328061.71為回歸平方和，
• 9907.85為殘差平方和。

上述返回的統(tǒng)計值，最常用的是自變量的系數(shù)a和常量b，如果在EXCEL自動化程序中，很少會用上述的返回一個表格的方式，因為LINEST返回的是一個數(shù)組，可以通過index函數(shù)取得數(shù)組中的每一個值。

x的系數(shù)a可以在任意單元格式輸入=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,1)，截距b可以在任意單元格式輸入=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,2)，index函數(shù)第一個參數(shù)是指定一個數(shù)組，第二和第三個參數(shù)是指定返回的行列位置。

所以上述的一元線性回歸的擬合直線函數(shù)為y=94.34x+93.92，相關(guān)系數(shù)為0.97。

多元線性回歸

多元線性回歸是指一個因變量y只與多個自變量x有線性相關(guān)關(guān)系，通過公式可以表示為如下圖：

a為每個自變量對因變量y的影響因素，我們以二元線性回歸為例，用EXCEL函數(shù)LINEST進行分析。數(shù)據(jù)如下，填充在EXCEL的A1:C10中。

選定A14:C18，在地址欄中輸入=LINEST(C2:C10,A2:B10,TRUE,TRUE)，按CTRL+SHIFT+ENTER，返回一個表格，表格中的每個單元格的公式顯示為：{=LINEST(C2:C10,A2:B10,TRUE,TRUE)}，如下表：

此表格和一元線性回歸的表格一樣，只是多了一列，因為多了一個自變量。多出一列的內(nèi)容是另一個自變量的系數(shù)和它的標準誤差值。同樣可以通過用INDEX函數(shù)取得數(shù)據(jù)的每一個值。根據(jù)上表可以得到擬合的線性回歸函數(shù)y=69.17×1+6.88×2+87.37

用EXCEL數(shù)據(jù)分析工具進行回歸分析

接下來通過EXCEL數(shù)據(jù)分析工具實現(xiàn)上述一元線性回歸分析的計算，并可以驗證上述的計算過程。如果你的EXCEL中找不到數(shù)據(jù)分析，請先為EXCEL添加數(shù)據(jù)分析工具的加載宏。

• 選中A1:B10，打開數(shù)據(jù)分析，選中“回歸”后，點擊“確定”按鈕。
• 按下圖填寫好自變量X、因變量Y的區(qū)域A1:A10,B1:B10，選擇“標志”，意思是說表格中包括有表頭字段，填寫輸出區(qū)域A26,點擊“確定”后即可。
• 結(jié)果如下，可以清晰的看到用LINEST計算出來的結(jié)果與數(shù)據(jù)分析工具分析得到的結(jié)果是一樣的。