excel在时间序列中的应用

第6章excel在时间序列分析中的应用6.1时间序列的指标分析6.1.1时间序列的水平和速度指标计算在时间序列分析中，水平和速度是最基本的内容。下面举例说明如何应用excel求时间序列的水平和速度指标。例题1.已知我国1993-2003年的人均GDP如表6-1。根据数据计算1994-2003年我国人均GDP的逐期增长量、累计增长量、平均增长量、环比发展速度、环比增长速度、平均发展速度和平均增长速度。表6-11993-2003年我国的人均GDP，（元，2000年价格）年份19931994199519961997199819992000200120022003人均GDP41014567499354125833622866127086756181358844在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:E12)”可以计算出各年环比发展速度的几何平均值，也就是平均发展速度；这时Excel2003会自动在F13中计算出平均增长速度。计算结果见表6-2。图6-1用Excel计算时间序列水平和速度指标的过程表6-2计算结果年份人均GDP逐期增长量累计增长量环比发展速度环比增长速度19934101----19944567466466111.36%11.36%19954993426892109.33%9.33%199654124191311108.39%8.39%199758334211732107.78%7.78%199862283952127106.77%6.77%199966123842511106.17%6.17%200070864742985107.17%7.17%200175614753460106.70%6.70%200281355744034107.59%7.59%200388447094743108.72%8.72%平均值474107.99%7.99%6.1.2用Excel求解累计法平均发展速度如果使用累计法来计算平均发展速度或平均增长速度得话，将会涉及到一元高次方程的求解问题。Excel的“规划求解”加载宏可以帮助我们计算相应的平均发展速度。“规划求解”加载宏的安装与“分析工具库”的安装过程类似，加载“规划求解”宏程序后在工具菜单中可以看到“规划求解”选项。下面举例说明具体的求解过程。例题2.2003年北京的造林面积为47168公顷。假设北京市计划在2004-2008年5年期间完成造林总面积30万公顷，每年造林面积的平均增长速度应该达到多少？这个问题适合用累计法来计算平均发展速度，然后再求平均增长速度。相应的计算公式为5234501()0iixxxxxaa。其中x为平均发展速度，51300000iia，047168a.在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年我国流通中现金总量，月末数（单位：亿元）1998199920002001200220031月1310811997160941701916726212452月1088612784139831491016642179373月1020111342132351436215545171074月1017311225136761462315864174415月998410889130761394215281171156月972010881130061394315097169577月1003711199131571407215358173628月1012911395133791437015712176079月10528122551389515065162341830610月10501121541359014484160151825111月10671124831387814780163461844012月112041345514653156891727819746在乘法模型中对季节性的时间序列进行分解的一般步骤是先计算季节指数；用剔除季节因素的序列拟合趋势方程分解出长期趋势；通过对剔除季节成分和长期趋势的序列进行移动平均获得循环变动；剔除前三个因素后的序列就是不规则变动。6.2.1用Excel计算季节指数对于有长期趋势的序列，计算季节指数时需要先对序列进行移动平均（由于移动的项数为偶数，还需要进行第二次的项数为2的移动平均）。接下来用原序列除以移动平均序列得到剔除长期趋势和循环变动的序列，然后根据新序列计算季节指数。对于流通中现金总量的例子，我们先要进行12期的移动平均，然后对新序列进行2期的移动平均。把数据整理成图6-5的形式，在单元格C8中输入公式“=AVERAGE(B2:B13)”，然后通过拖动填充柄在区域C9:C68种复制这个公式，这样就完成了第一次长度为12的移动平均。在单元格D8种输入公式“=AVERAGE(C8:C9)”，并在区域D9:D67中复制这个公式完成“移正”的移动平均。注意移动平均的结果与原数据位置的对应关系。进行了两次移动平均以后，新序列的首尾都比原序列少了6个数。接下来在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-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现金流通量的循环变动成分图6-13现金流通量的不规则变动成分