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

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,EXCEL数据处理电子教案,中国水利水电出版社,*,第,9,章,数据分析及其工具应用,【,本章知识点,】,公式审核与数据跟踪,模拟运算表的应用,单变量求解的应用,规划求解的应用,方案工具的应用,分析工具库的安装,分析工具库的功能,EXCEL数据处理电子教案,2,中国水利水电出版社,本章目录,9.1,公式审核：对公式和数据跟踪分析,9.2,模拟运算表：实现数据敏感度分析,9.3,单变量求解：实现规划目标搜索,9.4,方案管理器：实现多因素决策分析,9.5,规划求解：多约束条件下的优化分析,9.6,分析工具库安装与使用简介,小结,习题,EXCEL数据处理电子教案,3,中国水利水电出版社,9.1,公式审核：对公式和数据跟踪分析,9.1.1“,公式审核”工具栏,在,Excel,中，运行“工具”,/“,公式审核”,/“,显示公式审核工具栏”菜单命令，或者在,Excel,界面上已经存在的工具栏上任意位置单击右键，从弹出的右键工具栏快捷菜单中选择“公式审核”，都可以打开“公式审核”工具栏，如图,9-1,所示。,EXCEL数据处理电子教案,4,中国水利水电出版社,9.1.2,追踪引用单元格,EXCEL数据处理电子教案,5,中国水利水电出版社,9.1.3,追踪从属单元格,EXCEL数据处理电子教案,6,中国水利水电出版社,9.1.4,添加,/,编辑,/,删除批注,利用“公式审核”工具栏，可为单元格添加批注信息。,插入批注还有很多方法，例如,运行“插入”,/“,批注”命令；,在单元格上单击右键，从快捷菜单上选择“插入批注”命令；,运行“视图”,/“,工具栏”命令，从弹出的“工具栏”菜单中选择“审阅”，再从出现的“审阅”工具栏上选择“新批注”按钮。,EXCEL数据处理电子教案,7,中国水利水电出版社,9.1.5,圈释无效数据,EXCEL数据处理电子教案,8,中国水利水电出版社,9.2,模拟运算表：实现数据敏感度分析,9.2.1,单变量模拟运算表,所谓模拟运算表，实际上就是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。它将不同参数数值情况下的计算结果以列表形式显示出来，便于查看、比较和分析。,EXCEL数据处理电子教案,9,中国水利水电出版社,9.2.2,双变量模拟运算表,EXCEL数据处理电子教案,10,中国水利水电出版社,9.3,单变量求解：实现规划目标搜索,所谓的单变量求解，就是求解具有一个变量的方程，它通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值。利用单变量求解有助于解决一些实际工作中遇到的问题，特别适用于对规划目标某一引用数据的取值搜索。,EXCEL数据处理电子教案,11,中国水利水电出版社,9.4,方案管理器：实现多因素决策分析,前面介绍的单变量求解只是解决了目标确定下单个影响变量的取值问题；模拟运算表也最多只能解决两个变量的变动对于计算结果的影响，而对于一些更为复杂的，牵涉到的影响因素较多的决策问题，常常需要考虑更多的变量因素。,以上问题其实就是多因素下的最佳决策分析问题，也可以认为是更为复杂的“,What-if,分析”问题。,在,Excel,中，利用方案管理器工具，可以很好地解决上述问题。,EXCEL数据处理电子教案,12,中国水利水电出版社,9.4.1,方案概述,方案分析用于解决多变量求解问题，它能够对比多种不同方案并寻求最佳方案。在,Excel,中，所谓方案，就是已命名的一组输入值，这组输入值保存在工作表中，并可用来替换工作表中的模型参数，得到解决方案的输出结果。对于同一解题方案的模型参数，可以创建多组不同的参数值，得到各组不同的结论，每组参数和结论都是一个方案。,Excel,的方案分析，主要是针对单变量求解时只能考虑单一因素的局限性，同时模拟运算表也具有以下几个操作限制的情况下，提出的一种便于进行多因素分析的重要工具。,EXCEL数据处理电子教案,13,中国水利水电出版社,9.4.2,创建方案,创建方案是进行方案分析的首要关键问题，应根据实际问题的需要和现实条件的可行性来创建一组方案。,对于上面的净利润目标求解问题，根据数据模型分析，可以分别设计三种有助于增加净利润的可行方案：增加收入（包括增加销售收入、营业外收入和投资收益），减少费用（包括减少销售费用、管理费用和财务费用）和降低成本（包括降低销售成本和营业外支出）。,EXCEL数据处理电子教案,14,中国水利水电出版社,设置好的“方案管理器”对话框,EXCEL数据处理电子教案,15,中国水利水电出版社,9.4.3,显示方案结果,方案创建后，随时就可以执行方案，并查看不同方案的显示结果。操作步骤如下：,（,1,）打开并激活包含已经创建方案的工作表。,（,2,）运行“工具”,/“,方案”命令，弹出如图,9-26,所示“方案管理器”对话框。,（,3,）在“方案管理器”对话框中显示出所有已经制订好的方案，根据需要选中其中的某一方案名，然后单击对话框中的“显示”按钮即可显示方案的执行结果。,执行上述操作后，,Excel,就用创建方案时保存的可变单元格模拟值替换原来数据，并且此时工作表中目标单元格中的结果就是所选定方案的模拟计算结果，可供决策参考。,EXCEL数据处理电子教案,16,中国水利水电出版社,9.4.4,建立方案摘要,上述显示方案只能以逐个地进行方案的查看，如果能够将所有的方案汇总到一个工作表中，然后再对不同的方案的影响进行对比分析，将会非常有助于计划编制或问题决策人员综合考查各种方案的执行效果。,Excel,中提供了“方案摘要”功能，能很好地完成上述任务。,EXCEL数据处理电子教案,17,中国水利水电出版社,9.5,规划求解：多约束条件下的优化分析,9.5.1,规划求解概述,在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种约束资源实现最佳的经济效益，也就是达到常量最高、利润最大、成本最低、费用最省等目标。这就是本节要解决的在约束条件下寻求目标函数最优的规划问题。一般来讲，这类规划问题都具有如下三个特点：,（,1,）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。,（,2,）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。,（,3,）问题都有直接或间接影响约束条件的一组输入值。,EXCEL数据处理电子教案,18,中国水利水电出版社,9.5.2,输入规划模型,建立好规划模型后，下面就可以将规划模型的有关数据和公式输入到,Excel,的工作表中，以便为下一步进行规划求解做好准备。,以下为按照合同交货数量确定计划生产量的规划模型,EXCEL数据处理电子教案,19,中国水利水电出版社,9.4.3,进行规划求解,EXCEL数据处理电子教案,20,中国水利水电出版社,9.5.4,分析求解结果,EXCEL数据处理电子教案,21,中国水利水电出版社,9.5.5,编辑规划求解参数,当规划模型改变时，可以方便地修改有关参数，重新计算。,例如上面的例子中，合同发货数量、生产成本函数、单位产品库存成本和单月生产能力都可能发生变化，假设库存上涨为每套,5,万元，而突然,8,月份又加了一个,50,套的紧急订单，使,8,月份的合同发货数量变为,80,套。此时只需要修改,F5,：,F7,公式中原来的参数,3,改为,5,，并且调整,B6,单元格中的数据为,80,，然后再重新运行“工具”,/“,规划求解”命令，在弹出的“规划求解参数”对话框中直接单击“求解”按钮即可。,EXCEL数据处理电子教案,22,中国水利水电出版社,9.5.6,修改规划求解选项,EXCEL数据处理电子教案,23,中国水利水电出版社,9.6,分析工具库安装与使用简介,9.6.1,安装分析工具库,Excel,的许多分析工具都是由加载宏提供的，而这些加载宏在典型安装或最小化安装,Excel,时并不会被安装到系统中，所以许多用户安装,Excel,之后，在其命令选项的菜单中会缺少一些选项，如“数据”菜单中没有“获取外部数据”和“,MS Access,窗体”，“工具”菜单选项中没有“规划求解”、“数据分析”等功能。,要使用上面所介绍的分析工具，必须提供这些分析工具的加载宏。如果在安装,Excel,系统时没有安装加载宏，必须重新安装,Excel,程序，选择其中的“添加,/,删除”命令，安装,Excel,的加载宏。安装完加载宏程序之后，许多的分析工作并不是立即可以用的，还必须激活加载宏的程序。,EXCEL数据处理电子教案,24,中国水利水电出版社,9.6.2,分析工具库的应用,安装“分析工具库”加载宏之后，运行“工具”,/“,数据分析”命令，将弹出“数据分析”对话框，如图,9-43,所示。可以看出，数据分析工具库提供了很多数据统计与分析功能，例如描述统计分析、假设分析、相关分析、回归分析、方差分析等统计分析功能。,EXCEL数据处理电子教案,25,中国水利水电出版社,小 结,在,Excel,数据处理中，经常需要对数据进行各种分析和统计，在数据分析和统计之前，需要对数据进行审核以及对公式进行跟踪。为了对公式和数据进行跟踪分析，,Excel,专门提供了“公式审核”工具，通过该工具可以将任意单元格中的数据来源和公式结果显示出来。在审核工作表时，可以追踪引用单元格和从属单元格，也可以圈释无效数据。,在进行数据分析方面，本章主要介绍了模拟运算表、单变量求解、方案管理器以及规划求解工具的使用，在应用中它们各有优缺点，有自己的适用场所，读者要对此有个清晰的认识；另外本章最后还简单地介绍了加载宏和数据分析工具库的安装和使用。,总之，通过本章学习，读者应能对数据进行审核，对公式进行跟踪，并会利用有关工具进行数据分析和决策。,EXCEL数据处理电子教案,26,中国水利水电出版社,习 题,1,在,Excel,中，如何追踪指定单元格的引用单元格和从属单元格？,2,如何安装,Excel,菜单项中的“规划求解”菜单项？,3,什么是加载宏，主要有哪些用途？,Excel,加载宏的扩展名是什么？,4,上机自行将本章的所有实例操作一遍。,5,7,上机练习习题,-,具体要求详见教材和本书素材,EXCEL数据处理电子教案,27,中国水利水电出版社,