介紹統(tǒng)計學(xué)中的一元和多元線性回歸，并通過EXCEL自帶的統(tǒng)計函數(shù)LINEST、INDEX進(jìn)行手工計算，再通過EXCEL數(shù)據(jù)分析工具包進(jìn)行自動計算。
由于很多復(fù)雜的EXCEL自動化程序，需要用到自動化計算，EXCEL數(shù)據(jù)分析工具并不適用自動計算，反而EXCEL統(tǒng)計函數(shù)是很容易實現(xiàn)批量自動計算。
所以本文重點(diǎn)介紹EXCEL統(tǒng)計函數(shù)的使用。
回歸是研究一個隨機(jī)變量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稱為截距。
它的意思是當(dāng)x增減一個單位時，y會同樣增減a個單位的x，如a＝2時，x增加一個單位，y就增加2個單位x。
通過EXCEL統(tǒng)計函數(shù)LINEST來實現(xiàn)一元線性回歸分析，在EXCEL的A1到B10輸入如下數(shù)據(jù)：
| x | y |
|---|---|
| 1.1 | 200 |
| 1.9 | 245 |
| 2.5 | 367 |
| 4 | 400 |
| 4.5 | 550 |
| 5 | 540 |
| 5.9 | 667 |
| 7 | 770 |
| 1 | 210 |
使用LINEST線性回歸函數(shù)進(jìn)行手工計算。
LINEST函數(shù)可通過使用最小二乘法計算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，來計算某直線的統(tǒng)計值，然后返回描述此直線的數(shù)組。
也可以將 LINEST 與其他函數(shù)結(jié)合使用來計算未知參數(shù)中其他類型的線性模型的統(tǒng)計值，包括多項式、對數(shù)、指數(shù)和冪級數(shù)。因為此函數(shù)返回數(shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。
LINEST(known_y’s, [known_x's], [const], [stats])
附加回歸統(tǒng)計值如下：
| 統(tǒng)計值 | 說明 |
|---|---|
| se1,se2,…,sen | 系數(shù) m1,m2,…,mn 的標(biāo)準(zhǔn)誤差值。 |
| seb | 常量 b 的標(biāo)準(zhǔn)誤差值（當(dāng) const 為 FALSE 時，seb = #N/A）。 |
| r2 | 判定系數(shù)。y 的估計值與實際值之比，范圍在 0 到 1 之間。如果為 1，則樣本有很好的相關(guān)性，y 的估計值與實際值之間沒有差別。相反，如果判定系數(shù)為 0，則回歸公式不能用來預(yù)測 y 值。 |
| sey | Y 估計值的標(biāo)準(zhǔn)誤差。 |
| F | F 統(tǒng)計或 F 觀察值。使用 F 統(tǒng)計可以判斷因變量和自變量之間是否偶爾發(fā)生過可觀察到的關(guān)系。 |
| df | 自由度。用于在統(tǒng)計表上查找 F 臨界值。將從表中查得的值與 LINEST 函數(shù)返回的 F 統(tǒng)計值進(jìn)行比較可確定模型的置信區(qū)間。有關(guān)如何計算 df 的信息，請參閱本主題下文中的“說明”。 |
| ssreg | 回歸平方和。 |
| ssresid | 殘差平方和。 |
下面的圖示顯示了附加回歸統(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)}
| 94.34 | 93.92 |
| 6.20 | 25.89 |
| 0.97 | 37.62 |
| 231.78 | 7 |
| 328,061.71 | 9,907.85 |
對應(yīng)上表及上述的圖，解釋上表的各個參數(shù)的意義。
上述返回的統(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進(jìn)行分析。數(shù)據(jù)如下，填充在EXCEL的A1:C10中。
| x1 | x2 | y |
|---|---|---|
| 4 | 1.1 | 200 |
| 7 | 1.9 | 245 |
| 11 | 2.5 | 367 |
| 14 | 4 | 400 |
| 19 | 4.5 | 550 |
| 22 | 5 | 540 |
| 22 | 5.9 | 667 |
| 25 | 7 | 770 |
| 5 | 1 | 210 |
選定A14:C18，在地址欄中輸入=LINEST(C2:C10,A2:B10,TRUE,TRUE)，按CTRL+SHIFT+ENTER，返回一個表格，表格中的每個單元格的公式顯示為：{=LINEST(C2:C10,A2:B10,TRUE,TRUE)}，如下表：
| 69.17 | 6.88 | 87.37 |
| 34.05 | 9.14 | 28.11 |
| 0.9732 | 38.84 | #N/A |
| 109.00 | 6.00 | #N/A |
| 328,916.50 | 9,053.05 | #N/A |
此表格和一元線性回歸的表格一樣，只是多了一列，因為多了一個自變量。多出一列的內(nèi)容是另一個自變量的系數(shù)和它的標(biāo)準(zhǔn)誤差值。同樣可以通過用INDEX函數(shù)取得數(shù)據(jù)的每一個值。根據(jù)上表可以得到擬合的線性回歸函數(shù)y=69.17×1+6.88×2+87.37
接下來通過EXCEL數(shù)據(jù)分析工具實現(xiàn)上述一元線性回歸分析的計算，并可以驗證上述的計算過程。如果你的EXCEL中找不到數(shù)據(jù)分析，請先為EXCEL添加數(shù)據(jù)分析工具的加載宏。
