EXCEL应用时间数列分析与预测实用教案

1、9.1 时间时间(shjin)数列的基本特征数列的基本特征 9.1.1 时间数列的概念(ginin)与特点 9.1.2 时间数列的构成与分解返回(fnhu)首页第1页/共50页第一页，共51页。9.1.1 时间时间(shjin)数列的概念与特点数列的概念与特点 时间数列具有以下特点： （1）时间数列按时间先后顺序排列。 （2）时间数列是按一定方式(fngsh)搜集的一系列数据。 （3）时间数列中的观察值具有差异。 （4）时间数列中的数据不许遗漏。返回(fnhu)本节第2页/共50页第二页，共51页。9.1.2 时间数列时间数列(shli)的构成与分解的构成与分解 影响时间数列变动(bindng

2、)的因素主要有4种： （1）长期趋势（T）。 （2）季节变动(bindng)（S）。 （3）循环变动(bindng)（C）。 （4）不规则变动(bindng)（I）。 返回(fnhu)本节第3页/共50页第三页，共51页。9.2 移动移动(ydng)平均法分析与预测平均法分析与预测 9.2.1 移动平均法的概念及特点(tdin) 9.2.2 趋势图直接预测法 9.2.3 利用Excel创建公式预测 9.2.4 利用移动平均分析工具预测返回(fnhu)首页第4页/共50页第四页，共51页。9.2.1 移动移动(ydng)平均法的概念及特点平均法的概念及特点 移动平均法是测定时间数列趋势的一种方法

3、。它按一定的间隔长度逐期移动，计算一系列的移动平均数，来修匀原时间数列的波动，呈现出现象发展的变动趋势。采取移动平均法时，移动平均间隔的长度应长短适中。移动平均法是在算术平均法的基础上发展起来的预测(yc)方法，它利用过去若干期实际值的均值来预测(yc)现象的发展趋势。 简单移动平均公式如下： 返回(fnhu)本节第5页/共50页第五页，共51页。9.2.2 趋势图直接趋势图直接(zhji)预测法预测法例9-1 某电视机厂三年的销售额（万元）资料如图9-1（“移动平均.xls”工作表）所示，试对第四年的销售额进行预测。（1）产生“年季”变量。1）打开“移动平均”工作表。2）在C列选定任一个单元

4、格，选择“插入”菜单中的“列”选项，则原来C列的内容被移到D列。3）在C1单元格中输入标志(biozh)“年季”，在C2单元格中输入公式“=B2&CHAR(13)&A2”，再把单元格C2中的公式复制到C3:C13。结果如图9-2所示。 第6页/共50页第六页，共51页。 图9-1 “移动(ydng)平均”工作表 第7页/共50页第七页，共51页。 图9-2 产生(chnshng)“年季”变量第8页/共50页第八页，共51页。 （2）绘制销售额趋势图。 1）打开“图表向导”对话框，在“图表类型”列表中选择“折线图”项；在“子图表类型”列表中选择“数据点折线图”。 2）在“图表选项

5、”中，选择“标题”页面(y min)，在“图表标题”、“分类（X）轴”、“数值（Y）轴”中分别填入“销售额趋势图”、“季度”和“销售额”。 第9页/共50页第九页，共51页。图9-3 “图表(tbio)源数据”对话框第10页/共50页第十页，共51页。图9-4 “图表(tbio)选项”对话框第11页/共50页第十一页，共51页。 （3）在图表中插入趋势线进行预测。 1）单击图表以激活它，选取垂直轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，弹出“坐标轴格式”对话框。 2）选取水平轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，打开“对齐”页面，取消自动(zdng)设置

6、；打开“字体”页面，设置字号为8。单击“确定”按钮。 3）选取图中的折线，单击鼠标右键并从快捷菜单中选择“添加趋势线”选项，打开“添加趋势线”对话框。选择“类型”页面，在“趋势预测/回归分析类型”框中选择“移动平均”，设置“周期”为4。单击“确定”按钮产生趋势图，如图9-6所示。第12页/共50页第十二页，共51页。图9-5 “坐标轴格式(g shi)”对话框第13页/共50页第十三页，共51页。图9-6 销售额趋势(qsh)图返回(fnhu)本节第14页/共50页第十四页，共51页。9.2.3 利用利用Excel创建公式创建公式(gngsh)预测预测 可以利用Excel提供的均值函数进行移动

7、平均计算。 （1）打开“移动平均”工作表。 （2）在单元格E1中输入“公式预测值”。 （3）在单元格E6中输入公式“=AVERAGE(D2:D5)”，此处需要相对(xingdu)引用以便复制。 （4）把单元格E6中的公式复制到E7:E14各单元格中，结果如图9-7所示。可以看出，2001年第一季度的预测值为203万元。第15页/共50页第十五页，共51页。图9-7 移动平均预测(yc)结果 返回(fnhu)本节第16页/共50页第十六页，共51页。9.2.4 利用移动平均分析工具利用移动平均分析工具(gngj)预测预测 例9-2 某地区过去15年商品零售额资料如图9-8（“移动分析”工作(gn

8、gzu)表）所示，用移动分析工具进行预测。 （1）在“工具”菜单中选择“数据分析”选项，在弹出的“数据分析”对话框中选中“移动平均”选项，并单击“确定”按钮，此时将出现“移动平均”对话框，如图9-9所示。 （2）在输入区域中输入B3:B17，间隔设为3，在输出区域中输入C3，即输出区域的左上角的绝对引用。选择“图表输出”和“标准误差”。单击“确定”按钮，所得结果如图9-10所示。第17页/共50页第十七页，共51页。 图9-8 零售额资料(zlio) 第18页/共50页第十八页，共51页。 图9-9 “移动(ydng)平均”对话框第19页/共50页第十九页，共51页。图9-10 移动(ydng

9、)平均分析结果返回(fnhu)本节第20页/共50页第二十页，共51页。9.3 回归回归(hugu)法分析与预测法分析与预测 9.3.1 时间(shjin)数列预测工作表函数 9.3.2 使用直线函数和趋势函数进行线性预测 9.3.3 使用指数函数和增长函数进行非线性预测返回(fnhu)首页第21页/共50页第二十一页，共51页。9.3.1 时间数列时间数列(shli)预测工作表函数预测工作表函数 Excel提供(tgng)的回归分析函数主要有： 1FORECAST预测函数 2TREND趋势函数 3GROWTH增长函数 4LINEST线性拟合函数 5LOGEST函数 第22页/共50页第二十二

10、页，共51页。1FORECAST预测预测(yc)函数函数 该函数根据已有的数值计算或预测未来值。此预测值为基于给定(i dn)的 x 值推导出的 y 值。已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。 语法：FORECAST(x,known_ys,known_xs)第23页/共50页第二十三页，共51页。2TREND趋势趋势(qsh)函数函数 该函数返回一条线性回归拟合线的值。即找到适合(shh)已知数组known_ys和known_xs的直线（用最小二乘法），并返回指定数组new_xs在直线上对应的y值。 语法：T

11、REND(known_ys,known_xs,new_xs,const)第24页/共50页第二十四页，共51页。3GROWTH增长增长(zngzhng)函数函数 该函数根据现有的数据预测指数增长值。根据现有的x值和y值，GROWTH 函数返回一组新的x值对应(duyng)的y值。可以使用GROWTH工作表函数来拟合满足现有x值和y值的指数曲线。 语法：GROWTH(known_ys,known_xs,new_xs,const)第25页/共50页第二十五页，共51页。4LINEST线性拟合(n h)函数 该函数使用最小二乘法对已知数据(shj)进行最佳直线拟合，并返回描述此直线的数组。因为此函数

12、返回数值数组，所以必须以数组公式的形式输入。 直线的公式为： 或 （如果有多个区域的x值）。 语法：LINEST(known_ys,known_xs,const,stats)第26页/共50页第二十六页，共51页。5LOGEST函数(hnsh) 该函数在回归(hugu)分析中，计算最符合数据的指数回归(hugu)拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。 此曲线的公式为： 或 （如果有多个x值）。 语法：LOGEST(known_ys,known_xs,const,stats)返回(fnhu)本节第27页/共50页第二十七页，共51页。9.3.2

13、 使用直线函数使用直线函数(hnsh)和趋势函数和趋势函数(hnsh)进行线性预进行线性预测测 1使用(shyng)LINEST函数计算回归统计值 2使用(shyng)趋势函数TREND求预测值 3趋势预测 第28页/共50页第二十八页，共51页。 图9-11 销售额资料(zlio) 第29页/共50页第二十九页，共51页。 图9-12 计算结果第30页/共50页第三十页，共51页。 CD2参数b的估计值参数a的估计值3参数b的标准误差参数a的标准误差4判定系数R2y值估计标准误差5F统计值自由度6回归平方和残差平方和表9-1 LINEST函数输出结果(ji gu)对应的统计量第31页/共50

14、页第三十一页，共51页。图9-13 趋势(qsh)拟合值返回(fnhu)本节第32页/共50页第三十二页，共51页。9.3.3 使用指数函数使用指数函数(zh sh hn sh)和增长函数进行非线性预测和增长函数进行非线性预测 例9-4 某地区19912000年汽车销售量（万辆）资料如图9-14（“汽车销量”工作(gngzu)表）所示。试对其拟合指数曲线，并预测20012003年的销量。 1使用LOGEST函数计算回归统计量 2使用增长函数GROWTH计算预测值第33页/共50页第三十三页，共51页。图9-14 汽车销量数据(shj)第34页/共50页第三十四页，共51页。图9-15 LOGE

15、ST函数计算及预测(yc)结果返回(fnhu)本节第35页/共50页第三十五页，共51页。9.4 指数平滑指数平滑(pnghu)法分析与预测法分析与预测 9.4.1 指数平滑法的基本内容(nirng)与要求 9.4.2 指数平滑分析工具预测 9.4.3 最佳平滑常数的确定返回(fnhu)首页第36页/共50页第三十六页，共51页。9.4.1 指数平滑法的基本指数平滑法的基本(jbn)内容与要求内容与要求 指数平滑法是在移动平均法基础上发展起来的一种时间数列分析预测法，它通过计算指数平滑值，配合一定的时间数列预测模型对现象的未来进行预测。指数平滑法通过对历史时间数列进行逐层平滑计算，从而消除随机

16、因素的影响，识别经济现象基本变化趋势。 根据平滑次数不同，指数平滑法分为：一次指数平滑法、二次指数平滑法和三次指数平滑法等。它们(t men)的基本思想都是：预测值是以前观测值的加权和，且对不同的数据给予不同的权，新数据给较大的权，旧数据给较小的权。第37页/共50页第三十七页，共51页。 1一次指数平滑(pnghu)法 2二次指数平滑(pnghu)法 3三次指数平滑(pnghu)法 返回(fnhu)本节第38页/共50页第三十八页，共51页。9.4.2 指数平滑分析指数平滑分析(fnx)工具预测工具预测 例9-5 某企业1991年2000年的产值（万元）资料如图9-16（“平滑分析”工作表）

17、所示，下面利用指数平滑工具进行预测，具体步骤如下： （1）选择“工具”菜单中的“数据分析”命令，弹出“数据分析”对话框。 （2）在“输入区域”指定数据所在的单元格区域B1:B11；因指定的输入区域包含(bohn)标志行，所以选中“标志”复选框；在“阻尼系数”中指定系数0.3。如图9-17所示。 （3）单击“确定”按钮。这时，Excel给出一次指数平滑值，如图9-18所示。第39页/共50页第三十九页，共51页。 图9-16 “平滑分析(fnx)”工作表数据 第40页/共50页第四十页，共51页。 图9-17 “指数(zhsh)平滑”对话框第41页/共50页第四十一页，共51页。图9-18 指数

18、平滑分析(fnx)结果返回(fnhu)本节第42页/共50页第四十二页，共51页。9.4.3 最佳最佳(zu ji)平滑常数的确定平滑常数的确定 在指数平滑法中，预测(yc)成功的关键是的选择。的大小规定了在新预测(yc)值中新数据和原预测(yc)值所占的比例。值愈大，新数据所占的比重就愈大，原预测(yc)值所占比重就愈小，反之亦然。 若把一次指数平滑法的预测(yc)公式改写为： 第43页/共50页第四十三页，共51页。 最佳的平滑常数应使实际(shj)值和预测值之间的差最小，通常使预测误差的平方和的平方根（RMSPE）最小。 计算误差的公式为： 返回(fnhu)本节第44页/共50页第四十四

19、页，共51页。9.5 季节变动的测定季节变动的测定(cdng)与分析与分析 季节变动指现象受季节的影响而发生的变动，即现象在一年内或更短的时间内随着时序的更换，呈现周期重复的变化。季节变动的原因，既有自然因素又有社会因素。 季节变动分析是根据以月、季为单位的时间数列资料，测定以年为周期的、随着季节转变而发生的周期性变动的规律性。季节变动分析为了(wi le)消除偶然性因素的影响，至少需要三年以上的数据资料，年数愈多，偶然性因素消除得愈彻底。返回(fnhu)首页第45页/共50页第四十五页，共51页。 进行季节变动分析常采用长期趋势剔除法。长期趋势剔除法是指先配合趋势模型，确定各月（季）的趋势值加以剔除，再分析季节变动的方法。具体有乘法型时间数列季节变动分析和加法(jif)型时间数列季节变动分析。 乘法型时间数列季节变动分析是将长期趋势值去除相应的原时间数列的数据，剔除长期趋势影响，再用月（季）平均计算季节指数分析季节变动。首先