第12章 时间数列分析与预测 2009

1、LOGO于洪彦于洪彦 刘金星刘金星 张洪利张洪利Excel Excel 统计分析与决策统计分析与决策第第1212章章 时间序列分析与预测时间序列分析与预测 主要内容主要内容 12.1 时间数列的基本特征 12.2 移动平均分析与预测 12.3 回归分析与预测 12.4 指数平滑法与预测 12.5 季节变动的测定与分析 12.6 周期波动的测定与分析 v时间数列的概念与特点 时间数列按时间先后顺序排列 时间数列是按一定方式搜集的一系列数据 时间数列中的观察值具有差异 时间数列中的数据不许遗漏12.1 12.1 时间数列的基本特征时间数列的基本特征 v时间数列的构成与分解 乘法模型的一般形式为：

2、Y=TSCI 式中 Y、T是总量指标 S、C、I为比率 加法模型的一般形式为： Y=T+S+C+I 式中Y、T、S、C、I都是总量指标 12.2 12.2 移动平均分析与预测移动平均分析与预测v移动平均法的基本内容 移动平均法是在算术平均法的基础上发展起来的预测方法。 它是利用过去若干期实际值的均值来预测现象的发展趋势 简单移动平均公式如下： njjttANM111112.2 12.2 移动平均分析与预测移动平均分析与预测v利用趋势图测定长期趋势 例 12.1 对2008年的零售额进行预测。 产生“年季”变量 选择“移动平均”工作表。 在C列选定任一个单元格，选择“插入”菜单中的“列”选项，则

3、原来C列的内容被移到D列。在C1单元格中输入标志“年季” 在单元格C2中输入公式“=B2&CHAR(13)&A2”。再把单元格C2中的公式复制到C3：C21。结果如图所示。12.2 12.2 移动平均分析与预测移动平均分析与预测 根据图12.2中资料绘制销售额趋势图。 打开“插入”菜单中的“图表”选项，Excel弹出“图表向导”对话框。 在步骤1的“图表类型”列表中选择“折线图”选项；在“子图表类型”列表中选择“数据点折线图”。单击“下一步”按钮 ，进入向导步骤2对话框。12.2 12.2 移动平均分析与预测移动平均分析与预测 在步骤2的“图表数据源”对话框中，在数据区域输入

4、“C1：D21”，并单击“下一步”按钮，进入向导步骤3对话框。 在步骤3的“图表选项”中，选择“标题”页面，在“图表标题”、“分类（X）轴”、“数值（Y）轴”中分别填入“某地区销售额趋势图”、“季度”和“销售额（万元）”；选择“网格线”页面，把“数值（Y）轴”下的“主要网格线”设为空；选择“图例”页面，取消图例显示。单击“完成”按钮，得到趋势图。12.2 12.2 移动平均分析与预测移动平均分析与预测 在图表中插入趋势线进行预测 单击图表以激活它，选取垂直轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框。打开“刻度”页面，在“最小值”、“最大值”、“主要刻度单位

5、”中分别输入200、450和50；打开“字体”页面，设置字号为“8”。单击“确定”按钮。 选取水平轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框。打开“对齐”页面，取消自动设置；打开“字体”页面，设置字号为“8”。单击“确定”按钮。12.2 12.2 移动平均分析与预测移动平均分析与预测 选取图中的折线，单击鼠标右键并从快捷菜单中选择“添加趋势线”选项，打开“添加趋势线”对话框。选择“类型”页面，在“趋势预测/回归分析类型”框中选择“移动平均”，设置“周期”为4。 单击“确定”按钮。12.2 12.2 移动平均分析与预测移动平均分析与预测v 利用Excel创建

6、公式测定长期趋势 选择“移动平均”工作表。 在单元格E1中输入“公式预测值”。 在单元格E6中输入公式“=AVERAGE(D2：D5)”。 把单元格E6中的公式复制到E7：E22各单元格中。结果如图所示。12.2 12.2 移动平均分析与预测移动平均分析与预测v移动平均分析工具测定长期趋势 移动平均分析工具的内容12.2 12.2 移动平均分析与预测移动平均分析与预测 利用移动平均分析工具进行预测 选择“移动平均”工作表。 在单元格F1、G1中分别输入“分析工具预测值”和“预测标准误差”。 从“工具”菜单中选择“数据分析”选项，在弹出的“数据分析”对话框中选中“移动平均”选项，并单击“确定”按

7、钮，出现“移动平均”对话框。 在输入区域中输入D1：D21，选定标志位于第一行，间隔设为4，在输出区域中输入F2。选择图表输出和标准误差。单击“确定”按钮，所得结果如图所示。12.2 12.2 移动平均分析与预测移动平均分析与预测v 时间数列预测工作表函数 FORECASTFORECAST预测函数预测函数 TRENDTREND趋势函数趋势函数 GROWTHGROWTH增长函数增长函数 LINESTLINEST线性拟合函数线性拟合函数 LOGESTLOGEST函数函数v 使用直线函数LINEST和趋势函数TREND线性预测法 使用LINEST函数计算回归统计值 例12.2 用最小平方法进行趋 势

8、预测11、12月份的销售量。 选择“趋势”工作表。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势在E1中输入“回归统计量”。选择E2：F6，单击“插入”菜单，选择“函数”，Excel弹出“插入函数”对话框。在“选择类别”中选择“统计”，在“选择函数”中选择“LINEST”函数，则打开LINEST函数对话框。在Known_ys、 Known_xs和Stats后分别输入B2：B11、A2：A11、和1。按住Ctrl+Shift组合键，单击 “确定”按钮。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势 在选中E2：F6的情况下输入数组公式：“=LINEST(C2:C11,

9、B2:B11,1)”，同时按住Ctrl+Shift两键，再按回车键。得到的结果与上面操作相同。计算预测值： 在D1单元格中输入“预测值”。 在D2单元格中输入公式“=$F$2+$E$2*B2”。 将D2单元格中的公式复制到D3：D11各单元格。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势 使用趋势函数TREND求预测值 在单元格C1中输入“拟合值”。 选定单元格C2:C11。 在“插入”菜单中选择“函数”选项，打开“插入函数”对话框。 在“选择类别”中选择“统计”，在“选择函数”中选择TREND函数，单击“确定”按钮，将打开TREND对话框。12.3 12.3 回归分析测定长期

10、趋势回归分析测定长期趋势 在known_ys中输入B2：B11；在known_xs中输入A2：A11；由于不需预测，故忽略new_xs；意在保留常数项，故忽略const。按住Ctrl+Shift组合键，单击“确定”。 也可以在选定单元格C2：C11的情况下，输入数组公式“=TREND(B2:B11,A2:A11)”，按住Ctrl+Shift组合键，按回车键。输出结果如图所示。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势趋势预测在单元格A12中输入11。在单元格C12中输入公式：“=TREND(B2:B11,A2:A11,A12)”。用TREND函数也可以进行多期预测在区域A12

11、:A25中分别输入1124。选定区域C12:C25，输入数组公式：=TREND(B2:B11,A2:A11,A12:A25)，按住Ctrl+Shift组合键，按回车键。将在C12：C25的各单元格中返回第11到第24个时间点的预测值。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势v 使用指数函数LOGEST和增长函数GROWTH拟合长期趋势 例 12.3 拟合指数曲线，并预测20082011年产量。 使用LOGEST函数 计算回归统计量 选择“增长曲线”工作表。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势 选择E2：F6区域，单击“插入函数”快捷键，弹出“插入函数

12、”对话框。 在“选择类别”中选择“统计”，在“选择函数”中选择“LOGEST”函数，打开LOGEST对话框。 在Known_ys、 Known_xs和Stats后分别输入C2：C16、B2：B16和1。按住Ctrl+Shift组合键，再按回车键或“确定”按钮，得计算结果如图中E2：F6单元格所示。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势 使用增长函数GROWTH计算预测值 选择D2：D20单元格，在工具栏单击“插入函数”快捷键，弹出“插入函数”对话框。 在“选择类别”中选择“统计”，在“选择函数”中选择“GROWTH”函数，打开GROWTH对话框。 在Known_ys、 K

13、nown_xs、New_xs和Const后分别输入C2：C16、B2：B16、B2：B20和1。按住Ctrl+Shift组合键，同时按回车键或“确定”按钮，计算结果如图所示。 或者在选择D2：D20单元格的情况下，输入数组公式“=GROWTH(C2:C16,B2:B16, B2:B20,1)”，同时按住Ctrl+Shift组合键，同时按回车键。结果与上述相同。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势 绘制实际值与预测值的折线图： 在工具栏中单击 “图表向导”快捷图标打开“图表向导”对话框。 在步骤1中选择“图表类型”列表中的“折线图”，在“子图表类型”下选择“数据点折线图”

14、，单击“下一步”按钮。 确认数据区域为：sheet增长曲线！$A$2:$A$16，sheet增长曲线！$C$2:$D$16，单击“下一步”按钮。 在“标题”栏下输入“某地区彩色电视机产量发展趋势”，单击“完成”按钮，得到的结果如图所示。12.3 12.3 回归分析测定长期趋势回归分析测定长期趋势v指数平滑法的基本内容与要求 通过对历史时间数列进行逐层平滑计算，从而消除随机因素的影响，识别经济现象基本变化趋势，并以此预测未来。 递推公式 平滑常数的意义 一般说来，0.2 到 0.3 之间的数值可作为合理的平滑常数。这些数值表明本次预测需要将前期预测值的误差调整 20% 到 30%。 初始预测值的

15、确定问题 不指定初始预测值，计算机把第一个实际值作为初始预测值进行预测。 指定初始预测值，即令它等于前几期实际值的平均数。 指定初始预测值对用Excel进行计算的操作过程没有影响，只对预测结果产生影响。 *11tttYYY12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势v案例研究：小汽车租赁预测 例12.4 计算预测值 选择“指数平滑”工作表。 在单元格C2中输入公式“=AVERAGE(B2：B6)”。 在单元格C3中输入公式“=0.3*B2+0.7*C2”。 将单元格C3中的公式复制到区域C4:C12中。结果如图所示。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长

16、期趋势 绘制实际值与拟合值的折线图 单击工具栏中的“图表向导”快捷图标，打开“图表向导”对话框。 在步骤1中选择“图表类型”下的“折线图”，在“子图表类型”下选择“数据点折线图” ，单击“下一步”按钮。 输入数据区域：sheet指数平滑！$B$1:$C$12，单击“下一步”按钮。 在“标题”栏下输入“指数平滑”，单击“完成”按钮，如图所示。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势v 指数平滑分析工具预测 指数平滑分析工具的内容 指数平滑分析工具的使用 选择“指数平滑”工作表。 把区域A2:B12内容下移一行，在空白的单元格B2中输入公式“=AVERAGE(B3:B7)。

17、 打开“工具”菜单中的“数据分析”选项，弹出“数据分析”对话框。在“分析工具”列表中选择“指数平滑”选项，单击“确定”按钮，打开“指数平滑对话框” 。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势 在输入区域输入B2：B12，阻尼系数设为0.7，输出区域中输入C2，选择图表输出。单击“确定”按钮。 除单元格C2中显示的公式为“=B2”外，单元格C3:C12中显示的公式法均为平滑公式形式，这些是拟合值，要求的预测值，需把单元格C12中的公式复制到C13中。则得到的结果如图所示。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势v最佳平滑常数的确定 公式判断法 选择

18、“指数平滑”工作表。 在单元格F1中输入值0.3。 选取区域E1：F5，从“插入”菜单中选择“名称指定”选项，则弹出指定名称对话框，选中“最左列”，单击“确定”按钮。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势 选取区域D6：D11，从“插入”菜单中选择“名称指定”选项，在弹出的指定名称对话框，选中“首行”，单击“确定”按钮。 在单元格C2中输入公式“=AVERAGE(B2：B6)”。在单元格C3中输入公式“=平滑常数*B2+(1-平滑常数)*C2”，并复制到单元格C4:C11中。 在单元格D7中输入公式“=B7-C7”，并复制到单元格D8:D11中。 在单元格F3、F4、

19、F5中分别输入公式：“=AVERAGE(预测误差)”、“=STDEV(预测误差)”、和“=SQRT(平均误差2+标准误差2)”。结果如图所示。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势 在单元格F1中试用不同的值，则预测值、预测误差值、平均误差、标准误差和RMSPE的值以及图中曲线也将相应发生变化，比较RMSPE的大小，选取RMSPE最小时的值作为最优平滑常数进行预测。本例中平滑常数为0.85时RMSPE最小。此时得到的修饰后的指数平滑图如图所示。指 数 平 滑 曲 线51 01 52 02 51234567891 01 1租 赁 数拟 合 值 12.4 12.4 指数平

20、滑法测定长期趋势指数平滑法测定长期趋势 继“指数平滑”工作表，在“工具”菜单中选择“单变量求解”选项，出现单变量求解对话框。 在目标单元格、目标值、可变单元格中分别输入F5、0和F1，单击“确定”按钮。将出现单变量求解状态对话框，可以看到计算机的计算筛选过程。当计算机筛选出最终结果后，将停止计算。 工作表F列也在发生变化，如图所示。 单击“确定”按钮，计算机自动寻找的最佳平滑常数结果呈现在工作表上。12.4 12.4 指数平滑法测定长期趋势指数平滑法测定长期趋势12.5 12.5 季节变动的测定与分析季节变动的测定与分析 v季节变动测定的基本内容 简单平均法 长期趋势剔除法v 利用简单平均法测

21、定季节变动及其应用 例12.5 计算季节指数。 选择“简单平均”工作表。 在单元格H3中输入公式“AVERAGE(B3：G3)”，计算季平均数。 将单元格H3公式复制到H4:H6。 在单元格B7中输入公式“AVERAGE(B3：G6)”，计算总平均数。 在单元格I3中输入公式“H3$B$7”，并把它复制到I4：I6，计算各个季节指数，结果如图所示。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 v利用长期趋势剔除法测定季节变动及其应用 选择“长期趋势剔除”工作表。 在单元格E4中输入公式“AVERAGE(D2：D5)”。 在单元格F4中输入公式“AVERAGE(E4：E5)”。

22、把单元格E4：F4中的公式复制到E5：F19，调整其小数部分使显示1位小数，结果如图所示。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 在单元格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，点击自动求和工具（）两次。上述操作的结果如上图

23、所示。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 v 制作雷达图 单击工具栏中的“图表向导”快捷图标，打开“图表向导”对话框。 在步骤1中，于“图表类型”列表下选择“雷达图”，在“子图表类型”下选择“数据点雷达图”，单击“下一步”按钮。 输入数据区域为：sheet长期趋势剔除！$I$2:$I$5，单击“下一步”按钮。 在“标题”页面下输入“季节指数雷达图”，选择“图例”页面，取消显示图例。单击“完成”按钮。经修饰后得到的雷达图，如图所示： 季节指数雷达图0.800.901.001.10123412.5 12.5 季节变动的测定与分析季节变动的测定与分析 根据公式剔除季节变动的

24、影响，利用回归模型结合季节指数进行预测。 在区域J1：L1中分别输入标志“季节指数S序列”、“TCI*”和“预测值”。 选中单元格I2：I5，点击主菜单中“复制”图标。 选中单元格J2：J25，点击“编辑”菜单，选择“选择性粘贴”选项，在出现的选择性粘贴对话框中选择“数值”，单击“确定”按钮。 在单元格K2中输入公式“D2J2”，并把它复制到K2：K21，则区域K2：K21中的值即为季节调整后的序列TCI*。 选中单元格K2：K21，点击“编辑”菜单，选择“选择性粘贴”选项，在出现的选择性粘贴对话框中选择“数值”，单击“确定”按钮。把K2：K21中的公式形式变为数值形式。 选中单元格K2：K2

25、1，单击单元格K21右下角的填充句柄并向下拖动到单元格K25。当Excel用于这种方式自动填充时，区域K2:K21中的数列自动用线性趋势预测的数据对K22:K25进行扩展预测。这种方法相当于用TREND函数作简单线性回归，对序列21到24进行预测。 把回归预测的TCI*分量和季节分量S组合得到预测值Y*，即：Y*=TCI* S。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 在单元格L22中输入公式“I22*K22”，并把它复制到L23：L25。 结果如图所示。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 绘制实际销售额和预测值图表察看预测情况。 选取单元格C1：

26、D25，按住Ctrl键并选取单元格KI：K21和LI：L25，单击“图表向导”快捷图标，打开“图表向导”对话框。 在步骤1中，于“图表类型”列表下选择“折线图”，在“子图表类型”下选择“数据点折线图”，单击“下一步”按钮。 输入数据区域为：sheet长期趋势剔除！$ C1$2:$D$25，sheet长期趋势剔除！$ K$1:$K$21，sheet长期趋势剔除！$ L$1:$L$25，单击“下一步”按钮。 在“标题”页面下输入“实际值、季节调整TCI及预测值比较图”。单击“ 完成”按钮，结果如图所示。实际值、季节调整TCI及预测值比较图200250300350400450119933119943

27、119953119963119973119983销售额YTCI*预测值12.5 12.5 季节变动的测定与分析季节变动的测定与分析 v 利用哑变量进行季节调整与预测 选择“季节变动”工作表。 从“工具”菜单中，选择“数据分析”选项，出现“数据分析”工具对话框。 选中“回归”选项，单击“确定”按钮，出现“回归”对话框。 在“Y值输入区域”、“X值输入区域”、“输出区域”中分别输入D1:D21，E1:E21和K1；选择标志、置信度、残差、残差图、标准残差和线性拟合图。单击“确定”按钮。12.5 12.5 季节变动的测定与分析季节变动的测定与分析 计算机将在工作表中输出回归分析结果。下图为编辑过的一

28、部分结果： 回归分析工具产生的时序图中显示了实际值和拟合值，修饰后的图表如图所示。实 际 值 与 预 测 值 比 较 图200250300350400450119933119943119953119963119973119983时 间销售额Y预 测 值销 售 额季 节 变 动12.5 12.5 季节变动的测定与分析季节变动的测定与分析 v 使用TREND函数计算多个季度的拟合值和预测值。 把区域A18：C21的内容复制到单元格A22：C25，在单元格A22中输入1988。 选取区域E20：E21并把填充句柄向下拖到单元格E25。 把区域F18：I21的内容复制到单元格F22：I25。 在单元格J1中输入标记“预测值”。 选取区域J2：J25，按数组方式输入公式 “=T