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

实验五用EXCEL进行时间序列分析一、实验目的利用Excel进行时间序列分析二、实验内容1. 测定发展水平和平均发展水平2. 测定增长量和平均增长量3. 测定发展速度、增长速度和平均发展速度4. 计算长期趋势5. 计算季节变动三、实验指导时间序列分析常用的方法有两种：指标分析法和构成因素分析法。指标分析法，通过计算一系列时间序列分析指标，包含发展水平、平均发展 水平、增长量、平均增长量、发展速度、平均发展速度等来揭示现象的发展状况 和发展变化程度。构成因素分析法，是将时间序列看做由长期趋势、季节变动、循环变动、不 规则变动四种因素构成，将各影响因素分别从时间序列中分离出去并加以测定、 对未来发展做出预测的过程。发展水平：发展水平是指某一经济现象在各个时期达到的实际水平。在时间序列中，各指标数值就是该指标所反映的社会经济现象在所属时间的发展 水平。在时间序列中，我们用y表示指标值，t表示时间，则七(t=0,1,2,3,.,n) 表示各个时期的指标值。平均发展水平：平均发展水平又称“序时平均数”、“动态平均数”，是时间序 列中各项发展水平的平均数，反映现象在一段时期中发展的一般水平。增长量：增长量是指某一经济现象在一定时期增长或减少的绝对量。它是报告 期发展水平减基期发展水平之差。平均增长量：平均增长量是时间序列中的逐期增长量的序时平均数，它表明 现象在一定时段内平均每期增加(减少)的数量。公式表示如下：平均增长量=逐期增长量之和=累计增长量 逐期增长量个数一时间序列项数-1 发展速度：发展速度是说明事物发展快慢程度的动态相对数。它等于报告期水平 对基期水平之比。发展速度有两种：分为环比发展速度和定基发展速度。1.环 比发展速度：也称逐期发展速度，是报告期发展水平与前一期发展水平之比。2.定基发展速度：是报告期水平与固定基期水平之比。平均发展速度：平均发展速度是动态数列中各期环比发展速度或各期定基发展速 度中的环比发展速度的序时平均数。它说明在一定时期内发展速度的一般水平。 平均发展速度的计算方法有几何法和方程法。1. 几何法计算平均发展速度：实际动态数列各期环比发展速度连乘积等于理论 动态数列中各期平均发展速度的连乘积2. 方程法计算平均发展速度：方程法平均发展速度的特点是实际动态数列各项 之和等于理论动态数列各项之和，所以称为“累积法”(1) 测定发展水平和平均发展水平在时间匕上的观察值匕，就是该时间点的发展水平。平均发展水平是现象在时间t (i=1,2,n)上各期观察值7的平均数。ii 时期序列的序时平均数计算77 _ 7 + 7 + 7. _ T inn打开工作簿“实验5.1.xls”，利用“全国GDP”工作表中的数据，计算年度 平均国内生产总值，计算结果如图5.1.1所示：计算步骤：第一步：计算观察值个数n：在B14中输入公式“二count(A2:A13)”，得到 观察值个数为12。第二步：计算总国内生产总值：在B15中输入公式“二sum(A2:A13)”，得到 总生产总值为2821530.71。第三步：计算平均国内生产总值：在B16中输入公式“二B15/B14”，得到平均国内生产总值。 时点序列的序时平均数计算 YT连续时点序列序时平均数：y = r图 5.1.2计算步骤：第一步：计算T :打开工作簿“实验5.1.X1S”，利用“库存量”工作表中的数据，计算12月 份平均日库存量，计算结果如图5.1.2所示：ACDEG1甲一新品12月怛诛存量2日期广451011项武W627313库存量(件)606350我404T4610653152403785QQ2702001538平均日库存量51.22531用EXCEL计算平均发展水平资料和结果B4、C4、D4、E4、F4中输入各日期天数T，在G4中输入公式 =SUM(B4:F4) ”，第二步：计算 YT得到值为31天。:在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，得到平均日库存第三步：计算平均日库存量：量44件。Y + Y+ - n 一1n X T2n-1 间断时点序列序时平均数:工x T +工x T +Y =2122笑1Ti i=1打开工作簿“实验5.1.xls”，利用“存款余额”工作表中的数据，计算平 均存款余额，计算结果如图5.1.3所示：图5.1.3用EXCEL计算平均发展水平资料和结果计算步骤：第一步：计算两个时间点的平均观察值：在C5中输入公式“二average(B3:C3)”，并用鼠标拖曳将公式复制到D5： H5区域。第二步：算出间隔天数：t1、t2、t3、t4、t5。第三步：计算Ym + Yn xT值：在C9中输入公式“二C5*C7”，并用鼠标拖曳2n-1将公式复制到D9： H9区域。第四步：计算总天数：在B10输入公式:“二SUM(C7:H7)”，得到总天数365 天。第五步：计算平均存款额：在B11输入公式:“二SUM(C9:H9)/SUM(C7:H7)”， 得到平均存款额为120.03百万元。(2) 测定增长量和平均增长量增长量是报告期水平与基期水平之差，其中逐期增长量：ay = Y. -r 1 ；累积增长量：AY = Y -Y0。打开工作簿“实验5.2.xls”，利用“全国GDP”工作表中的数据，计算逐期 增长量、累积增长量和平均增长量，计算结果如图5.2所示：ABCD1年份I国内生产总值亿元逐期增长星集积增长呈I22000992-14.5532001109655.1710440.6210440.6242002120332.6910677.5221118.14S200315490.0736608.2162004139878.3424055. 5E60663. 7972005184937.3725059. 0385722. 82820062:16314.4331377. 06117099. 88920072:65810.3149495. EE166595. 76102008314045.4348235.12214830. 881120093409Q2.8126857.38241688. 261220104-01E12.8060609.9930229S. 2513201143104.0571591.25373889.5014逐期增长量之和373889.5015逐期增长量个数11.0016平均增长星33989.954E5图5.2用EXCEL计算增长量和平均增长量资料和结果计算步骤：第一步：计算逐期增长量：在C3中输入公式“二B3-B2”，并用鼠标拖曳将公 式复制到C4： C13区域。第二步：计算累计增长量：在D3中输入公式“二B3-$B$2”，并用鼠标拖曳公 式复制到D4： D13区域。第三步：计算平均增长量（水平法）：在B15中输入公式“二D13/11”，按回 车键，即可得到平均增长量。（3）测定发展速度、增长速度和平均发展速度发展速度是同一现象在两个不同时期发展水平对比的结果，用于描述现象在 观察期内的相对发展变化程度。发展速度二报告期水平/基期水平*100%。其中定基发展速度：X = 土 ；环比发展速度：X =匕i Y0Y 1增长速度又称增长率，是增长量和基期水平之比。增长速度二增减量/基期水 平二（报告期水平-基期水平）/基期水平=发展速度-1。其中定基增长速度：X =上匕=匕-1 ；环比增长速度：i Y YX = i-1 = i 1。i11平均发展速度是现象各个时期环比发展速度的平均数，用于描述现象在整个 观察期内平均发展变化的程度。水平法的计算公式：n nzm：=:匕Yq Y1Y 1 冒平均增长速度=平均发展速度-1。打开工作簿“实验5.3.X1S”，利用“全国GDP”工作表中的数据，计算定基 发展速度、环比发展速度和平均发展速度，计算结果如图5.3所示：ABCDEF1年份国内生产总值亿元；定基发展速度环比发展速度定基增长速度环比增长速度2200099214.5532001109055.171.1052327511.1052327510. 1)52330.10523342002120332.691.2128532561. 0973736120.2128530.09737452003135&22.761.3689802551.1287270320. 368980.12872762004159870.341.6114404591.1771100810. 511440. 1771172005184937.371.8640146031.1567381170.8640150.15673882006216314.432.1802692251.1696631681.1302690.1696639200?265010.312.6791464561. 2288145091.6791460.2288151U2000314045.433.1653162771.114644442.1653160.181464112009340902.013.436016291.0855205842.4360160.085521122010401512.304.046914491.177792583.0469140.1777931320114731D4.054.76S4946411.1783037S3.76849B0.17S3041415平均发展速度1.15257986216平均增长速度0.152579862图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，则移动平均序列写成：y =匕 +1 + + 。iK指数平滑法，是用过去的观察值的加权平均数作为趋势值，它是加权平均的一种特殊形式。y =aY + （1-a）Y。 t+1tt可以利用函数和公式法，或者使用工具进行分析。函数和公式法打开工作簿“实验5.4.xls”，利用“月度数据”工作表中的数据，分别用 移动平均法（K=3和K=4）和一次指数平滑法（a =0.2，一月份预测值为286331315） 计算2012年全国进出口总额的长期趋势，结果如图5.4.1所示。ABCDEF1月原进出口总千美元：三项移动平均四项移动平坷二项移正平埼a=0. 2212726D0339236331315226C4247D62835B512023259569DQ漩 1E6M2917675530CG4027327895303743060762363258757：3i)9El?011318045777238356210&53435B22D1326924095763280BG83G333成74茹3272G3G4G32992087230255661287328733311323iC2)GE332C720：G332753071307782657983292MO4333434沉3133 案 34CKS33173513031197278810934CO2993Q33110713433死3花16331835584315435439111031909551833441732233313495333782931332135434912r3391282593416c323F3425236743209D25831312365840927324547718143:30X.c63图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的时候，也 是同样的操作。分析工且恁)方差纽析：可重复双因素幼析 方差分析；无重复双因素分析 相关系数 协方差 描述统汁 指数平滑F-检验戏样本方差 傅利叶始祈 直方囹 确定 成消 帮助移动敖据分折朝平甘散括分析眼消)帮助Qj) I移动平均输入输入区域(X):标志位于第一行(L)间隔理)：输出选项输出区域但)：$c$is函新工作表组(F)： 新工作茂做)图表输出0标准误差(a)(b)图5.4.2移动平均法指数平滑法:单击“工具”|“数据分析”，打开数据分析对话框，如图5.4.3(a) 所示。选择“指数平滑”，弹出“指数平滑”对话框，在“输入区域”中输 入“B2： B13”，在“阻尼系数”输入“0.8”，在“输出区域”输入“F15”， 如图5.4.3(b)。单击确定就可以得到a =0.2的指数平滑值。辱.隹设芸蔺定I帮助娘|输 Jg(DE.己五或.5标志I：输出本输出区域:计上作去出LFJ：I新工假E囹表输出(b)(a)图5.4.3指数平滑法(5) 计算季节变动季节变动是指现象受到自然因素和社会习俗等因素影响而发生的有规律的 周期性变动。测定季节变动可分为两种：一是不排除长期趋势的影响，直接根据 原时间序列来测定；二是依据剔除长期趋势后的时间序列来测定。前者用简单平 均法，或者用移动平均趋势剔除法。简单平均法计算季节指数打开工作簿“实验5.5.1.xls”，利用“季度数据”工作表中的数据，计算 季节变动，结果如图5.5. 1所示。ABCDEF11内生产总值季度数据2年季1234全年合计397019765487419466284314045.442009109599831007S3S769817340902.8520101255569厂焚9226582613401512. S6201115075911585710900997480473104.17201216572912573E11953110S472519470.182DBL81745139CT6129162115562568S45.29同季合计33737. 7r638066.82543羽7. 72617SS010同季平均138956.3106344.5100424. 790587. 95109078.411季节指数127. 3溯97.49%92. m83. 05%100. 00%图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)所示。ABCDEF1年,季时间序号t总产值T四项移动平均 移正平均(T)剔除趋势值(Yt)=M2颁8年1月1970193227654878511.364337419481656. 4180083.880.92655446628483294.2682475.330.803762009目月510959984342.4283818.341.30767268310085225.7084784.060. 93018377838790047.3587636.530. 89459486981793709.3991878.370. 7599102010只月912888697179.0895444.241.35041121097748100378.2098778.640. 98961231192265105846.53103112.370. 89481341282613 110373. 69108110.110.7642142011目月13150759114559.48112466.581. 340515214115857118276.01116417.740.995216315109009122018. 31120147.160.90731741697480r 124488.78123253.540.790915浏芨F1月17165729r 127119.42125504.101.317419218125738129867.53128493.470. 978620319119531133871.63131869.580. 906421420108472r 137205.96135538.800.800322第13主目月21181745139613.78138409.871. 313123222139076r 142211. 30140912.540. 9S702432312916225424118862(a)图5.5.2计算剔除趋势值的结果年库2008120.92650.803720091.30760.98D10.89450.759920101.35040.9896庭9480.76422011L34O50.99520.90730.790920121.31740.97860_9064O.S0032013同季合计1.31316. 62890.98704. 93044. 52943. 913920. 0076 1同季平均1.325S0. 9S610. 90590. 7S3S1.0004季节指数132.53%98. 57%90. 55%78.湖100.00%(b)图5.5.2移动平均趋势剔除法计算季节指数结果计算步骤：第一步：计算四项移动平均：在D3中输入“二AVERAGE(C2:C5)”，并用鼠标 拖曳将公式复制到D4： D23区域。第二步：计算移正平均T：在E4中输入“二AVERAGE(D3:D4)”，并用鼠标拖 曳将公式复制到E5： E23区域。第三步：易。除长期趋势Yt：即计算Y/T:在F4中输入“二C4/E4”，并用鼠标 拖曳将公式复制到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区域。