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

第11章时间数列1第11章时间数列分析与预测11.1时间数列的基本特征11.2移动平均分析与预测11.3回归分析与预测11.4指数平滑法与预测11.5季节变动的测定与分析11.6周期波动的测定与分析下一页返回目录第11章时间数列211.1时间数列的基本特征11.1.1时间数列的特点11.1.2时间数列的构成与分解上一页下一页返回本章首页第11章时间数列311.1.1时间数列的特点时间数列按时间先后顺序排列时间数列是按一定方式搜集的一系列数据时间数列中的观察值具有差异时间数列中的数据不许遗漏上一页下一页返回本节首页第11章时间数列411.1.2时间数列的构成与分解上一页下一页返回本节首页通常把时间数列（Y）分解为以下四种变动：(1)长期趋势变动（T）(2)季节变动（S）(3)周期波动（C）(4)不规则变动（I）乘法模型的一般形式为：Y=T×S×C×I式中Y、T是总量指标,S、C、I为比率。加法模型的一般形式为：Y=T+S+C+I式中Y、T、S、C、I都是总量指标。第11章时间数列511.2移动平均分析与预测11.2.1移动平均法的概念及特点11.2.2趋势图直接预测法11.2.3利用Excel创建公式预测11.2.4移动平均分析工具预测上一页下一页返回本章首页第11章时间数列611.2.1移动平均法的概念及特点移动平均法是在算术平均法的基础上发展起来的预测方法。它是利用过去若干期实际值的均值来预测现象的发展趋势。简单移动平均公式如下：式中，N为期数；At-j+1：t-j+1期的实际值；Mt+1：t+1期的预测值。njjttANM1111上一页下一页返回本节首页第11章时间数列711.2.2趋势图直接预测法例已知1993年第1季度到1997年第四季度的某地区的季度零售额资料试对1998年的零售额进行预测。操作过程如下：上一页下一页返回本节首页第11章时间数列8(1)产生“年季”变量①打开“第11章时间数列分析与预测.XLS”工作簿，选择“移动平均”工作表如下图所示。②在C列选定任一个单元格，选择“插入”菜单中的“列”选项，则原来C列的内容被移到D列。上一页下一页返回本节首页第11章时间数列9③在C1单元格中输入标志“年季”，在C2单元格中输入公式“=B2&CHAR(13)&A2”，再把单元格C2中的公式复制到C3:C21。结果如下图所示。上一页下一页返回本节首页第11章时间数列10(2)根据图中资料绘制销售额趋势图①打开“插入”菜单中的“图表”选项，Excel弹出“图表向导”对话框如下图所示。上一页下一页返回本节首页第11章时间数列11②在“图表类型”列表中选择“折线图”选项；在“子图表类型”列表中选择“数据点折线图”。单击“下一步”按钮，进入向导步骤2对话框，如下图所示。上一页下一页返回本节首页第11章时间数列12③在“图表数据源”对话框中，在数据区域输入“C1:D21”，并单击“下一步”按钮，如下图所示。上一页下一页返回本节首页第11章时间数列13④在“图表选项”中，选择“标题”页面，在“图表标题”、“分类（X）轴”、“数值（Y）轴”中分别填入“某地区销售额趋势图”、“季度”和“销售额（万元）”。选择“网格线”页面，把“数值（Y）轴”下的“主要网格线”设为空。选择“图例”页面，取消图例显示。单击“完成”按钮，得到趋势图。上一页下一页返回本节首页第11章时间数列14(3)在图表中插入趋势线进行预测①单击图表以激活它，选取垂直轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框如下图所示。打开“刻度”页面，在“最小值”、“最大值”、“主要刻度单位”中分别输入200、450和50；打开“字体”页面，设置字号为“8”。单击“确定”按钮。上一页下一页返回本节首页第11章时间数列15②选取水平轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框如下图所示。打开“对齐”页面，取消自动设置；打开“字体”页面，设置字号为“8”。单击“确定”按钮。上一页下一页返回本节首页第11章时间数列16③选取图中的折线，单击鼠标右键并从快捷菜单中选择“添加趋势线”选项，打开“添加趋势线”对话框如下图所示。选择“类型”页面，在“趋势预测/回归分析类型”框中选择“移动平均”，设置“周期”为4。上一页下一页返回本节首页第11章时间数列17④单击“确定”按钮。如下图所示。某地区销售额趋势图20025030035040045011993311994311995311996311997311998上一页下一页返回本节首页第11章时间数列1811.2.3利用Excel创建公式预测例已知1993年第1季度到1997年第四季度的某地区的季度零售额资料，试对1998年的零售额进行预测。操作步骤如下:上一页下一页返回本节首页第11章时间数列19①打开“第11章时间数列分析与预测”工作簿，选择“移动平均”工作表。②在单元格E1中输入“公式预测值”。③在单元格E6中输入公式“=AVERAGE(D2:D5)”，此处需要相对引用以便复制。④把单元格E6中的公式复制到E7:E22各单元格中，结果如下图所示。上一页下一页返回本节首页第11章时间数列2011.2.4移动平均分析工具预测1.移动平均分析工具的内容移动平均分析工具对话框如下图所示。上一页下一页返回本节首页第11章时间数列212.利用移动平均分析工具进行预测①打开“第11章时间数列分析与预测.xls”工作簿，选择“移动平均”工作表。②在单元格F1、G1中分别输入“分析工具预测值”和“预测标准误差”。上一页下一页返回本节首页第11章时间数列22③从“工具”菜单中选择“数据分析”选项，在弹出的“数据分析”对话框中选中“移动平均”选项，并单击“确定”按钮，此时将出现“移动平均”对话框，如下图所示。上一页下一页返回本节首页第11章时间数列23④在输入区域中输入D1:D21，选定标志位于第一行，间隔设为4，在输出区域中输入F2，即输出区域的左上角的绝对引用。选择图表输出和标准误差。单击“确定”按钮，所得结果如下图所示。上一页下一页返回本节首页第11章时间数列243．使用移动平均分析工具注意事项①移动平均分析工具自动将公式放在工作表中。②单元格G8中含有的公式为：SQRT(SUMXMY2(D5:D8,F5:F8)/4)③请注意上表中用两种方法所得出的输出结果的差异：用移动平均分析工具得到的移动平均值较直接输入公式法输出结果上移了一个单元格。这意味着用数据分析工具所计算的第一个值，即293.6，是对1983年第四季度的预测。上一页下一页返回本节首页第11章时间数列25移动平均法的不足之处计算一次移动平均值必须储存多个实际值，当预测项目很多时，就要占据相当大的预测空间。注重最近的几个实际值，没有利用t-n期以前各期的数据信息。只对最近的一期进行预测，不能对更远的未来做出预测计划。上一页下一页返回本节首页第11章时间数列2611.3回归分析与预测11.3.1时间数列预测工作表函数11.3.2使用直线函数LINEST和趋势函数TREND线性预测法11.3.3使用指数函数LOGEST和增长函数GROWTH进行非线性预测上一页下一页返回本章首页第11章时间数列2711.3.1时间数列预测工作表函数FORECAST预测函数功能：根据给定的数据计算或预测未来值语法结构：ORECAST(x,known_y's,known_x's)TREND趋势函数功能：得到根据最小平方法所得的估计直线上的一组纵坐标值（y值）。语法结构：REND(known_y's,known_x's,new_x's,const)上一页下一页返回本节首页第11章时间数列28GROWTH增长函数功能：根据给定的数据拟合指数曲线，并预测指数增长值。语法结构：ROWTH(known_y's,known_x's,new_x's,const)LINEST线性拟合函数功能：使用最小平方法对已知数据拟合一个线性模型。语法结构：LINEST(known_y's,known_x's,const,stats)LOGEST函数功能：用于进行指数回归拟合曲线，并返回描述该曲线的数组。语法结构：LOGEST(known_y's,known_x's,const,stats)上一页下一页返回本节首页第11章时间数列2911.3.2使用直线函数LINEST和趋势函数TREND线性预测法1.使用LINEST函数计算回归统计值例有某公司99年1至10月份的销售量资料，试用最小平方法进行趋势预测，并预测11、12月份的销售量。上一页下一页返回本节首页第11章时间数列30操作步骤如下：①打开“第11章时间数列分析与预测.xls”工作簿，选择“趋势”工作表，如下图所示。②在E1中输入“回归统计量”。上一页下一页返回本节首页第11章时间数列31③选择E2:F6，单击“粘贴函数”，Excel弹出“粘贴函数”对话框，如下图所示。上一页下一页返回本节首页第11章时间数列32④在“函数分类”中选择“统计”，在“函数名”中选择“LINEST”函数，则打开LINEST函数对话框，如下图所示。上一页下一页返回本节首页第11章时间数列33⑤在Known_y’s、Known_x’s和Stats后分别输入B2:B11、A2:A11、和1。按住Ctrl+Shift组合键，单击“确定”按钮。得计算结果如下图中的E2:F6所示。上一页下一页返回本节首页第11章时间数列34根据上图中的计算结果可以写出如下估计方程：y=8.86+0.83*x据此方程计算预测值：①在D1单元格中输入“预测值”。②在D2单元格中输入公式“=$F$2+$E$2*B2”。③将D2单元格中的公式复制到D3：D16各单元格。上一页下一页返回本节首页第11章时间数列352．使用趋势函数TREND求预测值①在单元格C1中输入“拟合值”。②在“插入”菜单中选择“函数”选项，将打开“粘贴函数”对话框。③在“函数分类”中选择“统计”，在“函数名”中选择TREND函数，单击“确定”按钮，将打开TREND对话框，如下页图所示。上一页下一页返回本节首页第11章时间数列36上一页下一页返回本节首页第11章时间数列37④在known_y's中输入B2:B11；在known_x's中输入A2:A11；由于不需预测，故忽略new_x’s；本题意在保留常数项，故忽略const。按住Ctrl+Shift组合键，单击“确定”。也可以在选定单元格C2:C11的情况下，输入数组公式“=TREND(B2:B11,A2:A11)”，按住Ctrl+Shift组合键，按回车键。输出结果如下图所示。上一页下一页返回本节首页第11章时间数列383.趋势预测仍使用上图中的数据，预测过程如下：①在区域A12中输入11。②在单元格C12中输入公式：=TREND(B2:B11,A2:A11,A12)。该公式的含义是，给出区域B2:B11种的y变量和A2:A11中的x变量之间的关系，当x值为11时，y值是多少？上一页下一页返回本节首页第11章时间数列3911.3.3使用指数函数LOGEST和增长函数GROWTH进行非线性预测例已知某地区1981~1995年历年彩色电视机产量资料。试对其拟合指数曲线，并预测1996~1999年产量。上一页下一页返回本节首页第11章时间数列401．使用LOGEST函数计算回归统计量①打开“第11章时间数列分析与预测.xls”工作簿，选择“增长曲线”工作表如下图所示。上一页下一页返回本节首页第11章时间数列41②选择E2:F6区域，单击工具栏中的“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选择“统计”，在“函数名”中选择“LOGEST”函数，则打开LOGEST对话框，如下图11.20所示。上一页下一页返回本节首页第11章时间数列42③在Known_y’s、Known_x’s和Stats后分别输入C2:C16、B2:B16和1。按住Ctrl+Shift组合键，再