应用Excel进行时间序列分析

数据预测分析专题之一——时间序列预测管理科学与工程学院隋莉萍数据预测分析的两个主要方面：时间序列预测回归分析预测内容简介时间序列的概念和组成时间序列预测的步骤衡量预测准确性的指标移动平均模型和指数平滑模型趋势预测模型季节指数模型一、时间序列预测概述1.时间序列时间序列就是一个变量在一定时间段内不同时间点上观测值的集合。这些观测值是按时间顺序排列的，时间点之间的间隔是相等的。可以是年、季度、月、周、日或其它时间段。常见的时间序列有：按年、季度、月、周、日统计的商品销量、销售额或库存量，按年统计的一个省市或国家的国民生产总值、人口出生率等。产品名称（全部）求和项:销售金额年订购日期汇总1996年7月27861.894968月25485.274999月26381.3999910月37515.7249111月45600.0449412月45239.629971997年1月61258.069932月38483.634943月38547.219974月53032.952435月53781.289936月36362.802457月51020.857458月47287.669959月55629.24246一、时间序列预测概述2.时间序列预测方法定性分析方法定量分析方法外推法：找出时间序列观测值中的变化规律与趋势，然后通过对这些规律或趋势的外推来确定未来的预测值。包括：移动平均和指数平滑法趋势预测法季节指数法因果法：寻找时间序列因变量观测值与自变量观测值之间的函数依赖关系（因果关系/回归分析），然后利用这种函数关系和自变量的预计值来确定因变量的预测值。一、时间序列预测概述405060708090100110120130123456789101112月销量无趋势60657075808590951001051357911131517192123月销量线性趋势901001101201301401501601701357911131517192123月销量非线性趋势020406080100123456789101112月销售额第一年第二年季节成分3.时间序列成分趋势成分：显示一个时间序列在较长时期的变化趋势季节成分：反映时间序列在一年中有规律的变化循环成分：反映时间序列在超过一年的时间内有规律的变化不规则成分：不能归因于上述三种成分的时间序列的变化二、时间序列的预测步骤第一步，确定时间序列的类型即分析时间序列的组成成分（趋势成分/季节成分/循环成分）。第二步，选择合适的方法建立预测模型如果时间序列没有趋势和季节成分，可选择移动平均或指数平滑法如果时间序列含有趋势成分，可选择趋势预测法如果时间序列含有季节成分，可选择季节指数法第三步，评价模型准确性，确定最优模型参数第四步，按要求进行预测2112)(11tnttnttFYnenMSE三、移动平均模型和指数平滑模型适用于围绕一个稳定水平上下波动的时间序列。1.移动平均模型利用平均使各个时间点上的观测值中的随机因素互相抵消掉，以获得关于稳定水平的预测将包括当前时刻在内的N个时间点上的观测值的平均值作为对于下一时刻的预测值（N应选择得使MSE极小化）NiittYNF1111【例1】某汽油批发商在过去12周内汽油的销售数量如表所示：试在Excel工作表中建立一个移动平均预测模型来预测第13周的汽油销量。实例：移动平均模型周销量（千加仑）周销量（千加仑）117722221818319922423102051811176201222三、移动平均模型和指数平滑模型汽油销量观测值及其移动平均预测值图形19.80101520253012345678910111213周销量观测值移动平均预测值移动平均跨度=5MSE=4.11三、移动平均模型和指数平滑模型2.指数平滑模型（改进移动平均预测模型），将计算平均值时对于不同时期观测值的权数设置得不同：近期的权数较大，远期的权数较小2211)1()1(ttttYYYFtttFYF)1(1)(1ttttFYFF三、移动平均模型和指数平滑模型指数平滑的叠代算法时间序列观测值:tF时间序列预测值tttFYF)1(1:tY时间序列观测值:tF时间序列预测值)(1ttttFYFF:tY【例2】利用例1的数据在Excel工作表中建立一个指数平滑预测模型来预测第13周的汽油销量。实例：指数平滑模型汽油销量观测值及其指数平滑预测值20.07101520253012345678910111213周销量观测值指数平滑预测值平滑常数=0.3MSE=6.95实例：使用控件求解最优跨度和最优平滑指数【例4/例5】利用例1的数据在Excel工作表中建立一个利用函数和控件来控制移动跨度、平滑指数的移动平均模型和指数平滑预测模型来预测第13周的汽油销量。试探索共有几种利用MSE求最优跨度和平滑系数的途径？四、趋势预测模型对于含有线性趋势成分的时间序列，可以将预测变量在每一个时期的值和其对应时期之间的线性依赖关系表示为：利用使均方误差MSE极小的原则确定系数a与b，就可得到直线趋势方程。以此求得每一个Xi所对应的预测值:nibXaYiii,,2,1,bXaYˆiibXaYˆ四、趋势预测模型求解a和b的三种方法：利用Excel内建函数INTERCEPT()和SLOPE()利用数组函数LINEST()利用规划求解工具求解预测值的四种方法：利用线性趋势方程直接计算利用Excel内建函数TREND()利用Excel内建函数FORECAST()用特殊方法拖动观测值所在范围bXaYˆ实例：趋势预测模型【例3】针对NorthwindTraders公司月销售额时间序列，建立趋势预测模型，并预测该公司未来3个月的销售额。NorthwindTraders公司月销售额观测值及其直线趋势预测值103372.6099915.7496458.880200004000060000800001000001200001400001996年7月1996年9月1996年11月1997年1月1997年3月1997年5月1997年7月1997年9月1997年11月1998年1月1998年3月1998年5月1998年7月观测值预测值五、Holt模型))(1(11ttttTLXL11)1)((ttttTLLTtttTLY1实例：Holt预测模型【例6】某商场两年内各个月份的空调机销售额数据如下表所示。假定商场空调机前年最后一个月的销售额为42，前年销售额的平均月增长幅度为2.93。试建立一个Holt模型对商场未来的销售额进行预测。月份144月份757月份1379月份1996月份248月份867月份1482月份20100月份351月份972月份1580月份21100月份452月份1068月份1685月份22105月份558月份1172月份1794月份23110月份655月份1269月份1889月份24111商场各个月份空调销售额六、季节指数模型对于既含有线性趋势成分又含有季节成分的时间序列，须对其成分进行分解，这种分解建立在以下乘法模型的基础上：其中，Tt表示趋势成分，St表示季节成分，It表示不规则成分。由于不规则成分的不可预测，因此预测值就可表示为趋势成分和季节成分的乘积。ttttISTY六、季节指数模型建立季节指数模型的一般步骤：第一步，计算每一季（每季度，每月等等）的季节指数St。第二步，用时间序列的每一个观测值除以适当的季节指数，消除季节影响。第三步，为消除了季节影响的时间序列建立适当的趋势模型并用这个模型进行预测。第四步，用预测值乘以季节指数，计算出最终的带季节影响的预测值。实例：季节指数模型【例7】某工厂过去4年的空调机销量如下表所示，这些数据有明显的季节性波动，试建立一个季节指数模型来预测第5年每个季度的空调机销量。四年内每季度的电视机销量表年季度销量（千台）年季度销量（千台）114.831624.125.63637.546.547.8215.8416.325.225.936.83847.448.4实例：季节指数模型电视机销量观测值及其季度预测值7.096.498.639.1945678910第1年1季度第1年2季度第1年3季度第1年4季度第2年1季度第2年2季度第2年3季度第2年4季度第3年1季度第3年2季度第3年3季度第3年4季度第4年1季度第4年2季度第4年3季度第4年4季度第5年1季度第5年2季度第5年3季度第5年4季度实例：季节指数模型【例8】某工厂过去四个5年的纳税情况如右表所示，这些数据有明显的季节性波动，试建立一个季节指数模型来预测下一个5年的纳税情况。周期年纳税额(万元)119864.819874.119885.61989619906.5219915.819925.219936.419946.819957.431996619975.619987.119997.520007.8420016.320025.920037.52004820058.4本章小结本章重点是时间序列的四种EXCEL工作表预测模型移动平均模型指数平滑模型趋势预测模型季节指数模型主要函数和EXCEL技术OFFSET()、SUMXMY2()、INDEX()、MATCH()、INTERCEPT()、SLOPE()、LINEST()、TREND()、FORECAST()“规划求解”工具、“数据分析”工具、可调图形的制作bXaYˆNiittYNF1111tttFYF)1(1tttSTYˆ27应用Excel进行时间序列分析zfzf重点1、Excel进行移动平均分析的操作步骤2、Excel进行指数平滑分析的操作步骤3、Excel进行趋势外推预测法的操作步骤4、Excel进行时间序列分解法的操作步骤29移动平均法是一种简单平滑预测技术，它的基本思想是：根据时间序列资料、逐项推移，依次计算包含一定项数的序时平均值，以反映长期趋势的方法。因此，当时间序列的数值由于受周期变动和随机波动的影响，起伏较大，不易显示出事件的发展趋势时，使用移动平均法可以消除这些因素的影响，显示出事件的发展方向与趋势（即趋势线），然后依趋势线分析预测序列的长期趋势。30§1Excel进行移动平均分析的操作步骤简单移动平均法公式表明当t向前移动一个时期，就增加一个新近数据，去掉一个远期数据，得到一个新的平均数。由于它不断地“吐故纳新”，逐期向前移动，所以称为移动平均法。由于移动平均可以平滑数据，消除周期变动和不规则变动的影响，使得长期趋势显示出来，因而可以用于预测。其预测公式为：即以第t周期的一次移动平均数作为第t+1周期的预测值。1ttFS31趋势移动平均法(线性二次移动平均法)当时间序列没有明显的趋势变动时，使用一次移动平均就能够准确地反映实际情况，直接用第t周期的一次移动平均数就可预测第t+1周期之值。但当时间序列出现线性变动趋势时，用一次移动平均数来预测就会出现滞后偏差。因此，需要进行修正，修正的方法是在一次移动平均的基础上再做二次移动平均，利用移动平均滞后偏差的规律找出曲线的发展方向和发展趋势，然后才建立直线趋势的预测模型。故称为趋势移动平均法。32应用举例已知某商场1978～1998年的年销售额如下表所示，试预测1999年该商场的年销售额。年份销售额年份销售额1978321989761979411990731980481991791981531992841982511993861983581994871984571995921985641996951986691997101198767199810719886933下面使用移动平均工具进行预测，具体操作步骤如下：1.选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框