《Excel教程第9章时间数列分析与预测.ppt》由会员分享，可在线阅读，更多相关《Excel教程第9章时间数列分析与预测.ppt（51页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第9章章时间数列分析与预测时间数列分析与预测9.1时间数列的基本特征时间数列的基本特征9.2移动平均法分析与预测移动平均法分析与预测9.3回归法分析与预测回归法分析与预测9.4指数平滑法分析与预测指数平滑法分析与预测9.5季节变动的测定与分析季节变动的测定与分析本章学习目标本章学习目标uu时间数列的构成及影响因素时间数列的构成及影响因素uu时间数列分析的移动平均法时间数列分析的移动平均法uu时间数列分析的回归分析法时间数列分析的回归分析法uu时间数列分析的指数平滑法时间数列分析的指数平滑法uu利用长期趋势剔除法进行季节变动的分析利用长期趋势剔除法进行季节变动的分析9.1时间数列的基本特征时间
2、数列的基本特征9.1.1时间数列的概念与特点时间数列的概念与特点9.1.2时间数列的构成与分解时间数列的构成与分解返回首页9.1.1时间数列的概念与特点时间数列的概念与特点时间数列具有以下特点：时间数列具有以下特点：（1）时间数列按时间先后顺序排列。）时间数列按时间先后顺序排列。（2）时时间间数数列列是是按按一一定定方方式式搜搜集集的的一一系系列列数数据。据。（3）时间数列中的观察值具有差异。）时间数列中的观察值具有差异。（4）时间数列中的数据不许遗漏。）时间数列中的数据不许遗漏。返回本节9.1.2时间数列的构成与分解时间数列的构成与分解影响时间数列变动的因素主要有影响时间数列变动的因素主要有
3、4种：种：（1）长期趋势（）长期趋势（T）。）。（2）季节变动（）季节变动（S）。）。（3）循环变动（）循环变动（C）。）。（4）不规则变动（）不规则变动（I）。）。返回本节9.2移动平均法分析与预测移动平均法分析与预测9.2.1移动平均法的概念及特点移动平均法的概念及特点9.2.2趋势图直接预测法趋势图直接预测法9.2.3利用利用Excel创建公式预测创建公式预测9.2.4利用移动平均分析工具预测利用移动平均分析工具预测返回首页9.2.1移动平均法的概念及特点移动平均法的概念及特点移移动动平平均均法法是是测测定定时时间间数数列列趋趋势势的的一一种种方方法法。它它按按一一定定的的间间隔隔长长度
4、度逐逐期期移移动动，计计算算一一系系列列的的移移动动平平均均数数，来来修修匀匀原原时时间间数数列列的的波波动动，呈呈现现出出现现象象发发展展的的变变动动趋趋势势。采采取取移移动动平平均均法法时时，移移动动平平均均间间隔隔的的长长度度应应长长短短适适中中。移移动动平平均均法法是是在在算算术术平平均均法法的的基基础础上上发发展展起起来来的的预预测测方方法法，它它利利用用过过去去若若干干期期实实际际值值的的均均值值来来预预测测现现象象的的发展趋势。发展趋势。简单移动平均公式如下：简单移动平均公式如下：返回本节9.2.2趋势图直接预测法趋势图直接预测法例例9-1某某电电视视机机厂厂三三年年的的销销售售
5、额额（万万元元）资资料料如如图图9-1（“移移动动平平均均.xls”工工作作表表）所所示示，试试对对第四年的销售额进行预测。第四年的销售额进行预测。（1）产生）产生“年季年季”变量。变量。1）打开）打开“移动平均移动平均”工作表。工作表。2）在在C列列选选定定任任一一个个单单元元格格，选选择择“插插入入”菜菜单单中中的的“列列”选选项项，则则原原来来C列列的的内内容容被被移移到到D列。列。3）在在C1单单元元格格中中输输入入标标志志“年年季季”，在在C2单单元元格格中中输输入入公公式式“=B2&CHAR(13)&A2”，再再把把单单元元格格C2中中的的公公式式复复制制到到C3:C13。结结果果
6、如如图图9-2所示。所示。图9-1 “移动平均”工作表 图9-2 产生“年季”变量（2）绘制销售额趋势图。）绘制销售额趋势图。1）打开）打开“图表向导图表向导”对话框，在对话框，在“图表类型图表类型”列列表中选择表中选择“折线图折线图”项；在项；在“子图表类型子图表类型”列表中列表中选择选择“数据点折线图数据点折线图”。2）在）在“图表选项图表选项”中，选择中，选择“标题标题”页面，在页面，在“图图表标题表标题”、“分类（分类（X）轴轴”、“数值（数值（Y）轴轴”中中分别填入分别填入“销售额趋势图销售额趋势图”、“季度季度”和和“销售额销售额”。图9-3 “图表源数据”对话框图9-4 “图表选
7、项”对话框（3）在图表中插入趋势线进行预测。）在图表中插入趋势线进行预测。1）单单击击图图表表以以激激活活它它，选选取取垂垂直直轴轴，双双击击或或单单击击鼠鼠标标右右键键并并从从快快捷捷菜菜单单中中选选择择“坐坐标标轴轴格格式式”选选项项，弹弹出出“坐标轴格式坐标轴格式”对话框。对话框。2）选选取取水水平平轴轴，双双击击或或单单击击鼠鼠标标右右键键并并从从快快捷捷菜菜单单中中选选择择“坐坐标标轴轴格格式式”选选项项，打打开开“对对齐齐”页页面面，取取消消自自动动设设置置；打打开开“字字体体”页页面面，设设置置字字号号为为8。单单击击“确定确定”按钮。按钮。3）选选取取图图中中的的折折线线，单单
8、击击鼠鼠标标右右键键并并从从快快捷捷菜菜单单中中选选择择“添添加加趋趋势势线线”选选项项，打打开开“添添加加趋趋势势线线”对对话话框框。选选择择“类类型型”页页面面，在在“趋趋势势预预测测/回回归归分分析析类类型型”框框中中选选择择“移移动动平平均均”，设设置置“周周期期”为为4。单单击击“确确定定”按钮产生趋势图，如图按钮产生趋势图，如图9-6所示。所示。图9-5 “坐标轴格式”对话框图9-6 销售额趋势图返回本节9.2.3利用利用Excel创建公式预测创建公式预测可可以以利利用用Excel提提供供的的均均值值函函数数进进行行移移动动平平均均计计算。算。（1）打开）打开“移动平均移动平均”工
9、作表。工作表。（2）在单元格）在单元格E1中输入中输入“公式预测值公式预测值”。（3）在在 单单 元元 格格 E6中中 输输 入入 公公 式式“=AVERAGE(D2:D5)”，此此处处需需要要相相对对引引用用以以便复制。便复制。（4）把把单单元元格格E6中中的的公公式式复复制制到到E7:E14各各单单元元格格中中，结结果果如如图图9-7所所示示。可可以以看看出出，2001年年第一季度的预测值为第一季度的预测值为203万元。万元。图9-7 移动平均预测结果 返回本节9.2.4利用移动平均分析工具预测利用移动平均分析工具预测例例9-2某某地地区区过过去去15年年商商品品零零售售额额资资料料如如图
10、图9-8（“移移动动分分析析”工工作作表表）所所示示，用用移移动动分分析析工工具进行预测。具进行预测。（1）在在“工工具具”菜菜单单中中选选择择“数数据据分分析析”选选项项，在在弹弹出出的的“数数据据分分析析”对对话话框框中中选选中中“移移动动平平均均”选选项项，并并单单击击“确确定定”按按钮钮，此此时时将将出现出现“移动平均移动平均”对话框，如图对话框，如图9-9所示。所示。（2）在在输输入入区区域域中中输输入入B3:B17，间间隔隔设设为为3，在在输输出出区区域域中中输输入入C3，即即输输出出区区域域的的左左上上角角的的绝绝对对引引用用。选选择择“图图表表输输出出”和和“标标准准误误差差”
11、。单击单击“确定确定”按钮，所得结果如图按钮，所得结果如图9-10所示。所示。图9-8 零售额资料 图9-9 “移动平均”对话框图9-10 移动平均分析结果返回本节9.3回归法分析与预测回归法分析与预测9.3.1时间数列预测工作表函数时间数列预测工作表函数9.3.2使用直线函数和趋势函数进行线性预测使用直线函数和趋势函数进行线性预测9.3.3使用指数函数和增长函数进行非线性预使用指数函数和增长函数进行非线性预测测返回首页9.3.1时间数列预测工作表函数时间数列预测工作表函数Excel提供的回归分析函数主要有：提供的回归分析函数主要有：1FORECAST预测函数预测函数2TREND趋势函数趋势函
12、数3GROWTH增长函数增长函数4LINEST线性拟合函数线性拟合函数5LOGEST函数函数1FORECAST预测函数预测函数该该函函数数根根据据已已有有的的数数值值计计算算或或预预测测未未来来值值。此此预预测测值值为为基基于于给给定定的的x值值推推导导出出的的y值值。已已知知的的数数值值为为已已有有的的x值值和和y值值，再再利利用用线线性性回回归归对对新新值值进进行行预预测测。可可以以使使用用该该函函数数对对未未来来销销售售额、库存需求或消费趋势进行预测。额、库存需求或消费趋势进行预测。语法：语法：FORECAST(x,known_ys,known_xs)2TREND趋势函数趋势函数该函数返
13、回一条线性回归拟合线的值。即找到该函数返回一条线性回归拟合线的值。即找到适合已知数组适合已知数组known_ys和和known_xs的直线的直线（用最小二乘法），并返回指定数组（用最小二乘法），并返回指定数组new_xs在在直线上对应的直线上对应的y值。值。语法：语法：TREND(known_ys,known_xs,new_xs,const)3GROWTH增长函数增长函数该函数根据现有的数据预测指数增长值。根据现有该函数根据现有的数据预测指数增长值。根据现有的的x值和值和y值，值，GROWTH函数返回一组新的函数返回一组新的x值对值对应的应的y值。可以使用值。可以使用GROWTH工作表函数来拟
14、合满工作表函数来拟合满足现有足现有x值和值和y值的指数曲线。值的指数曲线。语法：语法：GROWTH(known_ys,known_xs,new_xs,const)4LINEST线性拟合函数线性拟合函数该函数使用最小二乘法对已知数据进行最佳直该函数使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式数返回数值数组，所以必须以数组公式的形式输入。输入。直线的公式为：直线的公式为：或或（如果有多个区域的（如果有多个区域的x值）。值）。语法：语法：LINEST(known_ys,known_xs,co
15、nst,stats)5LOGEST函数函数该函数在回归分析中，计算最符合数据的指数回归拟合曲该函数在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。数组，故必须以数组公式的形式输入。此曲线的公式为：此曲线的公式为：或或（如果有多个（如果有多个x值）。值）。语法：语法：LOGEST(known_ys,known_xs,const,stats)返回本节9.3.2使用直线函数和趋势函数进行线性预测使用直线函数和趋势函数进行线性预测1使用使用LINEST函数计算回归统计值函数
16、计算回归统计值2使用趋势函数使用趋势函数TREND求预测值求预测值3趋势预测趋势预测 图9-11 销售额资料 图9-12 计算结果CD2参数参数b的估计值的估计值参数参数a的估计值的估计值3参数参数b的标准误差的标准误差参数参数a的标准误差的标准误差4判定系数判定系数R2y值估计标准误差值估计标准误差5F统计值统计值自由度自由度6回归平方和回归平方和残差平方和残差平方和表9-1 LINEST函数输出结果对应的统计量图9-13 趋势拟合值返回本节9.3.3使用指数函数和增长函数进行非线性预测使用指数函数和增长函数进行非线性预测例例9-4某某地地区区19912000年年汽汽车车销销售售量量（万万辆
17、辆）资资料料如如图图9-14（“汽汽车车销销量量”工工作作表表）所所示示。试试对对其其拟拟合合指指数数曲曲线线，并并预预测测20012003年年的的销量。销量。1使用使用LOGEST函数计算回归统计量函数计算回归统计量2使用增长函数使用增长函数GROWTH计算预测值计算预测值图9-14 汽车销量数据图9-15 LOGEST函数计算及预测结果返回本节9.4指数平滑法分析与预测指数平滑法分析与预测9.4.1指数平滑法的基本内容与要求指数平滑法的基本内容与要求9.4.2指数平滑分析工具预测指数平滑分析工具预测9.4.3最佳平滑常数的确定最佳平滑常数的确定返回首页9.4.1指数平滑法的基本内容与要求指
18、数平滑法的基本内容与要求指指数数平平滑滑法法是是在在移移动动平平均均法法基基础础上上发发展展起起来来的的一一种种时时间间数数列列分分析析预预测测法法，它它通通过过计计算算指指数数平平滑滑值值，配配合合一一定定的的时时间间数数列列预预测测模模型型对对现现象象的的未未来来进进行行预预测测。指指数数平平滑滑法法通通过过对对历历史史时时间间数数列列进进行行逐逐层层平平滑滑计计算算，从从而而消消除除随随机机因因素素的的影影响，识别经济现象基本变化趋势。响，识别经济现象基本变化趋势。根根据据平平滑滑次次数数不不同同，指指数数平平滑滑法法分分为为：一一次次指指数数平平滑滑法法、二二次次指指数数平平滑滑法法和
19、和三三次次指指数数平平滑滑法法等等。它它们们的的基基本本思思想想都都是是：预预测测值值是是以以前前观观测测值值的的加加权权和和，且且对对不不同同的的数数据据给给予予不不同同的的权权，新数据给较大的权，旧数据给较小的权。新数据给较大的权，旧数据给较小的权。1一次指数平滑法一次指数平滑法2二次指数平滑法二次指数平滑法3三次指数平滑法三次指数平滑法返回本节9.4.2指数平滑分析工具预测指数平滑分析工具预测例例9-5某某企企业业1991年年2000年年的的产产值值（万万元元）资资料料如如图图9-16（“平平滑滑分分析析”工工作作表表）所所示示，下下面面利利用用指指数数平平滑滑工工具具进进行行预预测测，
20、具具体体步步骤骤如如下：下：（1）选选择择“工工具具”菜菜单单中中的的“数数据据分分析析”命命令，弹出令，弹出“数据分析数据分析”对话框。对话框。（2）在在“输输入入区区域域”指指定定数数据据所所在在的的单单元元格格区区域域B1:B11；因因指指定定的的输输入入区区域域包包含含标标志志行行，所所以以选选中中“标标志志”复复选选框框；在在“阻阻尼尼系系数数”中中指定系数指定系数0.3。如图。如图9-17所示。所示。（3）单单击击“确确定定”按按钮钮。这这时时，Excel给给出出一一次指数平滑值，如图次指数平滑值，如图9-18所示。所示。图9-16 “平滑分析”工作表数据 图9-17 “指数平滑”
21、对话框图9-18 指数平滑分析结果返回本节9.4.3最佳平滑常数的确定最佳平滑常数的确定在在指指数数平平滑滑法法中中，预预测测成成功功的的关关键键是是的的选选择择。的的大大小小规规定定了了在在新新预预测测值值中中新新数数据据和和原原预预测测值值所所占占的的比比例例。值值愈愈大大，新新数数据据所所占占的的比比重重就就愈愈大，原预测值所占比重就愈小，反之亦然。大，原预测值所占比重就愈小，反之亦然。若把一次指数平滑法的预测公式改写为：若把一次指数平滑法的预测公式改写为：最最佳佳的的平平滑滑常常数数应应使使实实际际值值和和预预测测值值之之间间的的差差最最小小，通通常常使使预预测测误误差差的的平平方方和
22、和的的平平方方根根（RMSPE）最小。最小。计算误差的公式为：计算误差的公式为：返回本节9.5季节变动的测定与分析季节变动的测定与分析季季节节变变动动指指现现象象受受季季节节的的影影响响而而发发生生的的变变动动，即即现现象象在在一一年年内内或或更更短短的的时时间间内内随随着着时时序序的的更更换换，呈呈现现周周期期重重复复的的变变化化。季季节节变变动动的的原原因因，既有自然因素又有社会因素。既有自然因素又有社会因素。季季节节变变动动分分析析是是根根据据以以月月、季季为为单单位位的的时时间间数数列列资资料料，测测定定以以年年为为周周期期的的、随随着着季季节节转转变变而而发发生生的的周周期期性性变变
23、动动的的规规律律性性。季季节节变变动动分分析析为为了了消消除除偶偶然然性性因因素素的的影影响响，至至少少需需要要三三年年以以上上的的数数据据资资料料，年年数数愈愈多多，偶偶然然性性因因素素消消除除得得愈愈彻底。彻底。返回首页进进行行季季节节变变动动分分析析常常采采用用长长期期趋趋势势剔剔除除法法。长长期期趋趋势势剔剔除除法法是是指指先先配配合合趋趋势势模模型型，确确定定各各月月（季季）的的趋趋势势值值加加以以剔剔除除，再再分分析析季季节节变变动动的的方方法法。具具体体有有乘乘法法型型时时间间数数列列季季节节变变动动分分析析和和加加法法型型时时间间数数列列季季节节变变动动分分析。析。乘乘法法型型
24、时时间间数数列列季季节节变变动动分分析析是是将将长长期期趋趋势势值值去去除除相相应应的的原原时时间间数数列列的的数数据据，剔剔除除长长期期趋趋势势影影响响，再再用用月月（季季）平平均均计计算算季季节节指指数数分分析析季季节节变变动动。首首先先，计计算算Y/T剔剔除除长长期期趋趋势势影影响响；其其次次，根根据据消消除除长长期期趋趋势势后后的的比比率率计计算算同同季季平平均均数数和和季季节节指指数数。季季节节指指数数如如果果大大于于100%则则表表示示旺旺季季，小小于于100%表表示示淡淡季季，在在100%左左右右表示平季。表示平季。加加法法型型时时间间数数列列季季节节变变动动分分析析是是将将原原时时间间数数列列的的实实际际数数据据减减去去长长期期趋趋势势值值，剔剔除除长长期期趋趋势势影影响响，再再同同月月（季季）平平均均计计算算季季节节差差分分析析季季节节变变动动。首首先先，计计算算（Y-T）剔剔除除长长期期趋趋势势影影响响；其其次次，根根据据剔剔除除长长期期趋趋势势后后的的离离差差计计算算同同期期平平均均数数和和季季节节差差。季季节节差差正正值值表表示示旺旺季季，负负值值表表示淡季，在示淡季，在0附近表示平季。附近表示平季。图9-19 “趋势剔除”工作表图9-20 长期趋势剔除法计算结果返回本节