首页 > Excel专区 > Excel教程 >

理解VLOOKUP函数第4个参数的真实含义

Excel教程 2022-01-06 21:10:59

VLOOKUP函数是大家最喜欢使用的函数之一,能够帮助我们实现很多任务。这里,让我们回过头来从细节处着手,再次深入探究VLOOKUP函数,进一步熟练掌握这个常用的函数。

VLOOKUP函数有4个参数,其中第4个参数是可选参数,如果我们在公式中省略这个参数,有时会得到错误的结果。如图1所示,在单元格区域A2:B6中查找编号为“2010”的名称,结果是“洗衣机”,很显然是错误的,应该是“冰箱”。

图1

如果将编号排序,就会得到正确的结果“冰箱”,如图2所示。

图2

在VLOOKUP函数中,虽然第4个参数可省略,但省略后其默认为TRUE,即近似匹配,要求所查找的数据必须按升序排列,否则就会得出意想不到的结果,如图1所示。

如果查找的数据列没有按升序排列,则应显式指定第4个参数值为FALSE,即精确匹配,才能得到正确的结果,如图3所示。

图3

因此,当VLOOKUP函数的第4个参数被忽略或者为TRUE时,将执行近似查找,所查找的数据列必须要按升序排列,以获得正确的结果。反之,如果所查找的数据列没有排序,那么VLOOKUP函数的第4个参数应设置为FALSE,执行精确查找。

下面再进一步探究。

如图4是一个收入—税率表。

图4

从图4中可以看出,如果某员工的收入是5000元,那么他要交税的税率为5%。如果另一位员工的收入是18000,他就要交税率为15%的税。

就上述两种情形来说,图4的表中没有一个精确的数字和实际收入相匹配,只能查找到实际收入介于的区间,从而找到相应的税率。这就是VLOOKUP函数第4个参数的真实含义,这也是为什么该参数的英文名为“range_lookup”。

因此,当VLOOKUP函数的第4个参数是TRUE或者被忽略时,告诉Excel去执行一个区间查找。进一步说,就是查找图4列A中的值,该值要等于或者小于但最接近所查找的值。因此,图4中的查找表可以简化为下图5.

图5

此时,使用第4个参数为TRUE或者忽略的VLOOKUP函数查找时,当查找的值大于等于0且小于3000时,返回税率为0;当查找的值大于等于3000且小于8000时,返回税率5%;依此类推。

这也是为什么查找列的数据必须要按照升序排列的原因。

示例1:基本的查找

如图6所示,要求查找收入为10000元的员工要交税的税率,公式为:

=VLOOKUP(A10,A2:B6,2,TRUE)

图6

示例2:查找日期

VLOOKUP函数能够查找最接近的日期,如图7所示。在单元格E2中输入公式:

=VLOOKUP(D2,$A$2:$B$4,2,TRUE)

向下拉至单元格E6。

图7

示例3:单列查找

查找表可以仅有1列,从这1列中查找并返回区间开始点的值。如图8所示,为2018年2月份每周一所在的日期。

图8

现在,在图8所示的查找表中查找指定的日期并返回该日期所在周一的日期,结果如图9所示。

图9

在单元格D2中的公式为:

=VLOOKUP(C2,$A$2:$A$6,1,TRUE)

向下拖至单元格D7。

示例4:比较两个列表

有时,我们需要比较两个列表,确定一个列表中包含另一个列表中的哪些项目。如图10所示,有“库存”表和“用户需求”表,要求根据“用户需求”表中的编号来查找“库存”表中相应编号的价格并将找到的结果输入到“用户需求”表中。

图10

可以使用VLOOKUP函数来完成,如图11所示。

图11

在单元格F3中输入公式:

=VLOOKUP(D3,$A$3:$B$6,2)

下拉至单元格F8。

咋一看,似乎任务完成!但仔细看一下,发现“用户需求”表中的编号1003和1005在“库存”表中不存在,但仍然得到了结果。阅读到这里的读者应该知道,公式中的VLOOKUP函数的第4个参数忽略,告诉Excel执行近似(区间)查找,此时Excel会找到小于但最接近查找值的值并返回相应的结果。但我们的这个示例中,并不需要这样。我们想要的是,如果找到的数据,就返回相应的值,没有找到就算了。

此时,应该将VLOOKUP函数的第4个参数明确设置为FALSE,当没有找到数据时,返回#N/A。如下图12所示。

图12

在单元格F3中的公式为:

=VLOOKUP(D3,$A$3:$B$6,2,FALSE)

现在,我们已经可以清楚地看到哪些编号对应的价格是“库存”表中没有的,因为它们会显示#N/A。

下面,我们稍作改进,让表格更加清楚地呈现结果,如图13所示。

图13

在单元格F3中的公式为:

=IFERROR(VLOOKUP(D3,$A$3:$B$6,2,FALSE),”没有库存”)

结语

在使用VLOOKUP函数时,你不能忽视其第4个参数,如果使用不恰当,会误导得出不正确的结果。然而,如果理解了第4个参数的真实含义并恰当运用,不仅能够更合理地使用VLOOKUP函数,而且可以有趣地使用VLOOKUP函数。


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.