用Excel做时间序列预测法实例分析

1、用excel做时间序列预测法实例分析4.3.1时间序列预测法概述1.时间序列预测法的概念,时间序列是指把历史统计资料按时间顺序排列起来得到的一组数据序列。例如，按月份排列的某种商品的销售量。时间序列预测法是将预测目标的历史数据按时间顺序排列成为时间序列，然后分析它随时间变化的发展趋势，外推预测目标的未来值。因此，时间序列预测法主要用于分析影响事物的主要因素比较困难或相关变量资料难以得到的情况，预测时先要进行时间序列的模式分析。时间序列预测法通常又分为移动平均法、指数平滑法、趋势外推法、季节分析法和生命周期法等。2.时间序列模式不同的时间序列预测方法只适用于一定的数据时间序列模式。时间序列的模式

2、，是指历史时间序列所反映的某种可以识别的事物变动趋势形态。时间序列的基本模式，可以归纳为水平型、趋势型、周期变动型和随机型四种类型，它们大体反映了市场供求变动的基本形态。(1)水平型。水平型时间序列模式是指时间序列各个观察值呈现出围绕着某个定值上下波动的变动形态。如某些非季节性的生活必需品的逐月销售量等。水平型的时间序列模式一般采用平均法进行预测。(2)趋势型。趋势型时间序列模式是指时间序列在一定时期内虽出现小范围的上下波动，但总体上呈现出持续上升或下降趋势的变动形态。如高档耐用消费品的经济寿命曲线等。趋势型时间序列模式依其特征不同又可分为线性和非线性趋势模式。一般采用趋势外推预测法。(3)周

3、期变动型。周期变动型时间序列模式是指随着时间的推移，时间序列呈现出有规则的上升与下降循环变动的形态。按时间序列循环波动的周期不同，可分为季节变动型模式和循环变动型模式两类。常见的是季节变动型模式，这种模式往往以年为变动周期，按月或按季度编制时间序列，如许多季节性消费品的按月、按季销售量等一般采用季节指数法进行预测。(4)随机型。随机型时间序列模式是指时间序列呈现出的变化趋势走向升降不定、没有一定规律可循的变动势态。这种现象往往是由于某些偶然因素引起的，如经济现象中的不规则变动、政治变动以及自然气候的突变等。对于这类时间序列模式，很难运用时间序列预测方法作出预测，但有时也可通过某种统计处理，消除

4、不规则因素的影响，找出事物固定的变化规律，从而进行分析预测。4.3.2移动平均预测法实例分析例2，某家电产品2009年112月份实际市场销售额如表4-2所示。试运用移动平均法和二次移动平均法，采用近4期数据预测2010年1月份的市场需求量。表4-2某产品2009年市场销售额（单位；万元）1.移动平均法概述移动平均法的计算过程是对一组近期实际值取平均值，将这个平均值作为下期预测值，逐项移动，形成一个序列平均数的时间序列。它是根据时间序列资料、逐项推移，依次计算包含一定项数的序时平均值，以反映长期变动趋势的方法。当时间序列的数值由于受到周期变动和随机波动的影响，起伏较大，不易显示出事件的发展趋势时

5、，使用移动平均法可以消除这些因素的影响，显示出事物的发展方向与趋势（即趋势线），然后依趋势线分析预测序列的长期趋势。移动平均法分为一次移动平均法和二次移动平均法，其计算公式如下：设有一时间序列义，&，4，则一次移动平均法计算公式为：二次移动平均是对一次移动平均值序列再进行一次移动平均。二次移动平均法的计算公式。运用excel构建移动平均法预测分析模型的步骤步骤1:根据已知数据构建产品市场需求量预测模型表格，如图4-5所示。步骤2:根据一次移动平均法思路及n=4输入公式。由于n=4，所以一次移动平均所在单元格e七e16的公式输入应该从e8单元格开始（即第5期），对实际销售额求平均值。在单元格e8

6、输入公式为m=average(d4：d7)，并运用公式复制填充生成e9:e16单元格公式。结果如图4-6所示。通过图4-6可以看到，通过一次移动平均法2010年1月份产品市场预测需求步骤3:根据二次移动平均法思路及n=4输入公式。由于n=4，所以二次移动平均所在单元格f4:f16的公式输入应该从f12单元格开始（即一次移动平均预测值第5期，实际值第9期），对一次移动平均预测销售额求平均值。在单元格f12输入公式为average(e8：e11)，并运用公式复制填充生成f13:e16单元格公式。预测结果如图4-7所示。通过图4-7可以看到，通过二次移动平均法2010年1月份产品市场预测需求量为10

7、77.88万元。average函数（）用法1.功能：average函数为返回参数的平均值算术平均值）。2语法：average(numberl,number2,）其中，numberl,number2,为需要计算平均值的1到如个参数。参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。廳通过上述分析我们可以看出，每次调整n值，有关公式还必须做重新的设计，_这样就减少了模型的动态适应。可不可以通过一种方式的公式输入使模型根据n值士的变化动态产生预测数据呢？答案是肯定的。3.动态模型优化的思路(1)根据n值

8、的变化，运用if函数通过n值与每一时期栏数据比较，自动产生输出数据的开始单元格。比如ri=2时，如果时期栏单元格数值大于2，则在一次移动平均单元格产生预测值。如果时期栏数据大于4(2+2)，则在二次移动平均单元格产生预测值。即自动在一次移动平均单元格e6开始产生数据，二次移动平均单元格f8产生数据。(2)根据n值的变化，通过时期栏数值与n值的相关比较运算及offset (reference,rows，cols,height,width)函数的偏移行数、列数及取几行、几列参数的设置，就可以自动产生每一期n个近期数据，然后对n个近期数据求平均值即可得到相应的一次移动平均预测值和二次移动平均预测值。

9、具体方法及步骤如下。步骤1:运用if函数自动产生输出预测值的开始单元格位置。通过n值与时期栏单元格数值比较，如果时期栏单元格数值大于n(2n)值，则对应的一次移动平均和二次移动平均单元格自动产生预测值，不然以一一”为输出。在一次移动平均单元格e4输入公式为“=|f(c4$e$3,“输出预测值”，“，），在二次移动平均单元格f4输公式中的一次移动平均和两次移动平均的单元格e3与f3的引用一定要用绝对引用形式来表示。如果把n值调整为5，则自动输出预测值的开始位置如图4-9所示。步骤2:运用offset函数自动产生某期数的前近n期数据。由于开始输出预测值的单元格一定是从实际值或一次移动预测值的第1期

10、开始，所以在输出预测值的第一个单元格，偏移行数一定为零。按照这个思路。一次移动平均法下的offset(reference，rows,cols,height,width)函数各参数可以这样设置：reference参数设置为实际销售额单元格区域$d$4:$d$15(为绝对引用），偏移行数rows参数设置为时期栏单元格值-n+1，偏移列数cols参数设置为0，行高height参数设置为n，列宽width参数设置为1。以n=3为例，则一次移动平均法第一个预测值输出单元格e7的前近3期数据生成公式为：=0ffset($d$4：$d$15,c7-$e$3-1，0，$e$3,1)。同理，二次移动平均法第一个

11、预测值输出单元格f10的公式为：=0ffset($e$4：$e$15,c10-$f$3-1,0，$f$3,1)。步骤3:求各期的预测值。按照一次移动平均和二次移动平均法的思路，运用average函数，求各期的预测值。以n=3为例，则一次移动平均单元格e7的预测值公式为：=average(offset($d$4：$d$15,c7-$e$3-1，0，$e$3，1)g二次移动平均单元格f10的预测值公式为：m=average(offset($e$4：$e$15,c10-$f$3-1,0，$f$3,1)g步骤4:根据上述方法，设计动态预测值输出公式。根据上述三步骤的思路及方法，把三步骤的公式进行综合，

12、设计一次移动平均与二次移动平均法下的动态预测值输出公式。在单元格e4输入的公式为：“=if(c4$e$3，average(offset($d$4：$d$15,c4-$e$3-1，0，$e$3，1)，）”。然后复制填充公式到单元e5:e15。在单位格f4输入公式为：“=if(c4($e$3+$f$3)，average(offset($e$4：$e$15,c4-$f$3-1,0，$f$3，1)，)则n=3时的运行结果如图4-10所示。步骤5:调整n值，检验其思路及公式输入的正确性。把n值调整为4，则预测值动态输出结果如图4-11所示。通过图4-11与图4-7对比分析，可以看出此预测值动态设计的思路

13、与公式输入是正确的。上述动态预测模型也可以预测不同时间间隔的移动平均预测的情况，如一次移动平均n=3，二次移动平均n=4也将动态产生预测值，结果如图4-12所示offset()函数用法1.功能：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。2语法：offset(reference,rows,cols,height,width)4.3.3指数平滑预测法实例分析例3，承例2，试运用一次指数平滑法预测下一年度1月份的市场需求。1.指数平滑预测法概述指数平滑预测法是对不规则的时间序列数据加以平滑，从而获得其变化规律和趋势，以此对未

14、来的经济数据进行推断和预测。根据平滑次数的不同，有一次指数平滑、二次指数平滑及高次指数平滑三种，但高次指数平滑很少使用。一次指数平滑法是根据前期的实测数和预测数，以加权因子为权数进行加权平均，来预测未来时间趋势的方法。一次指数平滑法计算公式为：式中，冬为时期t的实测值；为时期t的预测值；a为平滑系数，又称加权因子，取值范围为0al。将，&的表达式逐次代入+1中，展开整理后，得：从上式可以看出，新预测值是根据预测误差对原预测值进行修正得到的。a的大小表明了修正的幅度。a值愈大，修正的幅度愈大；a值愈小，修正的幅度愈小。因此，a值既代表了预测模型对时间序列数据变化的反应速度，又体现了预测模型修匀误

15、差的能力。 .在实际应用中，a值是根据时间序列的变化特性来选取的。若时间序列的波动不大，比较平稳，则a应取小一些，如0.10.3;若时间序列具有迅速且明显的变动倾向，贝ija应取大一些，如0.60.9。实质上，a是一个经验数据，通过多个a值进行试算比较而定，哪个a值引起的预测误差小，就采用哪个。2.运用excel进行一次指数平滑法预测分析，其分析步骤如下。步骤1:根据已知数据及一次平滑预测法的原理构建excel模型表格，如图4-13所示。步骤2:确定初始值。以前三个实测值的平均值作为初始值。其值保持在单元格c4，其公式为“=average(c5:c7)，结果如图4-13单元格c4所示。步骤3:

16、计算不同平滑系数下的平滑值。(1)在【工具】菜单上选择【数据分析】命令，弹出【数据分析】菜单，选择【指数平滑】分析工具，出现【指数平滑】菜单。(2)在【指数平滑】对话框上输入的平滑系数为0.2，如图4-14所示，按【确定】按钮后，结果如图4-15单元格d5:d16所示，输出图表如图4-16所示。1.【指数平滑】分析工具对话框中的【阻尼系数】值为l-a，而不是a。2.输出区域从d4开始，而不是从d5开始。3.同样的方法，在【指数平滑】对话框中分别输入平滑系统为0.4，0.7的相关参数，按【确定】按钮后，结果如图4_17单元格f5:f16，h5：h16所示。表4-3单元格公式e5 =power(c

17、5-d5)，2)g5 =power(c5-f5)，2)15 =power(c5-i)，2)e17 =sum(e5：e16)e18 =sqrt(e17/12)步骤4:计算各平滑系数下的平方误差合计和均方差。在单元格e5:e16,g5：g16和!5：m6计算出各期预测值与实际值之间的平方误差，在单元格e17，g17和117计算出平方误差合计，在单元格e18，g18和118计算出均方差。结果如图4-18所示。e5，g5,15,e17及e18的单元格公式如表4-3所示。一次指数平滑各平滑系数下平方误差合计和均方差结果步骤5:计算各平滑系数下的平方误差合计和均方差。在单元格e5:e16,g5：g16和1

18、5:116计算出各期预测值与实际值之间的平方误差，在单元格e17，g17和117计算出平方误差合计，在单元格e18，g18和118计算出均方差。结果如图4-18所示。e5,g5,15，e17及e18的单元格公式如表4-3所示。步骤6:选择最优的平滑系数，计算下期预测值。(1)通过不同平滑系数下的预测误差与均方差对比，动态选择出最小均方差下的平滑系数值作为计算下一期预测值的平滑系数。平滑系数单元格d19(d19,d20合并）公式为：k=index(e2：12，match(min(e18：118)，e18：118,0)，(2)根据最小均方差动态产生对应的本期预测值。对应最小均方差下的本期预测值单元

19、格h19(为h19，h20合并）公式为“=index(d16:116,match(min(d18：118)，e18：118,0)-1)(3)根据一次平滑值计算公式yt+1=axt+(1-a)yt,计算出下期的预测值。下期预测值所在单元格i20公式为“=f19+(1-d19)*h19”，其中，f19为第12期的实际值1092。结果如图4-19所不。通过图4-19可以看出，2010年1月份产品的市场需求量为110.91万元。4.3.4同季周期预测法实例分析例4，某啤酒产品20062009年各季市场需求如表4-4所示，试运用同季平均法预测2006年各季的市场需求。表4-4某啤酒产品20062009年各季市场需求量（单位：亿元）年 一季度 二季度 三季度 四季度2006 1.66 4.07 4.38 3.192007 1.46 4.72 5.76 3.452008 1.59 4.39 5.6 3.472009 2.42 4.14 5.02 2.761.同季周期平均法概述许多产品的市场需求具有季节性，如服装、空调和冷饮等。对于这类产品市场需求的预测，企业需要考虑季节波动的因素。同季平均法是分析、预测季节波动一种最常用、最简单的方法，主要用于受季节波动和不规则波动影响，而无明显趋势变动规律的产品市场需求预测。预测过程