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

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

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

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

4、各期平均发展速度的连乘积2 方程法计算平均发展速度：方程法平均发展速度的特点是实际动态数列各项 之和等于理论动态数列各项之和，所以称为 累积法”(1) 测定发展水平和平均发展水平在时间t上的观察值Y，就是该时间点的发展水平平均发展水平是现象在时间ti (i=1,2,n )上各期观察值Yi的平均数。n 丫i An 时期序列的序时平均数计算丫1 丫2YnY =n打开工作簿“实验5.1.xls ”，利用“全国GDP工作表中的数据，计算年度 平均国内生产总值，计算结果如图 5.1.1所示：AB1年份国内生产总值亿元22000992145532001109655.172002120332.6952003

5、135822766200415987B.34720051S4937.3782006216314.43g2007265810.31102008314045.43112009340902.811220104015180134731040514n1215总和2821530.7116年度平均国内生产总值235127.56图5.1.1 用EXCELS算平均发展水平资料及结果计算步骤：第一步：计算观察值个数 n：在B14中输入公式“ =count(A2:A13) ” ,得到 观察值个数为12。第二步：计算总国内生产总值：在 B15中输入公式“ =sum( A2:A13)”，得到 总生产总值为2821530

6、.71。第三步：计算平均国内生产总值：在 B16中输入公式“ =B15/B14”，得到平 均国内生产总值。 时点序列的序时平均数计算 瓦YT连续时点序列序时平均数：Y二Z T打开工作簿“实验5.1.xls ”，利用“库存量”工作表中的数据，计算 12月 份平均日库存量，计算结果如图5.1.2所示：ABCDEFG1?!商品12月偿i库存量2日期广45101C20212627313库存量(件)60535045404T461065315Y*T240378500270200158267平均日库存量51. 22581图5.1.2 用EXCELS算平均发展水平资料及结果计算步骤：第一步：计算a T : B

7、4 C4 D4 E4 F4中输入各日期天数T,在G4中输 入公式“ =SUM(B4:F4”，得到值为31天。第二步：计算a 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,得到平均日库存 量44件。 间

8、断时点序列序时平均数:T1丁2 Yn YnTn 4打开工作簿“实验5.1.xls ”，利用“存款余额”工作表中的数据，计算平 均存款余额，计算结果如图5.1.3所示：ACDBfGH12上年12月末2 月 2864月於日&月 30 HE 月 31S10月址日L2月刊日3存款亲栢(百万元:80941451321171261304(H+T2J/2(T&-TS/2(T3+T)/2(T4+T5J/2(Y5t6)/2(Te+YT)/2587119.5133.5124.5121.51286tl12t3tfltst.6T596161626161S(YS+)/2*t3(Y4+Y5)/2*t4(YY6)/2*t5

9、(Y6+Y7)/2*t$951337289,53448.577137411.5730810总天教36511平均存款命血120L 03图5.1.3 用EXCE计算平均发展水平资料及结果计算步骤：第一步：计算两个时间点的平均观察值：在C5中输入公式“ =average(B3:C3) ” ,并用鼠标拖曳将公式复制到 D5: H5区域。 第二步：算出间隔天数：t1、t2、t3、t4、t5。第三步：计算丫心+斗迖值：在C9中输入公式“ =C5*C7,并用鼠标拖曳2将公式复制到D9: H9区域。第四步：计算总天数：在 B10输入公式：“ =SUM(C7:H7”，得至U总天数365 天。第五步：计算平均存款

10、额：在 B11输入公式：“ =SUM(C9:H9)/SUM(C7:H7), 得到平均存款额为120.03百万元。(2) 测定增长量和平均增长量增长量是报告期水平与基期水平之差，其中逐期增长量：厶丫二Y -丫匸；累积增长量：Y =Yi -Y0。打开工作簿“实验5.2.xls ”，利用“全国GDP工作表中的数据，计算逐期 增长量、累积增长量和平均增长量，计算结果如图5.2所示：ABCD1年份園内生产总值亿元逐期増长量累积増氏虽22000992145532001109655.1710440. 6210440.6242002120332.6910677. 5221118.1452003135822.7

11、615490. 0736608. 21620041598783424055. 5860663. 7972005184937.3725059. 0385722. 8282006216314.4331377.06117099.88920072558103149495. 88166595.76102008314045 4348235.12214830.88112009340902.8126857. 38241688.26122010401512.3(60609. 9930229&25132011473104.0571591. 25373889, 5014逐期増长量之和373889. 5015逐期增长

12、量个数11.0016| 平均増长量33989. 95455图5.2 用EXCEL#算增长量和平均增长量资料及结果计算步骤：第一步：计算逐期增长量：在C3中输入公式“ =B3-B2”并用鼠标拖曳将公 式复制到C4: C13区域。第二步：计算累计增长量：在D3中输入公式“ =B3-$B$2”并用鼠标拖曳公 式复制到D4: D13区域。第三步：计算平均增长量（水平法）：在B15中输入公式“ =D13/11”按回 车键，即可得到平均增长量。（3）测定发展速度、增长速度和平均发展速度发展速度是同一现象在两个不同时期发展水平对比的结果，用于描述现象在观察期内的相对发展变化程度。发展速度 =报告期水平/基期

13、水平*100%YY其中定基发展速度：Xi二丄；环比发展速度：Xi二一匚YoY）增长速度又称增长率，是增长量和基期水平之比。增长速度=增减量/基期水 平=（报告期水平-基期水平）/基期水平=发展速度-1。其中定基增长速度：环比增长速度：Y：平均发展速度是现象各个时期环比发展速度的平均数，用于描述现象在整个 观察期内平均发展变化的程度。水平法的计算公式：XJ 丫2Y J丫0第Y平均增长速度=平均发展速度-1。打开工作簿“实验5.3.xls ”利用“全国GDP工作表中的数据，计算定基 发展速度、环比发展速度和平均发展速度，计算结果如图5.3所示：图5.3 用EXCE计算发展速度、增长速度和平均发展速

14、度资料及结果 计算步骤：第一步：计算定基发展速度： 将公式复制到C4: C13区域。第二步：计算环比发展速度： 公式复制到D4: D13区域。第三步：计算定基增长速度： 公式复制到D4: D13区域。第四步：计算环比增长速度： 公式复制到D4: D13区域。第五步：计算平均发展速度D13”C3中输入公式“ =B3/$B$2”并用鼠标拖曳D3中输入公式“ =B3/B2”并用鼠标拖曳将E3中输入公式“ =C3-1”并用鼠标拖曳将F3中输入公式“ =D3-1”并用鼠标拖曳将（水平法）：在B15中输入公式 按回车键，即可得到平均发展速度。第六步：计算平均增长速度（水平法）：在B16中输入公式 车键，即

15、可得到平均增长速度。（4）计算长期趋势趋势分析方法有许多，这里介绍移动平均法和指数平滑法， 序列进行平滑以消除其随机波动。移动平均法，是通过对时间序列逐期递移求得平均数作为趋势值或预测值的 一种平滑法。设移动间隔长度为K，则移动平均序列写成：“=GE0ME(ND3:“ =B15-1” 按回主要通过对时间ABCDEF1年份国内生产总值i亿元定基发展速度玳喷展速度定基増长谏度环比増怪速度2200099214.5532001109655.171,1052327511.1052327510.1052330.10523342002120332 691, 2128532561.0973736120. 21

16、28530. 09737452003135822.761, 3&S9B02551.1287270320. 363980.12872762004159B76341, 6114404591.177110081( 611440,4937.371, 864014&031.1567381176 8640150.15&73382006216314.432* 1802692251.169663168k1802690.169663g2007255B10 312* 6791464561. 228814509L 6791460. 228815102006314045.433.165316

17、2771.1814644442.1653160.181464112003340902.913.436016291.0855206842. 4360160. 085521122010401512.904.046914491.177792583. 0469140.177793132011473104.054* 7684946411.178303783. 7684950.1783041415平均抜展速度1均増长速度0.152579862Yi* Y k指数平滑法，是用过去的观察值的加权平均数作为趋势值，它是加权平均的一种特殊形式。: Y （1 - ： ）Y?。可以利用函数和公

18、式法，或者使用工具进行分析。函数和公式法打开工作簿“实验5.4.xls ”，利用“月度数据”工作表中的数据，分别用 移动平均法（K=3和K=4）和一次指数平滑法（： =0.2, 一月份预测值为 286331315）计算2012年全国进出口总额的长期趋势，结果如图5.4.1所示ABCDEr1月份进出口总值千美元三项移动平均四项移动平均d移正平均a=0, 2212726D033S2863313153225042470628633131523353512043325968900r 298156614F 29176754530064027827895303754306076235F 325S75779

19、3095130113180457772883F621065313562201r 32678175Sr 32657E5433269240952923002157632368836r 333667449F 32726964632992087230255661287320733311F 328902063尸 33257209833275307130T7326S79日329285043F 334349781F 332934045331735130311972788109345029990r 331137184F 330536216331835584315435439111031&095513F 334

20、417932尸 3331349533378293133213543491211339123259341688235r 342523674320902583131236634092732454771814333006360图5.4.1 用EXCEL计算长期趋势资料及结果计算步骤：第一步：计算三项移动平均：在 C3中输入“ =average（ B2： B4）”，并用鼠 标拖曳将公式复制到C3: C12区域。第二步：计算四项移动平均：在 D4中输入“ =average（B2： B5）”，并用鼠 标拖曳将公式复制到D4: D12区域。第三步：计算二项移正平均数：在 E4中输入“ =average（ D

21、4： 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的时候，也 是同样的操作。移动

22、平均数据分析帮助01)输入输入区域:标志位于第1行)间隔：输出选项输出区域辺：JCS1E辛GT住弄理日fP 1:图恚输岀)0标堆馍差(a)(b)图542 移动平均法指数平滑法：单击“工具” “数据分析”，打开数据分析对话框，如图5.4.3(a) 所示。选择“指数平滑”，弹出“指数平滑”对话框，在“输入区域”中输 入“B2： B13,在“阻尼系数”输入“ 0.8 ”，在“输出区域”输入“ F15”, 如图543 (b)。单击确定就可以得到g =0.2的指数平滑值。wmx亠尺方方方单可无S92S- 方疇 f_yf srawiF-偉頁編入区域 明尼亲数:标志Q$班2:$时归0.2菽工作薄舞图543

23、指数平滑法(5)计算季节变动季节变动是指现象受到自然因素和社会习俗等因素影响而发生的有规律的 周期性变动。测定季节变动可分为两种：一是不排除长期趋势的影响，直接根据 原时间序列来测定；二是依据剔除长期趋势后的时间序列来测定。前者用简单平均法，或者用移动平均趋势剔除法。简单平均法计算季节指数打开工作簿“实验5.5.1.xls”利用“季度数据”工作表中的数据，计算季节变动，结果如图5.5.1所示。ABCDET 一1国內生产总值季度数据2年季1234全年合计3200S9019*65487419466284314045. 442009109599831007838769817r340902. 8520

24、10128886974892265S2613r401512. 86201115059115S510900997480F473104.1720121657291253S11953110S42r519470.1820131ST451390%129162118862568845. 29同季合计r833737. 7r638066. 8r602548. 2r543527. 7261788010同季平均138956.3106344. 5100424. 790587. 95109078. 411季节指数127. 39%97. 49%92. 07%83. 05%100. 00%图5.5.1用简单平均法计算季节

25、指数结果计算步骤：第一步：计算出同季合计：在 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

26、(a)和5.5.2(b)所示。ABCDEF1年/季时间序号t总产值Y四项移动平均移正平均(T)剔除趋势值(yt)=T/r22008年1月1970193227654378511. 3643374194尸81656.4180083. 880.926554466284尸83294.2682475. 330. 8037 162009年1月5109599F84342.4283818. 341.3076 72683100尸85225.7084784. 060,9801 183778387尸90047.3537636. 530,8945 g4869817F93709.3991878.370. 7599 10

27、201。年1月9128886F97179.0895444. 241.3504 11121097748100378+2098778. 640,9896 11231192265105846+53103112. 370.S948 113g12S2613110373.69108110.110. 7642 1142011年1月13150759114559,4811246& 581.3405 1521411585?r 118276,01116417. 740. 9952 116316109009r 122018.31120147.160. 9073 11741697480124488.73123253. 540. 7909 182012年1月17165729r 127119.42125804.101.3174 J19213125738129867.53128493. 470.9786 20319119531133871.63131869. 580. 9064 2142010847