云社区 博客 博客详情

Excel2016 预测工作表

Tom forever 发表于 2020-02-09 12:40:44 2020-02-09
0
0

【摘要】 最近很多同事做明年的预算问到Excel 的数据预测功能,之前写过用Excel折线图线性回归预测以及forecast函数预测,今天给大家分享Excel 2016预测工作表功能,可以从历史数据分析出事物发展的未来趋势,并以图表的形式展现出来,直观地观察事物发展方向或发展趋势。操作步骤如下:Step1:点击数据表中的任意单元格,单击菜单数据→预测工作表。图 1Step2:选择“预测结束”日期,单击...

最近很多同事做明年的预算问到Excel 的数据预测功能,之前写过用Excel折线图线性回归预测以及forecast函数预测,今天给大家分享Excel 2016预测工作表功能,可以从历史数据分析出事物发展的未来趋势,并以图表的形式展现出来,直观地观察事物发展方向或发展趋势。操作步骤如下:

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版本新增的一个函数,点击预测结果数据表可以看到公式,如图11。



图 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 聚合具有相同时间戳的多个点。

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


扫一扫二维码,关注我的微信公众帐号,不断免费学习各种excel技巧。


本文转载自异步社区

文链接:https://www.epubit.com/articleDetails?id=NC7E3EF9197A000018CA117571B5DE260

登录后可下载附件,请登录或者注册

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:huaweicloud.bbs@huawei.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
评论文章 //点赞 收藏 0
点赞
分享文章到微博
分享文章到朋友圈

上一篇:Excel二维表转换成一维表

下一篇:怎样在Excel单元格中添加迷你图

评论 (0)


登录后可评论,请 登录注册

评论