与数据处理数据分析工具及应用

表1 各肥料成品用料及其价格表 表的意思是生产一个单位的肥料需要多少各种原材料多少单位
产品
泥土 有机垃圾 矿物质 修剪物
该厂目前的原材料、生产各种肥料需要的原材料比例， 各种肥料的单价等如下页各表所示。
问题：求出在现有的情况下，即利用原材料的现有库 存，应生产各种类型的肥料各多少数量才能获得最大 利润，最大利润是多少？
分析:所求是在现有的原材料情况下,应如何合理搭配, 才能获取生产产品的最大利润.
7.6 线性规划求解
操作方法：选择“审核”工具栏中“移去从属单元格中 追踪箭头”
7.2 数据审核及跟踪分析
3、 数据有效性 数据有效性：对数据进行检验和检查的有效方法，把错误 限制在数据输入阶段。
限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、 单元格的公式
7.2 数据审核及跟踪分析
数据限制的操作方法：选择“数据” —“有效性百度文库—在对 话 框中操作：
最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、 红茶的销售成本分别增长10%、6%、10%、7%。
[见ch7.xls—方案]
7.5 方案分析
建立方案解决工作表
建立方法如下，输入下表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中输入公式 “=B10*(1+$B$20)”， 并将其复制到D10~F10； 第11行数据是第7，8，9， 10行数据对应列之和；净 收入是相应的总销售额和 销售成本之差，E19的总 净收入是第13行数据之和。
目录
7.3 模拟运算表
1、概念 模拟运算表是对工作表中一个单元格区域内的数据进 行模拟运算，测试使用一个或两个变量的公式中变量 对运算结果的影响。
2、模拟运算表的类型 ①基于一个输入变量的表，用这个输入变量测试它对 多个公式的影响；——单模拟运算表
②基于两个输入变量的表，用这两个变量测试它们对 于单个公式的影响——双模拟运算表
例：[见ch7.xls—限定数据范围] 某班要建立一个成绩登记表，为了减少成绩输入错 误，可对成绩表中数据的输入类型及范围进行限制。
限制学号为8位字符，不能小于8位，也不能多于8 位。
限制所有学科成绩为0~100之间的整数。 限制科目列标题的取值范围，如“高数”不能输入
为“高等数学”。
7.2 数据审核及跟踪分析
7.6 线性规划求解
例： [见ch7.xls—规划求解]
某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢 的花朵等。该厂利用这些废物，并掺进不同比例的泥 土和矿物质来生产高质量的植物肥料，生产的肥料分 为底层肥料、中层肥料、上层肥料、劣质肥料4种。为 使问题简单，假设收集废物的劳动力是自愿的，除了 收集成本之外，材料成本是低廉的。
2、单变量求解方法 ▪ 在工作表中输入原始数据； ▪ 建立可变数公式； ▪ 设置求解公式：菜单“工具”—单变量求解—对话框 中输入：目标单元格、目标值、可变单元格
例： [见ch7.xls—单变量求解] 某公司想向银行贷款900万元人民币，贷款利率是8.7%， 贷款限期为8年，每年应偿还多少金额？
如果公司每年可偿还120万元，该公司最多可贷款多少 金额？
7.5 方案分析
输入方案变量值如下图所示：
7.5 方案分析
2、显示方案 选择“工具” —“方案”菜单—选择“方案管理器”对话 框中的某一方案—单击 “显示”按钮
3、建立方案报告[见ch7.xls—方案摘要] 选择“工具” —“方案”菜单—选择“方案管理器”对话 框中的某一方案—单击 “总结”按钮—在“方案总结” 对话框中结果类型中选择“方案总结”
重点： 掌握数据审核的方法 掌握模拟运算表的应用 掌握单变量求解的应用 掌握方案的应用 掌握规划求解的应用
难点： 掌握规划求解的应用 学时数： 12学时（上机6学时）
7.1 分析工具的安装
2、Excel内置加载宏
加载宏 分析工具 库 条件求和 向导 欧元工具
查阅向导
ODBC 加 载宏
与数据处理数据分析工具及应用
本章教学目的与要求
1、掌握宏的加载方法 2、掌握追踪从属或引用单元格的方法 3、掌握限定单元格数据的范围及圈释无效数据的
应用方法 4、掌握模拟运算表及变量求解的应用 5、掌握方案的建立和应用 6、掌握规划求解工具的应用 7、了解假设检验和回归分析等工具的应用
本章重点、难点及学时数
限定文本长度： “设置”选项卡中“允许” 下拉列表中 选择文本长度。
限定数据的有效范围：“设置”选项卡中“允许”下拉列 表中选择整数/小数-- 确定最大/小值
设置单元格有效范围：“设置”选项卡中“允许”下拉列 表中选择序列—输入序列值
设置输入提示信息： “输入信息”选项卡中输入要显示 的信息
7.2 数据审核及跟踪分析
数据审核使用追踪箭头，通过图形的方式显示或追踪单 元格与公式之间的关系。
2、数据审核的方式
追踪引用单元格[见ch7.xls—追踪引用单元格]
操作方法：选定菜单“工具”—“审核” —显示‘审核’ 工具栏—选择要追踪引用的含公式单元格—“审核”工 具栏中“追踪引用单元格”按钮—再次单击“追踪引用 单元格”按钮提供数据的下一级单元格
移去引用单元格追踪箭头：
操作方法：选择“审核”工具栏中“移去引用单元格中 追踪箭头”
7.2 数据审核及跟踪分析
追踪从属单元格[见ch7.xls—追踪从属单元格] 某单元格公式引用了其它单元格，则该单元格为从属单元
格。 操作方法：选定菜单“工具”—“审核” –显示‘审核’ 工具栏—选择要追踪从属单元格的单元格—“审核”工具 栏中“追踪从属单元格”按钮—再次单击“追踪从属单元 格”按钮提供从属的的单元格 移去引用单元格追踪箭头：
假设某人想贷款45万元购买一部车，要查看在不同 的利率和不同的偿还年限下，每个月应还的贷款金 额。假设要查看贷款利率为5%、5.5%、6.5%、7%、 7.5%、8%，偿还期限为10年、15年、20年、30年、 35年时，每月应归还的贷款金额是多少 ？
目录
7.4 单变量求解
1、概念
▪ 所谓单变量求解，就是求解具有一个变量的方程， Excel通过调整可变单元格中的数值，使之按照给定的 公式来满足目标单元格中的目标值.
4、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对 已输入数据的区域，不能显示出有误的数据。采用圈释无 效数据的方法，可以显示不满足有效性规则的错误单元格。 操作方法：（选择数据区域—设置数据有效性规 则）—选择“工具”菜单—“审核”—选择“显示审核 工具栏”—选中有效性检测的数据区域—单击“审核” 工具栏的“圈释无效数据”按钮 注：要先设置数据的有效范围，然后再圈释无效数据 例：[见ch7.xls—圈释无效数据] 某班要建立一个成绩登记表，已经对成绩表中数据 的输入类型及范围进行限制，找出其中不符合规定 的数据。
7.1 分析工具的安装
3、安装分析工具 选择“工具”|“加载宏”菜单—在对话框中选择所需 工具，按确定 注：若在安装EXCEL系统时没有安装加载宏，则必须重 新启动EXCEL的安装程序，选择其中的“添加/删除” 命令，安装EXCEL的加载宏。
目录
7.2 数据审核及跟踪分析
1、概念
数据审核是一种查找单元格数据错误来源的工具，快速 地找出具有引用关系的单元格，借此分析造成错误的单 元格。
前一问题可用PMT函数, 后一问题可用单变量求解。 目录
7.5 方案分析
1、概念 方案是已命名的一组输入值，是 Excel 保存在工作表中并 可用来自动替换某个计算模型的输入值，用来预测模型的 输出结果。
例: 已知某茶叶公司2004年的总销售额及各种茶叶的销售成本， 现要在此基础上制订一个五年计划。由于市场竞争的不断 变化，所以只能对总销售额及各种茶叶销售成本的增长率 做一些估计。最好的方案当然是总销售额增长率高，各茶 叶的销售成本增长率低。
2、规划求解问题的特点： 问题有单一的目标，如求运输的最佳路线、求生产的 最低成本、求产品的最大盈利，求产品周期的最短时 间等。
问题有明确的不等式约束条件，例如生产材料不能超 过库存，生产周期不能超过一个星期等。
问题有直接或间接影响约束条件的一组输入值。
7.6 线性规划求解
3、Excel规划求解问题的组成部分 （1）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划 求解的方案 （2）目标函数 目标函数表示规划求解要达到的最终目标，是规划求解 的关键。它是规划求解中可变量的函数 （3）约束条件 约束条件是实现目标的限制条件。 意义：通过规划求解，用户可为工作表的目标单元格 中的公式找到一个优化值，规划求解将直接或间接与 目标单元格公式相联系的一组单元格数值进行调整， 最终在目标单元格公式中求得期望的结果。
7.1 分析工具的安装
报告管理 为工作簿创建含有不同打印区域、自定义视面
器
以及方案的报告
规划求解 对基于可变单元格和条件单元格的假设分析方 案进行求解计算
模板工具 提供 Excel 的内置模板所使用的工具。使用内置 模板时就可自动访问这些工具
Internet Assistant VBA
通过使用 Excel 97 Internet Assistant 语法，开 发者可将 Excel 数据发布到 Web 上
（3部分）； 4、选择“数据”菜单—“模拟运算表”选项； 5、在“模拟运算表”对话框中输入引用单用格（行或列一
种） —确定
7.3 模拟运算表
例：[见ch7.xls—单变量模拟运算表] 假设某人正考虑购买一套住房，要承担一笔250 000 元的贷款，分15年还清。现想查看每月的还贷金额， 并想查看在不同的利率下，每月的应还贷金额。
若贷款额分别为400 000，550 000，800 000元， 每月的应还贷金额又是多少？
7.3 模拟运算表
4、双变量模拟运算表 概念：
单变量模拟运算表只能解决一个输入变量对一个或多个公式 计算结果的影响，要查看两个变量对公式计算结果的影响， 就要用到双变量模拟运算表。所谓双模拟变量，就是指公式 中有两个变量。公式中两个变量所在的单元格是任取的。可 以是工作表中任意空白单元格。
描述 添加财务、统计和工程分析工具和函数
对于数据清单中满足指定条件的数据进行求和 计算 将数值的格式设置为欧元的格式，并提供 EUROCONVERT函数以用于转换货币 创建一个公式，通过数据清单中的已知值查找 所需数据 利用安装的 ODBC 驱动程序，通过开放式数据 库互连（ODBC）功能与外部数据源相连
4、建立方案透视图[见ch7.xls—方案数据透视图] 选择“工具” —“方案”菜单—选择“方案管理器”对话 框中的某一方案—单击 “总结”按钮—在“方案总结” 对话框中结果类型中选择“方案数据透视表”
目录
7.6 线性规划求解
1、概述 EXCEL提供的规划求解工具，可求解出线性与非线性两种 规划求解问题，规划求解问题常用于解决产品比例、人员 调度、优化路线、调配材料等方面问题。
7.3 模拟运算表
操作步骤：
1、在工作表中建立模拟运算表的结构； 2、在行列交叉处输入模拟运算表要用到的公式； 3、选择包括公式,引用单元格和运算结果单元格区
域（3部分）； 4、选择“数据”菜单—“模拟运算表”选项； 5、在“模拟运算表”对话框中输入公式中行和列引
用的单用格—确定 例：[见ch7.xls—双变量模拟运算表]
7.3 模拟运算表
3、单变量模拟运算表 概念
在单变量模拟运算表中，输入数据的值被安排在一行或 一列中。同时，单变量模拟表中使用的公式必须引用 “输入单元格”。
输入单元格，就是被替换的含有输入数据的单元格
操作步骤：
1、在工作表中建立模拟运算表的结构； 2、输入模拟运算表要用到的公式； 3、选择包括公式、引用单元格和运算结果单元格区域