实验五-用EXCEL进行时间序列分析教案资料.docx

《实验五-用EXCEL进行时间序列分析教案资料.docx》由会员分享，可在线阅读，更多相关《实验五-用EXCEL进行时间序列分析教案资料.docx（11页珍藏版）》请在三一文库上搜索。

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

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

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

4、发展速度：方程法平均发展速度的特点是实际动态数列各项之和等于理论动态数列各项之和，所以称为“累积法”（1）测定发展水平和平均发展水平在时间t 上的观察值Y ，就是该时间点的发展水平。ii平均发展水平是现象在时间t （i=1,2,n）上各期观察值Y 的平均数。ii时期序列的序时平均数计算nYY + Y +L + YiY =i=112nnn打开工作簿“实验 5.1.xls”，利用“全国 GDP”工作表中的数据，计算年度平均国内生产总值，计算结果如图 5.1.1 所示：图 5.1.1 用 EXCEL 计算平均发展水平资料及结果计算步骤：第一步：计算观察值个数 n：在 B14 中输入公式“=count

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

6、输入公式“=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、序时平均数：Y + YY + YY + YT +T +L +T1223n-1n222n-112Y =n-1Tii=1打开工作簿“实验 5.1.xls”，利用“存款余额”工作表中的数据，计算平均存款余额，计算结果如图 5.1.3 所示： 图 5.1.3 用 EXCEL 计算平均发展水平资料及结果计算步骤：第 一 步 ： 计 算 两 个 时 间 点 的 平 均 观 察 值 ： 在 C5 中 输 入 公 式“=average(B3:C3)”, 并用鼠标拖曳将公式复制到 D5：H5 区域。第二步：算出间隔天数：t1、t2、t3、t4、t5。Y +Y第三步：计算T 值：在 C9 中输入公式“=C5*C7

8、”，并用鼠标拖曳n-1n2n-1将公式复制到 D9：H9 区域。第四步：计算总天数：在 B10 输入公式：“= SUM(C7:H7)”，得到总天数 365天。第五步：计算平均存款额：在 B11 输入公式：“=SUM(C9:H9)/SUM(C7:H7)”，得到平均存款额为 120.03 百万元。（2）测定增长量和平均增长量增长量是报告期水平与基期水平之差，其中逐期增长量：DY = Y - Y ；累ii-1积增长量： DY = Y - Y 。i0打开工作簿“实验 5.2.xls”，利用“全国 GDP”工作表中的数据，计算逐期增长量、累积增长量和平均增长量，计算结果如图 5.2 所示： 图 5.2

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

10、 =XXiiYYii0i-1增长速度又称增长率，是增长量和基期水平之比。增长速度=增减量/基期水平=（报告期水平-基期水平）/基期水平=发展速度-1。Y -YY其 中 定 基 增 长 速 度 ：= - ； 环 比 增 长 速 度 ：X1i0iYYi00Y -YYX =-1。ii-1iYYii-1i-1平均发展速度是现象各个时期环比发展速度的平均数，用于描述现象在整个观 察 期 内 平 均 发 展 变 化 的 程 度 。 水 平 法 的 计 算 公 式 ： Y Y L Y YYYnYX =n12inY01i-10平均增长速度=平均发展速度-1。打开工作簿“实验 5.3.xls”，利用“全国 GD

11、P”工作表中的数据，计算定基发展速度、环比发展速度和平均发展速度，计算结果如图 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：

12、D13 区域。第五步：计算平均发展速度（水平法）：在 B15 中输入公式“= GEOMEAN（D3：D13）” 按回车键，即可得到平均发展速度。第六步：计算平均增长速度（水平法）：在 B16 中输入公式“=B15-1”，按回车键，即可得到平均增长速度。（4）计算长期趋势趋势分析方法有许多，这里介绍移动平均法和指数平滑法，主要通过对时间序列进行平滑以消除其随机波动。移动平均法，是通过对时间序列逐期递移求得平均数作为趋势值或预测值的一 种 平 滑 法 。 设 移 动 间 隔 长 度 为 K ， 则 移 动 平 均 序 列 写 成 ： Y + Y +L + YY =。i+k-1ii+1Ki指数平滑法

13、，是用过去的观察值的加权平均数作为趋势值，它是加权平均的一种特殊形aaY = Y + (1- )Y式。t+1tt可以利用函数和公式法，或者使用工具进行分析。函数和公式法打开工作簿“实验 5.4.xls”，利用“月度数据”工作表中的数据，分别用移动平均法（K=3 和 K=4）和一次指数平滑法(a=0.2，一月份预测值为 286331315)计算 2012 年全国进出口总额的长期趋势，结果如图 5.4.1 所示。图 5.4.1 用 EXCEL 计算长期趋势资料及结果计算步骤：第一步：计算三项移动平均：在 C3 中输入“=average（B2：B4）”，并用鼠标拖曳将公式复制到 C3：C12 区域。

14、第二步：计算四项移动平均：在 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)所示。选择“移动平均”，弹出“移动平均”对话框，在

15、“输入区域”中输入“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）。单击确定就可以得到 = 0.2的指数平滑值。a （a）（b）图 5.4.3 指数平滑法（5）计算季节变动季节变动是指现象受

16、到自然因素和社会习俗等因素影响而发生的有规律的周期性变动。测定季节变动可分为两种：一是不排除长期趋势的影响，直接根据原时间序列来测定；二是依据剔除长期趋势后的时间序列来测定。前者用简单平均法，或者用移动平均趋势剔除法。简单平均法计算季节指数打开工作簿“实验 5.5.1.xls”，利用“季度数据”工作表中的数据，计算季节变动，结果如图 5.5.1 所示。图 5.5.1 用简单平均法计算季节指数结果计算步骤：第一步：计算出同季合计：在 B9 输入“=SUM(B3:B8)”，并用鼠标拖曳将公式复制到 C9：E9 区域。第二步：计算出全年合计：在 F3 输入“=SUM(B3:E3)”，并用鼠标拖曳将公

17、式复制到 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 中输入“=

18、AVERAGE(C2:C5)”，并用鼠标拖曳将公式复制到 D4：D23 区域。第二步：计算移正平均 T：在 E4 中输入“=AVERAGE(D3:D4)”，并用鼠标拖曳将公式复制到 E5：E23 区域。第三步：剔除长期趋势 Y ：即计算 Y/T：在 F4 中输入“=C4/E4”，并用鼠标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 区域。