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函数进行表格查询,1VLOOKUP函数的功能与语法格式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函数构造灵活的查询,1MATCH函数的功能与语法格式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,2INDEX函数的功能与语法格式,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函数和名称查询其他工作表中的数据,1INDIRECT函数功能与语法格式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,2INDIRECT函数应用实例：查找基本工资,EXCEL数据处理电子教案,中国水利水电出版社,20,8.2.5用OFFSET函数进行动态数据区域查询,1OFFSET函数功能与语法格式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数据处理电子教案,中国水利水电出版社,28,习 题,1请比较SUMIF、SUMPRODUCT和DSUM三个汇总函数在使用上不同之处。2什么是多重分类汇总？什么是嵌套汇总？二者有什么相同点和不同点？3在Excel中，多个数据表之间的“合并计算”包括按位置合并和按分类合两种方案，请说明这两种方案的不同应用场合以及操作时候需要注意的问题。4数据透视表具有什么用途？如何对数据透视表进行更新？5上机自行将本章的所有实例操作一遍68 上机练习习题-具体要求详见教材和本书素材,
