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

1、Excel与数据处理,第七章 数据分析工具及应用,本章教学目的与要求,1、掌握宏的加载方法 2、掌握追踪从属或引用单元格的方法 3、掌握限定单元格数据的范围及圈释无效数据的 应用方法 4、掌握模拟运算表及变量求解的应用 5、掌握方案的建立和应用 6、掌握规划求解工具的应用 7、了解假设检验和回归分析等工具的应用,本章重点、难点及学时数,重点： 掌握数据审核的方法 掌握模拟运算表的应用 掌握单变量求解的应用 掌握方案的应用 掌握规划求解的应用 难点： 掌握规划求解的应用 学时数： 12学时（上机6学时）,本章目录,7.1 分析工具的安装 7.2 数据审核及跟踪分析 7.3 模拟运算表 7.4 单变量求解 7.5 方案分析 7.6 线性规划求解 7.7 数据分析工具库 小结 思考与练习,7.1 分析工具的安装,1、加载宏的概念 加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数，扩充Excel的功能。 Excel加载宏的扩展名是.xla或.xll。 在默认情况下，Excel将下表列出的加载宏程序安装在如下某一磁盘位置：“Mic

2、rosoft OfficeOffice”文件夹下的“Library”文件夹或其子文件夹，或 Windows 所在文件夹下的“Profiles用户名Application DataMicrosoftAddIns”文件夹下。网络管理员也可将加载宏程序安装到其他位置。,7.1 分析工具的安装,2、Excel内置加载宏,7.1 分析工具的安装,7.1 分析工具的安装,3、安装分析工具 选择“工具”|“加载宏”菜单在对话框中选择所需工具，按确定 注：若在安装EXCEL系统时没有安装加载宏，则必须重新启动EXCEL的安装程序，选择其中的“添加/删除”命令，安装EXCEL的加载宏。,目录,7.2 数据审核及跟踪分析,1、概念 数据审核是一种查找单元格数据错误来源的工具，快速地找出具有引用关系的单元格，借此分析造成错误的单元格。 数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。 2、数据审核的方式 追踪引用单元格见ch7.xls追踪引用单元格 操作方法：选定菜单“工具”“审核” 显示审核工具栏选择要追踪引用的含公式单元格“审核”工具栏中“追踪引用单元格”按钮再次单击“追踪引用单元

3、格”按钮提供数据的下一级单元格 移去引用单元格追踪箭头： 操作方法：选择“审核”工具栏中“移去引用单元格中追踪箭头”,7.2 数据审核及跟踪分析,追踪从属单元格见ch7.xls追踪从属单元格 某单元格公式引用了其它单元格，则该单元格为从属单元格。 操作方法：选定菜单“工具”“审核” 显示审核工具栏选择要追踪从属单元格的单元格“审核”工具栏中“追踪从属单元格”按钮再次单击“追踪从属单元格”按钮提供从属的的单元格 移去引用单元格追踪箭头： 操作方法：选择“审核”工具栏中“移去从属单元格中追踪箭头”,7.2 数据审核及跟踪分析,3、 数据有效性 数据有效性：对数据进行检验和检查的有效方法，把错误限制在数据输入阶段。 限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式,7.2 数据审核及跟踪分析,数据限制的操作方法：选择“数据” “有效性”在对话 框中操作： 限定文本长度： “设置”选项卡中“允许” 下拉列表中选择文本长度。 限定数据的有效范围：“设置”选项卡中“允许”下拉列表中选择整数/小数- 确定最大/小值 设置单元格有效范围：“设置”选项卡中“允许”

4、下拉列表中选择序列输入序列值 设置输入提示信息： “输入信息”选项卡中输入要显示的信息,7.2 数据审核及跟踪分析,例：见ch7.xls限定数据范围 某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。 限制学号为8位字符，不能小于8位，也不能多于8位。 限制所有学科成绩为0100之间的整数。 限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。,7.2 数据审核及跟踪分析,4、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规则的错误单元格。 操作方法：（选择数据区域设置数据有效性规则）选择“工具”菜单“审核”选择“显示审核工具栏”选中有效性检测的数据区域单击“审核”工具栏的“圈释无效数据”按钮 注：要先设置数据的有效范围，然后再圈释无效数据 例：见ch7.xls圈释无效数据 某班要建立一个成绩登记表，已经对成绩表中数据的输入类型及范围进行限制，找出其中不符合规定的数据。,目录,7.3 模拟运算表,1、概念 模拟运算表是对工作表中一个单元格区

5、域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。 2、模拟运算表的类型 基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；单模拟运算表 基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响双模拟运算表,7.3 模拟运算表,3、单变量模拟运算表 概念 在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。 输入单元格，就是被替换的含有输入数据的单元格 操作步骤： 1、在工作表中建立模拟运算表的结构； 2、输入模拟运算表要用到的公式； 3、选择包括公式、引用单元格和运算结果单元格区域（3部分）； 4、选择“数据”菜单“模拟运算表”选项； 5、在“模拟运算表”对话框中输入引用单用格（行或列一种） 确定,7.3 模拟运算表,例：见ch7.xls单变量模拟运算表 假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。 若贷款额分别为400 000，550 000，800 000元，每月的应还贷金额又是多少？,

6、7.3 模拟运算表,4、双变量模拟运算表 概念： 单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，要查看两个变量对公式计算结果的影响，就要用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两个变量。公式中两个变量所在的单元格是任取的。可以是工作表中任意空白单元格。,7.3 模拟运算表,操作步骤： 1、在工作表中建立模拟运算表的结构； 2、在行列交叉处输入模拟运算表要用到的公式； 3、选择包括公式,引用单元格和运算结果单元格区域（3部分）； 4、选择“数据”菜单“模拟运算表”选项； 5、在“模拟运算表”对话框中输入公式中行和列引用的单用格确定 例：见ch7.xls双变量模拟运算表 假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少 ？,目录,7.4 单变量求解,1、概念 所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标

7、单元格中的目标值. 2、单变量求解方法 在工作表中输入原始数据； 建立可变数公式； 设置求解公式：菜单“工具”单变量求解对话框中输入：目标单元格、目标值、可变单元格 例： 见ch7.xls单变量求解 某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？ 如果公司每年可偿还120万元，该公司最多可贷款多少金额？ 前一问题可用PMT函数, 后一问题可用单变量求解。,目录,7.5 方案分析,1、概念 方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。 例: 已知某茶叶公司2004年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竞争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。 最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶的销售成本分别增长10%、6%、10%、7%。 见ch7.xls方案,7.5 方案分析,建立方案解决工作表,建立方法如下，输入下表A列、B列及第3行的

8、所有数据；在C4单元格中输入公式“=B4*(1+$B$16)”，然后将其复制到D4F4；在C7中输入公式“=B7*(1+$B$17)”,并将其复制到D7F7； 在C8中输入公式“=B8*(1+$B$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方案摘要 选择“工具” “方案”菜单选择“方案管理器”对话框中的某一方案单击 “总结”按钮在“方案总结”对话框中结果类型中选择“方案总结” 4、建立方案透视图见ch7.xls方案数据透视图 选择“工具” “方案”菜单选择“方案管理器”对话框中的某一方案单击 “总结”按钮在“方案总结”对话框中结果类

9、型中选择“方案数据透视表”,目录,7.6 线性规划求解,1、概述 EXCEL提供的规划求解工具，可求解出线性与非线性两种规划求解问题，规划求解问题常用于解决产品比例、人员调度、优化路线、调配材料等方面问题。 2、规划求解问题的特点： 问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时间等。 问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。 问题有直接或间接影响约束条件的一组输入值。,7.6 线性规划求解,3、Excel规划求解问题的组成部分 （1）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划求解的方案 （2）目标函数 目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数 （3）约束条件 约束条件是实现目标的限制条件。 意义：通过规划求解，用户可为工作表的目标单元格中的公式找到一个优化值，规划求解将直接或间接与目标单元格公式相联系的一组单元格数值进行调整，最终在目标单元格公式中求得期望的结果。,7.6 线性规划求解,例： 见ch7.xls规划求解 某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。 该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下页各表所示。 问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？ 分析:所求是在现有的原材料情况下,应如何合理搭配,才能获取生产产品的最大利润.,7.6 线性规划求解,表2 生产肥料的库存原材料,表1 各肥料成品用料及其价格表 表的意思是生产一个单位的肥料需要多少各种原材料多少单位,表3单位原材料成本单价,7.6 线性规划求解,建立规划求解模型步骤： 规划求解第一步建立求解工作表(输入原始数据及相应的各公式)

《excel与数据处理数据分析工具及应用》由会员san****019分享，可在线阅读，更多相关《excel与数据处理数据分析工具及应用》请在金锄头文库上搜索。