Excel 2016 预测工作表

最近很多同事做明年的预算问到Excel 的数据预测功能，之前小编写过用Excel折线图线性回归预测以及forecast函数预测，用Excel做一元线性回归预测分析（点击文字即可查看）。

今天给大家分享Excel2016预测工作表功能，可以从历史数据分析出事物发展的未来趋势，并以图表的形式展现出来，直观地观察事物发展方向或发展趋势。操作步骤如下：

Step1:点击数据表中的任意单元格，单击菜单数据→预测工作表。

图 1

Step2:选择“预测结束”日期，单击“创建”。

图 2

step3:预测结果在新的工作表中呈现，预测结果图表如图3，数据如图4。

图 3

图 4

图3蓝色折线是历史数据，橙色折线是未来预测数据。可以看到，表示未来预测数据的橙色折线基本上是平直的，没有得到正确的预测。出现这种情况，原因是没有设置正确的“季节性”参数。

Step4:单击预测工作表向导窗口左下角的“选项”按钮，查看预测的更多参数。将“预测结束”日期选定到“2016/12/1”，将“季节设置”由“自动检测”改为“手动设置”并将其值设置为“12”。这样改的原因是，我们的原始数据范围从 2014 年 1 月份开始，至 2015 年 12 月份结束，每个周期为 12 个月，而且需要从最后一期数据（2015 年 12 月份）开始，向后预测 1 年以内的数据。得到新的结果如图6和图7。

图 5

图 6

图 7

为了进一步了解 Excel 数据预测工作表的运行机制，下面让我们来仔细看看其他选项。除了上面提到的“预测结束”和“季节设置”之外，Excel 的预测工作表还有以下几个主要参数：

预测开始：从历史数据中的哪一期数据开始预测。

置信区间：设置预测值的上限和下限；该值越小，则上下限之间的范围越小。

图8和图9分别是置信区间70%和95%的预测趋势和置信上下限结果。

图 8

图 9

使用以下方式填充缺失插值点：默认为“内插”，是根据数据的加权平均值计算出的插值；也可以将其置为“0”，即不进行缺值的插值计算。

使用以下方式聚合重复项：以本数据为例，如果 2015 年 1月有好几个数据，Excel 在计算预测值时会将一个月内的多个值进行“聚合”，“聚合”的方式包括平均（默认）、计数、最大/最小/中值等。

以上面提到的“预测开始”为例，如果历史数据周期性比较强，可以将开始预测的时间设置为早于最后一期历史数据，这样做可以检测预测的准确性。在提前到周期末尾的那一期时（如第二年的最后一期数字），也有助于提高预测的准确性。

如图10，我们将“预测开始”定在 2014 年 12 月 1 日，它是第 1 个数据周期的最后一期。

图 10

可以看到，Excel 预测的数据（橙色折线），与实际存在的 2015 年 12 个月份的数据（蓝色折线），基本保持一致，有一定的预测准确性。Excel 预测工作表的功能，是基于一个叫 FORECAST.ETS 的功能实现的,是2016版本新增的一个函数，点击预测结果数据表可以看到公式，如图10。

图 11

FORECAST.ETS函数的功能：通过使用指数平滑 (ETS) 算法的 AAA 版本计算或预测基于现有（历史）值得出的未来值。

FORECAST.ETS 的语法：Forecast_ETS(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6)

参数说明：

Arg1	必需	DOUBLE	目标日期： 想要预测值的数据点。 目标日期可以是日期/时间或数字。
Arg2	必需	VARIANT	值： 要预测下一个点的历史值。
Arg3	必需	VARIANT	时间线： 日期或数值数据的独立数组或范围。时间线中的值之间必须具有一致步长，不能为零。
Arg4	可选	VARIANT	季节性： 一个数值。
Arg5	可选	VARIANT	数据完成功能： 尽管时间线需要数据点之间的固定步长，但是 Forecast_ETS 支持多达 30% 的缺少数据，并会自动对其进行调整。
Arg6	可选	VARIANT	聚合： 尽管时间线需要数据点之间的固定步长，但是 Forecast_ETS 聚合具有相同时间戳的多个点。

不懂函数也没有关系，知道如何使用预测工作表得到预测值即可。

今天的分享到此结束，如果想看更多历史文章，请从菜单所有文章查看，或者点击所有文章都在这里，点击文字即可查看。