EXCEL数据处理 第09章 数据分析及其工具应用.ppt

《EXCEL数据处理》电子教案 中国水利水电出版社 第 9 章 数据分析及其工具应用 【本章知识点】 公式审核与数据跟踪 模拟运算表的应用 单变量求解的应用 规划求解的应用 方案工具的应用 分析工具库的安装 分析工具库的功能 本章目录 9.1公式审核：对公式和数据跟踪分析 9.2 模拟运算表：实现数据敏感度分析 9.3单变量求解：实现规划目标搜索 9.4 方案管理器：实现多因素决策分析 9.5 规划求解：多约束条件下的优化分析 9.6分析工具库安装与使用简介 小结 习题 9.1公式审核：对公式和数据跟踪分析 9.1.1 “公式审核”工具栏 在Excel 中，运行“工具”/“公式审核”/“显示‘公式审核’工具栏”菜单命令，或者在Excel 界面上已经存在的工具栏上任意位置单击右键，从弹出的右键工具栏快捷菜单中选择“公式审核”，都可以打开“公式审核”工具栏，如图9-1所示。 9.1.2 追踪引用单元格 9.1.3 追踪从属单元格 9.1.4 添加/编辑/删除批注 利用“公式审核”工具栏，可为单元格添加批注信息。 插入批注还有很多方法，例如 运行“插入”/“批注”命令； 在单元格上单击右键，从快捷菜单上选择“插入批注”命令； 运行“视图”/“工具栏”命令，从弹出的“工具栏”菜单中选择“审阅”，再从出现的“审阅”工具栏上选择“新批注”按钮。 9.1.5 圈释无效数据 9.2 模拟运算表：实现数据敏感度分析 9.2.1 单变量模拟运算表 所谓模拟运算表，实际上就是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。它将不同参数数值情况下的计算结果以列表形式显示出来，便于查看、比较和分析。 9.2.2 双变量模拟运算表 9.3单变量求解：实现规划目标搜索 所谓的单变量求解，就是求解具有一个变量的方程，它通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值。利用单变量求解有助于解决一些实际工作中遇到的问题，特别适用于对规划目标某一引用数据的取值搜索。 9.4 方案管理器：实现多因素决策分析 前面介绍的单变量求解只是解决了目标确定下单个影响变量的取值问题；模拟运算表也最多只能解决两个变量的变动对于计算结果的影响，而对于一些更为复杂的，牵涉到的影响因素较多的决策问题，常常需要考虑更多的变量因素。 以上问题其实就是多因素下的最佳决策分析问题，也可以认为是更为复杂的“What-if分析”问题。 在Excel 中，利用方案管理器工具，可以很好地解决上述问题。 9.4.1 方案概述 方案分析用于解决多变量求解问题，它能够对比多种不同方案并寻求最佳方案。在Excel中，所谓方案，就是已命名的一组输入值，这组输入值保存在工作表中，并可用来替换工作表中的模型参数，得到解决方案的输出结果。对于同一解题方案的模型参数，可以创建多组不同的参数值，得到各组不同的结论，每组参数和结论都是一个方案。 Excel 的方案分析，主要是针对单变量求解时只能考虑单一因素的局限性，同时模拟运算表也具有以下几个操作限制的情况下，提出的一种便于进行多因素分析的重要工具。 9.4.2 创建方案 创建方案是进行方案分析的首要关键问题，应根据实际问题的需要和现实条件的可行性来创建一组方案。 对于上面的净利润目标求解问题，根据数据模型分析，可以分别设计三种有助于增加净利润的可行方案：增加收入（包括增加销售收入、营业外收入和投资收益），减少费用（包括减少销售费用、管理费用和财务费用）和降低成本（包括降低销售成本和营业外支出）。 设置好的“方案管理器”对话框 9.4.3 显示方案结果 方案创建后，随时就可以执行方案，并查看不同方案的显示结果。操作步骤如下： （1）打开并激活包含已经创建方案的工作表。 （2）运行“工具”/“方案”命令，弹出如图9-26所示“方案管理器”对话框。 （3）在“方案管理器”对话框中显示出所有已经制订好的方案，根据需要选中其中的某一方案名，然后单击对话框中的“显示”按钮即可显示方案的执行结果。 执行上述操作后，Excel 就用创建方案时保存的可变单元格模拟值替换原来数据，并且此时工作表中目标单元格中的结果就是所选定方案的模拟计算结果，可供决策参考。 9.4.4 建立方案摘要 上述显示方案只能以逐个地进行方案的查看，如果能够将所有的方案汇总到一个工作表中，然后再对不同的方案的影响进行对比分析，将会非常有助于计划编制或问题决策人员综合考查各种方案的执行效果。 Excel中提供了“方案摘要”功能，能很好地完成上述任务。 9.5 规划求解：多约束条件下的优化分析 9.5.1 规划求解概述 在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划