excel第九章

9.2 模拟运算表
1、概念

模拟运算表是对工作表中一个单元格区域内的 数据进行模拟运算，测试使用一个或两个变量 的公式中变量对运算结果的影响。 ①基于一个输入变量的表，用这个输入变量测 试它对多个公式的影响；——单模拟运算表 ②基于两个输入变量的表，用这两个变量测试 它们对于单个公式的影响——双模拟运算表
【例9.1】某商场的销售记录和销售汇总表如图所示。查 看销售汇总表中各数据的来源是否正确。
华信教育资源网
www.hxedu.com.cn
9.1.1追踪引用单元格
单击追踪引用单元格 单击公式选项卡
单击包括有公式的单元格
华信教育资源网
www.hxedu.com.cn
9.1.2追踪从属单元格

从属单元格

输入方案变量
华信教育资源网
www.hxedu.com.cn
9.4 方案分析

建立方案报告
华信教育资源网
www.hxedu.com.cn
9.4 方案分析

建立方案透视图
华信教育资源网
www.hxedu.com.cn
9.5 加载宏工具的安装
1.

加载宏的概念
加载宏是一种可选择性地安装到计算机中的软件组 件，用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数，扩充Excel的功能。 Excel2007加载宏的扩展名是.xlam。 在默认情况下，Excel将表9-1列出的加载宏程序安 装在如下某一磁盘位置：“Microsoft Office\Office”文件夹下的“Library”文件夹或其 子文件夹，或 Windows 所在文件夹下的 “Profiles\用户名\Application Data\Microsoft\AddIns”文件夹下。网络管理员 也可将加载宏程序安装到其他位置。


如果某个单元格中的公式引用了其它单元格， 那么此单元格就称为从属单元格。 当公式中所引用单元格的值发生变化时，公式 所在单元格（从属单元格）中的值也会随之变 化。例如，若B3单元格中包含公式 “=A3+A4”，则B3就是A3和A4的从属单元 格。当A3或A4单元格的值发生变化时，B3单 元格的值就会发生变化。

假设某人想贷款45万元购买一部车，要查看在 不同的利率和不同的偿还年限下，每个月应还 的贷款金额。假设要查看贷款利率为5%、 5.5%、6.5%、7%、7.5%、8%，偿还期 限为10年、15年、20年、30年、35年时，每 月应归还的贷款金额是多少
华信教育资源网
www.hxedu.com.cn
9.2.2 双变量模拟运算表
华信教育资源网
www.hxedu.com.cn


9.6 线性规划求解
2、Excel规划求解问题由以下3部分组成

（1）可变单元格 （2）目标函数 （3）约束条件
华信教育资源网
www.hxedu.com.cn
9.6.2 建立规划求解模型
3、案例



【例9.8】某肥料厂专门收集有机物垃圾，如 青草、树枝、凋谢的花朵等。该厂利用这些废 物，并掺进不同比例的泥土和矿物质来生产高 质量的植物肥料，生产的肥料分为底层肥料、 中层肥料、上层肥料、劣质肥料4种。为使问 题简单，假设收集废物的劳动力是自愿的，除 了收集成本之外，材料成本是低廉的。 该厂目前的原材料、生产各种肥料需要的原材 料比例，各种肥料的单价等如下各表所示。 问题：求出在现有的情况下，即利用原材料的 现有库存，应生产各种类型的肥料各多少数量 才能获得最大利润，最大利润是多少？ 华信教育资源网
2、单击“数据有效性” 右边的下箭头，选择 “圈释无效数据”
Excel就会将不 符合有效性规则 的数据圈释出来
华信教育资源网
www.hxedu.com.cn
9.1.3 数据有效性检验

设置输入提示信息和错误警告信息

这些信息都需要通过“数据有效性”对话框进 行设置。
华信教育资源网
www.hxedu.com.cn
2、数据审核的方式

9.1.1追踪引用单元格

引用单元格


是指被其他单元格中的公式引用的单元格，即 为指定公式提供数据的单元格。 当使用“追踪引用单元格”工具时，Excel会 用蓝色追踪箭头指明活动工作表中为公式提供 数据的单元格。如果选定单元格引用了其他工 作表或工作簿中的单元格，Excel会用黑色追 踪箭头从工作表图标指向选定的单元格。
2、案例

【例9.7】已知某茶叶公司2001年的总销售额及各种茶
叶的销售成本，现要在此基础上制订一个五年计划。由于 市场竟争的不断变化，所以只能对总销售额及各种茶叶销 售成本的增长率做一些估计。最好的方案当然是总销售额 增长率高，各茶叶的销售成本增长率低。 最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、 红茶的销售成本分别增长10%、6%、10%、7%。
www.hxedu.com.cn
9.4 方案分析

建立方案
（1）选择“数据”|“方案”菜单 1、单击“数据” 单击添加按钮， 然后在添加方案对话 框中输入方案名 2、单击“假设分析”|”方案管理器“
3、单击“添加“
4、输入“方案名称“
华信教育资源网
www.hxedu.com.cn
9.4 方案分析
华信教育资源网
www.hxedu.com.cn
wk.baidu.com
9.5 加载宏工具的安装
2、Excel内置加载宏
加 载 宏 分析工具库 条件求和向导 欧元工具 查阅向导 ODBC 加载宏 报告管理器 规划求解 模板工具 Internet Assistant VBA 描 述 添加财务、统计和工程分析工具和函数 对于数据清单中满足指定条件的数据进行求和计算 将数值的格式设置为欧元的格式，并提供EUROCONVERT函数 以用于转换货币 创建一个公式，通过数据清单中的已知值查找所需数据 利用安装的 ODBC 驱动程序，通过开放式数据库互连（ODBC） 功能与外部数据源相连 为工作簿创建含有不同打印区域、自定义视面以及方案的报告 对基于可变单元格和条件单元格的假设分析方案进行求解计算 提供 Excel 的内置模板所使用的工具。使用内置模板时就可自动 访问这些工具 通过使用 Excel 97 Internet Assistant 语法，开发者可将 Excel 数 据发布到 Web 上 华信教育资源网 www.hxedu.com.cn
华信教育资源网
www.hxedu.com.cn
9.1.1追踪引用单元格
单击追踪从属单元格 单击公式选项卡
单击包括要追踪的单元格
华信教育资源网
www.hxedu.com.cn
9.1.3 数据有效性检验
数据有效性


数据有效性检验是Excel为减少错误、核查数据正确性 而提供的一种数据检验工具。利用该工具可以设置单元 格数据输入的类型和范围，对错误的输入数据进行告警， 并拒不接受，把错误限制在输入阶段。此外，它还能对 已经完成的数据表设置有效性检验规则，并据此标识出 其中的错误数据。 案例
=PMT(B1/12,B2*12,D1)
单击“数 据”→“假设 分析”→“数 据表”
华信教育资源网
www.hxedu.com.cn
9.3 单变量求解
1、概念

所谓单变量求解，就是求解具有一个变量的方 程，Excel通过调整可变单元格中的数值，使 之按照给定的公式来满足目标单元格中的目标 值. 【例9.6】某公司想向银行贷款900万元人民 币，贷款利率是8.7%，贷款限期为8年，每 年应偿还多少金额？如果公司每年可偿还120 万元，该公司最多可贷款多少金额？
华信教育资源网
www.hxedu.com.cn

9.4 方案分析

建立方案解决工作表
建立方法如下，输入下表A 列、B列及第3行的所有数 据；在C4单元格中输入公 式“=B4*(1+$B$16)”， 然后将其复制到D4~F4； 在C7中输入公式 “=B7*(1+$B$17)”,并将 其复制到D7~F7；在C8中 输入公式 “=B8*(1+$B$18)”，并 将其复制到D8和F8；在C9 中输入公式 “=B9*(1+$B$19)”，并 将其复制到D9~F9；在C10 中输入公式 “=B9*(1+$B$20)”，并 将其复制到D10~F10；第 11行数据是第7，8，9，10 行数据对应列之和；净收 入是相应的总销售额和销 售成本之差，E19的总净收 华信教育资源网 入是第13行数据之和。
9.1 数据审核及跟踪分析
1、概念


数据审核是一种查找单元格数据错误来源的工 具，通过它可以快速地找出具有引用关系的单 元格，借此分析造成错误的单元格。 数据审核使用追踪箭头，通过图形的方式显示 或追踪单元格与公式之间的关系。 追踪引用单元格 追踪从属单元格
华信教育资源网
www.hxedu.com.cn
华信教育资源网
www.hxedu.com.cn
2、模拟运算表的类型


9.2.1 单变量模拟运算表

单变量模拟运算表概念

在单变量模拟运算表中，输入数据的值被安排在一 行或一列中。同时，单变量模拟表中使用的公式必 须引用“输入单元格”。所谓输入单元格，就是被 替换的含有输入数据的单元格 【例9.3】假设某人正考虑购买一套住房，要承担 一笔250 000元的贷款，分15年还清。现想查看每 月的还贷金额，并想查看在不同的利率下，每月的 应还贷金额。
9.1.3 数据有效性检验

成绩表示例
华信教育资源网
www.hxedu.com.cn
9.1.3 数据有效性检验

限制方法

单击“数据”选项卡中的“数据有效性”按钮
限定数据类型 限定方式
设置数据大小范围或长度
华信教育资源网
www.hxedu.com.cn
9.1.3 数据有效性检验

圈释无效数据
1、按前面介绍的方 法首先设置数据的 有效值范围:0-100；

案例

华信教育资源网
www.hxedu.com.cn
9.2.1 单变量模拟运算表
1、建立模拟运算表
2、选择“数据”→“假设分析”
→“数据表”
3、C4的公式中引用了B4 单元格，在实际计算 时，将用B列B5:B11 的值逐一代替公式中 的B4。
华信教育资源网
www.hxedu.com.cn
9.2.1 单变量模拟运算表
【例9.2】某班要建立一个成绩登记表，为了减少成绩输入错误， 可对成绩表中数据的输入类型及范围进行限制。

限制学号为8位字符，不能小于8位，也不能多于8位。 限制所有学科成绩为0~100之间的整数。 限制科目列标题的取值范围，如“高数”不能输入为“高等数 学”。 华信教育资源网
www.hxedu.com.cn
9.5 加载宏工具的安装

3、安装分析工具 （1）单击 Office 按钮钮 ，然后单击“Excel 选项”。 （2）单击“加载项”，然后在“管理”框中，选择“Excel 加 载宏”，然后单击“转到”。Excel会弹出图示的“加载宏”对 话框。 （3）在“加载宏”框对话中，选中要安装的加载宏。
【例9.4】对于例9.3而言，如果要查看在同等利息情况下， 分别贷款￥250 000，￥400 000，￥550 000， ￥800 000的每月还贷金额，则可建立如图9.5所示的模 拟运算表。
华信教育资源网
www.hxedu.com.cn
9.2.2 双变量模拟运算表


单变量模拟运算表只能解决一个输入变量 对一个或多个公式计算结果的影响，如果 想查看两个变量对公式计算的影响就需要 使用双变量模拟运算表。 案例
华信教育资源网
www.hxedu.com.cn
9.6 线性规划求解
1、规划求解问题的特点：

问题有单一的目标，如求运输的最佳路线、求 生产的最低成本、求产品的最大盈利，求产品 周期的最短时间等。 问题有明确的不等式约束条件，例如生产材料 不能超过库存，生产周期不能超过一个星期等。 问题有直接或间接影响约束条件的一组输入值。
华信教育资源网
www.hxedu.com.cn

案例

9.3 单变量求解
2、单变量求解方法
（1）建立求解公式：
单击“数据”→“假 设分析”→“单变量 求解”
（2）设置求解公式
（3）求解结果
华信教育资源网
www.hxedu.com.cn
9.4 方案分析
1、概念

方案是已命名的一组输入值，是 Excel 保存在工作表中并 可用来自动替换某个计算模型的输入值，用来预测模型的 输出结果。
Excel与数据处理 第3版
华信教育资源网
www.hxedu.com.cn
本章学习目标
1、了解加载宏的功能和用法 2、掌握数据审核的方法 3、掌握模拟运算表的建立方法 4、掌握单变量求解的方法 5、掌握方案建立和应用方法 6、掌握规划求解的方法 7、掌握分析工具库中的常用工具
华信教育资源网
www.hxedu.com.cn