excel做预测的方法集合

一. 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