Excel数据处理与分析实例教程(微课版第3版)-电子教案第11章分析数据

第11章分析数据教学内容.模拟运算表.单变量求解.方案分析.规划求解教学要求【知识目标】了解模拟运算表的功能、特点和适应范围了解单变量求解的功能、特点和适应范围了解方案分析的功能、特点和适应范围了解规划求解的功能、特点和适应范围【技能目标】.掌握模拟运算表数据分析工具的使用方法.单变量求解数据分析工具的使用方法.方案分析数据分析工具的使用方法.规划求解数据分析工具的使用方法教学重点.模拟运算表.单变量求解.方案分析.规划求解教学难点.模拟运算表.方案分析教学方法讲授法、演示法―课时数-5课时（讲授3课时，实验2课时）课程导入【思路】首先提出问题，然后组织学生进行讨论，最后总结同学们对数据分析工具的了解情况，引出数据分析的概念，说明数据分析工具的作用。【问题】.在Excel中，有哪些数据分析工具？.数据分析工具的作用是什么？.单变量模拟运算和双变量模拟运算的主要区别是什么？在实际应用中应如何选择？模拟运算表模拟运算表是对一个单元格区域中的数据进行模拟运算，测试在公式中使用变量时，变量值的变化对公式运算结果的影响。在Excel中可以构造2种类型的模拟运算表：单变量模拟运算表和双变量模拟运算表。前者可以测试1个输入变量的变化对公式的影响，后者可以测试2个输入变量的变化对公式的影响。单变量模拟运算表在单变量模拟运算表中，输入的数据值需要放在同一行或同一列中，并且单变量模拟运算表中使用的公式必须要引用“输入单元格”。输入单元格是指模拟运算表中其值不确定而需要用输入行或输入列的单元格中的值替换的单元格。双变量模拟运算表单变量模拟运算表只能解决1个输入变量对一个或多个公式计算结果的影响，如果想查看2个输入变量变化对公式计算结果的影响就需要用到双变量模拟运算表。单变量求解模拟运算表可以实现模拟不同变量的变化，计算可能达到的最终目标。有些应用需求恰恰相反，需要根据预先设定的目标来分析要达到该目标所需要实现的具体指标。在进行这样的分析时，往往由于计算方法较为复杂或许多因素交织在一起而很难进行，这时可以利用Excel提供的单变量求解命令来完成计算。以下通过具体示例说明单变量求解命令的应用方法。方案分析单变量求解只能解决具有1个未知变量的问题，模拟运算表最多只能解决2个变量变化引起的问题。如果要解决包括更多可变因素的问题，或是要在多种假设分析中找出最佳执行方案，上述2种工具就无法实现了，这时可以使用EXcel的方案分析工具来完成。Excel的方案分析主要用于多方案求解问题，利用方案管理器，模拟不同方案的大致结果，根据多个方案的对比分析，考查不同方案的优劣，从中寻求最佳的解决方案。命名单元格在应用方案分析工具之前，首先应做些准备工作。为了使创建的方案能够明确地显示有关变量，以及将来在进行方案总结时便于阅读方案摘要报告，需要先给有关变量所在的单元格命名。创建方案创建方案是方案分析的关键，应根据实际问题的需要和可行性来创建各个方案。创建方案报告应用“方案管理器”对话框中的“显示”按钮只能1一个方案、1一个方案地查看某个方案对利润总额的影响，如果能将所有方案汇总到1一个工作表中，形成1一个方案报讲授+演示【举例】某公司计划贷款1000万元，年限为10年，目前的年利率为3%,需要计算出分月偿还时每个月的偿还额。讲授+演示讲授+演示告，然后再对不同方案的影响进行比较分析，将更有助于决策人员综合考查各种方案的效果。11.4规划求解以上介绍的一些方法可以解决信息管理工作中的许多问题，但也存在一定的局限性。模拟运算表只能分析1~2个决策变量对最终目标的影响，方案分析在可行方案过多时操作会比较烦琐，单变量求解只能通过一个变量的变化求得一个目标值。但在实际工作中往往会有众多因素、无数方案需要计算、比较和分析，并从中选择出最优方案。Excel的规划求解工具可以避开难懂的细节，方便快捷地计算出各种规划问题的最佳解。规划问题种类繁多，归结起来可以分成2类：一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使用它们以获得最大的收益。从数学角度来看，规划问题都有下述3个共同特征。（1）决策变量：每个规划问题都有一组需要求解的未知数（x1,x2,…,xn），称为“决策变量”。这组决策变量的1一组确定值就代表1一个具体的规划方案。（2）约束条件:对于规划问题的决策变量通常都有一定的限制条件，称为“约束条件”。约束条件通常用包含决策变量的不等式或等式来表示。（3）目标函数：每个问题都有一个明确的目标，如利润最大或成本最小。目标通常是用与决策变量有关的表达式表示，称为“目标函数”。如果约束条件和目标函数都是线性函数，则称为线性规划；否则称为非线性规划。如果要求决策变量的值为整数，则称为整数规划。讲授+演示安装规划求解工具规划求解是Excel的1一个加载项，一般安装时默认不加载规划求解工具。如果需要使用规划求解工具，必须手工先进行加载。建立规划模型在进行规划求解时首先要将实际问题数学化、模型化，即将实际问题用一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示，这是求解规划问题的关键。然后才可以应用Excel的规划求解工具求解。输入规划模型建立好规划模型后，下一步工作是将规划模型的有关数据和公式输入到工作表中。求解规划模型分析求解结果国」本章小结 E本章主要介绍了Excel在数据分析方面的应用，通过不同的应用实例分别说明了EXcel的模拟运算表、单变量求解、方案分析、规划求解等数据分析工具的功能、特点、适应范围和使用方法。Excel的数据分析工具功能强大、使用方便，掌握和利用好数据分析工具，能够为管理决策提供更为有效的信息。邂课后练习一、选择题(见教材)二、填空题(见教材)三、问答题∙Excel提供了哪些数据分析工具？.模拟运算表有哪几种类型？.单变量求解方法用于解决哪方面的问题？.与模拟运算表相比，方案分析的优势体现在哪里？.在使用Excel进行规划求解时，若发现Excel功能区中没有“规划求解”命令，应怎样安装它？实训任务.某开发商想贷款100万元建一个山林果园，贷款利率5%,期限为25年，月偿还额是多少？如果有多种不同的贷款利率(3%、4%、5%、6%、7%)和不同贷款年限(10年、15年、20年、30年)可供选择，各种情况下的月偿还额各是多少？.对于第1题中的100万元贷款，若想每月还贷2万元，在贷款利率为6%的情况下，需要多少年才能还清？.根据第2题中的有关数据，分别建立贷款利率4%、贷款年限30年，利率5%、贷款年限20年和利率6%、贷款年限10年3种方案，并建立相应的方案报告。.某公司需要采购一批赠品用于促销活动，采购的目标品种有A、B、C