应用Excel进行时间序列分析说课材料.ppt

《应用Excel进行时间序列分析说课材料.ppt》由会员分享，可在线阅读，更多相关《应用Excel进行时间序列分析说课材料.ppt（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据预测(yc)分析专题之一时间序列预测(yc)管理科学与工程学院管理科学与工程学院隋莉萍隋莉萍第一页，共56页。数据(shj)预测分析的两个主要方面：时间时间(shjin)(shjin)序列预测序列预测回归分析预测回归分析预测第二页，共56页。内容简介时间序列的概念和组成时间序列预测的步骤衡量预测准确性的指标移动平均模型和指数(zhsh)平滑模型趋势预测模型季节指数(zhsh)模型第三页，共56页。一、时间(shjin)序列预测概述 1.1.时间序列时间序列时间序列就是一个变量在一定时间段内不同时间点上时间序列就是一个变量在一定时间段内不同时间点上观测值的集合观测值的集合 。这些观测值是按时

2、间顺序排列的，。这些观测值是按时间顺序排列的，时间点之间的间隔是相等的。可以是年、季度、月、时间点之间的间隔是相等的。可以是年、季度、月、周、日或其它周、日或其它(qt)(qt)时间段。时间段。常见的时间序列有：按年、季度、月、周、日统计的常见的时间序列有：按年、季度、月、周、日统计的商品销量、销售额或库存量，按年统计的一个省市商品销量、销售额或库存量，按年统计的一个省市或国家的国民生产总值、人口出生率等。或国家的国民生产总值、人口出生率等。第四页，共56页。第五页，共56页。一、时间(shjin)序列预测概述2.2.时间序列预测方法时间序列预测方法定性分析方法定性分析方法定量分析方法定量分析

3、方法外推法：找出时间序列观测值中的变化规律与趋势，然外推法：找出时间序列观测值中的变化规律与趋势，然后通过对这些后通过对这些(zhxi)(zhxi)规律或趋势的外推来确定未规律或趋势的外推来确定未来的预测值。包括：来的预测值。包括：移动平均和指数平滑法移动平均和指数平滑法趋势预测法趋势预测法季节指数法季节指数法因果法：寻找时间序列因变量观测值与自变量观测值之因果法：寻找时间序列因变量观测值与自变量观测值之间的函数依赖关系（因果关系间的函数依赖关系（因果关系/回归分析），然后利回归分析），然后利用这种函数关系和自变量的预计值来确定因变量的预用这种函数关系和自变量的预计值来确定因变量的预测值。测值

4、。第六页，共56页。一、时间序列(xli)预测概述无趋势无趋势线性趋势线性趋势非线性趋势非线性趋势季节成分季节成分3.3.时间时间(shjin)(shjin)序列成分序列成分趋势成分：显示一个时间趋势成分：显示一个时间(shjin)(shjin)序列在较长时期序列在较长时期的变化趋势的变化趋势 季节成分：反映时间季节成分：反映时间(shjin)(shjin)序列在一年中有规律序列在一年中有规律的变化的变化 循环成分：反映时间循环成分：反映时间(shjin)(shjin)序列在超过一年的时序列在超过一年的时间间(shjin)(shjin)内有规律的变化内有规律的变化 不规则成分：不能归因于上述三

5、种成分的时间不规则成分：不能归因于上述三种成分的时间(shjin)(shjin)序列的变化序列的变化第七页，共56页。二、时间序列(xli)的预测步骤 第一步，确定时间序列的类型第一步，确定时间序列的类型 即分析时间序列的组成成分（趋势成分即分析时间序列的组成成分（趋势成分/季节成分季节成分/循环成分）循环成分）。第二步，选择合适的方法建立预测模型第二步，选择合适的方法建立预测模型 如果时间序列没有趋势和季节成分，可选择移动平均或指数平滑如果时间序列没有趋势和季节成分，可选择移动平均或指数平滑法法如果时间序列含有趋势成分，可选择趋势预测法如果时间序列含有趋势成分，可选择趋势预测法如果时间序列含

6、有季节成分，可选择季节指数法如果时间序列含有季节成分，可选择季节指数法第三步，评价模型准确性，确定最优模型参数第三步，评价模型准确性，确定最优模型参数 第四步，按要求第四步，按要求(yoqi)(yoqi)进行预测进行预测第八页，共56页。三、移动(ydng)平均模型和指数平滑模型适用于围绕一个稳定水平上下波动的时间适用于围绕一个稳定水平上下波动的时间(shjin)(shjin)序列。序列。1.1.移动平均模型移动平均模型利用平均使各个时间利用平均使各个时间(shjin)(shjin)点上的观测值中的点上的观测值中的随机因素互相抵消掉，以获得关于稳定水平的预测随机因素互相抵消掉，以获得关于稳定水

7、平的预测将包括当前时刻在内的将包括当前时刻在内的N N个时间个时间(shjin)(shjin)点上的观点上的观测值的平均值作为对于下一时刻的预测值（测值的平均值作为对于下一时刻的预测值（N N应选择应选择得使得使MSEMSE极小化）极小化）第九页，共56页。【例1】某汽油批发商在过去12周内汽油的销售(xioshu)数量如表所示：试在Excel工作表中建立一个移动平均预测模型来预测第13周的汽油销量。实例(shl)：移动平均模型第十页，共56页。三、移动平均模型和指数(zhsh)平滑模型第十一页，共56页。三、移动平均(pngjn)模型和指数平滑模型2.2.指数平滑指数平滑(pnghu)(pn

8、ghu)模型模型（改进移动平均预测模型），将计算平均值（改进移动平均预测模型），将计算平均值时对于不同时期观测值的权数设置得不同：时对于不同时期观测值的权数设置得不同：近期的权数较大，远期的权数较小近期的权数较大，远期的权数较小第十二页，共56页。三、移动平均(pngjn)模型和指数平滑模型指数(zhsh)平滑的叠代算法时间序列观测值时间序列观测值时间序列预测值时间序列预测值时间序列观测值时间序列观测值时间序列预测值时间序列预测值第十三页，共56页。【例2】利用例1的数据在Excel工作表中建立一个指数平滑预测(yc)模型来预测(yc)第13周的汽油销量。实例(shl)：指数平滑模型第十四页，

9、共56页。实例(shl)：使用控件求解最优跨度和最优平滑指数【例【例4/4/例例5 5】利用例】利用例1 1的数据在的数据在ExcelExcel工作表中建工作表中建立一个立一个(y)(y)利用函数和控件来控制移动跨利用函数和控件来控制移动跨度、平滑指数的移动平均模型和指数平滑预度、平滑指数的移动平均模型和指数平滑预测模型来预测第测模型来预测第1313周的汽油销量。周的汽油销量。试探索共有几种利用试探索共有几种利用MSEMSE求最优跨度和平滑系数求最优跨度和平滑系数的途径？的途径？第十五页，共56页。四、趋势预测(yc)模型 对于含有线性趋势成分的时间序列，可以将预测变量在每一个时期的值和其对应

10、时期之间的线性依赖关系表示为：利用使均方误差MSE极小(j xio)的原则确定系数a与b，就可得到直线趋势方程。以此求得每一个Xi所对应的预测值:第十六页，共56页。四、趋势预测(yc)模型求解求解a a和和b b的三种方法：的三种方法：利用利用ExcelExcel内建函数内建函数INTERCEPT()INTERCEPT()和和SLOPE()SLOPE()利用数组函数利用数组函数LINEST()LINEST()利用规划求解工具利用规划求解工具求解预测值的四种方法：求解预测值的四种方法：利用线性趋势方程利用线性趋势方程 直接计算直接计算利用利用ExcelExcel内建函数内建函数TREND()T

11、REND()利用利用ExcelExcel内建函数内建函数FORECAST()FORECAST()用特殊方法拖动观测值所在用特殊方法拖动观测值所在(suzi)(suzi)范围范围第十七页，共56页。实例(shl)：趋势预测模型【例3】针对Northwind Traders公司月销售额时间(shjin)序列，建立趋势预测模型，并预测该公司未来3个月的销售额。第十八页，共56页。五、Holt模型(mxng)第十九页，共56页。实例(shl)：Holt预测模型【例6】某商场两年内各个月份的空调机销售额数据如下表所示。假定商场空调机前年最后一个月的销售额为42，前年销售额的平均月增长幅度为2.93。试建

12、立一个Holt模型对商场未来(wili)的销售额进行预测。月份144月份757月份1379月份1996月份248月份867月份1482月份20100月份351月份972月份1580月份21100月份452月份1068月份1685月份22105月份558月份1172月份1794月份23110月份655月份1269月份1889月份24111商场商场(shngchng)各个月份空调销售额各个月份空调销售额第二十页，共56页。六、季节(jji)指数模型对于既含有线性趋势成分又含有季节成分的时间序列，须对其成分进行分解，这种分解建立在以下乘法模型的基础上：其中，Tt表示趋势成分，St表示季节成分，It表

13、示不规则成分。由于不规则成分的不可预测(yc)，因此预测(yc)值就可表示为趋势成分和季节成分的乘积。第二十一页，共56页。六、季节(jji)指数模型建立季节指数模型的一般步骤：建立季节指数模型的一般步骤：第一步，计算每一季（每季度，每月等等）的季节指数第一步，计算每一季（每季度，每月等等）的季节指数St St。第二步，用时间序列的每一个第二步，用时间序列的每一个(y)(y)观测值除以适当观测值除以适当的季节指数，消除季节影响。的季节指数，消除季节影响。第三步，为消除了季节影响的时间序列建立适当的趋势模第三步，为消除了季节影响的时间序列建立适当的趋势模型并用这个模型进行预测。型并用这个模型进行

14、预测。第四步，用预测值乘以季节指数，计算出最终的带季节影第四步，用预测值乘以季节指数，计算出最终的带季节影响的预测值。响的预测值。第二十二页，共56页。实例：季节指数(zhsh)模型【例7】某工厂过去4年的空调机销量如下表所示，这些数据(shj)有明显的季节性波动，试建立一个季节指数模型来预测第5年每个季度的空调机销量。四年内每季度的电视机销量表 第二十三页，共56页。实例(shl)：季节指数模型第二十四页，共56页。实例：季节(jji)指数模型【例8】某工厂过去四个5年的纳税情况如右表所示，这些数据有明显的季节性波动(bdng)，试建立一个季节指数模型来预测下一个5年的纳税情况。周期年纳税额

15、(万元)119864.819874.119885.61989619906.5219915.819925.219936.419946.819957.431996619975.619987.119997.520007.8420016.320025.920037.52004820058.4第二十五页，共56页。本章(bnzhn)小结本章重点是时间序列的四种EXCEL工作表预测模型移动平均模型指数平滑模型趋势(qsh)预测模型季节指数模型主要函数和EXCEL技术OFFSET()、SUMXMY2()、INDEX()、MATCH()、INTERCEPT()、SLOPE()、LINEST()、TREND()

16、、FORECAST()“规划求解”工具、“数据分析”工具、可调图形的制作第二十六页，共56页。27应用应用Excel进行时间序列进行时间序列(xli)分析分析第二十七页，共56页。zfzf重点重点(zhngdin)v1 1、ExcelExcel进行移动平均分析的操作步骤进行移动平均分析的操作步骤v2 2、ExcelExcel进行指数平滑分析的操作步骤进行指数平滑分析的操作步骤v3 3、ExcelExcel进行趋势进行趋势(qsh)(qsh)外推预测法的操作步骤外推预测法的操作步骤v4 4、ExcelExcel进行时间序列分解法的操作步骤进行时间序列分解法的操作步骤第二十八页，共56页。29移动

17、平均法是一种简单平滑预测技术，它的基本思想是：根据时间序列资料、逐项推移，依次计算(jsun)包含一定项数的序时平均值，以反映长期趋势的方法。因此，当时间序列的数值由于受周期变动和随机波动的影响，起伏较大，不易显示出事件的发展趋势时，使用移动平均法可以消除这些因素的影响，显示出事件的发展方向与趋势（即趋势线），然后依趋势线分析预测序列的长期趋势。第二十九页，共56页。301Excel进行进行(jnxng)移动平均分析的操作步骤移动平均分析的操作步骤简单移动平均法简单移动平均法公式表明当公式表明当t向前移动一个时期，就增加一个新近数据向前移动一个时期，就增加一个新近数据(shj)，去掉一个远期数

18、据，去掉一个远期数据(shj)，得到一个新的平均数。由于它不断地，得到一个新的平均数。由于它不断地“吐故纳新吐故纳新”，逐期向前移动，所以称，逐期向前移动，所以称为移动平均法。为移动平均法。由于移动平均可以平滑数据，消除(xioch)周期变动和不规则变动的影响，使得长期趋势显示出来，因而可以用于预测。其预测公式为：即以第t周期的一次移动平均数作为第t+1周期的预测值。第三十页，共56页。31趋势移动趋势移动(ydng)平均法平均法(线性二次移动线性二次移动(ydng)平平均法均法)当时间序列没有明显的趋势变动时，使用一次移动当时间序列没有明显的趋势变动时，使用一次移动(ydng)平均就能够准确

19、地反映实际情况，直接用平均就能够准确地反映实际情况，直接用第第t周期的一次移动周期的一次移动(ydng)平均数就可预测第平均数就可预测第t+1周期之值。但当时间序列出现线性变动趋势时，用周期之值。但当时间序列出现线性变动趋势时，用一次移动一次移动(ydng)平均数来预测就会出现滞后偏差。平均数来预测就会出现滞后偏差。因此，需要进行修正，修正的方法是在一次移动因此，需要进行修正，修正的方法是在一次移动(ydng)平均的基础上再做二次移动平均的基础上再做二次移动(ydng)平均，平均，利用移动利用移动(ydng)平均滞后偏差的规律找出曲线的平均滞后偏差的规律找出曲线的发展方向和发展趋势，然后才建立

20、直线趋势的预测发展方向和发展趋势，然后才建立直线趋势的预测模型。故称为趋势移动模型。故称为趋势移动(ydng)平均法。平均法。第三十一页，共56页。32应用应用(yngyng)举例举例已知某商场19781998年的年销售额如下(rxi)表所示，试预测1999年该商场的年销售额。年份销售额年份销售额19783219897619794119907319804819917919815319928419825119938619835819948719845719959219856419969519866919971011987671998107198869第三十二页，共56页。33下面使用(shyng

21、)移动平均工具进行预测，具体操作步骤如下：1.选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框中，选择移动平均工具。下面使用(shyng)移动平均工具进行预测，具体操作步骤如下：选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框中，选择移动平均工具。第三十三页，共56页。34第三十四页，共56页。35第三十五页，共56页。36从图可以看出，该商场的年销售额具有明显的线性增长趋势。因此要进行预测，还必须先作二次移动平均，再建立直线趋势的预测模型。而利用Excel2000提供的移动平均工具只能作一次移动平均，所以在一次移动平均的基础上再进行移动平均即可。二

22、次移动平均的方法同上，求出的二次移动平均值及实际值与二次移动平均值的拟合曲线，如下图所示。再利用前面(qinmian)所讲的截距和斜率计算公式可得：第三十六页，共56页。37于是可得t=21时的直线趋势(qsh)预测模型为：预测(yc)1999年该商场的年销售额为：第三十七页，共56页。382Excel进行指数平滑进行指数平滑(pnghu)分析的操作步骤分析的操作步骤移动平均法的预测值实质上是以前观测(gunc)值的加权和，且对不同时期的数据给予相同的加权。这往往不符合实际情况。指数平滑法则对移动平均法进行了改进和发展，其应用较为广泛。指数平滑法的基本理论根据平滑次数不同，指数平滑法分为：一次

23、指数平滑法、二次指数平滑法和三次指数平滑法等。但它们的基本思想都是：预测值是以前观测(gunc)值的加权和，且对不同的数据给予不同的权，新数据给较大的权，旧数据给较小的权。第三十八页，共56页。39一次指数一次指数(zhsh)平滑法平滑法设时间序列为，则一次指数平滑公式为：式中为第t周期的一次指数平滑值；为加权系数(xsh)，01。为了弄清指数平滑的实质，将上述公式依次展开，可得：因为加权系数(xsh)符合指数规律，且又具有平滑数据的功能，所以称为指数平滑。用上述平滑值进行预测，就是一次指数平滑法。其预测模型为：即以第t周期的一次指数平滑值作为第t+1期的预测值。第三十九页，共56页。40二次

24、指数平滑法二次指数平滑法当时间序列没有明显的趋势变动时，使用当时间序列没有明显的趋势变动时，使用第第t周期一次指数平滑就能直接预测第周期一次指数平滑就能直接预测第t+1期期之值。但当时间序列的变动出现直线趋势时，之值。但当时间序列的变动出现直线趋势时，用一次指数平滑法来预测仍存在着明显的滞用一次指数平滑法来预测仍存在着明显的滞后偏差。因此，也需要进行修正。修正的方后偏差。因此，也需要进行修正。修正的方法也是在一次指数平滑的基础法也是在一次指数平滑的基础(jch)上再作上再作二次指数平滑，利用滞后偏差的规律找出曲二次指数平滑，利用滞后偏差的规律找出曲线的发展方向和发展趋势，然后建立直线趋线的发展

25、方向和发展趋势，然后建立直线趋势预测模型。故称为二次指数平滑法。势预测模型。故称为二次指数平滑法。第四十页，共56页。41应用(yngyng)举例已知某厂19781998年的钢产量如下(rxi)表所示，试预测1999年该厂的钢产量。年份钢产量年份钢产量1978676198920311979825199022341980774199125661981716199228201982940199330061983115919943093198413841995327719851524199635141986166819973770198716881998410719881958第四十一页，共56页。

26、42下面利用指数平滑(pnghu)工具进行预测，具体步骤如下：选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框中，选择指数平滑(pnghu)工具。这时将出现指数平滑(pnghu)对话框，如图所示。第四十二页，共56页。43在输入框中指定输入参数。在输入区域指定数据所在(suzi)的单元格区域B1:B22；因指定的输入区域包含标志行，所以选中标志复选框；在阻尼系数指定加权系数0.3。注:阻尼系数不是平滑常数(阻尼系数=1-平滑常数)在输出选项框中指定输出选项。本例选择输出区域，并指定输出到当前工作表以C2为左上角的单元格区域；选中图表输出复选框。单击确定按钮。这时，Exce

27、l给出一次指数平滑值，如下图所示。第四十三页，共56页。44从图可以看出，钢产量具有明显的线性增长趋势。从图可以看出，钢产量具有明显的线性增长趋势。因此需使用二次指数平滑法，即在一次指数平滑的基础因此需使用二次指数平滑法，即在一次指数平滑的基础(jch)上再进行指数平滑。所得结果如下图所示。上再进行指数平滑。所得结果如下图所示。第四十四页，共56页。45第四十五页，共56页。463.Excel进行进行(jnxng)趋势外推预测法的操作步骤趋势外推预测法的操作步骤统计资料表明统计资料表明,大量社会经济现象的发展主要是渐进的大量社会经济现象的发展主要是渐进的,其发展其发展相对于时间具有一定的规律性

28、相对于时间具有一定的规律性.因此因此,预测对象依时间变化呈现某种上升或下降的趋势预测对象依时间变化呈现某种上升或下降的趋势,并且无并且无明显的季节波动明显的季节波动,又能找到一条合适的函数曲线反映这种变化又能找到一条合适的函数曲线反映这种变化趋势时趋势时,就可以用时间就可以用时间t为自变量为自变量,时序数值时序数值y为因变量为因变量,建立回建立回归趋势模型归趋势模型,当有理由相信这种趋势能够延伸到未来时当有理由相信这种趋势能够延伸到未来时,赋予变赋予变量量t所需要的值所需要的值,可以得到可以得到(ddo)相应时刻的时间序列未来值相应时刻的时间序列未来值,即趋势外推预测法即趋势外推预测法适用于有

29、长期趋势的时间序列。适用于有长期趋势的时间序列。选择工具菜单中的数据分析命令，弹出数据分析对话框。选择工具菜单中的数据分析命令，弹出数据分析对话框。在分析工具列表框中，选回归工具。在分析工具列表框中，选回归工具。这时，将弹出回归对话框，如图所示。这时，将弹出回归对话框，如图所示。第四十六页，共56页。47第四十七页，共56页。48指定输入参数。在输入Y区域(原始的时间序列数据y)、输入X区域(y对应的时间t)指定相应数据所在的单元格区域.并选定标志复选框，在置信水平框内键入95%。对于一些特殊的回归模型，可以根据需要指定常数为0（即）。指定输出选项。这里选择输出到新工作表组，并指定工作表名称(

30、mngchng)为“回归模型”，选定残差（即随机误差项）和正态分布中的所有输出选项，以观察相应的结果。单击确定按钮。最后得到回归分析的计算结果。第四十八页，共56页。49利用趋势利用趋势(qsh)和季节成分进行和季节成分进行预测预测yt=TtStIt非季节非季节(jji)化处理化处理步骤：计算季节步骤：计算季节(jji)指数、进行非季节指数、进行非季节(jji)化处理、明确存在趋势、分析。化处理、明确存在趋势、分析。第四十九页，共56页。50(1)移动平均(pngjn),平滑掉时间序列的随机因素.(2)原始数据与平滑后的数据相除得季节性指数.(3)消除季节性因素的影响.(4)构造长期趋势模型.(5)进行预测第五十页，共56页。51第五十一页，共56页。52第五十二页，共56页。53消除消除(xioch)季节影响后的数据季节影响后的数据第五十三页，共56页。54第五十四页，共56页。55第五十五页，共56页。56第五十六页，共56页。