必威体育精装版(Excel高级应用).ppt

第三章 Excel高级应用任课老师：程道建上课班级：化工和环工10级E-mail: chengdj@mail.buct.edu.cn 本章课程主要内容 第一节 EXCEL解不确定值 第二节 EXCEL插值与拟合 第三节 EXCEL线性规划求解 第四节 EXCEL统计分析应用 第一节 EXCEL解不确定值 在Excel中，除了对确定的值进行分析和求解，也可以对按一定规律变化的值进行自动的模拟运算，对可变值进行求解。 还可以通过一定的约束条件进行规划求解，这些分析称做假设分析。 在Excel中，提供了变量模拟运算表、单变量求解、方案管理器和规划求解四种工具，本章就重点介绍这方面的内容及其在Excel中的相关操作。 3.1.1 变量模拟运算表 变量模拟运算表是进行数据分析的方法之一，它可以将某计算公式中的一个变量参数或两个变量参数设置为可变量，在运算中，利用可变参数同时运算多个数值，以提高运算的效率。 根据变量参数的数量不同，可分为单变量模拟运算表和双变量模拟运算表两类，本节就对这两种变量模拟运算表进行详细介绍。 3.1.1 单变量模拟运算表 单变量模拟运算表用于在公式中有单个变量变动时的计算分析。在日常生活中，如购房、买车等需要进行大笔投资时，可以采取按揭的购买方式，如果要对不同的按揭方式进行选择，即可利用单变量模拟表进行快速求解。 例如，某人要贷款1000000元购买房产，预期5年还清贷款，现在有5种年利率的选择，分别为4.88%，5.24%，5.34%，6.02%，6.47%，要计算这几种方案的每月还款额，其具体方法如下： 3.1.1 双变量模拟运算表 双变量模拟运算表用于在公式中有两个变量变动时的计算分析。同样是在日常生活中，如购房、买车等需要进行大笔投资时，可采取按揭的购买方式，如果要对不同的按揭方式进行选择，也可以利用双变量模拟表进行快速求解。例如，某人要贷款购买房产，预期5年还清贷款，现在有7种年利率的选择，分别为4.88%，5.24%，5.34%，6.02%，6.47%，6.84%，7.15%，此时还有不同的贷款额可以选择，分别为800000元、900000元、1000000元、1100000元、1200000元、1300000元，1400000元，1500000元，要计算这几种方案的每月还款额，其具体方法如下： 3.1.2 单变量求解 单变量求解是针对某一问题已经有了预期的结果，需要推测出与此结果有关的变量的值，而知道结果，由果索因的过程，因此，单变量在进行预测和设定目标的分析中应用广泛。 单变量求解 单变量求解是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线型方程。 单变量求解工具可以解决许多数据管理中涉及一个变量的求解问题。 3.1.2 单变量求解基本原理 3.1.2 单变量求解操作示例 3.1.3 方案管理器 可以看到，上述两种假设分析工具只能处理一到两个变动参数的问题分析，在实际应用中，经常遇到有多个变动参数的问题，要解决这样的问题，就需要Excel中的方案管理器来解决，本节就针对此工具进行详细介绍。 方案管理器 在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响； Excel提供了方案管理器来解决上述问题，利用方案管理器可以很方便地对多种方案进行模拟分析。 3.1.3 方案管理器应用实例 在实际工作中，应用方案管理的例子很多，此处以某品牌促销产品的改进方案为例来介绍其应用。某品牌在今年的促销活动结束后，发现收入低于成本，因此提出了四个方案对此促销活动加以改进，改变了收入和成本要素的数量，分析这些改进方案的实用性，具体方法如下： 3.1.3 方案管理器应用实例 创建方案后需要对方案进行管理，在Excel中，方案管理的工作一般通过方案管理器完成，具体包括显示某一方案、方案的添加和删除、方案的编辑、方案的合并等内容，操作比较简便，本小节会做简要介绍。 1．显示某一方案 2．编辑与删除方案 3．合并方案 3.1.4 规划求解 在实际的求解问题中，还有一种求解设定条件下最大值与最小值的任务，此时，就需要利用Excel中的规划求解工具进行分析求解。此工具可以针对线性的约束条件进行问题的求解，最终通过各个变量的不断变化迭代，实现对目标值的求解，本节就对规划求解工具进行详细介绍，并辅以比较系统的举例来完善其介绍。 3.1.4 规划求解基本原理 “规划求解”是将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果的方法。“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。 规划求解的基本