营长说

01

函数预测法

案例 ：已知某公司2013年到2018年的销售量数据，利用线性回归法预测公司2019年的销售量。

Excel中线性预测函数有FORECAST和TREND。这两个函数都是根据一条线性回归拟合线返回一个预测值。

先来看看这两个函数的语法结构。

FORECAST函数的语法结构：

FORECAST (X计算值，Y值区间，X值区间）

TREND函数的语法结构：

TREND(Y值区间，X值区间，X计算值）

在单元格C9应用FORECASET函数：

=FORECAST( B9 , C2:C7 , B2:B7 )

在单元格C10应用TREND函数：

=TREND( C2:C7 , B2:B7 , B10 )

这两个函数计算的结果一致，2019年销售量预测都是18316。

02

图表预测法

将2013-2018年销售数据绘制成散点图。在图表中选中数据系列，点击【右键】-【添加趋势线】。在趋势线上，点击【右键】-【设置趋势线格式】，可以选择趋势线样式，并勾选显示公式和R平方值。

最终得到线性回归方程y=1233.1x+9684，相关系数为0.9201，说明当前随着年份的增长，收入增长稳定，呈强正相关关系。

利用回归方程计算出2019年的预计销售量，与前面用FORECAST和TREND函数线性预测结果一致，都是18316。

03

预测工作表法

预测工作表是Excel2016新增的功能，可以看做函数与图表的功能组合，从历史数据分析出业务发展趋势，并以图表的形式展现出来，直观地展示业务发展方向或趋势，命令位置如下。

案例 ：现有2015年-2018年每月的销售数据表，销售数据有周期性，请根据48个月的历史数据，预测2019年1-12月的销售数据。

点击【预测工作表】，弹出的对话框中选择预测结束的日期，还可以展开高级选项，设置预测开始、置信区间、季节性等参数。

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

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

季节性 ：一般周期性的规律，可以为自动检测或手动设置，设置一个周期数，比如12，表示每12个数据作为一个周期进行预测，常用于周末数据，月度数据和年度数据等的预测。

预测结果将自动在新的工作表中呈现。

三列数据分别是趋势预测（数字）、置信下限（数字）、置信上限（数字），这分别代表着预测值，预测下限、预测上限。仔细看会发现这里就是用到了FORECAST.ETS函数。

从Excel 2016开始FORECAST函数已经形成了多个分支，用于预测不同类型的数据，允许基于指数平滑进行预测。营长将这几个函数的含义和适用版本整理出来。

可以看出线性回归预测函数更新为FORECAST.LINEAR，增加了指数平滑预测函数FORECAST.ETS，及其预测项目函数，更适合对有季节性或周期性业务数据的预测。

FORECAST.ETS函数结构如下：

FORECAST.ETS（预测时间点，已知数据，已知时间线，季节性周期，完整性，聚合方式）

预测时间点 ：即目标日期或时间

已知数据 ：历史数据区间

已知时间线 ：时间间隔相等，即步长一致

季节性周期 ：周期长度值，默认为值1，即自动检测季节性，0表示无季节性，即做线性预测。

完整性 ：时间线有缺失点一般按临近点平均值处理

聚合方式 ：对相同时间多个数据处理方式，默认0为AVERAGE，即多个数据的平均值。

在D列列出2019年12个月份的日期，在E2单元格输入公式：

=FORECAST.ETS( D2 , $B$2:$B$49 , $A$2:$A$49 , 12 )

公式是按12个月作为周期长度进行预测的，预测值和之前48个月的历史数据制作成折线图。