excel与数据分析

《excel与数据分析》由会员分享，可在线阅读，更多相关《excel与数据分析（58页珍藏版）》请在金锄头文库上搜索。

1、1,第六章 数据的变动 趋势分析,授课教师：马银戌,2,本章主要教学内容,第一节 观察数据的变动趋势特征,第二节 时间序列的趋势外推分析,第三节 时间序列的移动平均分析,第四节 时间序列的指数平滑分析,3,第一节 观察数据的变动趋势特征,一、从数据看未来 二、时间序列分析的一般步骤 三、时间序列的预处理 四、时间序列的描述分析,4,一、从数据看未来,如果认为事物过去的变化规律将会持续到未来，未来将是事物过去变化规律的延伸，那么，我们就可以利用目前已经掌握的那些反映事物过去发展特征的数据，预见事物未来的发展趋势。从数据看未来的意义正在于此。 采用科学的数量分析方法，从数据出发预见事物的未来发展趋

2、势，是我们实现分析目标的有效途径。时间序列分析方法正是一种基于事物过去的数据资料，寻找其随着时间推移的变化规律，从而建立时间序列模型以预测事物未来发展的数值特征的有效预测方法。,5,从数据看未来的基础是数据，而这些数据是具有时间特征的。我们将那些依时间先后顺序排列起来的一系列有相同内涵的数据称之为时间序列。 时间序列的构成要素有两个,现象所属的时间,现象在不同时间上的数值,时间序列的变量值是由众多复杂因素共同作用的结果，影响时间序列的因素按其性质和作用，可归纳为以下四类：长期趋势( T ) 、季节变动( S ) 、循环变动( C )、不规则变动( I ) 时间序列是这四部分的综合影响结果。时间

3、序列的分析任务就是要正确确定时间序列的性质，对构成时间序列的各种因素加以分解，再分别测定其对时间序列变动的影响。 本课程主要讲述长期趋势的测定及Excel实现。,6,二、时间序列分析的一般步骤,1、收集历史资料，编制时间序列，并将数据以恰当的格式组织在Excel中； 2、绘制统计图，初步观察数据的基本特征； 3、对时间序列进行分析，寻找事物随时间变化而呈现的特征和规律； 4、根据时间序列的特征和分析的要求，选择恰当的模型进行数据建模，并对模型进行检验和评价，以求获得最合适的模型； 5、利用模型进行分析和预测。,7,时间序列的预处理通常包括数据准备、数据的图形化观察、数据加工等主要工作。它是时间

4、序列分析建模的基础。 1、数据准备 数据准备是时间序列分析的首要工作，其主要目标是要根据分析目的收集数据，并将数据以恰当的格式组织在Excel中，一般将数据按时间先后顺序按列录入即可。,三、时间序列的预处理,8,2、图形化观察数据 图形化观察数据的目的是对时间序列发展变化特征有一个初步的总体把握，它是今后选择恰当模型、对数据进行深入分析的前提。 通过图形化观察，可以对现象进行基本特征的描述分析；还可以把握时间序列的诸多特征，包括：时间序列的总体趋势是上升或下降，是否存在有规律的上下波动；时间序列的变化是否具有周期性特点；时间序列的波动幅度是否存在规律性；时间序列是否存在异常值，不同时间点上的数

5、据是否可能存在一定关系等。,9,时间序列的图形化观察和分析，可以通过柱形图和折线图反映现象的基本特征和变动趋势，并利用图形对事物进行基本描述分析。,柱形图的制作及时间序列的基本描述分析：,（1）点击“插入” 点击“图表”，出现下面的复选框： (或直接在Excel表上方的工具栏中点击图表向导： ),10,（2）选择“柱形图”，单击“下一步”，选择系列产生在行或列； （3）在“数据区域”后面的空白框中输入要分析的数据区域单元格地址；,（4）单击“系列”项，在“分类（X）轴标志”空白框中指定时间所在的单元格地址；,11,（5）单击“下一步”，出现下面对话框，需根据内容和需要填写图表标题、分类（X）轴

6、的标题、数值（Y）轴的标题；,（6）单击“下一步”，选择图表位置；然后单击“完成”。 （7）进一步编辑图表，以符合分析的要求，还可以添加趋势线，并显示趋势方程。,12,13,添加趋势线：,右击需要添加趋势线的柱形图，选择“添加趋势线”，出现“添加趋势线”的复选框，可根据柱形图的基本形状选择所要添加的趋势线类型，然后单击“确定”。,还可以在“选项”中选择“显示公式”，即可在图中显示出所添加的趋势线的方程式。,14,15,折线图的制作及时间序列的趋势特征观察：,（1）点击“插入” 点击“图表”，出现下面的复选框： (或直接在Excel表上方的工具栏中点击图表向导： ),（2）选择“折线图”，并选择

7、“子图表类型”；,16,（3）单击“下一步”，出现下面对话框，在“数据区域”后面的空白框中输入要分析的数据区域单元格地址；,（4）单击“系列”项，在“分类（X）轴标志”空白框中指定时间所在的单元格地址；,17,（5）单击“下一步”，出现下面对话框，需根据内容和需要填写图表标题、分类（X）轴的标题、数值（Y）轴的标题；,（6）单击“下一步”，选择图表位置；然后单击“完成”。 （7）利用折线图进行基本描述分析，或通过观察折线图的形状为下一步建立模型做准备。,18,趋势线的主要类型及判断：,线性：线性趋势线适合于稳定增加或减少的数据值。如果数据点构成的图案类似一条直线，或数据的一次增长量（逐期增长量

8、）为常数或接近一常数，则表明数据为线性趋势。,对数：对数趋势线适合于初期快速增加或减少然后趋于稳定的数据值。对于对数趋势线，数据可以包含负数和正数。,多项式：多项式趋势线适合于表示波动较大的数据值。当需要分析大量数据的偏差时，可以使用多项式趋势线。,19,乘幂：乘幂趋势线适合于显示以特定速率增加的数据值。对于乘幂趋势线，数据不可以包含负数和零值。 指数：指数曲线适合于显示环比发展速度接近常数的时间序列。,移动平均：移动平均趋势线使用弯曲趋势线显示数据值，同时平滑数据波动，这样可以更清晰地显示图案或趋势。,实际中还可以对多项数据的时间序列进行比较分析：,可以对多个时间序列在同一图中作出多条折线图

9、，或者可以作簇型柱状图，然后对多个事物的发展趋势做比较分析。,20,21,3、原始数据的再加工 原始数据的再加工是根据研究任务的要求，将所搜集的大量原始资料，进行进一步的整理和计算新的指标数据等，以得到能满足分析要求的数据资料。 在Excel中，可通过自行编辑公式进行各种计算，也可利用有关的函数公式自动计算。,22,对于时间序列，可以利用增长量、平均增长量、增长速度、平均增长速度等描述性指标进行描述分析。,四、时间序列的描述分析,1、增长量,增长量 = 报告期水平 基期水平,在Excel中可以通过自己编写公式计算。,2、平均增长量,23,平均增长量的计算可利用各个逐期增长量通过插入AVERAG

10、E函数计算。,3、增长速度,发展速度和增长速度在Excel中可通过自己编写公式计算。,24,4、平均增长速度,平均增长速度 = 平均发展速度 1,平均发展速度：,平均发展速度在EXCEL中可通过插入GEOMEAN函数计算。,对河北省装备制造业的基本描述分析：,25,第二节 时间序列的趋势外推分析,一、线性趋势的外推分析 二、非线性趋势的外推分析,26,趋势外推是事物发展渐进过程的一种主要预测方法，是时间序列分析中的传统分析方法。它认为事物的内在发展规律能够体现在数据当中，可从时间序列的数据本身提炼出其发展的规律性，并假定根据过去资料建立的趋势外推模型能够适合未来，然后通过趋势的外推对未来进行预

11、测。 趋势外推分析方法是基于回归分析原理的。它将所要研究的时间序列观测值序列看做回归模型的因变量，将表式时间顺序的序列作为模型的自变量。自变量可以没有实际意义，仅表示数据点的先后顺序，也可以有实际意义，如表示年份或月份等，但这些并不代表绝对数量，而只是先后顺序的一种表示。,27,一、线性趋势的外推分析,由于时间序列随时间推移可能呈现出线性趋势或非线性趋势，因此，趋势外推分析中的模型也分为线性趋势外推模型和非线性趋势外推模型。,如果一个时间序列的散点图呈现直线型分布或时间序列的逐期增长量（一级增长量）相对稳定，为一常数或接近常数，可利用线性趋势外推模型进行拟合。,线性趋势外推模型的理论形式为：,

12、式中，a为趋势线在纵轴上的截距，是时间t为0时y的趋势预测值；b为趋势线的斜率，表示时间t变动一个单位引起的时间序列观测值的平均变动量。,28,式中，a为趋势线在纵轴上的截距，是时间t为0时y的趋势预测值；b为趋势线的斜率，表示时间t变动一个单位引起的时间序列观测值的平均变动量。,线性趋势外推分析在Excel中的实现方法：,方法一：利用图表加趋势线实现,方法二：利用线性函数LINEST实现,方法三：利用“数据分析”的“回归”功能实现,29,方法一：利用图表加趋势线实现,（1）录入时间序列数据； （2）点击“插入” 点击“图表”； （3）选择图表类型，一般选折线图或xy散点图； （4）填写图表标

13、题等，选择图表输出位置，完成图表； （5）右击图表，选择“添加趋势线”，然后在趋势线类型中选择“线性”，在“选项”中选择“显示公式”和“显示R平方值”，然后单击“确定”，即可得到线性模型及R方值。 （6）要预测某个时间的数值，在空白单元格中按照已得到的线性模型自行编辑公式，并将t 值代入即可。 案例：根据江南某市2009年7月2011年12月的出口额资料，采用线性模型测定其长期趋势，并预测2012年3月份的出口额。,30,方法二：利用线性函数LINEST实现,（1）打开已录好的时间序列数据； （2）在E1单元格中输入“回归统计量”； （3）选择E2:F6，单击“插入”单击“函数”，出现如下对话

14、框：,（4）在类别中选择“统计”，在“选择函数”中选择“LINEST”函数，单击“确定”，出现下面的对话框：,31,（5）在“Known_ys”中输入因变量y值所在的单元格地址；在“Known_xs”中输入自变量x值（即序号t）所在的单元格地址；在“Stats”中输入“1”； （6）按住Ctrl+Shift组合键，单击“确定”，即可得到计算结果在E2：F6。,32,33,根据Excel的输出结果，趋势方程为：,该趋势方程的判定系数为0.8792，即时间变量可以解释出口额的的总变差的87.92%，因此，该趋势模型非常显著。利用该模型可进行预测。 预测方法： （1）在E8单元格中输入要 “日期”，

15、在F8单元格中输入“出口额预测值”，在E9单元格中输入要预测的日期“2012年3月”； （2）在F9单元格中输入公式“=F2+E2*33” （3）按下Enter键，即出预测结果：11175.932,34,方法三：利用“数据分析”的“回归”功能实现,（1）打开已录好的时间序列数据； （2）选择“工具”菜单中的“数据分析”子菜单，在“分析工具”的复选框中选择“回归”；,（3）单击“确定”，出现如下对话框：,35,（4）给出因变量Y和自变量X的数据所在的单元格区域，在“输出选项”中选择输出的位置，同时选择“线性拟合图”；,（5）单击“确定”，输出回归结果。,36,37,对输出结果中主要数据的解释：

16、（结合某市出口额的案例）,输出结果包括“SUMMARY OUTPUT”（摘要输出）和“RESIDUAL OUTPUT”（残差输出）两部分以及线性拟合图。,（1）“Multiple R”是时间t和因变量出口额Y之间的简单相关系数 r ，等于0.9376，表明两者间高度线性正相关； （2）“R Square”是判定系数R2 ,等于0.8792。判定系数R2是测定直线回归模型拟合优度的一个重要指标，其意义同相关系数 r具有一致性 。计算结果表明，出口额的总误差中有87.92%可以由出口额随时间而变动的依存关系来解释，因此这条回归直线的拟合优度很好；,38,（3）F检验和t检验的P值均大于a（0.05），线性回归模型检验通过；,（4）“Coefficients”下面的两个数是回归方程的两个参数值，其中，截距 a = 4893.698，回归系数（斜率） b =190.3707；据此，可写出样本回归方程：,上述趋势方程表明该市出口额存在着明显的上升趋势，且时间每增加一个月，出口额平均增加190.3707万元；据此可利用模型进行预测。 预测方法通过编辑公式计算：“=F17