excel在时间序列中的应用.docx

excel在时间序列中的应用.docx

《excel在时间序列中的应用.docx》由会员分享，可在线阅读，更多相关《excel在时间序列中的应用.docx（11页珍藏版）》请在冰豆网上搜索。

excel在时间序列中的应用

第6章excel在时间序列分析中的应用

6.1时间序列的指标分析

6.1.1时间序列的水平和速度指标计算在时间序列分析中，水平和速度是最基本的内容。

下面举例说明如何应用excel求时间序列的水平和速度指标。

例题1.已知我国1993-2003年的人均GDP如表6-1。

根据数据计算1994-2003年我国人均GDP的逐期增长量、累计增长量、平均增长量、环比发展速度、环比增长速度、平均发展速度和平均增长速度。

表6-11993-2003年我国的人均GDP，（元，2000年价格）

年份

1993

1994

1995

1996

1997

1998

1999

2000

2001

2002

2003

人均GDP

4101

4567

4993

5412

5833

6228

6612

7086

7561

8135

8844

在Excel中输入数据（图6-1），在C、D、E、F列存储需要计算的各个指标。

在C3单元格输入公式“=B3-B2”可以计算出逐期增长量；在D3单元格输入公式

“=B3-$B$2”可以计算出累计增长量；在E3单元格输入公式“=B3/B2”可以计算出环比发展速度；在F3单元格输入公式“=E3-1”可以计算出环比增长速度。

选中区域C3:

F3，用填充柄复制公式至区域C4:

F12，可以计算出1995年-2003年的相应指标。

在单元格C13中输入公式“=Average（C3:

C12）”可以计算出平均增长量，在单元格E13中输入公式“=GEOMEAN（E3:

E1”2）可以计算出各年环比发展速度的几何平均值，也就是平均发展速度；这时Excel2003会自动在F13中计算出平均增长速度。

计算结果见表6-2。

图6-1用Excel计算时间序列水平和速度指标的过程

表6-2计算结果

年份

人均GDP

逐期增长量

累计增长量

环比发展速度

环比增长速度

1993

4101

-

-

-

-

1994

4567

466

466

111.36%

11.36%

1995

4993

426

892

109.33%

9.33%

1996

5412

419

1311

108.39%

8.39%

1997

5833

421

1732

107.78%

7.78%

1998

6228

395

2127

106.77%

6.77%

1999

6612

384

2511

106.17%

6.17%

2000

7086

474

2985

107.17%

7.17%

2001

7561

475

3460

106.70%

6.70%

2002

8135

574

4034

107.59%

7.59%

2003

8844

709

4743

108.72%

8.72%

平均值

474

107.99%

7.99%

6.1.2用Excel求解累计法平均发展速度如果使用累计法来计算平均发展速度或平均增长速度得话，将会涉及到一元高次方程的求解问题。

Excel的“规划求解”加载宏可以帮助我们计算相应的平均发展速度。

“规划求解”加载宏的安装与“分析工具库”的安装过程类似，加载“规划求解”宏程序后在工具菜单中可以看到“规划求解”选项。

下面举例说明具体的求解过程。

例题2.2003年北京的造林面积为47168公顷。

假设北京市计划在2004-2008年5年期间

完成造林总面积30万公顷，每年造林面积的平均增长速度应该达到多少？

这个问题适合用累计法来计算平均发展速度，然后再求平均增长速度。

相应的计算公式为

5

ai300000，a047168.

i1

在Excel的A2单元格中输入公式“=B2+B2^2+B2^3+B2^4+B2^5-300000/47168”，然后点击“工具”→“规划求解”，在弹出的对话框中作如下设置（图6-2）：

在“设置目标单元格”中输入A2，指定单元格的目标值为0；在可变单元格中输入被A2引用的单元格“B2”（单击“推测”按钮可以自动完成输入）。

单击“求解”后Excel会在B2单元格中给出计算

结果（图6-3）。

从图6-3可知，当年平均发展速度等于108.13%，即年平均增长速度等于8.13%时，可以完成5年造林30万公顷的目标。

这时A2中公式的计算结果为5.98*10-7，是一个非常接近于0

的值。

图6-2规划求解参数的设定

图6-3规划求解结果

6.2时间序列的分解

按传统的统计学理论，时间序列通常可以分解为长期趋势T、季节变动S、循环变动C和

不规则变动I四个组成成分。

这四个成分可以写成乘法模型或加法模型的形式。

这一节我们以乘法模型为例说明使用Excel进行时间序列分解的方法。

[例题3]表6-3和图6-4是我国1998-2003年我国流通中现金总量（月末数）。

试对序列进行时间序列分解。

表6-31998-2003年我国流通中现金总量，月末数（单位：

亿元）

1998

1999

2000

2001

2002

2003

1月

13108

11997

16094

17019

16726

21245

2月

10886

12784

13983

14910

16642

17937

3月

10201

11342

13235

14362

15545

17107

4月

10173

11225

13676

14623

15864

17441

5月

9984

10889

13076

13942

15281

17115

6月

9720

10881

13006

13943

15097

16957

7月

10037

11199

13157

14072

15358

17362

8月

10129

11395

13379

14370

15712

17607

9月

10528

12255

13895

15065

16234

18306

10月

10501

12154

13590

14484

16015

18251

11月

10671

12483

13878

14780

16346

18440

12月

11204

13455

14653

15689

17278

19746

在乘法模型中对季节性的时间序列进行分解的一般步骤是先计算季节指数；用剔除季节因素的序列拟合趋势方程分解出长期趋势；通过对剔除季节成分和长期趋势的序列进行移动平均获得循环变动；剔除前三个因素后的序列就是不规则变动。

6.2.1用Excel计算季节指数

对于有长期趋势的序列，计算季节指数时需要先对序列进行移动平均（由于移动的项数为偶数，还需要进行第二次的项数为2的移动平均）。

接下来用原序列除以移动平均序列得到剔除长期趋势和循环变动的序列，然后根据新序列计算季节指数。

对于流通中现金总量的例子，我们先要进行12期的移动平均，然后对新序列进行2期的

移动平均。

把数据整理成图6-5的形式，在单元格C8中输入公式“=AVERAGE（B2:

B13）”，然后通过拖动填充柄在区域C9:

C68种复制这个公式，这样就完成了第一次长度为12的移动

平均。

在单元格D8种输入公式“=AVERAGE（C8:

C9）”，并在区域D9:

D67中复制这个公式

完成“移正”的移动平均。

注意移动平均的结果与原数据位置的对应关系。

进行了两次移动

接下来在E8中输入公式“=B8/D8”并把这个公式复制到E9:

E67，得到剔除了长期趋势和循环变动的序列。

图6-5季节指数的计算过程：

移动平均

把得到的E序列整理成图6-6的形式，计算每行中5个数据的算术平均数得到H列的12个季节指数。

由于这12个季节指数之和等于11.9889，在I2中输入公式“=H2*12/$H$14”可以得到调整后的季节指数。

在H列中复制这个公式可以得到其他季节指数。

调整后的季节指数之和等于1200%。

图6-6季节指数的计算过程：

指数计算

根据得到的12个季节指数可是得到图6-7。

从图中可以看出，由于春节和元旦影响，每

年1月、2月和12月的现金流通量高于趋势值（100%），4月底和9月底由于劳动节和国庆节的因素的影响现金流通量接近趋势值，其它月份都比较明显地低于趋势值。

可见，现金流通量受节假日的影响非常明显。

图6-7现金流通量的季节指数

用原始数据除以相应的季节指数得到的序列称为季节调整后的序列（图6-8）。

一般来

说对长期趋势和循环变动的分析都是以季节调整后的序列为基础的。

6.2.2用Excel分析长期趋势长期趋势一般是根据季节调整后序列的趋势方程得到。

在Excel中建立一个新的序列作

为趋势方程中的t，取值为1至72。

使用Excel的分析工具库用季节调整后的序列对t作回归，

得到的趋势方程如下：

y=9747.057+122.0164t

（95.53）（50.23）

括号内为t检验值，方程的判定系数等于0.973。

要根据趋势方程得到相应的趋势值，在F2中输入公式

“=FORECAST（E2,$D$2:

$D$73,$E$2:

$E$73）”，然后在F列的其余单元格中复制该公式。

用趋势值乘以相应的季节指数可以得到对原序列的拟合值（图6-9中的G列），计算结果见图

6-10。

图6-9趋势值的计算过程

图6-10实际值、趋势值和拟合值

6.2.3用Excel分析循环变动和不规则变动用原始数据除以季节指数和长期趋势值可以得到一个只包含循环变动和不规则变动的

新序列（CI）。

对这个新序列进行3项的移动平均消除不规则变动就可以得到循环变动（C）

了。

用CI序列除以C序列就可以得到不规则变动序列I。

相应的计算过程可参考图6-11。

图6-12是循环变动成分的图形，从图形中可以看出在1998-2003年期间现金流通量大致

出现了1998-1999以及2000-2003年两个周期波动。

图6-13是不规则变动的图形。

相对于长期趋势和季节变动而言，时间序列的循环变动和不规则变动成分的应用相对要少一些。

图6-11循环变动和不规则变动的计算过程

图6-12现金流通量的循环变动成分