第9章数据分析工具的用excel2007版教材.ppt

本章学习目标 1、了解加载宏的功能和用法 2、掌握数据审核的方法 3、掌握模拟运算表的建立方法 4、掌握单变量求解的方法 5、掌握方案建立和应用方法 6、掌握规划求解的方法 7、掌握分析工具库中的常用工具 9.1 数据审核及跟踪分析 1、概念 数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格。 数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。 2、数据审核的方式 追踪引用单元格 追踪从属单元格 9.1.1追踪引用单元格 引用单元格 是指被其他单元格中的公式引用的单元格，即为指定公式提供数据的单元格。 当使用“追踪引用单元格”工具时，Excel会用蓝色追踪箭头指明活动工作表中为公式提供数据的单元格。如果选定单元格引用了其他工作表或工作簿中的单元格，Excel会用黑色追踪箭头从工作表图标指向选定的单元格。 【例9.1】某商场的销售记录和销售汇总表如图所示。查看销售汇总表中各数据的来源是否正确。 9.1.1追踪引用单元格 9.1.2追踪从属单元格 从属单元格 如果某个单元格中的公式引用了其它单元格，那么此单元格就称为从属单元格。 当公式中所引用单元格的值发生变化时，公式所在单元格（从属单元格）中的值也会随之变化。例如，若B3单元格中包含公式“=A3+A4”，则B3就是A3和A4的从属单元格。当A3或A4单元格的值发生变化时，B3单元格的值就会发生变化。 9.1.1追踪引用单元格 9.1.3 数据有效性检验 数据有效性 数据有效性检验是Excel为减少错误、核查数据正确性而提供的一种数据检验工具。利用该工具可以设置单元格数据输入的类型和范围，对错误的输入数据进行告警，并拒不接受，把错误限制在输入阶段。此外，它还能对已经完成的数据表设置有效性检验规则，并据此标识出其中的错误数据。 案例 【例9.2】某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。 限制学号为8位字符，不能小于8位，也不能多于8位。 限制所有学科成绩为0~100之间的整数。 限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。 9.1.3 数据有效性检验 成绩表示例 9.1.3 数据有效性检验 限制方法 单击“数据”选项卡中的“数据有效性”按钮 9.1.3 数据有效性检验 圈释无效数据 9.1.3 数据有效性检验 设置输入提示信息和错误警告信息 这些信息都需要通过“数据有效性”对话框进行设置。 9.2 模拟运算表 1、概念 模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。 2、模拟运算表的类型 ①基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；——单模拟运算表 ②基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响——双模拟运算表 9.2.1 单变量模拟运算表 单变量模拟运算表概念 在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。所谓输入单元格，就是被替换的含有输入数据的单元格 案例 【例9.3】假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。 9.2.1 单变量模拟运算表 9.2.1 单变量模拟运算表 【例9.4】对于例9.3而言，如果要查看在同等利息情况下，分别贷款￥250 000，￥400 000，￥550 000，￥800 000的每月还贷金额，则可建立如图9.5所示的模拟运算表。 9.2.2 双变量模拟运算表 单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，如果想查看两个变量对公式计算的影响就需要使用双变量模拟运算表。 案例 假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少 9.2.2 双变量模拟运算表 9.3 单变量求解 1、概念 所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值. 案例 【例9.6】某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？如果公司每年可偿还120万元，该公司最多可贷款多少金额？ 9.3 单变量求解 2、单变量求解方法 （1）建立求解公式： 9.4 方案分析 1、概念 方案是已命名