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