应用Excel进行时间序列分析

7.09
6.49
9.19
8.63
实例：季节指数模型
周期
年
1986
1987
【例8】某工厂过去四
1
1988 1989
个5年的纳税情况如右
1990
表所示，这些数据有 明显的季节性波动， 试建立一个季节指数
1991
1992
2
1993
1994
1995
模型来预测下一个5年
1996
的纳税情况 。
1997
3
1998
一、 时间序列预测概述
3.时间序列成分
趋势成分：显示一个时间序列在较长时期的变化趋势 季节成分：反映时间序列在一年中有规律的变化 循环成分：反映时间序列在超过一年的时间内有规律的变化 不规则成分：不能归因于上述三种成分的时间序列的变化
销量 销量 销量 销售额
130
105
170
100
1999
2000
2001
2002
4
2003
2004
2005
纳税额(万元) 4.8 4.1 5.6 6 6.5 5.8 5.2 6.4 6.8 7.4 6 5.6 7.1 7.5 7.8 6.3 5.9 7.5 8 8.4
本章小结
本章重点是时间序列的四种EXCEL工作表预测模型
移动平均模型
Ft1
19
9
22
4
23
10
20
5
18
11
17
6
20
12
22
试在Excel工作表中建立一个移动平均预测模型来预测 第13周的汽油销量。
三、移动平均模型和指数平滑模型
汽油销量观测值及其移动平均预测值图形
移动平均跨度=5 MSE=4.11
30
销量观测值
移动平均预测值
25
19.80 20
15
10
周
1 2 3 4 5 6 7 8 9 10 11 12 13
29
§1 Excel进行移动平均分析的操作步骤
简单移动平均法 公式表明当t向前移动一个时期，就增加一个新近数据，去掉一个远期数据， 得到一个新的平均数。由于它不断地“吐故纳新”，逐期向前移动，所以称 为移动平均法。
由于移动平均可以平滑数据，消除周期变动和不规则变动的影响， 使得长期趋势显示出来，因而可以用于预测。其预测公式为：
三、移动平均模型和指数平滑模型
2. 指数平滑模型
（改进移动平均预测模型），将计算平均值时对 于不同时期观测值的权数设置得不同：近期的权 数较大，远期的权数较小
Ft1 Yt (1 )Yt1 (1 )2 Yt2 Ft1 Yt (1 )Ft Ft1 Ft (Yt Ft )
60000
40000
20000
0
1996年7月 1996年9月 1996年11月 1997年1月 1997年3月 1997年5月 1997年7月 1997年9月 1997年11月 1998年1月 1998年3月 1998年5月 1998年7月
五、Holt模型
Lt X t (1 )( Lt1 Tt1 )
月份1 月份2 月份3 月份4 月份5 月份6
商场各个月份空调销售额
44 月份7 57 月份13 79 月份19 96 48 月份8 67 月份14 82 月份20 100 51 月份9 72 月份15 80 月份21 100 52 月份10 68 月份16 85 月份22 105 58 月份11 72 月份17 94 月份23 110 55 月份12 69 月份18 89 月份24 111
Tt (Lt Lt1 )(1 )Tt1
Yt1 Lt Tt
实例：Holt预测模型
【例6】某商场两年内各个月份的空调机销售额数
据如下表所示。假定商场空调机前年最后一个月的 销售额为42，前年销售额的平均月增长幅度为2.93。 试建立一个Holt模型对商场未来的销售额进行预测。
1 N
N
Yt i1
i 1
指数平滑模型 Ft1 Yt (1 )Ft 趋势预测模型 Yˆ a bX 季节指数模型 Yˆt Tt St
主要函数和EXCEL技术
OFFSET()、SUMXMY2()、INDEX()、MATCH()、INTERCEPT()、
可得到直线趋势方程。以此求得每一个Xi所对应
的预测值:
Yˆi a bXi
四、趋势预测模型
求解a和b的三种方法：
利用Excel内建函数INTERCEPT()和SLOPE() 利用数组函数LINEST() 利用规划求解工具
求解预测值的四种方法：
利用线性趋势方程 Yˆ a bX 直接计算 利用Excel内建函数TREND() 利用Excel内建函数FORECAST() 用特殊方法拖动观测值所在范围
数据预测分析专题之一 ——时间序列预测
管理科学与工程学院 隋莉萍
数据预测分析的两个主要方面：
源自文库时间序列预测 回归分析预测
内容简介
时间序列的概念和组成 时间序列预测的步骤 衡量预测准确性的指标 移动平均模型和指数平滑模型 趋势预测模型 季节指数模型
一、 时间序列预测概述
SLOPE()、LINEST()、TREND()、FORECAST()
“规划求解”工具、“数据分析”工具、可调图形的制作
应用Excel进行时间序列分析
27
重点
❖ 1、Excel进行移动平均分析的操作步骤 ❖ 2、Excel进行指数平滑分析的操作步骤 ❖ 3、Excel进行趋势外推预测法的操作步骤 ❖ 4、Excel进行时间序列分解法的操作步骤
六、季节指数模型
建立季节指数模型的一般步骤：
第一步，计算每一季（每季度，每月等等）的季节指数St 。
第二步，用时间序列的每一个观测值除以适当的季节指数， 消除季节影响。
第三步，为消除了季节影响的时间序列建立适当的趋势模型 并用这个模型进行预测。
第四步，用预测值乘以季节指数，计算出最终的带季节影响 的预测值。
1.时间序列 时间序列就是一个变量在一定时间段内不同时间点
上观测值的集合 。这些观测值是按时间顺序排列 的，时间点之间的间隔是相等的。可以是年、季 度、月、周、日或其它时间段。 常见的时间序列有：按年、季度、月、周、日统计 的商品销量、销售额或库存量，按年统计的一个 省市或国家的国民生产总值、人口出生率等。
120
100
110
95
100
90
90
85
80
80
70
75
160 150 140 130 120
80
第一年
第二年
60
40
60
70
110
20
50 40
65 60
月
100 90
月
0
月
月
1 2 3 4 5 6 7 8 9 110 131 152 7 9 11 13 15 17 19 21 233 5 7 9 11 13 15 17 19 211 232 3 4 5 6 7 8 9 10 11 12
三、移动平均模型和指数平滑模型
指数平滑的叠代算法
Ft1 Yt (1 )Ft
Yt : 时间序列观测值
Ft : 时间序列预测值
Ft1 Ft (Yt Ft )
Yt : 时间序列观测值
Ft : 时间序列预测值
实例：指数平滑模型
【例2】利用例1的数据在Excel工作表中建立一个 指数平滑预测模型来预测第13周的汽油销量。
无趋势
线性趋势
非线性趋势
季节成分
二、时间序列的预测步骤
第一步，确定时间序列的类型
即分析时间序列的组成成分（趋势成分/季节成分/循环成分）。
第二步，选择合适的方法建立预测模型
如果时间序列没有趋势和季节成分，可选择移动平均或指数平滑法
如果时间序列含有趋势成分，可选择趋势预测法
如果时间序列含有季节成分，可选择季节指数法
产品名称
（全部）
求和项:销售金额 年 1996年
1997年
订购日期 7月 8月 9月 10月 11月 12月 1月 2月 3月 4月 5月 6月 7月 8月 9月
汇总 27861.89496 25485.27499 26381.39999 37515.72491 45600.04494 45239.62997 61258.06993 38483.63494 38547.21997 53032.95243 53781.28993 36362.80245 51020.85745 47287.66995 55629.24246
汽油销量观测值及其指数平滑预测值
平滑常数=0.3 MSE=6.95
30
销量观测值
指数平滑预测值
25
20.07 20
15
10
周
1 2 3 4 5 6 7 8 9 10 11 12 13
实例：
使用控件求解最优跨度和最优平滑指数
【例4/例5】利用例1的数据在Excel工作表中建立一 个利用函数和控件来控制移动跨度、平滑指数的移 动平均模型和指数平滑预测模型来预测第13周的汽 油销量。
41
6.3
2
5.2
2
5.9
3
6.8
3
8
4
7.4
4
8.4
10
9
8
7
6
5
4
实例：季节指数模型
电视机销量观测值及其季度预测值
第1年1季度 第1年2季度 第1年3季度 第1年4季度 第2年1季度 第2年2季度 第2年3季度 第2年4季度 第3年1季度 第3年2季度 第3年3季度 第3年4季度 第4年1季度 第4年2季度 第4年3季度 第4年4季度 第5年1季度 第5年2季度 第5年3季度 第5年4季度
将包括当前时刻在内的N个时间点上的观测值的平均值作 为对于下一时刻的预测值（N应选择得使MSE极小化）
Ft 1

1 N
N
Yt i1
i 1
实例：移动平均模型
【例1】某汽油批发商在过去12周内汽油的销售数量如 表所示：
周 销量（千加仑） 周 销量（千加仑）
1
17
7
22
2
21
8
18
3
六、季节指数模型
对于既含有线性趋势成分又含有季节成分的时间序 列，须对其成分进行分解，这种分解建立在以下乘 法模型的基础上：
Yt Tt St It 其中，Tt表示趋势成分，St表示季节成分，It表示
不规则成分。由于不规则成分的不可预测，因此预 测值就可表示为趋势成分和季节成分的乘积。
第三步，评价模型准确性，确定最优模型参数
MSE

1 n
n t 1
et2
1 n
n t 1
(Yt
Ft )2
第四步，按要求进行预测
三、移动平均模型和指数平滑模型
适用于围绕一个稳定水平上下波动的时间序列。
1.移动平均模型
利用平均使各个时间点上的观测值中的随机因素互相抵消 掉，以获得关于稳定水平的预测
一、 时间序列预测概述
2.时间序列预测方法
定性分析方法 定量分析方法
外推法：找出时间序列观测值中的变化规律与趋势，然后 通过对这些规律或趋势的外推来确定未来的预测值。包括： 移动平均和指数平滑法 趋势预测法 季节指数法
因果法：寻找时间序列因变量观测值与自变量观测值之间 的函数依赖关系（因果关系/回归分析），然后利用这种 函数关系和自变量的预计值来确定因变量的预测值。
zf
zf
移动平均法是一种简单平滑预测技术，它的 基本思想是：根据时间序列资料、逐项推移， 依次计算包含一定项数的序时平均值，以反 映长期趋势的方法。因此，当时间序列的数 值由于受周期变动和随机波动的影响，起伏 较大，不易显示出事件的发展趋势时，使用 移动平均法可以消除这些因素的影响，显示 出事件的发展方向与趋势（即趋势线），然 后依趋势线分析预测序列的长期趋势。
实例：趋势预测模型
【例3】针对Northwind Traders公司月销售额时 间序列，建立趋势预测模型，并预测该公司未来3 个月的销售额。
140000 120000 100000
80000
Northwind Traders公司月销售额观测值及其直线趋势预测值
观测值
预测值
103372.60 96458.88 99915.74
实例：季节指数模型
【例7】某工厂过去4年的空调机销量如下表所示，这
些数据有明显的季节性波动，试建立一个季节指数模 型来预测第5年每个季度的空调机销量 。

四年内每季度的电视机销量表
年 季度 销量（千台） 年 季度 销量（千台）
11
4.8
31
6
2
4.1
2
5.6
3
6
3
7.5
4
6.5
4
7.8
21
5.8
试探索共有几种利用MSE求最优跨度和平滑系数的途径？
四、趋势预测模型
对于含有线性趋势成分的时间序列，可以将预测 变量在每一个时期的值和其对应时期之间的线性 依赖关系表示为：
Yi a bXi i , i 1,2, , n
Yˆ a bX
利用使均方误差MSE极小的原则确定系数a与b，就