Excel 统计分析与决策第12章 时间序列分析与预测 主要内容 12.1 时间数列的基本特征 12.2 移动平均分析与预测 12.3 回归
分析与预测 12.4 指数平滑法与预测 12.5 季节变动的测定与分析 12.6 周期波动的测定与分析 时间数列的概念与特点时间数
列按时间先后顺序排列时间数列是按一定方式搜集的一系列数据 时间数列中的观察值具有差异 时间数列中的数据不许遗漏12.1 时间数列的
基本特征 时间数列的构成与分解乘法模型的一般形式为： Y=T×S×C×I 式中 Y、T是总量指标
S、C、I为比率加法模型的一般形式为： Y=T+S+C+I 式中Y、T、S、C、I都
是总量指标 12.2 移动平均分析与预测移动平均法的基本内容移动平均法是在算术平均法的基础上发展起来的预测方法。它是利用过去若干期
实际值的均值来预测现象的发展趋势简单移动平均公式如下： 12.2 移动平均分析与预测利用趋势图测定长期趋势例 12.1 对2
008年的零售额进行预测。产生“年季”变量选择“移动平均”工作表。在C列选定任一个单元格，选择“插入”菜单中的“列”选项，则原来C
列的内容被移到D列。在C1单元格中输入标志“年季”在单元格C2中输入公式“=B2&CHAR(13)&A2”。再把单元格C2中的公式
复制到C3：C21。结果如图所示。12.2 移动平均分析与预测根据图12.2中资料绘制销售额趋势图。打开“插入”菜单中的“图表”选
项，Excel弹出“图表向导”对话框。在步骤1的“图表类型”列表中选择“折线图”选项；在“子图表类型”列表中选择“数据点折线图”。
单击“下一步”按钮 ，进入向导步骤2对话框。12.2 移动平均分析与预测在步骤2的“图表数据源”对话框中，在数据区域输入 “C1：
D21”，并单击“下一步”按钮，进入向导步骤3对话框。在步骤3的“图表选项”中，选择“标题”页面，在“图表标题”、“分类（X）轴”
、“数值（Y）轴”中分别填入“某地区销售额趋势图”、“季度”和“销售额（万元）”；选择“网格线”页面，把“数值（Y）轴”下的“主要
网格线”设为空；选择“图例”页面，取消图例显示。单击“完成”按钮，得到趋势图。12.2 移动平均分析与预测在图表中插入趋势线进行预
测单击图表以激活它，选取垂直轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框。打开“刻度”页面，在
“最小值”、“最大值”、“主要刻度单位”中分别输入200、450和50；打开“字体”页面，设置字号为“8”。单击“确定”按钮。选取
水平轴，双击或单击鼠标右键并从快捷菜单中选择“坐标轴格式”选项，Excel弹出对话框。打开“对齐”页面，取消自动设置；打开“字体”
页面，设置字号为“8”。单击“确定”按钮。12.2 移动平均分析与预测选取图中的折线，单击鼠标右键并从快捷菜单中选择“添加趋势线”
选项，打开“添加趋势线”对话框。选择“类型”页面，在“趋势预测/回归分析类型”框中选择“移动平均”，设置“周期”为4。单击“确定”
按钮。12.2 移动平均分析与预测利用Excel创建公式测定长期趋势选择“移动平均”工作表。在单元格E1中输入“公式预测值”。在单
元格E6中输入公式“=AVERAGE(D2：D5)”。把单元格E6中的公式复制到E7：E22各单元格中。结果如图所示。12.2 移
动平均分析与预测移动平均分析工具测定长期趋势移动平均分析工具的内容12.2 移动平均分析与预测利用移动平均分析工具进行预测选择“移
动平均”工作表。在单元格F1、G1中分别输入“分析工具预测值”和“预测标准误差”。从“工具”菜单中选择“数据分析”选项，在弹出的“
数据分析”对话框中选中“移动平均”选项，并单击“确定”按钮，出现“移动平均”对话框。在输入区域中输入D1：D21，选定标志位于第一
行，间隔设为4，在输出区域中输入F2。选择图表输出和标准误差。单击“确定”按钮，所得结果如图所示。12.2 移动平均分析与预测时间
数列预测工作表函数FORECAST预测函数TREND趋势函数GROWTH增长函数LINEST线性拟合函数LOGEST函数使用直线函
数LINEST和趋势函数TREND线性预测法使用LINEST函数计算回归统计值例12.2 用最小平方法进行趋 势预测11、12
月份的销售量。选择“趋势”工作表。12.3 回归分析测定长期趋势在E1中输入“回归统计量”。选择E2：F6，单击“插入”菜单，选择
“函数”，Excel弹出“插入函数”对话框。在“选择类别”中选择“统计”，在“选择函数”中选择“LINEST”函数，则打开LINE
ST函数对话框。在Known_y’s、 Known_x’s和Stats后分别输入B2：B11、A2：A11、和1。按住Ctrl+S
hift组合键，单击 “确定”按钮。12.3 回归分析测定长期趋势在选中E2：F6的情况下输入数组公式：“=LINEST(C2:C
11,B2:B11,1)”，同时按住Ctrl+Shift两键，再按回车键。得到的结果与上面操作相同。计算预测值：在D1单元格中输入
“预测值”。在D2单元格中输入公式“=$F$2+$E$2B2”。将D2单元格中的公式复制到D3：D11各单元格。12.3 回归分
析测定长期趋势使用趋势函数TREND求预测值在单元格C1中输入“拟合值”。选定单元格C2:C11。在“插入”菜单中选择“函数”选项
，打开“插入函数”对话框。在“选择类别”中选择“统计”，在“选择函数”中选择TREND函数，单击“确定”按钮，将打开TREND对话
框。12.3 回归分析测定长期趋势在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组合键，按回车键。输出结果如图所示。
12.3 回归分析测定长期趋势趋势预测在单元格A12中输入11。在单元格C12中输入公式：“=TREND(B2:B11,A2:A1
1,A12)”。用TREND函数也可以进行多期预测在区域A12:A25中分别输入11~24。选定区域C12:C25，输入数组公式：
=TREND(B2:B11,A2:A11,A12:A25)，按住Ctrl+Shift组合键，按回车键。将在C12：C25的各单元格
中返回第11到第24个时间点的预测值。12.3 回归分析测定长期趋势使用指数函数LOGEST和增长函数GROWTH拟合长期趋势例
12.3 拟合指数曲线，并预测2008~2011年产量。使用LOGEST函数 计算回归统计量选择“增长曲线”工作表。12.3 回
归分析测定长期趋势选择E2：F6区域，单击“插入函数”快捷键，弹出“插入函数”对话框。在“选择类别”中选择“统计”，在“选择函数”
中选择“LOGEST”函数，打开LOGEST对话框。在Known_y’s、 Known_x’s和Stats后分别输入C2：C16、
B2：B16和1。按住Ctrl+Shift组合键，再按回车键或“确定”按钮，得计算结果如图中E2：F6单元格所示。12.3 回归分
析测定长期趋势使用增长函数GROWTH计算预测值选择D2：D20单元格，在工具栏单击“插入函数”快捷键，弹出“插入函数”对话框。在
“选择类别”中选择“统计”，在“选择函数”中选择“GROWTH”函数，打开GROWTH对话框。在Known_y’s、 Known_
x’s、New_x’s和Const后分别输入C2：C16、B2：B16、B2：B20和1。按住Ctrl+Shift组合键，同时按回
车键或“确定”按钮，计算结果如图所示。或者在选择D2：D20单元格的情况下，输入数组公式“=GROWTH(C2:C16,B2:B1
6, B2:B20,1)”，同时按住Ctrl+Shift组合键，同时按回车键。结果与上述相同。12.3 回归分析测定长期趋势绘制实
际值与预测值的折线图：在工具栏中单击 “图表向导”快捷图标打开“图表向导”对话框。在步骤1中选择“图表类型”列表中的“折线图”，在
“子图表类型”下选择“数据点折线图”，单击“下一步”按钮。确认数据区域为：sheet增长曲线！$A$2:$A$16，sheet增长
曲线！$C$2:$D$16，单击“下一步”按钮。在“标题”栏下输入“某地区彩色电视机产量发展趋势”，单击“完成”按钮，得到的结果如
图所示。12.3 回归分析测定长期趋势指数平滑法的基本内容与要求通过对历史时间数列进行逐层平滑计算，从而消除随机因素的影响，识别经
济现象基本变化趋势，并以此预测未来。递推公式平滑常数的意义一般说来，0.2 到 0.3 之间的数值可作为合理的平滑常数。这些数值表
明本次预测需要将前期预测值的误差调整 20% 到 30%。初始预测值的确定问题不指定初始预测值，计算机把第一个实际值作为初始预测值
进行预测。指定初始预测值，即令它等于前几期实际值的平均数。指定初始预测值对用Excel进行计算的操作过程没有影响，只对预测结果产生
影响。 12.4 指数平滑法测定长期趋势案例研究：小汽车租赁预测例12.4 计算预测值选择“指数平滑”工作表。在单元格C2中输入公
式“=AVERAGE(B2：B6)”。在单元格C3中输入公式“=0.3B2+0.7C2”。将单元格C3中的公式复制到区域C4:
C12中。结果如图所示。12.4 指数平滑法测定长期趋势绘制实际值与拟合值的折线图单击工具栏中的“图表向导”快捷图标，打开“图表向
导”对话框。在步骤1中选择“图表类型”下的“折线图”，在“子图表类型”下选择“数据点折线图” ，单击“下一步”按钮。输入数据区域：
sheet指数平滑！$B$1:$C$12，单击“下一步”按钮。在“标题”栏下输入“指数平滑”，单击“完成”按钮，如图所示。12.4
指数平滑法测定长期趋势 指数平滑分析工具预测指数平滑分析工具的内容指数平滑分析工具的使用选择“指数平滑”工作表。把区域A2:B1
2内容下移一行，在空白的单元格B2中输入公式“=AVERAGE(B3:B7)。打开“工具”菜单中的“数据分析”选项，弹出“数据分析
”对话框。在“分析工具”列表中选择“指数平滑”选项，单击“确定”按钮，打开“指数平滑对话框” 。12.4 指数平滑法测定长期趋势在
输入区域输入B2：B12，阻尼系数设为0.7，输出区域中输入C2，选择图表输出。单击“确定”按钮。除单元格C2中显示的公式为“=B
2”外，单元格C3:C12中显示的公式法均为平滑公式形式，这些是拟合值，要求的预测值，需把单元格C12中的公式复制到C13中。则得
到的结果如图所示。12.4 指数平滑法测定长期趋势最佳平滑常数的确定公式判断法选择“指数平滑”工作表。在单元格F1中输入值0.3。
选取区域E1：F5，从“插入”菜单中选择“名称——指定”选项，则弹出指定名称对话框，选中“最左列”，单击“确定”按钮。12.4 指
数平滑法测定长期趋势选取区域D6：D11，从“插入”菜单中选择“名称——指定”选项，在弹出的指定名称对话框，选中“首行”，单击“确
定”按钮。在单元格C2中输入公式“=AVERAGE(B2：B6)”。在单元格C3中输入公式“=平滑常数B2+(1-平滑常数)C
2”，并复制到单元格C4:C11中。在单元格D7中输入公式“=B7-C7”，并复制到单元格D8:D11中。在单元格F3、F4、F5
中分别输入公式：“=AVERAGE(预测误差)”、“=STDEV(预测误差)”、和“=SQRT(平均误差^2+标准误差^2)”。结
果如图所示。12.4 指数平滑法测定长期趋势在单元格F1中试用不同的α值，则预测值、预测误差值、平均误差、标准误差和RMSPE的值
以及图中曲线也将相应发生变化，比较RMSPE的大小，选取RMSPE最小时的值作为最优平滑常数进行预测。本例中平滑常数为0.85时R
MSPE最小。此时得到的修饰后的指数平滑图如图所示。12.4 指数平滑法测定长期趋势继“指数平滑”工作表，在“工具”菜单中选择“单
变量求解”选项，出现单变量求解对话框。在目标单元格、目标值、可变单元格中分别输入F5、0和F1，单击“确定”按钮。将出现单变量求解
状态对话框，可以看到计算机的计算筛选过程。当计算机筛选出最终结果后，将停止计算。 工作表F列也在发生变化，如图所示。 单击“确定
”按钮，计算机自动寻找的最佳平滑常数结果呈现在工作表上。12.4 指数平滑法测定长期趋势12.5 季节变动的测定与分析 季节变动测
定的基本内容简单平均法长期趋势剔除法利用简单平均法测定季节变动及其应用例12.5 计算季节指数。选择“简单平均”工作表。在单元格
H3中输入公式“＝AVERAGE(B3：G3)”，计算季平均数。将单元格H3公式复制到H4:H6。在单元格B7中输入公式“＝AVE
RAGE(B3：G6)”，计算总平均数。在单元格I3中输入公式“＝H3／$B$7”，并把它复制到I4：I6，计算各个季节指数，结果
如图所示。12.5 季节变动的测定与分析 利用长期趋势剔除法测定季节变动及其应用选择“长期趋势剔除”工作表。在单元格E4中输入公式
“＝AVERAGE(D2：D5)”。在单元格F4中输入公式“＝AVERAGE(E4：E5)”。把单元格E4：F4中的公式复制到E5
：F19，调整其小数部分使显示1位小数，结果如图所示。12.5 季节变动的测定与分析 在单元格G4中输入公式“＝D4／F4”，并把
它复制到G5：G19。在单元格H2中输入公式“＝AVERAGE(H6，H10，H14，H18)”, 并把它复制到单元格H3中。在单
元格H4中输入公式“＝AVERAGE(H4，H8，H12，H16)”， 并把它复制到单元格H5中。选取单元格H6，点击自动求和工具
（∑）两次。在单元格I2输入公式“＝H24／$H$6”， 把它复制到单元格I3：I5。选取单元格I6，点击自动求和工具（∑）两次
。上述操作的结果如上图所示。12.5 季节变动的测定与分析 制作雷达图单击工具栏中的“图表向导”快捷图标，打开“图表向导”对话框。
在步骤1中，于“图表类型”列表下选择“雷达图”，在“子图表类型”下选择“数据点雷达图”，单击“下一步”按钮。输入数据区域为：she
et长期趋势剔除！$I$2:$I$5，单击“下一步”按钮。在“标题”页面下输入“季节指数雷达图”，选择“图例”页面，取消显示图例。
单击“完成”按钮。经修饰后得到的雷达图，如图所示：12.5 季节变动的测定与分析 根据公式剔除季节变动的影响，利用回归模型结合季节
指数进行预测。在区域J1：L1中分别输入标志“季节指数S序列”、“TCI”和“预测值”。选中单元格I2：I5，点击主菜单中“复制
”图标。选中单元格J2：J25，点击“编辑”菜单，选择“选择性粘贴”选项，在出现的选择性粘贴对话框中选择“数值”，单击“确定”按钮
。在单元格K2中输入公式“＝D2／J2”，并把它复制到K2：K21，则区域K2：K21中的值即为季节调整后的序列TCI。选中单元
格K2：K21，点击“编辑”菜单，选择“选择性粘贴”选项，在出现的选择性粘贴对话框中选择“数值”，单击“确定”按钮。把K2：K21
中的公式形式变为数值形式。选中单元格K2：K21，单击单元格K21右下角的填充句柄并向下拖动到单元格K25。当Excel用于这种方
式自动填充时，区域K2:K21中的数列自动用线性趋势预测的数据对K22:K25进行扩展预测。这种方法相当于用TREND函数作简单线
性回归，对序列21到24进行预测。把回归预测的TCI分量和季节分量S组合得到预测值Y，即：Y=TCI S。12.5 季节变
动的测定与分析 在单元格L22中输入公式“＝I22K22”，并把它复制到L23：L25。结果如图所示。12.5 季节变动的测定与
分析 绘制实际销售额和预测值图表察看预测情况。选取单元格C1：D25，按住Ctrl键并选取单元格KI：K21和LI：L25，单击“
图表向导”快捷图标，打开“图表向导”对话框。在步骤1中，于“图表类型”列表下选择“折线图”，在“子图表类型”下选择“数据点折线图”
，单击“下一步”按钮。输入数据区域为：sheet长期趋势剔除！$ C1$2:$D$25，sheet长期趋势剔除！$ K$1:$K$
21，sheet长期趋势剔除！$ L$1:$L$25，单击“下一步”按钮。在“标题”页面下输入“实际值、季节调整TCI及预测值比较
图”。单击“ 完成”按钮，结果如图所示。12.5 季节变动的测定与分析 利用哑变量进行季节调整与预测选择“季节变动”工作表。从“工
具”菜单中，选择“数据分析”选项，出现“数据分析”工具对话框。选中“回归”选项，单击“确定”按钮，出现“回归”对话框。在“Y值输入
区域”、“X值输入区域”、“输出区域”中分别输入D1:D21，E1:E21和K1；选择标志、置信度、残差、残差图、标准残差和线性拟
合图。单击“确定”按钮。12.5 季节变动的测定与分析 计算机将在工作表中输出回归分析结果。下图为编辑过的一部分结果：回归分析工具
产生的时序图中显示了实际值和拟合值，修饰后的图表如图所示。12.5 季节变动的测定与分析 使用TREND函数计算多个季度的拟合值和
预测值。把区域A18：C21的内容复制到单元格A22：C25，在单元格A22中输入1988。选取区域E20：E21并把填充句柄向下
拖到单元格E25。把区域F18：I21的内容复制到单元格F22：I25。在单元格J1中输入标记“预测值”。选取区域J2：J25，按
数组方式输入公式 “=TREND(D2:D21,E2:H21,E2:H25,1)”， 调整小数位数为1。结果如图J列所示。12.5
季节变动的测定与分析 周期波动的基本内容 周期波动是指在相当长的时期中，现象所呈现的一种有规则的变动。它可能是一种景气变动、经济
变动或其他周期变动，也可以代表经济或某个特定行业发展变化中的波动。每一个周期都有大致相同的过程：复苏、扩张、衰退和收缩。它所反映的
是经济现象在连续时间内重复出现的涨落情况，强调的是现象的重复再现性。 12.6 周期波动的测定与分析 城镇居民储蓄额周期波动选择
“周期波动”工作表。使用长期趋势剔除法计算季节指数S，并得到长期趋势与周期波动序列TCI（在I列）。该过程计算结果呈现在C~I列。
12.6 周期波动的测定与分析 选中单元格J2：J55，输入数组公式“=GROWTH(I2:I55)”，按住Ctrl+Shift键，按下回车键，便可计算出长期趋势T分量。在单元格K2中输入公式“=I2/J2”，并把它复制到K3:K55各单元格中，便得到剔除趋势分量后的周期波动与不规则变动分量CI。为消除不规则变动的影响，对CI分量进行中心化移动平均。在单元格L3中输入公式“=AVERAGE(K2:K5)” ，并把它复制到L4:L54各单元格中。在单元格M3中输入公式“=AVERAGE(L3:L4)” ，并把它复制到M4:M54各单元格中。则M列中即为周期波动序列。操作结果如图所示：12.6 周期波动的测定与分析 选定M4:M54各单元格，单击工具栏中的“图表向导”快捷图标，打开“图表向导”对话框。在步骤1中选择“图表类型”下的折线图，在“子图表类型”下选择数据点折线图，单击“下一步”按钮。确认数据区域为：sheet周期波动！$A$1:$A$54，sheet周期波动！$M$1:$M$54，单击“下一步”按钮。在“标题”栏下输入“城镇居民储蓄周期波动图”，选择“图例”页面，取消显示图例。单击“完成”按钮。经修饰后的图表如图所示：12.6 周期波动的测定与分析 |
|
