《EXCEL数据处理》-第08章.ppt

第8章 数据的查询与核对 EXCEL数据处理 电子教案 中国水利水电出版社 2 本章知识点 利用查找命令在工作表中查询数据掌握主要查询函数的作用及其使用方法利用LOOKUP VLOOKUP HLOOKUP函数查询数据利用CHOOSE MATCH INDEX函数查询数据应用INDIRECT和名称构造实用的数据查询利用OFFSET函数对动态区域进行数据查询掌握利用DGET函数和记录单进行数据库数据查询利用数组公式构造功能强大的统计查询掌握数据表之间数据核对的三种不同方法 EXCEL数据处理 电子教案 中国水利水电出版社 3 本章目录 8 1利用查找命令查询数据8 2利用查询与引用函数进行查询8 3数据库表格中的数据查询8 4利用名称查询数据8 5利用数组公式进行查询8 6数据表之间的数据核对小结习题 EXCEL数据处理 电子教案 中国水利水电出版社 4 8 1利用查找命令查询数据 8 1 1用 查找 命令进行数据查找的操作步骤 EXCEL数据处理 电子教案 中国水利水电出版社 5 8 1 2在 查找 命令中利用通配符进行数据查找 如果对查找内容不清楚 或需要查找含有相近单并不是相同的文本的记录 在 查找 命令的搜索条件中可使用通配符 操作时 用 表示一个任意字符 用 表示多个任意字符 EXCEL数据处理 电子教案 中国水利水电出版社 6 8 2利用查询与引用函数进行查询 利用上一节介绍的利用查询命令进行数据和文本查询的方法 只是将指定关键字的位置查询出来 但是很多情况下 除了需要将指定关键字的位置查询出来之外 还需要将关键字所在行或所在列的数据都查找出来 另外 在查询中不仅要从工作表的某个单元格区域查找 还可能需要从工作表的多个单元格区域甚至多个工作表乃至多个工作簿中查找 EXCEL数据处理 电子教案 中国水利水电出版社 7 8 2 1使用CHOOSE函数从值的列表中选择一个值 CHOOSE函数的功能是从值的列表中选择一个值 它有点类似于计算机程序语言中的分情况选择语句 使用该函数可以返回多达29个基于给定待选数值中的任一数值 CHOOSE函数的语法格式如下 CHOOSE index num value1 value2 其中 参数index num用以指明待选参数序号的参数值 它必须为1到29之间的数字 或者是包含数字1到29的公式或单元格引用 如果index num为1 函数CHOOSE返回value1 如果为2 函数CHOOSE返回value2 以此类推 如果index num小于1或大于列表中最后一个值的序号 函数CHOOSE返回错误值 VALUE 如果index num为小数 则在使用前将被截尾取整 EXCEL数据处理 电子教案 中国水利水电出版社 8 CHOOSE函数应用实例1 礼品发放 EXCEL数据处理 电子教案 中国水利水电出版社 9 CHOOSE函数应用实例2 销售提成 EXCEL数据处理 电子教案 中国水利水电出版社 10 8 2 2用VLOOKUP和HLOOKUP函数进行表格查询 1 VLOOKUP函数的功能与语法格式VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值 并且由此返回表格或数组当前行中指定列处的数值 VLOOKUP函数的语法格式如下 VLOOKUP lookup value table array col index num range lookup 其中 lookup value是需要在数据表第一列中查找的数值 table array是需要在其中查找数据的数据表 col index num是table array中待返回的匹配值的列序号 range lookup为一个逻辑值 指明VLOOKUP 函数返回时 是 精确匹配 还是 近似匹配 如果其取值为 TRUE 或省略 则返回近似匹配值 如果为 FALSE 将返回精确匹配值 如果找不到 则返回错误值 N A EXCEL数据处理 电子教案 中国水利水电出版社 11 VLOOKUP函数的一个简单应用 EXCEL数据处理 电子教案 中国水利水电出版社 12 VLOOKUP应用实例 员工情况查询表 EXCEL数据处理 电子教案 中国水利水电出版社 13 8 2 3用MATCH和INDEX函数构造灵活的查询 1 MATCH函数的功能与语法格式MATCH函数的灵活性比LOOKUP 包括VLOOKUP HLOOKUP 更强 它可以在工作表的一行 或一列 中进行数据查询 并返回数据在行 或列 中的位置 如果需要找出数据在某行 或某列 的位置而不是数据本身 则应该使用MATCH函数 MATCH函数的语法格式如下 MATCH lookup value lookup array match type 可以看出 该函数包含三个参数 其中 lookup value为需要在Look array中查找的数值 lookup array为可能包含所要查找数值的区域 match type指名了查找方式 其取值可以为数字 1 0或1 EXCEL数据处理 电子教案 中国水利水电出版社 14 2 INDEX函数的功能与语法格式 INDEX函数返回指定的行与列交叉处的单元格引用 如果引用由不连续的选定区域组成 可以选择某一连续区域 函数INDEX有两种语法形式 数组形式和引用形式 数组形式通常返回数值或数值数组 引用形式通常返回引用 一般情况下应用引用形式的比较多 引用形式的语法格式如下 INDEX reference row num column num area num 其中各参数解释如下 reference是对一个或多个单元格区域的引用 row num为引用中某行的行序号 函数从该行返回一个引用 column num为引用中某列的列序号 函数从该列返回一个引用 area num选择引用中的一个区域 并返回该区域中row num和column num的交叉区域 EXCEL数据处理 电子教案 中国水利水电出版社 15 3 MATCH函数和INDEX函数构造查询实例1 行列交叉查询 EXCEL数据处理 电子教案 中国水利水电出版社 16 4 MATCH函数和INDEX函数构造查询实例2 根据两列以上数据查询 EXCEL数据处理 电子教案 中国水利水电出版社 17 8 2 4利用INDIRECT函数和名称查询其他工作表中的数据 1 INDIRECT函数功能与语法格式INDIRECT函数返回由文本字符串指定的引用 该函数能够对引用进行计算 并显示其内容 当需要更改公式中单元格的引用 而不更改公式本身 可使用该函数 INDIRECT函数的语法格式如下 INDIRECT ref text a1 可以看出 该函数包含两个参数 其中 Ref text为对单元格的引用 它可以是包含A1样式或R1C1样式的引用 定义为引用的名称或对文本字符串单元格 如 G2 B 5 的引用 如果ref text不是合法的单元格的引用 函数INDIRECT返回错误值 REF a1为一逻辑值 指明包含在单元格ref text中的引用的类型 如果a1为TRUE或省略 ref text被解释为A1样式的引用 如果a1为FALSE 则被解释为R1C1样式的引用 EXCEL数据处理 电子教案 中国水利水电出版社 18 INDIRECT函数的一些简单应用 EXCEL数据处理 电子教案 中国水利水电出版社 19 2 INDIRECT函数应用实例 查找基本工资 EXCEL数据处理 电子教案 中国水利水电出版社 20 8 2 5用OFFSET函数进行动态数据区域查询 1 OFFSET函数功能与语法格式OFFSET函数可以对动态数据进行查询 它以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 其语法格式如下 OFFSET reference rows cols height width 可以看出 该函数最多包含五个参数 后两个参数为可选项 其中 Reference作为偏移量参照系的引用区域 它必须为对单元格或相连单元格区域的引用 否则 函数OFFSET返回错误值 VALUE Rows为相对于偏移量参照系左上角单元格 上 下 偏移的行数 Cols为相对于偏移量参照系左上角单元格 左 右 偏移的列数 Heigh为高度 即所要返回的引用区域的行数 Width为宽度 即所要返回的引用区域的列数 EXCEL数据处理 电子教案 中国水利水电出版社 21 2 OFFSET函数应用实例 根据月份查询汇总数据 EXCEL数据处理 电子教案 中国水利水电出版社 22 EXCEL数据处理 电子教案 中国水利水电出版社 23 8 3数据库表格中的数据查询 8 3 1利用记录单进行数据查询8 3 2利用数据库函数查询数据 EXCEL数据处理 电子教案 中国水利水电出版社 24 8 4利用名称查询数据 EXCEL数据处理 电子教案 中国水利水电出版社 25 8 5利用数组公式进行查询 EXCEL数据处理 电子教案 中国水利水电出版社 26 8 6数据表之间的数据核对 8 6 1利用查询函数进行数据核对8 6 2利用条件格式化进行数据核对8 6 4利用合并计算法进行数据核对 EXCEL数据处理 电子教案 中国水利水电出版社 27 小结 对数据进行汇总可以通过有关汇总函数进行 其中SUM函数可以进行无条件汇总求和 但是当要进行一些满足指定条件的数据汇总时 就需要依靠SUMIF SUMPRODUCT DSUM或者联合SUM函数和IF函数 分类汇总功能可以方便地实现数据库表格按照指定的关键字进行分类汇总 根据需要还可以进行多重分类汇总和嵌套分类汇总 另外 对于管理中特定场合下的数据汇总 读者也要熟练操作 例如累加汇总 动态区域汇总 若干个最大 小 数值汇总 不连续区域汇总以及当数据区域中存在错误时如何进行汇总等 数据的多表合并可以通过三维引用 利用SUM函数来实现 另外 Excel还专门提供了 合并计算 功能 根据所用数据区域不同 它有按位置合并和按分类合并两种方案 数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表 而数据透视图是形象生动的图表 还可以制作不同格式的数据透视报告 通过本章学习 读者应能熟练地进行数据汇总 多表合并和数据透视 EXCEL数据处理 电子教案 中国水