实验五用EXCEL进行时间序列讲解.docx

1、实验五用EXCEL进行时间序列讲解 EXCEL进行时间序列分析实验五 用 一、实验目的 进行时间序列分析? 利用Excel 二、实验内容 测定发展水平和平均发展水平1 测定增长量和平均增长量2. 测定发展速度、增长速度和平均发展速度3. 计算长期趋势4. 计算季节变动5. 三、实验指导 时间序列分析常用的方法有两种：指标分析法和构成因素分析法。平均发展包含发展水平、指标分析法，通过计算一系列时间序列分析指标，水平、增长量、平均增长量、发展速度、平均发展速度等来揭示现象的发展状况 和发展变化程度。构成因素分析法，是将时间序列看做由长期趋势、季节变动、循环变动、不规则变动四种因素构成，将各影响因素

2、分别从时间序列中分离出去并加以测定、 对未来发展做出预测的过程。 发展水平： 发展水平是指某一经济现象在各个时期达到的实际水平。 各指标数值就是该指标所反映的社会经济现象在所属时间的发展在时间序列中，,n)(t=0,1,2,3,水平。在时间序列中，我们用y表示指标值，t表示时间，则yt 表示各个时期的指标值。，是时间”、“动态平均数 平均发展水平：平均发展水平又称“序时平均数 序列中各项发展水平的平均数，反映现象在一段时期中发展的一般水平。增长量是指某一经济现象在一定时期增长或减少的绝对量。它是 增长量： 报告期发展水平减基期发展水平之差。它表明平均增长量是时间序列中的逐期增长量的序时平均数，

3、 平均增长量： 现象在一定时段内平均每期增加（减少）的数量。公式表示如下： 累计增长量逐期增长量之和=平均增长量= 逐期增长量个数时间序列项数-1 发展速度：发展速度是说明事物发展快慢程度的动态相对数。它等于报告期水平对基期水平之比。发展速度有两种：分为环比发展速度和定基发展速度。 1环比发展速度：也称逐期发展速度，是报告期发展水平与前一期发展水平之比。 2定基发展速度：是报告期水平与固定基期水平之比。 平均发展速度：平均发展速度是动态数列中各期环比发展速度或各期定基发展速度中的环比发展速度的序时平均数。它说明在一定时期内发展速度的一般水 平。 平均发展速度的计算方法有几何法和方程法。 1几何

4、法计算平均发展速度：实际动态数列各期环比发展速度连乘积等于理论动态数列中各期平均发展速度的连乘积 2方程法计算平均发展速度：方程法平均发展速度的特点是实际动态数列各项之和等于理论动态数列各项之和，所以称为“累积法” （1）测定发展水平和平均发展水平 在时间上的观察值，就是该时间点的发展水平。 Ytii平均发展水平是现象在时间（i=1,2,n）上各期观察值的平均数。 Ytii时期序列的序时平均数计算 n?YiY?Y?Y? 12?i1n?Y? nn打开工作簿“实验5.1.xls”，利用“全国GDP”工作表中的数据，计算年度平均国内生产总值，计算结果如图5.1.1所示： 图5.1.1 用EXCEL计

5、算平均发展水平资料及结果 计算步骤： 第一步：计算观察值个数n：在B14中输入公式“=count(A2:A13)”,得到观察值个数为12。 第二步：计算总国内生产总值：在B15中输入公式“=sum（A2:A13)”，得到 。2821530.71总生产总值为 第三步：计算平均国内生产总值：在B16中输入公式“=B15/B14”，得到平均国内生产总值。 时点序列的序时平均数计算 ?YT 连续时点序列序时平均数： ?Y ?T打开工作簿“实验5.1.xls”，利用“库存量”工作表中的数据，计算12月份平均日库存量，计算结果如图5.1.2所示： 图5.1.2 用EXCEL计算平均发展水平资料及结果 计算

6、步骤： ?T：B4、C4、D4、E4第一步：计算、F4中输入各日期天数T，在G4中输入公式“=SUM(B4:F4)”，得到值为31天。 ?YT：在B5中输入公式“=B3*B4”, 得到日期第二步：计算14的值240件，C5中输入公式“=C3*C4”，得到日期510的值378件，D5中输入公式“=D3*D4”，得到日期1120的值500件，E5中输入公式“=E3*E4”，得到日期2126的值270件，F5中输入公式“=F3*F4”，得到日期2731的值200件。在G5中输入公式“=SUM(B5:F5)” 可得值1588。 第三步：计算平均日库存量：在B7中输入公式“=G5/G4，得到平均日库存量

7、44件。 间断时点序列序时平均数： Y?YY?YY?Y23n?n121?T?TT? n2?11222 Y? n?1?Tii?1打开工作簿“实验5.1.xls”，利用“存款余额”工作表中的数据，计算平 所示：5.1.3均存款余额，计算结果如图 图5.1.3 用EXCEL计算平均发展水平资料及结果 计算步骤： 第一步：计算两个时间点的平均观察值：在C5中输入公式“=average(B3:C3)”, 并用鼠标拖曳将公式复制到D5：H5区域。 第二步：算出间隔天数：t1、t2、t3、t4、t5。 Y?Ynn?1?T值：在第三步：计算C9中输入公式“=C5*C7”，并用鼠标拖曳 1n?2将公式复制到D9

8、：H9区域。 第四步：计算总天数：在B10输入公式：“=SUM(C7:H7)”，得到总天数365 天。 第五步：计算平均存款额：在B11输入公式：“=SUM(C9:H9)/SUM(C7:H7)”，得到平均存款额为120.03百万元。 （2）测定增长量和平均增长量 ?Y?Y?Y；累增长量是报告期水平与基期水平之差，其中逐期增长量：1i?i?Y?Y?Y。积增长量： 0i打开工作簿“实验5.2.xls”，利用“全国GDP”工作表中的数据，计算逐期 所示：5.2增长量、累积增长量和平均增长量，计算结果如图 图5.2 用EXCEL计算增长量和平均增长量资料及结果 计算步骤： 第一步：计算逐期增长量：在C

9、3中输入公式“=B3-B2”，并用鼠标拖曳将公式复制到C4：C13区域。 第二步：计算累计增长量：在D3中输入公式“=B3-$B$2”，并用鼠标拖曳公式复制到D4：D13区域。 第三步：计算平均增长量（水平法）：在B15中输入公式“=D13/11”，按回车键，即可得到平均增长量。 （3）测定发展速度、增长速度和平均发展速度 发展速度是同一现象在两个不同时期发展水平对比的结果，用于描述现象在观察期内的相对发展变化程度。发展速度=报告期水平/基期水平*100%。 YYii ；环比发展速度：其中定基发展速度：?XX? iiYYi?10增长速度又称增长率，是增长量和基期水平之比。增长速度=增减量/基期

10、水平=（报告期水平-基期水平）/基期水平=发展速度-1。 Y?YYi0i?1X?；环比增长速度：度基其中定增长速： iYY00Y?YYi1ii?1?X。 iYYi?1i?1平均发展速度是现象各个时期环比发展速度的平均数，用于描述现象在整个：式公算计的法平水。度程的化变展发均平内期察观 YYYY ni21X? ?nnYYYY00?11i平均增长速度=平均发展速度-1。 打开工作簿“实验5.3.xls”，利用“全国GDP”工作表中的数据，计算定基发展速度、环比发展速度和平均发展速度，计算结果如图5.3所示： 图5.3 用EXCEL计算发展速度、增长速度和平均发展速度资料及结果 计算步骤： 第一步：

11、计算定基发展速度：在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

12、-1”，按回车键，即可得到平均增长速度。 （4）计算长期趋势 趋势分析方法有许多，这里介绍移动平均法和指数平滑法，主要通过对时间序列进行平滑以消除其随机波动。 移动平均法，是通过对时间序列逐期递移求得平均数作为趋势值或预测值的：成写列序均平动移则，K为度长隔间动移设。法滑平种一 Y?Y?Y? i?i?1ki?1?Y。 iK指数平滑法，是用过去的观察值的加权平均数作为趋势值，它是加权平均的一种特殊形? Y)(Y?1?Y式。t?1tt可以利用函数和公式法，或者使用工具进行分析。 函数和公式法打开工作簿“实验5.4.xls”，利用“月度数据”工作表中的数据，分别用?(）和一次指数平滑法和K=4移动平

13、均法（K=3=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中输入28633131

14、5F3中输入，在“=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)如图，打开数据分析对话框，单击“工具”|“数据分析”指数平滑法：，弹出“指数平滑”对话

15、框，在“输入区域”中输所示。选择“指数平滑”，”，在“输出区域”输入“F15”，在“阻尼系数”输入“入“B2：B130.8? 。单击确定就可以得到（b）的指数平滑值。5.4.3如图2.?0 （a） （b） 5.4.3 指数平滑法图 5）计算季节变动（季节变动是指现象受到自然因素和社会习俗等因素影响而发生的有规律的周期性变动。测定季节变动可分为两种：一是不排除长期趋势的影响，直接根据原时间序列来测定；二是依据剔除长期趋势后的时间序列来测定。前者用简单平均法，或者用移动平均趋势剔除法。 简单平均法计算季节指数打开工作簿“实验5.5.1.xls”，利用“季度数据”工作表中的数据，计算季节变动，结果如

16、图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”，利用“季度数

17、据”工作表中的数据，计算 所示。5.5.2(b)和5.5.2(a)季节变动，结果如图 （a） 图5.5.2 计算剔除趋势值的结果 （b） 图5.5.2 移动平均趋势剔除法计算季节指数结果 计算步骤： 第一步：计算四项移动平均：在D3中输入“=AVERAGE(C2:C5)”，并用鼠标拖曳将公式复制到D4：D23区域。 第二步：计算移正平均T：在E4中输入“=AVERAGE(D3:D4)”，并用鼠标拖曳将公式复制到E5：E23区域。 第三步：剔除长期趋势Y，并用鼠标”=C4/E4中输入“F4：在Y/T：即计算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区域。