作为一个电子表格处理程序,EXCEL除了具有强大的计算和汇总功能外,其内嵌的各类功能函数在进行表格处理中给我们提供了极大的便利,可以极高的提高工作效率和准确性。

    下面介绍几个在实际工作中可能会经常用到的查函数:MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查函数不仅仅具有查对的功能,同时还能根据查的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用,并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。

1.MATCH()
格式:MATCH(lookup_value,lookup_array,match_type)
从英文含义我们可以看出,它是一个匹配的函数,主要功能是按照match_type参数所指定的查方式,在lookup_array参数所代表的数据组中查lookup_value参数代表的数值,如果查成功则返回lookup_value在lookup_array中的位置,不成功返回错误信息#N/A。

EXCEL在“编辑”菜单中提供的“查”功能只能进行单一数值的查,而MATCH()函数可以实现批
量数值的查,所需要的只是在需要查的首个单元格中定义好该函数,然后拖动该单元格直至到需要的范围,所有需要查对单元格的查结果会自动完成。

参数解释:
lookup_value——需要查的值,可以是实际的数字、字符串或逻辑值,也可以是某一单元格的引用,通常使用单元格的引用;
lookup_array——查范围,可以是定义的数组,也可以是自定义的多个连续单元格的引用,这个引用我们可以通过两种方式实现:

? 直接输入连续单元格的地址,通常使用绝对引用地址,如$B$2:$B$50;

? 选定一个连续的单元格区域后,通过“插入”excel 查字符串函数菜单中“定义”项的“名称”给这段单元格区域定义一个名称,如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。

match_type,查的方式,其值为-1、0、1,默认为1。其中-1是在lookup_array查大于或等于
lookup_value的最小数值,要求lookup_array必须按降序排列;0是在lookup_array查等于lookup_value的第一个数值,不需要排序;1是在lookup_array查小于或等于lookup_value的最大数值,要求lookup_array必须按升序排列。

函数举例:
例1:有两个工作簿 BOOK1和BOOK2,它们的SHEET1中都具有名为“卡片号”的列,列标为B(假设BOOK1中该列包含数据的单元格为B2:B80,BOOK2中包含数据的单元格为B2:B1000),想查对一下BOOK1中的卡片号是否能够在BOOK2中的卡片号中全部查到。
按照以上的需求我们可以进行如下的操作:

(1)定义引用的名称,拖动鼠标选中BOOK2中B2:B1000单元格,通过“插入”菜单的“定义”“名称”项,给其定义为NAME1;

(2)在BOOK1中插入一空列,假定列标为H,并在H2单元格中输入如下的公式:
=MATCH(B2,“BOOK2.XLS”!NAME1,0);


(3)选中H2单元格,将鼠标指针移动至该单元格右下角的黑点处,此时指针变为小黑十字形状,按住左键,拖动鼠标至H80单元格,此时H2到H80单元格会自动显示出B2至B80在BOOK2中定义的NAME1所代表的单元格区域的位置(如果到),或是#N/A(未到);

(4)通过“数据”菜单的“筛选”“自动筛选”功能项,可以查看所有匹配的项(H列的数值大于0),或是所有不匹配的项(H列的数值为#N/A)。
以上1、2步骤可以合并为直接在H2单元格中输入如下的函数:
=MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0)
需要注意的是,一定要使用$B$2:$B$1000,不能使用B2:B1000,否则在拖动鼠标时,其单元格范围会随着发生变化,产生错误的结果。另外在引用外部工作簿时,如果定义了引用名称,书写格式为:“工作簿名称”!引用名称,如“BOOK2.XLS”!NAME1;如果使用表格区域引用,格式为:[工作簿名称]工作表名称!表格区域,如[BOOK2.XLS]SHEET1!$B$2:$B$1000。

2.LOOKUP()、HLOOKUP()、VLOOKUP()
LOOKUP()、HLOOKUP()、VLOOKUP()函数的功能都是在数组或表格中查指定的数值,并按照函数参数设定得值返回表格或数组当前列(行)中指定行(列)处的数值。

由于LOOKUP()函数在单行(列)区域查数值,并返回第二个单行(列)区域中相同位置的数值,或是在数组的第一行(列)中查数值,返回最后一行(列)相同位置处的数值,其适用范围具有比较大的局限性,在实际的应用中,通常使用更加灵活的HLOOKUP()和VLOOKUP()函数。

HLOOKUP()和VLOOKUP()的作用类似,其区别是HLOOKUP()在表格或数组的首行查数值,返回表格或数组当前列中指定行的数值,而VLOOKUP()是在表格或数组的首列查数值,并返回表格或数组当前行中指定列的数值。这里所说的表格是按单元格地址设定的一个表格区域,如A2:E8。

HLOOKUP()函数的格式如下:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
参数解释:
lookup_value——需要在表格或数组第一行中查的数值,可以是数值、字符串或引用;
table_array——需要在其中查数值的表格区域、数组或是表格区域的引用;
row_index_num——为 table_array 中待返回的匹配值的行序号;
range_lookup——为一逻辑值,为TRUE或省略该值时,要求table_array第一行的数据必须升序排列,否则会得到错误的结果,同时表示待查内容与查内容近似匹配就可以了,如果不能精确匹配的话,则函数返回小于lookup_value的最大数值;如果为FALSE,不需要table_array的数值进行排序,并要求精确匹配,如果没有到则函数返回#N/A。
VLOOKUP()函数的格式如下:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
函数的参数中除了col_index_num表示table_array 中待返回的匹配值的列序号外,其他参数的意义和HLOOKUP()相同。

函数举例:
例2:在BOOK2中,需要根据每一行中的资产类别(列标为D),自动的计算出该资产的折旧年限。
我们可以进行如下的操作:
(1)由于在现有的表格当中不存在资产类别和折旧年限的对应关系,因此首先要根据资产分类的情况构造出一个数组,数组的第一行为资产的类别名称,第二行为资产所对应的折旧年限,{“类别1”,“类别2”,“类别3”,“类别4”,“类别5”;4,6,7,8,10};

(2)在BOOK2的SHEET1中插入一空列,列标为K,在K2单元格中输入如下的公式:
=HLOOKUP(D2, {“类别1”,“类别2”,“类别3”,“类别4”,“类别5”;4,6,7,8,10},2,FALSE),

(3)选中K2单元格,将鼠标指针移动至该单元格右下角的黑点处,指针变为小黑十字形状时,按住左键,拖动鼠标至K80单元格,折旧年限会自动地显示出来。

例3:按照BOOK1 中SHEET1的卡片号(列标为B)从BOOK2中SHEET1的型号列(列标为E)获取与该卡片号对应的内容,从而在BOOK1的SHEET1中自动的生成一个新列(列标为U)。
解决方法
(1)由于是在列中查匹配的值,需要返回当前行制定列的值,因此适宜使用VLOOKUP()函数,确定需要查的数值为从BOOK1/SHEET1的B2到B80,待查的范围为BOOK2/SHEET1的B
2:E1000,返回匹配值的列序号为4。

(2)在BOOK1/SHEET1的U2单元格,输入如下的公式:
=VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$E$1000,4,FALSE)

(3)选中U2单元格,将鼠标指针移动至该单元格右下角的黑点处,指针变为小黑十字形状时,按住左键,拖动鼠标至U80单元格,对应的型号会在U列中显示出来。

3.IF()
格式:IF(logical_test,value_if_true,value_if_false)
IF函数的作用是根据逻辑判断的结果返回相应的值
参数解释:
logical_test——逻辑表达式,
value_if_true——结果为TRUE时,函数返回的值;
value_if_false——结果为FALSE时,函数返回的值;
value_if_true和 value_if_false都可以是其他的公式,也可以是IF函数的嵌套进行多级判断,嵌套不能超过7层。
当逻辑表达式包含有函数的时候,可以使用ISERROR()函数来判断函数返回的结果是否是错误值(包括#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!)。
ISERROR()函数的格式为ISERROR(value),其中参数value为要检测的值。
函数举例:
例4:在例1的基础上,如果到卡片号,则在BOOK1的SHEET1的N列的相应行处显示“相符”,否则显示“未发现”。
解决办法:
在BOOK1的SHEET1的N2单元格处输入如下的公式:
=IF(ISERROR(MATCH(B2,“BOOK1.XLS”!NAME1,0))=FALSE,“相符”,“未发现”)

在实际的工作中,可能会先判断一下某个单元格中的数值(数字、文本或引用)在另外的一个工作簿中是否存在相应的匹配数值,如果到匹配值则进行下一步的计算,如果没有可能要以某些值代替。在这种情况下,我们就可以配合使用多个函数来实现这个目的。下面我们通过一个
例子进行说明。

例5:从BOOK2/SHEET1中查与BOOK1/SHEET1的卡片号相匹配的行,并将该行中“购置日期”列(列标为F)的数值显示在BOOK1/SHEET1的G列中相应的单元格中,要求如果没有到匹配的数值,则在BOOK1/SHEET1的G列的相应单元格中填写“1996/1/1”。
我们可以进行如下的操作:
(1)在G2单元格中输入如下的公式:
=IF(ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))=FALSE,VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE),“1996/1/1”)
(2)选中G2单元格,将鼠标指针移动至该单元格右下角的黑点处,指针变为小黑十字形状时,按住左键,拖动鼠标至G80单元格,对应的购置日期会在G列中显示出来。
在上述公式中, ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))作为一个逻辑表达式,判断是否在BOOK2/SHEET1的B列中到匹配的数值,如果结果为FALSE,表明到匹配的值,进而IF()函数计算VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE),
也就是将BOOK2/SHEET1中相匹配的行中F列的内容赋值给G2;若为TRUE,表示没有到,G2的值为1996/1/1,以此类推。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。