专栏首页大数据杂谈【Excel系列】Excel数据分析:时间序列预测

【Excel系列】Excel数据分析:时间序列预测

移动平均

18.1 移动平均工具的功能

“移动平均”分析工具可以基于特定的过去某段时期中变量的平均值,对未来值进行预测。移动平均值提供了由所有历史数据的简单的平均值所代表的趋势信息。使用此工具适用于变化较均匀的销售量、库存或其他趋势的预测。预测值的计算公式如下:

18.2 移动平均工具的使用

例:对图中的数据按时间跨度为3进行移动平均预测。

表 18-1 观测值数据

操作步骤:

(1)建立EXCEL数据清单(图图 182:B列)

(2)“数据|分析|数据分析|移动平均”弹出移动平均对话框,并设置如下:

图 18-1 移动平均对话框

数据源区域:在此输入待分析数据区域的单元格引用。该区域必须由包含四个或四个以上的数据单元格的单列组成。

标志位于第一行:如果数据源区域的第一行中包含标志项,请选中此复选框。

间隔:在此输入需要在移动平均计算中包含的数值个数。默认间隔为 3。

输出区域:在此输入对输出表左上角单元格的引用。如果选中了“标准误差”复选框,Excel 将生成一个两列的输出表,其中右边的一列为标准误差值。如果没有足够的历史数据来进行预测或计算标准误差值,Excel 会返回错误值 #N/A。

输出区域必须与数据源区域中使用的数据位于同一张工作表中。因此,“新工作表”和“新工作簿”选项均不可用。

图表输出:选中此选项可在输出表中生成一个嵌入直方图。

标准误差:如果选中此复选框,则在在输出表的一列中包含标准误差值。

(3)单击“确定”得到移动平均预测结果

图 18-2 移动平均预测结果

图 18-3 移动平均预测结果(公式显示模式)

18.3 移动平均工具的缺点与改进

移动平均工具在设置对话框时,要求输入时间间隔,该间隔不能用单元格的引用,因此不能由优化工具(规划求解)来求得最优时间间隔;故建议直接利用公式。操作如下:

(1)在C2单元格输入如图所示公式,并复制到C3:C13单元格区域。则当改变F3单元格的间隔值时,其平均预测值将改变。

图 18-4 移动平均预测公式

(2)在F3单元格输入如图 184所示公式,求得均方误。

=SUMXMY2(OFFSET(B2,F2,0,12-F2,1),OFFSET(C2,F2,0,12-F2,1))/(12-F2)

(3)在E4:E11单元格输入不同间隔,利用数据表求得均方误。

(4)选中E3:F11单元格区域,“数据|假设分析|数据表”,弹出模拟运算表对话框,并设置如下:

图 18-5 数据表设置

(5)单击“确定”,得最优时间跨度。

图 18-6 数据表运算结果

由图可见,时间跨度为5时,均方误最小。

指数平滑

19.1 指数平滑法简介

指数平滑法(Exponential Smoothing,ES)是布朗(Robert G..Brown)所提出,布朗、认为时间序列的态势具有稳定性或规则性,所以时间序列可被合理地顺势推延;他认为最近的过去态势,在某种程度上会持续的未来,所以将较大的权数放在最近的资料。即:

19.2 EXCEL指数平滑工具的使用

指数平滑常数取值至关重要。平滑常数决定了平滑水平以及对预测值与实际结果之间差异的响应速度。平滑常数a越接近于1,远期实际值对本期平滑值的下降越迅速;平滑常数a越接近于 0,远期实际值对本期平滑值影响程度的下降越缓慢。由此,当时间数列相对平稳时,可取较大的a。

例:对如下12周的数据,利用EXCEL指数平滑工具求各期平滑值。

(1)在EXCEL中输入数据。

图 19-1 数据资料

(2)从“数据”选项卡选择“数据分析”,选择“指数平滑”,单击“确定”弹出对话框如下:

图 19-2 指数平滑对话框

(3)单击“确定”得到指数平滑结果(图 193,公式显示模型图 194)

图 19-3 指数平滑结果

图 19-4 指数平滑结果(公式显示模式)

图中C列为平滑值,D列的标准误差。此标准误差为近3期的平均标准误。

19.3 指数平滑工具的缺点与改进

指数平滑工具在设置对话框时,要求输入阻尼系数,因此对于求得的平滑结果有两个问题:一是不能由优化工具(规划求解)来求得最优平滑系数;二是对于近期的平均标准误不能人为地改变时间跨度。因此不建议使用指数平滑工具,而建议直接利用公式。操作如下:

(1)根据公式(4)在C2单元格输入“=B2”,确定Y的初值,在C3单元格输入如图5所示公式,其中平滑系数引用F2单元格的值,以便利用“规划求解”工具进行优化。将C3单元格的公式复制到C3:C13单元格区域,得指数平滑值。

(2)在F3单元格输入如图 195所示公式,求得误差平方和,该值与标准误同时达到最小。

图 19-5 指数平滑模型的建立(公式显示模式)

图 19-6 指数平滑模型的建立(普通显示模式)

(3)利用“规划求解”工具求得最优平滑系数。

从“数据”选项卡选择“规划求解”,调出规划求解参数对话框,并设置如图 197其约束添加如下:单击“添加”,弹出图 198所示添加约束对话框,并设置如图 198.单击“确定”,返回规划求解参数对话框。

图 19-7 规划求解参数对话框的设置

图 19-8 添加约束

(4)在图 197所示对话框中单击“选项”,设置为“假定非负”,单击“确定”返回规划求解参数对话框。

图 19-9 规划求解选项设置“假定非负”

在图7所示对话框中单击“求解”,得最优平滑系数如图 1910所示。即最优平滑系数为0.2843。

图 19-10 规划求解结果

傅利叶分析

20.1 傅利叶分析基本知识简介

傅利叶分析Fourier analysis 是分析学中18世纪逐渐形成的一个重要分支,主要研究函数的傅利叶变换及其性质。Excel中的傅立叶分析是求解离散型快速傅立叶变换和逆变换。

快速傅利叶变换(Fast Fourier Transform, FFT),是离散傅利叶变换的快速算法,也可用于计算离散傅利叶变换的逆变换。快速傅利叶变换有广泛的应用,如数字信号处理、计算大整数乘法、求解偏微分方程等等。在经济管理中可用于判断时间序列周期性。

20.2 傅利叶工具时间序列频谱分析中的应用

对于时间序列,可以展开成傅利叶级数,进行频谱分析。对于时间序列xt其傅立叶级数展开式为展开成傅立叶级数:

20.3 傅利叶分析工具应用操作

步骤

(1)输入数据并中心化:时间、时间序号t、观测值xt、中心化(减x平均值)、求频率fi(=i/N)。

(2)由傅立叶分析工具求中心化数据序列的傅立叶变换。

(3)IMREAL和IMAGINARY提取实部和虚部,按公式5计算频率强度(或由IMCONJUGATE求得共轭复数,再由IMPRODUCT求得两共轭复数乘积,得频率强度。

(4)以频率为横坐标、频率强度为纵坐标,绘制频率强度图。

(5)分析周期性。由频率强度最大的所对应的频率倒数即得周期。

例:某时间序列如图 20-1

图 20-1 时间序列观测值及其图形

由图可见,序列显现周期性变化,在整个时期范围内,周期为4.下面利用傅立叶分析工具进行频谱分析。

(1)在B18单元格输入“=AVERAGE(B2:B17)”求得观测值的平均值;在C2单元格输入“=B2/B$18”,将观测值中心化(均值为0,并仍保持原序列的方差),并复制到C3:C17

图 20-2 傅立叶变换及频率强度计算过程

(2)从“数据”选项卡选择“数据分析”|选择“傅利叶分析”弹出对话框并设置如图 20-3:

图 20-3 傅利叶分析对话框

(2)单击“确定”生成傅立叶变换序列(图 20-2 D列)。

(3)在E2单元格输入“=IMCONJUGATE(D2)”求得傅利叶变换值的共轭复数,并复制到E3:E17;在F3至F17输入1至15,列出周期序列;在G3单元格输入“=F3/16”求得频率,并复制到G4:G17;在H3单元格输入“=IMPRODUCT(D3:E3)*8”(即根据公式5)求得频率强度,并复制到H4:H17。(见图3)

(4)以G3:H17为源数据,插入散点图,得图 204所示频率强度频谱图。

图 20-4 频率强度频谱图

由图可见,图形完全对称,通常只取左半部分。频率强度最大的所对应的频率为0.25,其倒数为4,即周期为4。

本文分享自微信公众号 - 大数据杂谈(BigData07)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-07-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【Excel系列】Excel数据分析:相关与回归分析

    相关系数 15.1 相关系数的概念 著名统计学家卡尔·皮尔逊设计了统计指标——相关系数(Correlation coefficient)。相关系数是用以反映变量...

    数据科学社区
  • 【Excel系列】Excel数据分析:抽样设计

    一、随机数发生器 1. 随机数发生器主要功能 “随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。...

    数据科学社区
  • 【Excel系列】Excel数据分析:参数估计

    一、描述统计 在数据分析的时候,一般首先要对数据进行描述性统计分析(Descriptive Analysis),以发现其内在的规律,再选择进一步分析的方法。描述...

    数据科学社区
  • [Json框架选型]Android开发中应该使用哪一种主流json框架?

    前段时间@寒江不钓同学针对国内Top500和Google Play Top200 Android应用做了全面的分析(具体分析报告见文末的参考资料...

    open
  • 学界 | CMU提出对抗生成网络:可实现对人脸识别模型的神经网络攻击

    机器之心
  • Android 5秒学会使用手势解锁功能

    本文讲述的是一个手势解锁的库,可以定制显示隐藏宫格点、路径、并且带有小九宫格显示图,和震动!让你学会使用这个简单,高效的库!

    砸漏
  • 大规模场景下 k8s 集群的性能优化

    对于不同 object 进行分库存储,首先应该将数据与状态分离,即将 events 放在单独的 etcd 实例中,在 apiserver 的配置中加上--etc...

    我的小碗汤
  • CSS实现文本向两端对齐的方法

    飞奔去旅行
  • ARM架构下的Docker环境,OpenJDK官方没有8版本镜像,如何完美解决?

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    程序员欣宸
  • spark整合hive+hbase做数据实时插入及实时查询分析

            使用的spark是2.0.1,hive是1.2.1,hbase是1.2.4,hadoop是2.6.0,zookeeper是3.4.9

    尚浩宇

扫码关注云+社区

领取腾讯云代金券