统计学基础 课件 任务五 Excel在时间数列分析中的应用

主讲：任务五：EXCEL在时间数列分析中的应用统计学基础目录01022实训教学内容033实训教学要求044实训操作步骤1实训教学目的利用Excel软件可对时间数列中的指标进行分析,描绘现象发展过程、预测现象未来发展趋势,同时还可大大缩短计算时间、提高计算效率,使趋势分析更加快捷、具体。通过本任务实训内容的教学,学生可初步掌握利用Excel软件进行时间数列指标分析的方法,提高动手操作能力,也将学生从烦琐的手工数据计算中解脱出来,激发学习积极性,提高学习效率和效果。01实训教学目的02实训教学内容1.利用Excel计算时间数列水平指标。3.利用Excel软件计算时间数列长期趋势。实训教学内容2.利用Excel软件计算时间数列速度指标。。03实训教学要求

1.学生先组成学习小组,一般由2-4人组成。学生在上机操作前先熟悉Excel统计软件相关功能,课上通过教师演示,学生上机操作,掌握学习内容,同时,结合前面所学内容,利用相关资料反复训练,达到熟练操作目的。2.学生课后利用微课资源和网络资源,巩固所学内容。一、时间数列水平指标分析

（一）计算增长量第一步:将原始资料输入Excel工作表中。第二步:计算逐期增长量。单击单元格D4,输入“=D3-C3”,然后利用填充柄功能,按住鼠标左键的同时向右拖拽至单元格H4,分别计算出各年的逐期增长量。第三步:计算累积增长量。单击单元格D5,输入“=D4-744127”,或者输入“=D4-$C$4”,然后利用填充柄功能,分别计算出各年的累积增长量。04实训操作步骤实训资料:表5-26（二）计算平均增长量选中单元格H6,单击【公式】→【插入函数】,选择【AVREAGE】函数后,单击【确定】按钮。在弹出的函数参数对话框中,在【Number1】中选择单元格区域“D4:H4”后,单击【确定】按钮,或者选中单元格H7,输入“=H5/5”即可计算出平均增长量。01020102030405060708序时平均数计算时期数列平均数时点数列平均数-间隔相等连续时点数列时点数列平均数-间隔不等连续时点数列时点数列平均数-间隔相等的间断数列时点数列平均数-间隔不等的间断数列相对数时间数列平均数时间数列（三）计算平均发展水平(1)根据时期数列计算序时平均数。一般采用简单算术平均法,因此可用Excel中AVERAGE函数计算。上例中,单击任一空单元格,输入“=Average(C4:H4)”,即可得到平均国内生产总值为937013.2亿元。(2)根据时点数列计算序时平均数。根据时点数列计算序时平均数,应考虑以下四种情况:

第一种:对于间隔相等的连续时点数列,可采用简单算术平均法,用Excel中的AVERAGE函数计算。第二种:对于间隔不等的连续时点数列,可以每次变动持续的间隔长度为权数,采用加权算术平均法计算。（三）计算平均发展水平实训资料:表5-27所示,——间隔不等连续时点数列具体操作步骤如下:将原始资料输入Excel工作表中,单击任一空单元格,输入“=(B3*15+C3*6+D3*10)/31”,即可得到平均职工人数为215人。第三种:对于间隔相等的间断时点数列,采用首尾折半法。实训资料:表5-28所示具体操作步骤如下:将原始资料输入Excel工作表中,单击任一空单元格,输入“=SUM(B3/2,C3,D3,E3/2)/(4-1)”,即可得到平均职工人数为307人。第四种:对于间隔不等的间断时点数列,采用加权序时平均法。实训资料:表5-29所示具体操作方法如下:将原始资料输入Excel工作表中,单击任一空单元格,输入“=SUM((B3+C3)/2*2,(C3+D3)/2*5,(D3+E3)/2*2,(E3+F3)/2*3))/(2+5+2+3)”,即可得到平均职工人数为250人。2.根据相对数时间数列计算序时平均数(1)由两个时期数列对比形成的相对数时间数列,采用简单算术平均法。实训资料:表5-30第一,已知a、b,。将原始资料输入Excel工作表中,单击任一空单元格,输入“=SUM(B3:F3)/SUM(B4:F4)*100”,得到销售额平均计划完成程度为109.55%。第二,已知a、c,。分两步:首先计算分子数列的序时平均数,单击任一空单元格(H3),输入“=SUM(B3:F3)/5”,得到390;其次,计算分母数列的序时平均数,单击任一空单元格(H4),输入“=SUM(B3/(B5/100),C3/(C5/100),D3/(D5/100),E3/(E5/100),F3/(F5/100))/5”,得到356,单击单元格H5,输入“=H3/H4*100”,即可得到销售额平均计划完成程度为109.55%。第三,已知b、c,求c-。分两步:首先,计算分子数列的序时平均数,单击任一单元格(I3),输入“=SUM(B4*B5/100,C4*C5/100,D4*D5/100,E4*E5/100,F4*F5/100)/5”,得到390;其次,计算分母数列的序时平均数,单击任一单元格(I4),输入“=SUM(B4:F4)/5”,得到356,单击单元格I5,输入“=I3/I4*100”,即可得到销售额平均计划完成程度为109.55%。(2)由两个时点数列对比形成的相对数时间数列计算序时平均数,①由间隔相等的间断时点数列对比形成的相对数时间数列计算序时平均数,要采用首尾折半法。实训资料:表5-31第一步:将原始资料输入Excel工作表中。第二步:计算女职工人数的序时平均数,单击单元格F3,输入“=SUM(B3/2,C3,D3,E3/2)/(4-1)”,得到112。第三步:计算全部职工人数的序时平均数,单击单元格F4,输入“=SUM(B4/2,C4,D4,E4/2)/(4-1)”,得到260。第四步:单击单元格F5,输入“=F3/F4*100”,得到平均比重为42.95%。②由间隔不等的间断时点数列对比形成的相对数时间数列计算平均发展水平,要采用加权序时平均法。相关计算公式为具体操作步骤如下:第一步:将上表资料输入Excel工作表中。第二步:计算分子数列的临时工全年序时平均数。单击单元格G2,输入“=SUM((B2+C2)/2*5,(C2+D2)/2*1,(D2+E2)/2*4,(E2+F2)/2*2)/12”,得到临时工全年序时平均数为13.67人。第三步:计算分母数列的全部职工全年序时平均数。单击单元格G4,输入“=SUM((B4+C4)/2*5,(C4+D4)/2*1,(D4+E4)/2*4,(E4+F4)/2*2)/12”,得到全部职工全年序时平均数为724.08人。第四步:单击单元格G5,输入“=G2/G4”,得到平均比重为1.89%。二、时间数列速度指标分析(一)发展速度实训资料:表5-33具体操作步骤如下:第一步:将原始资料输入Excel工作表中。第二步:计算环比发展速度。单击单元格D4,输入“=D3/C3*100”,计算出2017年的环比发展速度,然后利用填充柄功能,计算出单元格E4到H4的值,得到各年的环比发展速度。第三步:计算定基发展速度。单击单元格D5,输入“=D4/744127*100”,计算出2017年的定基发展速度,然后利用填充柄功能,计算出单元格E5到H5的值,得到各年的定基发展速度实训资料:同表5-31。具体操作步骤：第一步:将原始资料输入Excel工作表中。第二步:计算环比增长速度。单击单元格D4,输入“=(D3-C3)/C4*100”,计算出2017年的环比增长速度,然后利用填充柄功能,分别计算出各年的环比增长速度。第三步:计算定基增长速度。单击单元格D5,输入“=(D3-744127)/744127*100”,计算出2017年的定基增长速度,然后利用填充柄功能,分别计算出各年的定基增长速度。(二)增长速度(三)平均发展速度1.已知各年环比发展速度求平均发展速度Excel提供GEOMEAN(几何平均)函数计算几何平均数。单击任一空单元格,单击【公式】→【插入函数】,选择函数类别【统计】中的【GEOMEAN】函数,进入GEOMEAN函数参数设置对话框;在【Number1】栏输入环比发展速度所在单元格区域“D5:H5”,单击【确定】按钮后即求得平均发展速度为108.98%实训资料:表5-332.已知最初水平和最末水平求平均发展速度，可按以下公式并利用对数函数LOG10来计算平均发展速度。具体操作步骤如下:第一步:单击单元格I3,单击【公式】→【数学和三角函数】,在弹出的下拉列表中选择【LOG10】函数,进入LOG10函数参数设置对话框;在【Number】栏中输入“H3”,计算出最末水平的对数为6.0583。第二步:单击单元格I4,单击【公式】→【数学和三角函数】,在弹出的下拉列表中选择【LOG10】函数,进入LOG10函数参数设置对话框;在【Number】栏中输入“C3”,计算出最初水平的对数为5.8716。第三步:单击单元格I5,输入“=(I3-I4)/5”得到对数0.0373;单击单元格I6,输入“=10^I5”或“=POWER(10,I5)”,求得真数为1.0898,即108.98%三、时间数列构成分析(一)长期趋势的测定1.移动平均法在Excel中,对于移动平均法,可通过AVERAGE函数,并利用填充柄功能求解。实训资料:表5-34具体操作步骤：第一步:将原始资料输入Excel工作表中。第二步:计算三项移动平均。单击单元格C4,输入“=AVERAGE(B3,B4,B5)”,得到52,然后利用填充柄功能计算出单元格C6到C14的数值。2.最小二乘法(1)直线趋势的测定实训资料:同表5-35。具体操作步骤如下:

第一步:将原始资料输入Excel工作表中。在A列中输入年份,在B列中输入年序号,在C列中输入产品销售额。第二步:。单击单元格E2,输入“=B2*B2”,然后利用填充柄功能计算出单元格E3到E8的数值,并计算出合计数。第三步:计算ty。单击单元格F2,输入“=B2*C2”,然后利用填充柄功能计算出单元格F3到F8的数值,并计算出合计数。第四步:计算参数a、b的值。先计算b值,单击单元格E12,输入“=(7*E10-B10*C10)/(7*E10-B10*B10)”,得到b值为1.72;再计算a值,单击单元格F12,输入“=C10/7-E12*B10/7”,得到a值为10.54。直线趋势方程为(2)使用INTERCEPT函数计算截距。利用已知的x值和y值计算直线与y轴的交叉点。交叉点是以通过已知的x值和y值绘制的最佳拟合回归线为基础的。当自变量为0时,可使用INTERCEPT函数确定因变量的值。语法结构:INTERCEPT(Known_y's,Known_x’s)。Known_y's:因变量的观察值或数据的集合。Known_x's:自变量的观察值或