Excel操作技巧之查找和引用函数

Excel查找和引用函数在Excel中，可以用非常简便的方法，快速创建与其它数据库管理系统建立的类似关系性数据库表格，并可对这种数据库表格进行数据排序、筛选、数据透视、汇总分析等数据处理和数据分析操作。1数据库表格及其功能Excel中的数据库实际上就是工作表中的一个二维表。Excel数据库表格样式及其特点在建立和使用Excel数据库表格时，用户必须遵循以下的基本准则：一个数据库最好单独占据一个工作表，避免将多个数据库放到一个工作表上。数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开。避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同。Excel数据库表格遵循的准则避免将关键数据放到数据库左右两侧，防止数据筛选时这些数据被隐藏。字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别。条件区域不要放在数据库的数据区域下方。因为用记录单添加数据时，Excel会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。应用Excel数据库表格，可以进行简单的数据组织和管理工作，比如排序、筛选、分类、汇总、查询、数据透视等操作。在数据量不大，数据种类不多，企业规模不大时，用Excel进行数据的组织和管理功能会给数据处理工作带来许多方便，它能简化工作步骤，提高工作效率。另外，Excel具有相当强大的数据计算功能，提供了许多有用的函数和数据分析工具，如财务函数、统计函数、图表分析等，这些功能恰好是某些专业数据库较弱的地方。Excel的数据库功能及其局限性然而，Excel并不能取代真正意义上的数据库系统，也不能用于建立较为复杂的管理信息系统。在数据量很大，数据的种类较多，数据的关系比较复杂时，用户用它来建立数据库管理信息系统时，很难处理好数据之间的各种关系。Excel使用如下排序规则：数字按从最小的负数到最大的正数排序；字母按照英文字母A～Z和a～z的先后顺序排序。在对文本进行排序时，Excel从左到右一个字符一个字符地进行排序比较。特殊符号以及包含数字的文本，升序按如下排列：

0~9（空格）!"#$%&()*,./:;?@[\]^_`{|}~+<=>A~Za~z在逻辑值中，FALSE（相当于0）排在TRUE（相当于1）之前；所有错误值的优先级等效；空格总是排在最后；汉字的排序可以按笔画，也可按汉语拼音的字典顺序。2数据的排序按单个关键字排序就是根据数据表中某一列的内容进行排序，包括“升序”和“降序”两种方式。其功能实现的最好方法就是采用工具按钮法，操作时，只要将光标置于待排序的列中；然后单击常用工具栏上“升序”按钮或“降序”按钮即可。说明：按某一列数据作为关键字排序时，只需单击该列中任一单元格，而不用全选该列数据。单个关键字段排序所谓多关键字排序，就是对数据表中的数据按两个或两个以上的关键字段进行排序。进行多关键字的排序，可使数据在主要关键字段相同的情况下，再按次要关键字段排序；在主要关键字、次要关键字段都相同的情况下，数据仍可按第三关键字段有序。多关键字排序汉字与数值不不同，数值有有大小可比，，而汉字本身身没有。为了了处理的方便便，人们按照照一定的规则则（按照字母母顺序、按照照笔画顺序））确定汉字的的“大小”次次序。默认为按照字字母顺序，要要想按照笔画画顺序，可以以在对话框中中进行设置。。汉字进行按笔笔画排序如何实现排序序后仍能快速速返回原来的的顺序？常见的两种情情况：第一种情形：：操作只是刚刚刚发生过，，这很好办——直接按下【Ctrl+Z】撤消操作即可可；第二种情形：：如果原来的的表格本身已已经有了一个个排序索引字字段（如学号号、职工编号号，日期型数数），只要按按照原来那个个排序索引字字段再重新排排序一次即可可。但是，如果不不是上述两种种情况，则需需要采用引入辅助列的的方法通过以下步骤骤来实现。数据排序操作作的应用技巧巧如何快速删除除数据区域内内的空行？有时候，处于于数据分区显显示或者错误误操作等原因因，数据区域域中可能包含含很多空行。。这些空行的的存在，会影影响到对数据据的分析。对对这些空行如如果一行一行行地操作，非非常费时。利用“数据排排序时，空格格总是排在最最后”这一排排序规则，则则可以通过数数据排序的方方法删除数据据区域中的所所有空行，并并且不打乱数数据区域中各各行数据的顺顺序。RANK函数及其应用用RANK函数用来返回回一个数值在在一组数值中中的排位，其其语法格式为为：RANK(number,ref,order)该函数共包括括三个参数，，其中：Number为需要找到排排位的数字；；Ref为包含一组数数字的数组或或引用；Order为一数字，指指明排位的方方式，为0或省略，按降降序排列排位位，不为零，，按升序排列列进行排位。。排序函数应用用如何快速给一一组数据记录录填上排位序序号？方法一：给数数据排序，然然后使用数据据填充方法方法二：使用用Rank()函数如何取得一个个数据集中的的第K个最大值？LARGE和SMALL函数及其应用用LARGE函数和SMALL函数的功能分分别是用来返返回一个数据据集中第k个最大值和第第k个最小值语法格式分别别为：LARGE(array,k)::Large(c2:c20,5)SMALL(array,k)::Small(c2:c20,5)当数据库表格格制作好之后后，有时还需需要根据指定定条件从众多多数据中筛选选特定的记录录。Excel中提供了两种种筛选方法：：“自动筛选选”和“高级级筛选”，它它们可以将那那些符合条件件的记录显示示在工作表中中，而将其他他不满足条件件的记录隐藏藏起来；或者者将筛选出来来的记录送到到指定位置存存放，而原数数据表不动。。3数据的筛选选执行“数据据”/“筛选”/“自动筛选””命令，使使“自动筛筛选”项为为选中状态态（打上对对号），即即可进行自自动筛选。。自动筛选自动筛选非非常方便，，通过它可可以实现以以下的筛选选操作：对某一字段段筛选出符符合某特定定值的记录录——单击需要筛筛选字段的的筛选器箭箭头，从下下拉菜单中中直接选择择某特定值值即可。例如：“筛筛选出学历历为硕士的的记录”对同一字段段进行“与与”运算和和“或”运运算——单击需要筛筛选字段的的筛选器箭箭头，从下下拉菜单中中选择“自自定义”，，在弹出对对话框中进进行设置即即可。例如：“筛筛选出学历历为硕士或或博士的记记录“””筛筛选出毕业业年份在2002-2004之间的记录录”对不同字段段之间进行行“与”运运算——只要通过多多次进行自自动筛选即即可。例如：“筛筛选出学历历为硕士或或专业为会会计的记录录”可以筛选出出最大／最最小的若干干个／若干干百分比））记录——只要单击需需要筛选字字段的筛选选器箭头，，从下拉菜菜单中选择择“前10个””即可可。例如：“筛筛选出年龄龄最大的5个记录”但是，自动动筛选无法法实现多个个字段之间间的“或””运算，这这时就需要要使用高级级筛选使用高级筛筛选的注意意事项：高级筛选必必须指定一一个条件区区域。如果“条件件区域”与与数据库表表格在一张张工作表上上，筛选前前，确定光光标放置到到数据库中中某一单元元格上。执行“将筛筛选结果复复制到其他他位置”时时，在“复复制到”文文本框中输输入或选取取将来要放放置位置的的左上角单单元格即可可，不要指指定某区域域。条件区域可可以定义多多个条件，，以便用来来筛选符合合多个条件件的记录。。高级筛选条件区域的的设置实例例及其图形形化解释高级筛选条条件区域中中可以使用用通配符““*”和““？”。查找姓王的的人员信息息？查找姓名中中包含有‘‘学’字的的人员信息息？查找1980年10月1日前出生的的人员记录录？查找1980年出生的人人员信息？？在高级筛选选的条件区区域中，可可以将公式式的计算结结果作为条条件使用。。例如，找找出成绩表表中高于平平均分的学学生记录，，找出工资资表中高于于或低于平平均收入的的职工档案案，找出人人员档案表表中超过平平均年龄10岁以上的人人员的信息息等。此时时，因为平平均成绩、、平均收入入和平均年年龄都不是是一个常数数条件，它它们全部都都是需要根根据工作表表计算的结结果。在Excel中，可以创创建计算条条件的条件件区域，用用计算条件件进行高级级筛选。将公式结果果用于高级级筛选的条条件区域当筛选的条条件不是一一个常数，，而是随数数据清单中中数据变化化的计算结结果，此时时无法直接接利用高级级筛选进行行数据筛选选。通过计计算条件可可以解决此此种情况。。计算条件的的条件区域域设置规则则：字段名行为为空条件行中输输入包含有有公式或函函数的条件件查找3月份出生的的人员记录录?E1单元格无内内容在E2单元格输入入公式：=month(d10)=3条件区域为为E1:E2查找笔试成成绩大于平平均笔试成成绩的人员员信息?E1单元格不输输入内容E2单元格输入入公式：=K10>average($k$10:$k$39)条件区域为为:E1:E2认识工作表表区域单元格引用用方式相对引用:A1绝对引用:$A$1混合引用:$A1,A$1单元元格格引引用用的的分分类类一维维引引用用：：A1:A10、A2:G2二维维引引用用：：A1:C5三维维引引用用：：Sheet1:sheet3!A1基础础概概念念-引用用数组组的的概概念念由文文本本、、数数值值、、日日期期、、逻逻辑辑、、错错误误值值等等元元素素组组成成的的集集合合，，行行方方向向；；列列方方向向常量量数数组组和和内内存存数数组组数组组的的分分类类一维维数数组组：：行行数数组组（（垂垂直直数数组组））和和列列数数组组(水平平数数组组））{1，2，3，4，5}或{””A””;””B””;””C””;””D””;””E””}二维维数数组组：：多多行行多多列列数数组组{0，””差差””；；60，““中中””；；80,””良””；；90，””优优””}基础础概概念念-数组组Offset函数数将源源引引用用根根据据指指定定的的行行，，列列偏偏移移来来产产生生新新的的单单元元格格引引用用Offset函数数语语法法Offset(基点点，，行行数数，，列列数数，，[高度度],[宽度度])函数数特特征征“引引用用基基点点””只只能能是是单单元元格格引引用用，，而而不不能能使使数数组组[高度度],[宽度度]用于于指指定定目目标标的的单单元元格格区区域域大大小小，，如如果果不不指指定定，，则则目目标标引引用用尺尺寸寸与与源源引引用用相相同同[高度度],[宽度度]允许许用用户户使使用用负负数数作作为为参参数数Offset函数数将A5区域域进进行行偏偏移移后后产产生生的的新新的的C7:E11公式式：：=Offset(A5,2,2,5,3)=C7:E11Offset函数数图图例例Match函数数：：返返回回查查找找值值在在范范围围中中的的位位置置序序号号Match函数数的的灵灵活活性性比比LOOKUP（包包括括VLOOKUP、HLOOKUP）更更强强，，它它可可以以在在工工作作表表的的一一行行（（或或一一列列））中中进进行行数数据据查查询询，，并并返返回回数数据据在在行行（（或或列列））中中的的位位置置。。如果果需需要要找找出出数数据据在在某某行行（（或或某某列列））的的位位置置而而不不是是数数据据本本身身，，则则应应该该使使用用Match函数数。Match函数数的的语语法法格格式式如如下下：：Match（lookup_value，lookup_array，match_type）lookup_value为需需要要在在Look_array中查查找找的的数数值值。。lookup_array为可可能能包包含含所所要要查查找找数数值值的的区区域域Match函数数match_type指明明了了查查找找方方式式，，其其取取值值可可以以为为数数字字-1，0或1当match_type为1，函函数数MATCH查找找小小于于或或等等于于lookup_value的最最大大数数值值。。Lookup_array必须须按按升升序序排排列列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。若match_type为0，函数MATCH查找等于于lookup_value的第一个个数值。。Lookup_array可以按任任何顺序序排列。如果match_type为-1，函数MATCH查找大于于或等于于lookup_value的最小数数值。Lookup_array必须按降降序排列列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。。如果省略略match_type，则假设设为1。查看“BO1”在数据序序列中A4:A9的位置公式为：：=Match(F3,A4:A9,0)Match函数图例例Index函数返回指定定的行与与列交叉叉处的单单元格引引用。语法：Index（reference，row_num，column_num，area_num）reference是对一个个或多个个单元格格区域的的引用。。row_num为引用中中某行的的行序号号，函数数从该行行返回一一个引用用。column_num为引用中中某列的的列序号号，函数数从该列列返回一一个引用用。area_num选择引用用中的一一个区域域，并返返回该区区域中row_num和column_num的交叉区区域。注意事项项通常情况况下，行行号、列列号参数数不支持持使用数数组Index函数返回指定定的行与与列交叉叉处的单单元格引引用公式：=INDEX((A4:C9,E4:F8),2,3,1)返回值Index函数图例例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为小数，则在在使用前将被被截尾取整。。Choose函数Lookup函数可以返回回向量（单行行区域或单列列区域）或数数组中的数值值。此系列函函数用于在表表格或数值数数组的首行查查找指定的数数值，并由此此返回表格或或数组当前列列中指定行处处的数值。当当比较值位于于数据表的首首行，并且要要查找下面给给定行中的数数据时，使用用函数HLookup。当比较值位位于要进行数数据查找的左左边一列时，，使用函数VLookupLookup函数函数Lookup的向量形式是是在单行区域域或单列区域域（向量）中中查找数值，，然后返回第第二个单行区区域或单列区区域中相同位位置的数值。。基本语法形式式为：Lookup(lookup_value,lookup_vector,result_vector)Lookup_value为函数LOOKUP在第一个向量量中所要查找找的数值。Lookup_value可以为数字、、文本、逻辑辑值或包含数数值的名称或或引用。Lookup_vector为只包含一行行或一列的区区域。Lookup_vector的数值可以为为文本、数字字或逻辑值。。

需要注意的是Lookup_vector的数值必须按按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数数LOOKUP不能返回正确确的结果。文文本不区分大大小写。Lookup函数Result_vector只包含一行或或一列的区域域，其大小必必须与lookup_vector相同。如如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于于lookup_value的最大数值。。

如果lookup_value小于lookup_vector中的最小值，，函数LOOKUP返回错误值#N/A。LOOKUP的数组形式在在数组的第一一行或第一列列查找指