导读:说起数据查询功能,很多小伙伴们都不陌生,知道VLOOKUP, LOOKUP或者INDEX+MATCH等函数可以实现。这一期,小编将会罗列可能出现的各种情况,以事实案例为基础,全方位解读数据查询功能。请耐心观看,阅读仅需五分钟。

本篇将分为以下六个应用场景来逐一介绍

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

一:从上到下正向查询(根据姓名查找电话):

从上到下:返回第一个满足条件的值

正向查询:查询键[姓名]在查询值[电话]的左方。

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

根据zhang3查找时,返回第一个找到的电话13917980013

解析:

-VLOOKUP(A16, B$2:C$11, 2, 0):在B$2:C$11查询区域中,查询A16,返回查询区域B$2:C$11中的第二列

-INDEX(C$2:C$11, MATCH(A16, B$2:B$11, 0)):首先查找A16在B$2:B$11查询区域中所在的行号X,然后返回C$2:C$11中第X行数据

-INDEX(C$2:C$11, MATCH(TRUE, A16=B$2:B$11, 0)):A16=B$2:B$11返回的是{TRUE,FALSE}的数组,MATCH(TRUE, A16=B$2:B$11, 0)返回数组中为TRUE的行号X,然后返回C$2:C$11中第X行数据

注意:

Lookup函数对源数据的排序要求比较严格,如果源数据顺序是打乱的,Lookup函数返回值很可能是错误,所以此案例中不能使用Lookup函数。此外,在使用MATCH函数时,如果最后一个参数为1或者-1时,要求第二个参数(查询区域)是有顺序的。

二:从下到上正向查询(根据姓名查找电话)

从下到上:从上到下查询返回最后一个满足条件的值

正向查询:查询键[姓名]在查询值[电话]的左方。

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

根据zhang3查找时,返回最后一个找到的电话13917980003

解析:

-LOOKUP(1, 0/(A21=B$2:B$11), C$2:C$11): 0/(A21=B$2:B$11)返回的是{#DIV/0!,0}的数组Arr,Lookup查询时会自动排除#DIV/0!,然后返回数组中小于等于1的最大值,也就是返回最后一个满足条件的值。

-INDEX(C$2:C$11, MATCH(1, 0/(A21=B$2:B$11), 1)):和上面公式原理类似。

三:从上到下逆向查询(根据姓名查找工号)

逆向查询:查询键[姓名]在查询值[工号]的右方。

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

公式:

-INDEX(A$2:A$11, MATCH(A26, B$2:B$11, 0))

-INDEX(A$2:A$11, MATCH(TRUE, A26 = B$2:B$11, 0))

四:从下到上逆向查询(根据姓名查找工号)

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

公式:

-LOOKUP(1, 0/(A31 = B$2:B$11), A$2:A$11)

-INDEX(A$2:A$11, MATCH(1, 0/(A31=B$2:B$11), 1))

五:多个条件联合查询(根据姓名+电话查找工号)

如果查询条件有多个的时候,怎么做呢?请看下面的公式:

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

公式:

-INDEX(A$2:A$11, MATCH(1, 0/((A40=B$2:B$11)*(B40=C$2:C$11)), 1))

-LOOKUP(1, 0/((A40=B$2:B$11)*(B40=C$2:C$11)), A$2:A$11)

-INDEX(A$2:A$11,MATCH(1,(A40=B$2:B$11)*(B40=C$2:C$11), 0))

六:返回多个查询结果

当查找到多个结果时,要一次性返回的话,如何做呢?请看下面的动画演示:

Office Excel数据查询案列全方位解析 史上最全 不信你用不上

解析:

首先,增加[辅助列重复次数]通过公式COUNTIF(C$2:C2, C2)计算姓名的重复次数。

然后,增加[辅助列姓名],公式:D2&"-"&A2

最后,在D14单元格中输入公式VLOOKUP(A$14&"-"&ROW(1:1),B$2:E$11,4,0),查询第一个满足条件的。然后往下拉,可以找到所有满足条件的结果。

总结:

这一期主要讲了Excel数据查询的各种情况,用到的函数有:VLOOKUP, LOOKUP, INDEX+MATCH。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注