PDF转Excel:高效去除AI特征,实现数据自由编辑
731
2022-10-19
10个示例让你的VLOOKUP函数应用从入门到精通(下)
在上一期10个示例让你的VLOOKUP函数应用从入门到精通(上)中,我们介绍了5个示例,下面介绍另外的5个示例。
示例6:使用通配符进行部分查找
当需要在列表中查找一个值但没有完全匹配时,需要部分查找。
如图11所示,如果想要查找列表中的“长江”,但是列表中只有“长江发电集团”。
图11
由于列A中没有完全匹配,因此不能使用“长江”作为查找值。近似匹配也会导致错误的结果,并且需要列表以升序排序。
然而,可以在VLOOKUP函数中使用通配符来获得匹配。
在单元格D2中输入公式:
=VLOOKUP(“*”&C2&”*”,$A$2:$A$6,1,FALSE)
并向下拖至单元格D6,如图12所示。
图12
在上面的公式中,不是按照原样使用查找值,而是在其两侧连接了星号(*)通配符。星号通配符可以代表任意数量的字符。
这样,告诉Excel需要查找包含单元格C2中文本的任意文本。在示例中,遍历A2:A6并查找其中包含“长江”的单元格。
注意,如果列表中有两个单元格都含有“长江”,那么VLOOKUP函数只返回第1个找到的值。
示例7:尽管匹配查找值但返回错误
有时候,明明有匹配的查找值,但VLOOKUP函数却返回错误。
例如,下图13所示,列表中有一个匹配值“Matt”,但VLOOKUP函数仍然返回错误。
图13
其实,我们看到的只是表面,没有看到列表中文本字符串的前后可能存在空格。如果在文本字符串中有额外的空格,包括之前、之后或中间的空格,都不是一个匹配值。
这是从数据库或者从其他地方导入数据时经常会碰到的情况。
可以使用TRIM函数来解决这个问题。TRIM函数移除文本字符串中的前导或尾部的空格,或者中间多余的空格。
下面的公式获得正确的结果:
=VLOOKUP(“Matt”,TRIM($A$2:$A$9),1,0)
这是一个数组公式,输入完后要同时按下Ctrl+Shift+Enter键。
图14
示例8:执行区分大小写的查找
默认情况下,在VLOOKUP函数中的查找值不区分大小写。例如,查找值是MATT、matt、或者Matt,对于VLOOKUP函数来说都是一样的,返回找到的第一个匹配值而不管大小写。
如果想执行区分大小写的查找,那就需要与VLOOKUP函数一起使用EXACT函数。
如图15所示。
图15
可以看到,单元格A2、A4和A5中有相同的姓名但大小写不同,在右侧是相应的数学成绩。
由于VLOOKUP函数不具备区分大小写查找值的功能,因此上面的示例中将总是返回数值38。
要使之区分大小写,需要使用辅助列,如图16所示。
图16
在单元格B2中输入公式:=ROW(),下拉至单元格B9,填充辅助列,这些数值为单元格所在行的行号。
在单元格F2中输入数组公式:
=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)
其中:
EXACT(E2,$A$2:$A$9)——将单元格E2中的查找值与单元格区域A2:A9中所有的值进行比较,返回由TRUE/FALSE组成的数组{TRUE;FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE},其中TRUE表示完全匹配。
EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))——将行号与上面由TRUE/FALSE组成的数组相乘,TRUE值将最终得到行号数字,其他的为0。本例中为{2;0;0;0;0;0;0;0}。
MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9)))——从上面所得的数组中返回最大值。本例中为2。
将上面得到的值2作为查找值并在单元格区域B2:C9中执行查找返回相应的值。
示例9:多条件查找
在最基本的VLOOKUP函数用法中,查找一个查找值,返回找到的值所在行相应的值。但是,经常会碰到使用VLOOKUP函数执行多条件查找的情形。
如图17所示,表中有学生姓名、测试的类型以及数学成绩。
图17
要得到每个学生每类测试对应的数学成绩,需要创建一个唯一的查找值。为此,添加一个辅助列,如图18所示。
图18
在单元格C2中的公式:
=A2&” ”&B2
下拉至单元格C16。这样,在列C中创建了一个唯一值列表,能够使用这个辅助列作为查找值。
在单元格G3中的公式为:
=VLOOKUP($F3&” “&G$2,$C$2:$D$16,2,0)
向右向下拖放至单元格区域G3:I7,结果如图19所示。
公式中将学生姓名和测试类型组合作为查找值。
图19
在示例中,我们组合两个文本作为辅助列时使用了分隔符,这能避免一些意想不到的情况。如下图20所示,如果不使用分隔符,组合后的值会相同。
图20
示例10:在使用VLOOKUP函数时处理错误
在不能找到指定的查找值时,VLOOKUP函数返回错误值,你可能不希望这些错误值影响数据的美观。
可以使用有意义的文本诸如“没有找到”来代替错误值。
在图21中,试图查找黄蓉的成绩,但返回一个错误值#N/A,因为列表中没有这个名字。
图21
下面使用IFERROR函数配合VLOOKUP函数让有意义的文本代替错误值:
=IFERROR(VLOOKUP(D2,$A$2:$A$6,2,0),”没有找到“)
IFERROR函数检查第1个参数的返回值是否为错误值,如果不是错误值则返回该值,否则返回第2个参数的值,本例中为“没有找到”。
图22
如果是Excel 2007以前的版本,则使用公式:
=IF(ISERROR(VLOOKUP(D2,$A$2:$B$6,2,0)),”没有找到“,VLOOKUP(D2,$A$2:$B$6,2,0))
结语
VLOOKUP函数是我们经常要使用的查找函数,结合具体情形,配合使用其他函数以及使用一些技巧,能够让VLOOKUP函数发挥出更大的功能。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。