时间数列分析与预测(excel)ppt课件

1、第11章 时间数列分析与预测 11.1 时间数列的根本特征 11.2 挪动平均分析与预测 11.3 回归分析与预测 11.4 指数平滑法与预测 11.5 季节变动的测定与分析 11.6 周期动摇的测定与分析 下一页前往目录11.1 时间数列的根本特征 11.1.1 时间数列的特点 11.1.2 时间数列的构成与分解 上一页下一页前往本章首页11.1.1 时间数列的特点 时间数列按时间先后顺序陈列时间数列是按一定方式搜集的一系列数据 时间数列中的察看值具有差别 时间数列中的数据不许脱漏 上一页下一页前往本节首页11.1.2 时间数列的构成与分解 上一页下一页前往本节首页通常把时间数列Y分解为以下

2、四种变动：(1)长期趋势变动T(2)季节变动S (3)周期动摇C (4)不规那么变动I乘法模型的普通方式为：Y=TSCI 式中 Y、T是总量目的, S、C、I为比率。加法模型的普通方式为：Y=T+S+C+I 式中Y、T、S、C、I都是总量目的。 11.2 挪动平均分析与预测 11.2.1 挪动平均法的概念及特点 11.2.2 趋势图直接预测法11.2.3 利用Excel创建公式预测 11.2.4 挪动平均分析工具预测 上一页下一页前往本章首页11.2.1 挪动平均法的概念及特点 挪动平均法是在算术平均法的根底上开展起来的预测方法。它是利用过去假设干期实践值的均值来预测景象的开展趋势。简单挪动平

3、均公式如下： 式中，N 为期数；At-j+1 ：t-j+1期的实践值； Mt+1 ：t+1期的预测值。上一页下一页前往本节首页11.2.2 趋势图直接预测法例 知1993年第1季度到1997年第四季 度的某地域的季度零售额资料试对 1998年的零售额进展预测。操作过程如下： 上一页下一页前往本节首页(1)产生“年季变量翻开“第11章 时间数列分析与预测.XLS任务簿，选择“挪动平均任务表如以下图所示。 在C列选定任一个单元格，选择“插入菜单中的“列选项，那么原来C列的内容被移到D列。上一页下一页前往本节首页在C1单元格中输入标志“年季 ，在C2单元格中输入公式 “=B2&CHAR(13)&A2

4、 ，再把单元格C2中的公式复制到C3:C21。结果如以下图所示。 上一页下一页前往本节首页(2)根据图中资料绘制销售额趋势图翻开“插入菜单中的“图表选项，Excel弹出“图表导游对话框如以下图所示。上一页下一页前往本节首页在 “图表类型列表中选择“折线图选项；在“子图表类型列表中选择“数据点折线图。单击“下一步按钮 ，进入导游步骤2对话框，如以下图所示 。上一页下一页前往本节首页在 “图表数据源对话框中，在数据区域输入 “C1:D21，并单击“下一步按钮，如以下图所示 。上一页下一页前往本节首页在“图表选项中，选择“标题页面，在“图表标题、“分类X轴、“数值Y轴中分别填入“某地域销售额趋势图、

5、“季度和“销售额万元。选择“网格线页面，把“数值Y轴下的“主要网格线设为空。选择“图例页面，取消图例显示。单击“完成按钮，得到趋势图 。上一页下一页前往本节首页(3)在图表中插入趋势线进展预测单击图表以激活它，选取垂直轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式选项，Excel弹出对话框如以下图所示。翻开“刻度页面，在“最小值、“最大值、“主要刻度单位中分别输入200、450和50；翻开“字体页面，设置字号为“8。单击“确定按钮 。上一页下一页前往本节首页选取程度轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式选项，Excel弹出对话框如以下图所示。翻开“对齐页面，取消自动设置；翻

6、开“字体页面，设置字号为“8。单击“确定按钮。 上一页下一页前往本节首页选取图中的折线，单击鼠标右键并从快捷菜单中选择“添加趋势线选项，翻开“添加趋势线对话框如以下图所示。选择“类型页面，在“趋势预测/回归分析类型框中选择“挪动平均，设置“周期为4。上一页下一页前往本节首页单击“确定按钮。如以下图所示。 上一页下一页前往本节首页11.2.3 利用Excel创建公式预测 例 知1993年第1季度到1997年第四季 度的某地域的季度零售额资料，试 对1998年的零售额进展预测。操作步骤如下:上一页下一页前往本节首页翻开“第11章 时间数列分析与预测任务簿，选择“挪动平均任务表。在单元格E1中输入“

7、公式预测值。在单元格E6中输入公式“=AVERAGE(D2:D5)，此处需求相对援用以便复制。把单元格E6中的公式复制到E7:E22各单元格中，结果如以下图所示 。上一页下一页前往本节首页11.2.4 挪动平均分析工具预测 1. 挪动平均分析工具的内容 挪动平均分析工具对话框如以下图所示。上一页下一页前往本节首页2. 利用挪动平均分析工具进展预测翻开“第11章 时间数列分析与预测.xls任务簿，选择“挪动平均任务表。在单元格F1、G1中分别输入“分析工具预测值和“预测规范误差 。上一页下一页前往本节首页从“工具菜单中选择“数据分析选项，在弹出的“数据分析对话框中选中“挪动平均选项，并单击“确定

8、按钮，此时将出现“挪动平均对话框，如以下图所示。上一页下一页前往本节首页在输入区域中输入D1:D21，选定标志位于第一行，间隔设为4，在输出区域中输入F2，即输出区域的左上角的绝对援用。选择图表输出和规范误差。单击“确定按钮，所得结果如以下图所示。 上一页下一页前往本节首页3运用挪动平均分析工具本卷须知挪动平均分析工具自动将公式放在任务表中。单元格G8中含有的公式为： SQRT(SUMXMY2(D5:D8, F5:F8)/4)请留意上表中用两种方法所得出的输出结果的差别：用挪动平均分析工具得到的挪动平均值较直接输入公式法输出结果上移了一个单元格。这意味着用数据分析工具所计算的第一个值，即293

9、.6，是对1983年第四季度的预测。 上一页下一页前往本节首页挪动平均法的缺乏之处计算一次挪动平均值必需储存多个实践值，当预测工程很多时，就要占据相当大的预测空间。注重最近的几个实践值，没有利用t-n期以前各期的数据信息。只对最近的一期进展预测，不能对更远的未来做出预测方案。 上一页下一页前往本节首页11.3 回归分析与预测 11.3.1 时间数列预测任务表函数 11.3.2 运用直线函数LINEST和趋势函数TREND 线性预测法 11.3.3 运用指数函数LOGEST和增长函数GROWTH进展非线性预测 上一页下一页前往本章首页11.3.1 时间数列预测任务表函数 FORECAST预测函数

10、 功能：根据给定的数据计算或预测未来值 语法构造：ORECAST(x,known_ys,known_xs)TREND趋势函数 功能：得到根据最小平方法所得的估计直线上的一 组纵坐标值y 值。 语法构造：REND(known_ys,known_xs,new_xs,const) 上一页下一页前往本节首页GROWTH增长函数 功能：根据给定的数据拟合指数曲线，并预测指数 增长值。 语法构造：ROWTH(known_ys,known_xs,new_xs,const) LINEST线性拟合函数 功能：运用最小平方法对知数据拟合一个线性模型。 语法构造：LINEST(known_ys,known_xs,c

11、onst,stats)LOGEST函数 功能：用于进展指数回归拟合曲线，并前往描画该曲线 的数组。 语法构造：LOGEST(known_ys,known_xs,const,stats) 上一页下一页前往本节首页11.3.2 运用直线函数LINEST和趋势函数TREND线性预测法 1. 运用LINEST函数计算回归统计值例 有某公司99年1至10月份的销售量资料， 试用最小平方法进展趋势预测，并预 测11、12月份的销售量。 上一页下一页前往本节首页操作步骤如下：翻开“第11章 时间数列分析与预测.xls任务簿，选择“趋势任务表，如以下图所示。 在E1中输入“回归统计量。上一页下一页前往本节首页

12、选择E2:F6，单击“粘贴函数，Excel弹出“粘贴函数对话框，如以下图所示。上一页下一页前往本节首页在“函数分类中选择“统计，在“函数名中选择“LINEST函数，那么翻开LINEST函数对话框，如 以下图所示。上一页下一页前往本节首页在Known_ys、 Known_xs和Stats后分别输入B2:B11、A2:A11、和1。按住Ctrl+Shift组合键，单击 “确定按钮。得计算结果如以下图中的E2:F6所示 。上一页下一页前往本节首页根据上图中的计算结果可以写出如下估计方程： y=8.86+0.83*x 据此方程计算预测值：在D1单元格中输入“预测值。在D2单元格中输入公式“=$F$2+

13、$E$2*B2。将D2单元格中的公式复制到D3：D16各单元格。上一页下一页前往本节首页2运用趋势函数TREND求预测值在单元格C1中输入“拟合值。在“插入菜单中选择“函数选项，将翻开“粘贴函数对话框。在“函数分类中选择“统计，在“函数名中选择TREND函数，单击“确定按钮，将翻开TREND对话框，如下页图所示。上一页下一页前往本节首页上一页下一页前往本节首页在known_ys中输入B2:B11；在known_xs中输入A2:A11；由于不需预测，故忽略new_xs；此题意在保管常数项，故忽略const。按住Ctrl+Shift组合键，单击“确定。 也可以在选定单元格C2:C11的情况下，输入

14、数组公式“=TREND(B2:B11,A2:A11)，按住Ctrl+Shift组合键，按回车键。输出结果如以下图所示。 上一页下一页前往本节首页3. 趋势预测仍运用上图中的数据，预测过程如下： 在区域A12中输入11。在单元格C12中输入公式：=TREND(B2:B11,A2:A11,A12)。该公式的含义是，给出区域B2:B11种的y变量和A2:A11中的x变量之间的关系，当x值为11时，y值是多少？上一页下一页前往本节首页11.3.3 运用指数函数LOGEST 和增长函数GROWTH进展非线性预测 例 知某地域19811995年历年彩色电视 机产量资料。试对其拟合指数曲线，并 预测1996

15、1999年产量。 上一页下一页前往本节首页1运用LOGEST函数 计算回归统计量翻开“第11章 时间数列分析与预测.xls任务簿，选择“增长曲线任务表如以下图所示。 上一页下一页前往本节首页选择E2:F6区域，单击工具栏中的“粘贴函数快捷键，弹出“粘贴函数对话框，在“函数分类中选择“统计，在“函数名中选择“LOGEST函数，那么翻开LOGEST对话框，如以下图11.20所示。 上一页下一页前往本节首页在Known_ys、 Known_xs和Stats后分别输入C2:C16、B2:B16和1。按住Ctrl+Shift组合键，再按回车键或“确定按钮，得到计算结果如以下图中E2:F6单元格所示。 上

16、一页下一页前往本节首页根据单元格E2，F2中的计算结果可以写出如下估计方程：可以根据上面的指数曲线方程计算预测值：在D2单元格中输入公式“=$F$2*$E$2B2 ，将D2单元格中的公式复制到D3:D20元格，各年的预测值便一目了然了。但运用增长函数可以直接得到预测结果。上一页下一页前往本节首页2. 运用增长函数GROWTH计算预测值选择D2:D20单元格，在工具栏单击“粘贴函数快捷键，弹出“粘贴函数对话框，在“函数分类中选择“统计，在“函数名中选择“GROWTH函数，翻开GROWTH对话框，如以下图所示。 上一页下一页前往本节首页在Known_ys、 Known_xs、New_xs和Cons

17、t后分别输入C2:C16、B2:B16、B2:B20和1。按住Ctrl+Shift组合键，同时按回车键或“确定按钮，计算结果如以下图所示。 上一页下一页前往本节首页3. 绘制实践值与预测值的折线图在工具栏中单击 “图表导游快捷图标，翻开“图表导游对话框。在步骤1中选择“图表类型列表中的“折线图，在“子图表类型下选择“数据点折线图，单击“下一步按钮，进入图表导游步骤2对话框。确认数据区域为：sheet增长曲线!$A$2:$A$16，sheet增长曲线!$C$2:$D$16，单击“下一步按钮，进入图表导游步骤3对话框。上一页下一页前往本节首页在“标题栏下输入“某地域彩色电视机产量开展趋势，单击“完

18、成按钮，得到的结果如以下图所示 。上一页下一页前往本节首页11.4 指数平滑法与预测 11.4.1 指数平滑法的根本内容与要求 11.4.2 案例研讨：小汽车租赁预测 11.4.3 指数平滑分析工具预测 11.4.4 最正确平滑常数确实定 上一页下一页前往本章首页11.4.1 指数平滑法的根本内容与要求 指数平滑法经过对历史时间数列进展逐层平滑计算，从而消除随机要素的影响，识别经济景象根本变化趋势，并以此预测未来。 上一页下一页前往本节首页递推公式为： 其中：平滑常数，值域在0到1之间，其大小决议了本次预测对前期预测误差的修正程度。Excel称1-的值为“阻尼因子。Yt ：t期的实践察看值；Y

19、t*、Yt+1* ：分别为t 期、t+1期的预测值。 上一页下一页前往本节首页指数平滑法的两点阐明：1平滑常数的意义 普通说来，0.2到0.3之间的数值可作为合理的平 滑常数。这些数值阐明本次预测需求将前期预 测值的误差调整 20% 到 30%。2初始预测值确实定问题 可以指定初始预测值，即令它等于前几期如前 6期或前12期等实践值的平均数 。上一页下一页前往本节首页11.4.2 案例研讨：小汽车租赁预测 例 冬天即将降临，某从事汽车租赁业务的经理着手调查客户对防雪汽车的需求情况。经过监测后，一场初冬的暴风雪席卷了整个地域，正如所料，每天的需求量都有显著增长。这时，能够想知道第10天应该贮藏多

20、少辆防雪汽车以备第11天运用。上一页下一页前往本节首页(1) 计算预测值 翻开“第11章 时间数列分析与预测.xls任务簿，选择“指数平滑任务表如以下图所示。 上一页下一页前往本节首页在单元格C2中输入公式 “=AVERAGE(B2:B6)。在单元格C3中输入公式 “=0.3*B2+0.7*C2。将单元格C3中的公式复制到区域C4:C11中，结果如以下图所示 。上一页下一页前往本节首页(2)绘制实践值与拟合值的折线图单击工具栏中的“图表导游快捷图标，翻开“图表导游对话框。选择“图表类型下的“折线图，在“子图表类型中选择“数据点折线图，单击“下一步按钮 ，进入图表导游步骤2对话框。输入数据区域：

21、sheet指数平滑!$A$2:$C$12，单击“下一步按钮，进入图表导游步骤3对话框。上一页下一页前往本节首页在“标题栏下输入“指数平滑，单击“完成按钮，得到折线图如以下图所示 。上一页下一页前往本节首页11.4.3 指数平滑分析工具预测 1指数平滑分析工具的内容Excel所提供的指数平滑分析工具，为进展指数平滑分析与预测提供了方便。指数平滑分析工具对话框如以下图所示。 上一页下一页前往本节首页2指数平滑分析工具的运用翻开“第11章 时间数列分析与预测.XLS任务簿，选择“指数平滑任务表。把区域A2:B11内容下移一行，在空白的单元格B2中输入公式“=AVERAGE(B3:B7)。翻开“工具菜

22、单中的“数据分析选项，弹出“数据分析对话框，在“分析工具列表中选择“指数平滑选项，单击“确定按钮，翻开“指数平滑对话框。 上一页下一页前往本节首页在输入区域输入B2:B11，阻尼系数设为0.7，输出区域中输入C2，选择图表输出。单击“确定按钮 。除单元格C2中显示的公式为“=B2外，单元格C3:C12中显示的公式法均为平滑公式方式，这些是拟合值，要求的预测值，需把单元格C12中的公式复制到C13中。那么得到的结果如以下图所示。上一页下一页前往本节首页11.4.4 最正确平滑常数确实定 最正确的平滑常数应使实践值和预测值之间的差最小，通常是预测误差的平方和的平方根RMSPE最小。上一页下一页前往

23、本节首页上一页下一页前往本节首页计算误差的公式为： RMSPE= 式中：Yi 为实践观测值； 为预测值； 为实践值的平均值。 总S1公式判别法翻开“第11章 时间数列分析与预测.XLS任务簿，选择“指数平滑任务表。在单元格F1中输入值0.3 。选取区域E1:F5，从“插入菜单中选择“称号指定选项，那么弹出指定称号对话框，如以下图所示，选中“最左列，单击“确定按钮。 上一页下一页前往本节首页选取区域D7:D19，从“插入菜单中选择“称号指定选项，在弹出的“指定称号对话框中，选中“首行，单击“确定按钮。在单元格C3中输入公式“=平滑常数*B2+(1-平滑常数)*C2，并把它复制到单元格C4:C11

24、中。在单元格D7中输入公式“=B7-C7，并把它复制到单元格D8:D11中。上一页下一页前往本节首页在单元格F3、F4、F5中分别输入公式：“=AVERAGE(预测误差)、“=STDEV(预测误差)、和“=SQRT(平均误差2+规范误差2)。结果如以下图所示 。上一页下一页前往本节首页在单元格F1中试用不同的值，那么预测值、预测误差值、平均误差、规范误差和RMSPE的值以及图中曲线也将相应发生变化，比较RMSPE的大小，选取RMSPE最小时的值作为最优平滑常数进展预测。本例中平滑常数为0.85时RMSPE最小。此时得到的修饰后的指数平滑图如以下图所示。 上一页下一页前往本节首页2利用“单变量求

25、解自动寻觅最正确平滑常数 单变量求解是指经过调整其他单元格内的 数值为某一单元格寻求一个特定值。单变量求解对话框如以下图所示。上一页下一页前往本节首页单变量求解的实现过程继11.4.3所示的任务表，在“工具菜单中选择“单变量求解选项，将出现“单变量求解对话框。 在目的单元格、目的值、可变单元格中分别输入F5、0和F1，单击“确定按钮。求解的结果如下页图1所示，同时，任务表F列也在发生变化，如下页图2所示。 单击“确定按钮 ，那么计算机自动寻觅的最正确平滑常数结果被呈如今任务表上。 上一页下一页前往本节首页图1图2上一页下一页前往本节首页11.5 季节变动的测定与分析 11.5.1 长期趋势剔除

26、法 11.5.2 利用哑变量进展季节调整与预测 上一页下一页前往本章首页11.5.1 长期趋势剔除法 长期趋势剔除法是在挪动平均法的根底上，以乘法模型Y=TS C I 为实际根底的测定季节变动的方法，它能防止长期趋势与周期动摇的影响，净化季节变动的规律性，从而实现较为准确的预测。 上一页下一页前往本节首页长期趋势剔除法的计算步骤：利用中心化挪动平均计算长期趋势与周期动摇要素TCi。从时间数列中剔除掉TCi ，就得到季节动摇与不规那么变动SIi： SIi =Yi/Tci。按季求SIi的平均数，从而剔除不规那么变动I，得到各季季节指数Si1。计算公式为： 式中：N为年数。上一页下一页前往本节首页对

27、初始季节指数调整为正规化季节指数Sj。根据的公式为：计算剔除季节变动后的时间数列TCIi：TCIi=Yi/Si。对TCIi序列进展外推预测，得到一组预测值Ti 。计算最终预测值：Y* i= Sj Ti 。 上一页下一页前往本节首页例 知1993年第1季度到1997年第四 季度的某地域的季度零售额资料， 试对1998年的零售额进展预测 。上一页下一页前往本节首页操作过程： 翻开“第11章 时间数列分析与预测.XLS任务簿，选择“长期趋势剔除任务表，如以下图所示 。上一页下一页前往本节首页在单元格E4中输入公式“AVERAGE(D2:D5)。在单元格F4中输入公式 “AVERAGE(E4:E5)。

28、把单元格E4:F4中的公式复制到E19:F19，调整其小数部分使显示1位小数，结果如以下图所示。上一页下一页前往本节首页在单元格G4中输入公式“D4F4，并把它复制到G5:G19。在单元格H2中输入公式 “AVERAGE(H6,H10,H14,H18), 并把它复制到单元格H3中。在单元格H4中输入公式 “AVERAGE(H4，H8，H12，H16)， 并把它复制到单元格H5中。 上一页下一页前往本节首页选取单元格H6，点击自动求和工具两次。选取单元格I2，输入公式“H2*4$H$6， 并把它复制到单元格I3:I5。选取单元格I6，点击自动求和工具两次。上述操作的结果如以下图所示。 上一页下一

29、页前往本节首页经过雷达图表现季节指数的意义:单击工具栏中的“图表导游快捷图标，翻开“图表导游对话框。在“图表类型列表下选择“雷达图，在“子图表类型下选择“数据点雷达图，单击“下一步按钮，进入图表导游步骤2对话框。输入数据区域为： sheet长期趋势剔除!$I$2:$I$5，单击“下一步按钮，进入图表导游步骤3对话框。 上一页下一页前往本节首页在“标题页面下输入“季节指数雷达图，选择“图例页面，取消显示图例。单击“完成按钮。经修饰后得到的雷达图如以下图所示。结论：销售额在第一季度进入淡季，在第四季度那么到达旺季。 上一页下一页前往本节首页预测： 在区域J1:L1中分别输入标志“季节指数S序列、“

30、TCI*和“预测值。选中单元格I2:I5，点击主菜单中“复制图标 。选中单元格J2:J25，点击“编辑菜单，选择“选择性粘贴选项，在出现的选择性粘贴对话框中选择“值，单击“确定按钮。在单元格K2中输入公式“D2J2，并把它复制到K2:K21。上一页下一页前往本节首页选中单元格K2:K21，点击“编辑菜单，选择“选择性粘贴选项，在出现的选择性粘贴对话框中选择“值，单击“确定按钮。选中单元格K2:K21，单击单元格K21右下角的填充句柄并向下拖动到单元格K25。在单元格L22中输入公式 “I22*K22。并把它复制到L23:L25。上述操作结果如下页图所示。 上一页下一页前往本节首页上一页下一页前

31、往本节首页可以经过绘制实践销售额和预测值图表察 看预测情况。选取单元格C1:D25，按住Ctrl键并选取单元格L1:L25，单击工具栏中的“图表导游快捷图标，翻开“图表导游对话框。在 “图表类型列表下选择“折线图，在“子图表类型下选择“数据点折线图，单击“下一步按钮。输入数据区域为： sheet长期趋势剔除!$ C1$2:$D$25，sheet长期趋势剔除!$ L$1:$L$25，单击“下一步按钮。上一页下一页前往本节首页在“标题页面下输入“实践值、季节调整TCI及预测值比较图。单击“ 完成按钮，结果如以下图所示。 上一页下一页前往本节首页11.5.2 利用哑变量进展季节调整与预测这种季节调整

32、方法是以季节变动的变动服从加法模型为前提，同时利用季节影响和时间作为自变量，进展多元回归分析。时间变量可用时间序号表示，季节变量可用哑变量来描画 上一页下一页前往本节首页操作过程：翻开“第11章 时间数列分析与预测.xls任务簿，选择“季节变动任务表如以下图所示。 上一页下一页前往本节首页从“工具菜单中，选择“数据分析选项，那么出现“数据分析工具对话框。选中“回归选项，并单击“确定按钮，此时将出现“回归对话框。如以下图所示。上一页下一页前往本节首页在“Y值输入区域、“X值输入区域、“输出区域中分别输入D1:D21，E1:E21和K1；选择标志、置信度、残差、残差图、规范残差和线性拟合图。单击“

33、确定按钮。 以下图所示为编辑过的一部分结果。 上一页下一页前往本节首页根据上图中输出的计算结果可以得出拟合方程为： 销售额Y=311.005+5.106*时间-56.601* 第一季度-19.387* 第二季度- 22.574*第三季度回归分析工具产生的时序图中显示了实践值和拟合值，如以下图所示。上一页下一页前往本节首页预测：把区域A18:C21的内容复制到单元格A22:C25，在单元格A22中输入1988。选取区域E20:E21并把填充句柄向下拖到单元格E25。把区域F18:I21的内容复制到单元格F22:I25。在单元格J1中输入标志“预测值选取区域J2:J25，按数组方式输入公式 “=TREND(D2:D21,E2:H21,E2:H25,1)， 调整小数位数为1。上一页下一页前往本节首页结果如以下图中J列所示。上一页下一页前往本节首页11.6 周期