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

1、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）测定发展水平和平均发展水平在时间上的观察值， 就是该时间点的发展水平。 Yt ii 平均发展水平是现象在时间（ i=1,2, ,n ）上各期观察值的平均数。 Yt ii 时期序列的序时平均数计算 nY i Y YY ?12 i1nYnn 打开工作簿“实验”，利用“全国 GDP”工作表中的数据，计算年度平均国内生产总值，计算结果如图所示：图用 EXCEL计算平均发展水平资料及结果计算步骤：第一步：计算观察值

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

6、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，得到平均日库存量 44 件。

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

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

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

10、 =增减量 / 基期水平 =（报告期水平 - 基期水平） / 基期水平 =发展速度 -1 。Y YY i0i1X ；环比增长速度：度基其中定增长速：iYY 00Y YY i1ii 1 X 。 iYY i 1i 1 平均发展速度是现象各个时期环比发展速度的平均数， 用于描述现象在整个： 式公算计的法平水。 度程的化变展发均平内期察观YYYYni21X?nnYYYY 00 11i 平均增长速度 =平均发展速度 -1 。打开工作簿“实验”，利用“全国 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）”按回车键，即可得到平均发展速度

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

13、“月度数据” 工作表中的数据，分别用 ( ）和一次指数平滑法和K=4 移动平均法（K=3=0.2，一月份预测值为286331315)计算 2012 年全国进出口总额的长期趋势，结果如图5.4.1 所示。图用 EXCEL计算长期趋势资料及结果计算步骤：第一步：计算三项移动平均：在 C3 中输入“ =average（B2：B4）”，并用鼠标拖曳将公式复制到 C3：C12 区域。第二步：计算四项移动平均：在 D4 中输入“ =average（B2：B5）”，并用鼠标拖曳将公式复制到 D4：D12 区域。第三步：计算二项移正平均数：在 E4 中输入“ =average（ D4：D5）”，并用公式拖曳将

14、公式复制到 E4： E11 区域。第四步：计算一次指数平滑预测数：在F2 中输入 286331315F3中输入 ，在“ =0.2*B2+(1-0.2)*F2 ”，并用鼠标拖曳将公式复制到 F4：F14 区域。 F14 为未来下一个月的预测值。工具分析法移动平平均法：单击“工具”|“数据分析”，打开数据分析对话框， 如图所示。选择“移动平均”，弹出“移动平均” 对话框，在“输入区域” 中输入“B2：B13”，在“间隔”输入“ 3”，在“输出区域”输入“ C15”，如图（ b）。单击确定就可以得到 n=3 的移动平均值。 n=5 的时候，也 是同样的操作。（ a） (b)移动平均法图如图，打开数据

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

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