实验五-用EXCEL进行时间序列分析

实验五 用E X C E L 进行时间序列分析

一、实验目的

利用Excel 进行时间序列分析

二、实验内容

1．测定发展水平和平均发展水平

2. 测定增长量和平均增长量

3. 测定发展速度、增长速度和平均发展速度

4. 计算长期趋势

5. 计算季节变动

三、实验指导

时间序列分析常用的方法有两种：指标分析法和构成因素分析法。

指标分析法，通过计算一系列时间序列分析指标，包含发展水平、平均发展水平、增长量、平均增长量、发展速度、平均发展速度等来揭示现象的发展状况和发展变化程度。

构成因素分析法，是将时间序列看做由长期趋势、季节变动、循环变动、不规则变动四种因素构成，将各影响因素分别从时间序列中分离出去并加以测定、对未来发展做出预测的过程。 发展水平： 发展水平是指某一经济现象在各个时期达到的实际水平。

在时间序列中，各指标数值就是该指标所反映的社会经济现象在所属时间的发展水平。在时间序列中，我们用y 表示指标值，t 表示时间，则t y (t=0,1,2,3,…,n)表示各个时期的指标值。

平均发展水平： 平均发展水平又称“序时平均数”、“动态平均数”，是时间序列中各项发展水平的平均数，反映现象在一段时期中发展的一般水平。

增长量： 增长量是指某一经济现象在一定时期增长或减少的绝对量。它是报告期发展水平减基期发展水平之差。

平均增长量：平均增长量是时间序列中的逐期增长量的序时平均数，它表明现象在一定时段内平均每期增加（减少）的数量。公式表示如下：

发展速度：发展速度是说明事物发展快慢程度的动态相对数。它等于报告期水平对基期水平之比。发展速度有两种：分为环比发展速度和定基发展速度。 1．环比发展速度：也称逐期发展速度，是报告期发展水平与前一期发展水平之比。 2．定基发展速度：是报告期水平与固定基期水平之比。 平均发展速度：平均发展速度是动态数列中各期环比发展速度或各期定基发展速度中的环比发展速度的序时平均数。它说明在一定时期内发展速度的一般水平。

平均发展速度的计算方法有几何法和方程法。

1．几何法计算平均发展速度：实际动态数列各期环比发展速度连乘积等于理论动态数列中各期平均发展速度的连乘积

2．方程法计算平均发展速度：方程法平均发展速度的特点是实际动态数列各项之和等于理论动态数列各项之和，所以称为“累积法”

（1）测定发展水平和平均发展水平

在时间i t 上的观察值i Y ，就是该时间点的发展水平。

平均发展水平是现象在时间i t （i=1,2,…,n ）上各期观察值i Y 的平均数。

①时期序列的序时平均数计算

打开工作簿“实验5.1.xls ”，利用“全国GDP ”工作表中的数据，计算年度平均国内生产总值，计算结果如图5.1.1所示：

图5.1.1 用EXCEL 计算平均发展水平资料及结果

计算步骤：

第一步：计算观察值个数n ：在B14中输入公式“=count(A2:A13)”,得到观察值个数为12。 第二步：计算总国内生产总值：在B15中输入公式“=sum （A2:A13)”，得到总生产总值为2821530.71。

第三步：计算平均国内生产总值：在B16中输入公式“=B15/B14”，得到平均国内生产总值。 ②时点序列的序时平均数计算 连续时点序列序时平均数：∑∑=T

YT Y 打开工作簿“实验5.1.xls ”，利用“库存量”工作表中的数据，计算12月份平均日库存量，计算结果如图5.1.2所示：

图5.1.2 用EXCEL 计算平均发展水平资料及结果

计算步骤：

第一步：计算∑T ：B4、C4、D4、E4、F4中输入各日期天数T ，在G4中输入公式“=SUM(B4:F4)”，得到值为31天。

第二步：计算∑YT ：在B5中输入公式“=B3*B4”, 得到日期1~4的值240件，C5中输入公式“=C3*C4”，得到日期5~10的值378件，D5中输入公式“=D3*D4”，得到日期11~20的值500件，E5中输入公式“=E3*E4”，得到日期21~26的值270件，F5中输入公式“=F3*F4”，得到日期27~31的值200件。在G5中输入公式“=SUM(B5:F5)” 可得值1588。

第三步：计算平均日库存量：在B7中输入公式“=G5/G4，得到平均日库存量44件。

③间断时点序列序时平均数：

打开工作簿“实验5.1.xls ”，利用“存款余额”工作表中的数据，计算平均存款余额，计算结果如图5.1.3所示：

图5.1.3 用EXCEL 计算平均发展水平资料及结果

计算步骤：

第一步：计算两个时间点的平均观察值：在C5中输入公式“=average(B3:C3)”, 并用鼠标拖曳将公式复制到D5：H5区域。

第二步：算出间隔天数：t1、t2、t3、t4、t5。 第三步：计算

112

--?+n n n T Y Y 值：在C9中输入公式“=C5*C7”，并用鼠标拖曳将公式复制到D9：H9区域。

第四步：计算总天数：在B10输入公式：“= SUM(C7:H7)”，得到总天数365天。

第五步：计算平均存款额：在B11输入公式：“=SUM(C9:H9)/SUM(C7:H7)”，得到平均存款额为120.03百万元。

（2）测定增长量和平均增长量

增长量是报告期水平与基期水平之差，其中逐期增长量：1--=?i i Y Y Y ；累积增长量：0Y Y Y i -=?。

打开工作簿“实验5.2.xls ”，利用“全国GDP ”工作表中的数据，计算逐期增长量、累积增长量和平均增长量，计算结果如图5.2所示：

图5.2 用EXCEL 计算增长量和平均增长量资料及结果

计算步骤：

第一步：计算逐期增长量：在C3中输入公式“=B3-B2”，并用鼠标拖曳将公式复制到C4：C13区域。

第二步：计算累计增长量：在D3中输入公式“=B3-$B$2”，并用鼠标拖曳公式复制到D4：D13区域。

第三步：计算平均增长量（水平法）：在B15中输入公式“=D13/11”，按回车键，即可得到平均增长量。

（3）测定发展速度、增长速度和平均发展速度

发展速度是同一现象在两个不同时期发展水平对比的结果，用于描述现象在观察期内的相对发展变化程度。发展速度=报告期水平/基期水平*100%。 其中定基发展速度：0Y Y X i i =；环比发展速度：1

-=i i i Y Y X 增长速度又称增长率，是增长量和基期水平之比。增长速度=增减量/基期水平=（报告期水平-基期水平）/基期水平=发展速度-1。 其中定基增长速度：1000-=-=Y Y Y Y Y X i i i ；环比增长速度：11

11-=-=---i i i i i i Y Y Y Y Y X 。 平均发展速度是现象各个时期环比发展速度的平均数，用于描述现象在整个观察期内平均发展变化的程度。水平法的计算公式：n n n i i Y Y Y Y Y Y Y Y X 0

11201=???=- 平均增长速度=平均发展速度-1。

打开工作簿“实验5.3.xls ”，利用“全国GDP ”工作表中的数据，计算定基发展速度、环比发展速度和平均发展速度，计算结果如图5.3所示：

图5.3 用EXCEL 计算发展速度、增长速度和平均发展速度资料及结果

计算步骤：

第一步：计算定基发展速度：在C3中输入公式“=B3/$B$2”，并用鼠标拖曳将公式复制到C4：C13区域。

第二步：计算环比发展速度：在D3中输入公式“=B3/B2”，并用鼠标拖曳将公式复制到D4：D13区域。

第三步：计算定基增长速度：在E3中输入公式“=C3-1”，并用鼠标拖曳将公式复制到D4：D13区域。

第四步：计算环比增长速度：在F3中输入公式“=D3-1”，并用鼠标拖曳将公式复制到D4：D13区域。

第五步：计算平均发展速度（水平法）：在B15中输入公式“= GEOMEAN （D3：D13）” 按回车键，即可得到平均发展速度。

第六步：计算平均增长速度（水平法）：在B16中输入公式“=B15-1”，按回车键，即可得到平均增长速度。

（4）计算长期趋势

趋势分析方法有许多，这里介绍移动平均法和指数平滑法，主要通过对时间序列进行平滑以消除其随机波动。

移动平均法，是通过对时间序列逐期递移求得平均数作为趋势值或预测值的一种平滑法。设移动间隔长度为K ，则移动平均序列写成：K

Y Y Y Y k i i i i 11-+++++= 。 指数平滑法，是用过去的观察值的加权平均数作为趋势值，它是加权平均的一种特殊形式。t

t t Y Y Y ?)1(?1αα-+=+。 可以利用函数和公式法，或者使用工具进行分析。

①函数和公式法

打开工作簿“实验 5.4.xls ”，利用“月度数据”工作表中的数据，分别用移动平均法（K=3和K=4）和一次指数平滑法(α=0.2，一月份预测值为286331315)计算2012年全国进出口总额的长期趋势，结果如图5.4.1所示。

图5.4.1 用EXCEL 计算长期趋势资料及结果

计算步骤：

第一步：计算三项移动平均：在C3中输入“=average （B2：B4）”，并用鼠标拖曳将公式复制到C3：C12区域。

第二步：计算四项移动平均：在D4中输入“=average （B2：B5）”，并用鼠标拖曳将公式复制到D4：D12区域。

第三步：计算二项移正平均数：在E4中输入“=average （D4：D5）”，并用公式拖曳将公式复制到E4：E11区域。

第四步：计算一次指数平滑预测数：在F2中输入286331315，在F3中输入“=0.2*B2+(1-0.2)*F2”，并用鼠标拖曳将公式复制到F4：F14区域。F14为未来下一个月的预测值。

②工具分析法

移动平平均法：单击“工具”|“数据分析”，打开数据分析对话框，如图 5.4.2(a)所示。选择“移动平均”，弹出“移动平均”对话框，在“输入区域”中输入“B2：B13”，在“间隔”输入“3”，在“输出区域”输入“C15”，如图5.4.2（b ）。单击确定就可以得到n=3的移动平均值。n=5的时候，也是同样的操作。

（a ） (b)

图5.4.2 移动平均法

指数平滑法：单击“工具”|“数据分析”，打开数据分析对话框，如图 5.4.3(a)所示。选择“指数平滑”，弹出“指数平滑”对话框，在“输入区域”中输入“B2：B13”，在“阻尼系数”输入“0.8”，在“输出区域”输入“F15”，如图5.4.3（b ）。单击确定就可以得到2.0=α的指数平滑值。

（a）（b）

图5.4.3 指数平滑法

（5）计算季节变动

季节变动是指现象受到自然因素和社会习俗等因素影响而发生的有规律的周期性变动。测定季节变动可分为两种：一是不排除长期趋势的影响，直接根据原时间序列来测定；二是依据剔除长期趋势后的时间序列来测定。前者用简单平均法，或者用移动平均趋势剔除法。

①简单平均法计算季节指数

打开工作簿“实验5.5.1.xls”，利用“季度数据”工作表中的数据，计算季节变动，结果如图5.5.1所示。

图5.5.1 用简单平均法计算季节指数结果

计算步骤：

第一步：计算出同季合计：在B9输入“=SUM(B3:B8)”，并用鼠标拖曳将公式复制到C9：E9区域。

第二步：计算出全年合计：在F3输入“=SUM(B3:E3)”，并用鼠标拖曳将公式复制到F4：F9区域。

第三步：计算出同季平均：在B10输入“=B9/6”，并用鼠标拖曳将公式复制到C10：E10区域。在F10输入“=F9/24”。

第四步：计算季节指数：在B11输入“=B10/$F$10”，并用鼠标拖曳将公式复制到C11：F11区域。

②移动平均趋势剔除法计算季节指数

打开工作簿“实验5.5.2.xls”，利用“季度数据”工作表中的数据，计算季节变动，结果如图5.5.2(a)和5.5.2(b)所示。

（a）

图5.5.2 计算剔除趋势值的结果

（b）

图5.5.2 移动平均趋势剔除法计算季节指数结果

计算步骤：

第一步：计算四项移动平均：在D3中输入“=AVERAGE(C2:C5)”，并用鼠标拖曳将公式复制到D4：D23区域。

第二步：计算移正平均T：在E4中输入“=AVERAGE(D3:D4)”，并用鼠标拖曳将公式复制到E5：E23区域。

：即计算Y/T：在F4中输入“=C4/E4”，并用鼠标拖曳将公式复制到第三步：剔除长期趋势Y

t

F5：F23区域。

第四步：重新排列F4：F23区域中的数字，使同季的数字位于一列，共排成六列，如图5.5.2（b）所示。

第五步：计算各年同季合计：在B35单元格中输入公式“=SUM(B29:B34)”，并用鼠标拖曳将公式复制到C35：E35区域。在F35中输入公式“=SUM(B35:E35)”

第六步：计算各年统计平均：在B36单元格中输入公式“=B35/5”，并用鼠标拖曳将公式复制到C36：E36区域。在F36中输入公式“=F35/20”

第七步：计算季节指数：在B37中输入公式“=B36/$F$36”，并用鼠标拖曳将公式复制到C37：F37区域。