Excel在统计中的应用 第9章 时间数列分析与预测.pdf

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