EXCEL中查找函数的妙用

EXCEL中查找函数的妙用（巨好用的公式）

作为一个电子表格处理程序,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;

? 选定一个连续的单元格区域后,通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称,如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),自动的计算出该资产的折旧年限。

我们可以进行如下的操作: