一. LINEST函數

首先，一元線性回歸的方程：

y = a + bx

相應的，多元線性回歸方程式：

y = a + b1x1 + b2x2 + … + bnxn

這里：

• y - 因變量即預測值
• x - 自變量
• a - 截距
• b - 斜率

LINEST的可以返回回歸方程的?截距(a) 和 斜率(b)?和其他回歸統計值。

（1）LINEST 函數語法

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

• known_y's?(必須) 因變量，單行/單列
• known_x's?(必須) 自變量，單行/單列
• const(可選) ：
  • TRUE[默認]：正常計算截距?a
  • FALSE：強制截距?a = 0，此時回歸方程?y = bx
• stats(可選) :
  • TRUE：返回統計值
  • FALSE[默認]：不返回統計值，只返回斜率和截距

注意 LINEST 函數返回值為數組，需要使用數組三鍵? CTRL + SHIFT + ENTER

使用SLOPE得到的斜率結果與LINEST 函數是一樣的

（2）LINEST 返回的回歸統計值

當LINEST函數參數?stats = TRUE，此時返回值包含統計值：

如果回歸模型為多元線性方程：?

LINEST函數返回值順序：

最后三行，從第三列開始返回值為#NA，可以通過 IFERROR 函數進行嵌套以消除

二. LINEST 使用舉例

（1）一元線性回歸:?

【例1】廣告投入與雨傘的銷量

這里：

• Advertising 是自變量 x (B2:B13)，Umbrellas sold 是因變量 y (C2:C13)
• 選中單元格 E2:F2 輸入?= LINEST(C2:C13, B2:B13)，CTRL + SHIFT + ENTER
• 這里 0.526 是斜率，-4.994 是截距
• 回歸方程為： y=?4.994+0.526?x
• 預測：如果投入廣告為 $50，預測雨傘的銷量為：

-4.994 + 0.526*50 = 21.3

a）通過函數獲取回歸方程斜率

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

LINEST (C2:C13,B2:B13) 返回值為 1 行 2 列的數組

b）通過函數獲取回歸方程截距

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

函數對比：

（2）2. 多元線性回歸：?

【例2】廣告投入，下雨量與雨傘的銷量

如果存在兩個或更多的自變量?，�1，�2...?，那么這些自變量必須位于相鄰列，整體作為 LINEST 函數 的參數?known_x's?.

注意，對于多元線性回歸， LINEST函數以逆序的形式返回的 「斜率」，從右往左分別為?

對于例2：

• Rainfall 是自變量?X1?(B2:B13)，Advertising 是自變量?X2?(C2:C13)，Umbrellas sold 是因變量?y?(D2:D13)。
• 選中單元格 F2:H2 輸入?= LINEST(D2:D13, B2:C13)，CTRL + SHIFT + ENTER
• 這里 0.309 是斜率?b2?，0.186 是斜率 b1?，-10.739是截距
• 回歸方程為：?y=?10.739+0.186x1+0.309x2
• 預測：如果投入廣告為 $50，當月平均降雨量為 100 mm，預測雨傘的銷量為：-10.739 + 0.186 * 100 + 0.309 *50 = 23.31

（3）使用LINEST 函數進行一元線性回歸預測

在一元線性回歸的應用中，LINEST 除了可以直接返回 斜率 b 以及截距 a 之外，通過結合函數SUM / SUMPRODUCT?可以實現給定自變量 (X) 預測因變量 (y)。

回到例1， 當10月(Oct) 廣告支出為 $50，此時預測雨傘銷量為:

= SUM(LINEST(C2:C10, B2:B10)*{50,1})

實際應用時，對于給定的自變量(x) ，一般放在單元格中，同時相鄰單元格輸入 1。

例如，下圖 E2 輸入自變量 x，F2 輸入常量 1，單元格 G2 代表計算的預測值?y，通過：

• SUMPRODUCT?（使用?ENTER）

= SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

• SUM（使用?CTRL + SHIFT + ENTER）

= SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

（4） 使用LINEST 函數進行多元線性回歸預測

同樣在多元線性回歸的應用中，LINEST 也可以結合函數SUM / SUMPRODUCT?可以實現給定多個自變量 (?X1,X2...?) 預測因變量 (y)。

回到例2， 當廣告支出為 $50 (?X2?)，下雨量為100 (?X1)，此時預測雨傘的銷量為：

= SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

注意，對于多元線性回歸， LINEST函數以逆序的形式返回的 「斜率」，從右往左分別為? ?。因此在如上函數中常數數組順序為{50,100,1} 分別代表

實際應用時，對于給定的多個自變量(x) ，放在相鄰單元格中，同時最后單元格輸入 1。

例如，下圖 F2 輸入自變量?X2?，G2 輸入自變量?X1?，H2 輸入常量 1，單元格 I2 代表計算的預測值?y，通過：

• SUMPRODUCT （使用?ENTER）

= SUMPRODUCT(LINEST(C2:C10, B2:B10)(F2:H2))

• SUM （使用?CTRL + SHIFT + ENTER）

= SUM(LINEST(C2:C10, B2:B10)(F2:H2))

（5）使用LINEST 進行線性回歸的統計值

前面關于LINEST函數的語法中，只要參數?stats = TRUE?函數會返回回歸統計值。

對于例2， 若要返回回歸統計值：

= LINEST(D2:D13, B2:C13, TRUE, TRUE)

這里列 B 和列 C 分別代表兩個自變量，因此選擇 3 行(2個斜率一個截距) 5 列的區域 [F2:H6]，同時輸入如上公式

對于LINEST返回值包含 #NA 錯誤，可以使用嵌套 IFERROR 函數，如下：? = IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

下圖解釋了LINEST函數返回統計值的含義：

簡單介紹除斜率和截距外的其他返回值：

三. 5 個關于LINEST函數的知識點

四. LINEST 函數報錯處理

1. LINEST 返回值只有斜率值，此時應檢查公式是否為數組公式輸，即是否使用?CTRL + SHIFT + ENTER?輸入
2. REF！錯誤，檢查參數?known_x's?和參數?known_y's?是否大小一致
3. VALUE 錯誤
4. 檢查 參數?known_x's?和參數?known_y's?是否包含空單元格，文本值，文本型數值
5. 檢查參數?const?或?stat?輸入值非 FALSE / TRUE