EXCEL与数据分析.ppt

1 第六章数据的变动趋势分析 授课教师 马银戌 2 本章主要教学内容 第一节观察数据的变动趋势特征 第二节时间序列的趋势外推分析 第三节时间序列的移动平均分析 第四节时间序列的指数平滑分析 3 第一节观察数据的变动趋势特征 一 从数据看未来二 时间序列分析的一般步骤三 时间序列的预处理四 时间序列的描述分析 4 一 从数据看未来 如果认为事物过去的变化规律将会持续到未来 未来将是事物过去变化规律的延伸 那么 我们就可以利用目前已经掌握的那些反映事物过去发展特征的数据 预见事物未来的发展趋势 从数据看未来的意义正在于此 采用科学的数量分析方法 从数据出发预见事物的未来发展趋势 是我们实现分析目标的有效途径 时间序列分析方法正是一种基于事物过去的数据资料 寻找其随着时间推移的变化规律 从而建立时间序列模型以预测事物未来发展的数值特征的有效预测方法 5 从数据看未来的基础是数据 而这些数据是具有时间特征的 我们将那些依时间先后顺序排列起来的一系列有相同内涵的数据称之为时间序列 时间序列的构成要素有两个 现象所属的时间 现象在不同时间上的数值 时间序列的变量值是由众多复杂因素共同作用的结果 影响时间序列的因素按其性质和作用 可归纳为以下四类 长期趋势 T 季节变动 S 循环变动 C 不规则变动 I 时间序列是这四部分的综合影响结果 时间序列的分析任务就是要正确确定时间序列的性质 对构成时间序列的各种因素加以分解 再分别测定其对时间序列变动的影响 本课程主要讲述长期趋势的测定及Excel实现 6 二 时间序列分析的一般步骤 1 收集历史资料 编制时间序列 并将数据以恰当的格式组织在Excel中 2 绘制统计图 初步观察数据的基本特征 3 对时间序列进行分析 寻找事物随时间变化而呈现的特征和规律 4 根据时间序列的特征和分析的要求 选择恰当的模型进行数据建模 并对模型进行检验和评价 以求获得最合适的模型 5 利用模型进行分析和预测 7 时间序列的预处理通常包括数据准备 数据的图形化观察 数据加工等主要工作 它是时间序列分析建模的基础 1 数据准备数据准备是时间序列分析的首要工作 其主要目标是要根据分析目的收集数据 并将数据以恰当的格式组织在Excel中 一般将数据按时间先后顺序按列录入即可 三 时间序列的预处理 8 2 图形化观察数据图形化观察数据的目的是对时间序列发展变化特征有一个初步的总体把握 它是今后选择恰当模型 对数据进行深入分析的前提 通过图形化观察 可以对现象进行基本特征的描述分析 还可以把握时间序列的诸多特征 包括 时间序列的总体趋势是上升或下降 是否存在有规律的上下波动 时间序列的变化是否具有周期性特点 时间序列的波动幅度是否存在规律性 时间序列是否存在异常值 不同时间点上的数据是否可能存在一定关系等 9 时间序列的图形化观察和分析 可以通过柱形图和折线图反映现象的基本特征和变动趋势 并利用图形对事物进行基本描述分析 柱形图的制作及时间序列的基本描述分析 1 点击 插入 点击 图表 出现下面的复选框 或直接在Excel表上方的工具栏中点击图表向导 10 2 选择 柱形图 单击 下一步 选择系列产生在行或列 3 在 数据区域 后面的空白框中输入要分析的数据区域单元格地址 4 单击 系列 项 在 分类 X 轴标志 空白框中指定时间所在的单元格地址 11 5 单击 下一步 出现下面对话框 需根据内容和需要填写图表标题 分类 X 轴的标题 数值 Y 轴的标题 6 单击 下一步 选择图表位置 然后单击 完成 7 进一步编辑图表 以符合分析的要求 还可以添加趋势线 并显示趋势方程 12 13 添加趋势线 右击需要添加趋势线的柱形图 选择 添加趋势线 出现 添加趋势线 的复选框 可根据柱形图的基本形状选择所要添加的趋势线类型 然后单击 确定 还可以在 选项 中选择 显示公式 即可在图中显示出所添加的趋势线的方程式 14 15 折线图的制作及时间序列的趋势特征观察 1 点击 插入 点击 图表 出现下面的复选框 或直接在Excel表上方的工具栏中点击图表向导 2 选择 折线图 并选择 子图表类型 16 3 单击 下一步 出现下面对话框 在 数据区域 后面的空白框中输入要分析的数据区域单元格地址 4 单击 系列 项 在 分类 X 轴标志 空白框中指定时间所在的单元格地址 17 5 单击 下一步 出现下面对话框 需根据内容和需要填写图表标题 分类 X 轴的标题 数值 Y 轴的标题 6 单击 下一步 选择图表位置 然后单击 完成 7 利用折线图进行基本描述分析 或通过观察折线图的形状为下一步建立模型做准备 18 趋势线的主要类型及判断 线性 线性趋势线适合于稳定增加或减少的数据值 如果数据点构成的图案类似一条直线 或数据的一次增长量 逐期增长量 为常数或接近一常数 则表明数据为线性趋势 对数 对数趋势线适合于初期快速增加或减少然后趋于稳定的数据值 对于对数趋势线 数据可以包含负数和正数 多项式 多项式趋势线适合于表示波动较大的数据值 当需要分析大量数据的偏差时 可以使用多项式趋势线 19 乘幂 乘幂趋势线适合于显示以特定速率增加的数据值 对于乘幂趋势线 数据不可以包含负数和零值 指数 指数曲线适合于显示环比发展速度接近常数的时间序列 移动平均 移动平均趋势线使用弯曲趋势线显示数据值 同时平滑数据波动 这样可以更清晰地显示图案或趋势 实际中还可以对多项数据的时间序列进行比较分析 可以对多个时间序列在同一图中作出多条折线图 或者可以作簇型柱状图 然后对多个事物的发展趋势做比较分析 20 21 3 原始数据的再加工原始数据的再加工是根据研究任务的要求 将所搜集的大量原始资料 进行进一步的整理和计算新的指标数据等 以得到能满足分析要求的数据资料 在Excel中 可通过自行编辑公式进行各种计算 也可利用有关的函数公式自动计算 22 对于时间序列 可以利用增长量 平均增长量 增长速度 平均增长速度等描述性指标进行描述分析 四 时间序列的描述分析 1 增长量 增长量 报告期水平 基期水平 在Excel中可以通过自己编写公式计算 2 平均增长量 23 平均增长量的计算可利用各个逐期增长量通过插入AVERAGE函数计算 3 增长速度 发展速度和增长速度在Excel中可通过自己编写公式计算 24 4 平均增长速度 平均增长速度 平均发展速度 1 平均发展速度 平均发展速度在EXCEL中可通过插入GEOMEAN函数计算 对河北省装备制造业的基本描述分析 25 第二节时间序列的趋势外推分析 一 线性趋势的外推分析二 非线性趋势的外推分析 26 趋势外推是事物发展渐进过程的一种主要预测方法 是时间序列分析中的传统分析方法 它认为事物的内在发展规律能够体现在数据当中 可从时间序列的数据本身提炼出其发展的规律性 并假定根据过去资料建立的趋势外推模型能够适合未来 然后通过趋势的外推对未来进行预测 趋势外推分析方法是基于回归分析原理的 它将所要研究的时间序列观测值序列看做回归模型的因变量 将表式时间顺序的序列作为模型的自变量 自变量可以没有实际意义 仅表示数据点的先后顺序 也可以有实际意义 如表示年份或月份等 但这些并不代表绝对数量 而只是先后顺序的一种表示 27 一 线性趋势的外推分析 由于时间序列随时间推移可能呈现出线性趋势或非线性趋势 因此 趋势外推分析中的模型也分为线性趋势外推模型和非线性趋势外推模型 如果一个时间序列的散点图呈现直线型分布或时间序列的逐期增长量 一级增长量 相对稳定 为一常数或接近常数 可利用线性趋势外推模型进行拟合 线性趋势外推模型的理论形式为 式中 a为趋势线在纵轴上的截距 是时间t为0时y的趋势预测值 b为趋势线的斜率 表示时间t变动一个单位引起的时间序列观测值的平均变动量 28 式中 a为趋势线在纵轴上的截距 是时间t为0时y的趋势预测值 b为趋势线的斜率 表示时间t变动一个单位引起的时间序列观测值的平均变动量 线性趋势外推分析在Excel中的实现方法 方法一 利用图表加趋势线实现 方法二 利用线性函数LINEST实现 方法三 利用 数据分析 的 回归 功能实现 29 方法一 利用图表加趋势线实现 1 录入时间序列数据 2 点击 插入 点击 图表 3 选择图表类型 一般选折线图或xy散点图 4 填写图表标题等 选择图表输出位置 完成图表 5 右击图表 选择 添加趋势线 然后在趋势线类型中选择 线性 在 选项 中选择 显示公式 和 显示R平方值 然后单击 确定 即可得到线性模型及R方值 6 要预测某个时间的数值 在空白单元格中按照已得到的线性模型自行编辑公式 并将t值代入即可 案例 根据江南某市2009年7月 2011年12月的出口额资料 采用线性模型测定其长期趋势 并预测2012年3月份的出口额 30 方法二 利用线性函数LINEST实现 1 打开已录好的时间序列数据 2 在E1单元格中输入 回归统计量 3 选择E2 F6 单击 插入 单击 函数 出现如下对话框 4 在类别中选择 统计 在 选择函数 中选择 LINEST 函数 单击 确定 出现下面的对话框 31 5 在 Known y s 中输入因变量y值所在的单元格地址 在 Known x s 中输入自变量x值 即序号t 所在的单元格地址 在 Stats 中输入 1 6 按住Ctrl Shift组合键 单击 确定 即可得到计算结果在E2 F6 32 33 根据Excel的输出结果 趋势方程为 该趋势方程的判定系数为0 8792 即时间变量可以解释出口额的的总变差的87 92 因此 该趋势模型非常显著 利用该模型可进行预测 预测方法 1 在E8单元格中输入要 日期 在F8单元格中输入 出口额预测值 在E9单元格中输入要预测的日期 2012年3月 2 在F9单元格中输入公式 F2 E2 33 3 按下Enter键 即出预测结果 11175 932 34 方法三 利用 数据分析 的 回归 功能实现 1 打开已录好的时间序列数据 2 选择 工具 菜单中的 数据分析 子菜单 在 分析工具 的复选框中选择 回归 3 单击 确定 出现如下对话框 35 4 给出因变量Y和自变量X的数据所在的单元格区域 在 输出选项 中选择输出的位置 同时选择 线性拟合图 5 单击 确定 输出回归结果 36 37 对输出结果中主要数据的解释 结合某市出口额的案例 输出结果包括 SUMMARYOUTPUT 摘要输出 和 RESIDUALOUTPUT 残差输出 两部分以及线性拟合图 1 MultipleR 是时间t和因变量出口额Y之间的简单相关系数r 等于0 9376 表明两者间高度线性正相关 2 RSquare 是判定系数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 F18 33 预测结果同样为11175 932万元 39 二 非线性趋势的外推分析 如果一个时间序列随着时间的推移呈现出非线性变动趋势 则可利用非线性趋势外推模型进行拟合 在趋势外推分析的发展过程中 人们根据事物的发展规律归纳出了可概括各种时间序列发展非线性规律的曲线模型 这里主要介绍几种常用的 可在Excel中拟合的非线性趋势模型 包括二次曲线 对数曲线和指数曲线 非线性趋势的外推分析在Excel中的实现主要是通过插入图表 通过折线图或xy散点图 然后添加趋势线来显示时间序列的变动规律的 40 一 二次曲线趋势的外推分析 二次曲线 抛物线 是多项式曲线的一种 是一种常见的曲线形式 如果时间序列中现象的二级增长量为常数 可配合二次曲线趋势方程 二次曲线的趋势方程为 在Excel中的实现是通过插入图表并添加趋势线实现的 案例 根据某化肥厂历年的产量进行趋势分析 并预测其2012年的产量 41 二 对数曲线趋势的外推分析 对数曲线也是一种常见的曲线形式 如果时间序列的初期快速增加或减少然后趋于稳定 则可配合对数曲线趋势方程 对数曲线的趋势方程为 在Excel中的实现是通过插入图表并添加趋势线实现的 案例 根据某纺织厂历年的销售额进行趋势分析 并预测其2012年的产量 42 三 指数增长曲线趋势的外推分析 指数增长曲线适合于显示环比发展速度接近常数的时间序列 如果时间序列中现象的环比发展速度为常数 可配合指数增长曲线趋势方程 指数增长曲线的趋势方程为 在Excel中的实现是通过插入图表并添加趋势线实现的 案例 根据我国历年的轿车产量进行趋势分析 并预测其2012年的产量 43 时间序列的趋势外推分析方法主要是用于中长期的预测分析 应注意到 趋势外推分析中模型的选择是以人们对事物发展规律的充分认知为基础的 是建立在长期积累的经验模型基础上的 虽然长期的实践人们已经总结出了大量的可借用的经验模型 但现实中事物的变化是纷繁复杂的 同时 由于模型选择上存在较强的主观性 这可能将产生因模型选择的差异性而导致的对同样的数据作出不同的分析结论 因此 在时间序列的分析中 选择最优模型是至关重要的 模型优劣的评价标准通常是模型对数据点的拟合程度 拟合程度越高 模型效果越好 通常采用判定系数作为拟合优度高低的判断工具 判定系数越大 拟合优度越高 44 第三节时间序列的移动平均分析 一 移动平均分析的基本思想二 利用Excel实现简单移动平均 45 一 移动平均分析的基本思想 移动平均法是从时间序列中 取最近期的n个观测值计算其平均数 作为下一期的预测值 在连续计算平均数时 每当得到一个新的观测值 就把它计入平均数中 并去掉最早期的观测值 使平均数成为一个随时间而移动的平均数 设时间序列各期的观测值顺次为 设时间序列的移动间隔为k 则第t 1期的预测值为第t期的平均值 计算公式为 46 二 利用Excel实现简单移动平均分析 1 打开已录好的时间序列数据 2 选择 工具 菜单中的 数据分析 子菜单 在 分析工具 的复选框中选择 移动平均 3 单击 确定 出现如下对话框 47 4 在 输入区域 框中输入时间序列观测值所在的单元格地址 如果包含时间序列的标题行 则选择 标志位于第一行 5 在 间隔 框中输入简单移动平均的时间间隔k 如本例指定为5 48 6 在 输出选项 中选择输出的位置 同时选择 图表输出 然后单击 确定 完成 最后一期的移动平均数即可作为下一年的预测值 简单移动平均法主要适合于平稳型的时间序列 案例 根据我国历年的人口性别比进行移动平均分析 并预测2011年的人口性别比 49 第四节时间序列的指数平滑分析 一 指数平滑分析的基本思路二 利用Excel实现指数平滑分析 50 一 指数平滑分析的基本思路 指数平滑法是移动平均法的改良方法 它是用时间序列过去值的加权平均数来预测未来的值 并且通过权数大小体现事物发展不同时期与现实联系的紧密程度 同时 指数平滑法对权数的处理简单易行 因而在实际中应用广泛 指数平滑分析因权数的选择和平滑方法不同而分成多种模型形式 包括一次指数平滑模型 二次指数平滑模型 三次指数平滑模型 利用excel实现的主要是一次指数平滑模型 51 一次指数平滑预测法是用近期预测误差的一部分调整平滑统计量 即用本期的预测值 加上本期预测误差乘一个加权系数 得到本期的平滑值 作为下一期的预测值 计算公式为 52 平滑系数和阻尼系数的取值均介于0 1之间 平滑系数越大 表明预测结果对实际观测值的变动反应越敏感 但预测会变得不稳定 平滑系数较小将导致预测值的滞后 一般平滑系数介于0 2 0 3之间 表明应将当前预测值调整20 30 以修正以前的预测 阻尼系数反映利用本期实际值的信息的程度 一般介于0 7 0 8之间 对于一个时间序列来说 客观上存在一个最佳阻尼系数 使得实际值与预测值误差最小 通常可通过设置多个阻尼系数进行试算 从而找到一个实际值与预测值误差平方和最小的阻尼系数作为最佳阻尼系数 53 对于第一期的预测值的计算方法 可以采用第一期的实际值替代 也可以采用前几期的实际值的平均值替代 一般用前几期的实际值的平均值替代 指数平滑法在Excel中的实现可通过两种方法 直接输入公式法和利用指数平滑分析工具法 无论采用哪种方法都要事先人为确定平滑系数和阻尼系数 1 直接输入公式法 结合案例 利用我国2000 2011年人口性别比资料 预测2012年人口性别比 操作步骤 首先设定平滑系数为0 3 阻尼系数为0 7 54 1 打开已录好的时间序列数据 2 在单元格C2中输入公式 AVERAGE B2 B4 3 在单元格C3中输入公式 0 3 B2 0 7 C2 4 将单元格C3中的公式复制到区域C4 C12中 5 通过图表向导制作实际值与预测值的折线图 结果如下 55 1 利用指数平滑分析工具 1 打开已录好的时间序列数据 把数据区域A2 B13的内容向下移动一行 在空白的单元格B2中输入公式 AVERAGE B3 B5 2 选择 工具 菜单中的 数据分析 子菜单 在 分析工具 的复选框中选择 指数平滑 出现如下框图 3 单击 确定 出现如下对话框 56 4 在 输入区域 框中输入时间序列观测值所在的单元格地址 如果包含时间序列的标题行 则选择 标志 5 在 阻尼系数 框中输入已确定好的数值 0 7 6 在 输出区域 中输入 C 2 选择 图表输出 7 单击 确定 得到平滑结果如下 57 58 7 将单元格C14中的公式复制到单元格C15中 得到2012年的平滑预测值105 64 时间序列趋势分析方法的选择 1 当时间序列为水平型态势时 可选择一次移动平均法或一次指数平滑法 2 若时间序列呈现明显的线性上升或下降趋势 可采用线性趋势外推分析法 3 若时间序列呈现明显的非线性变动趋势 可采用非线性趋势外推分析法