由于Excel的数据量较多,所以相信很多小伙伴在日常的excel函数应用中,都会经常用到查询类函数,比如说根据工号查询姓名、根据学生查询成绩等等。今天小编要给大家讲解的是在excel进行逆向查询的秘诀,小伙伴们不要错过哦!
首先第一步,我们以生动的例子为基础,向大家讲解具体的操作步骤:首先我们看第一个例子,如下图,关于明星的配偶的查询方法:
E2单元格公式为
=VLOOKUP(D2,A:B,2,0)
以上的这个公式表示的具体含义是我们以D2单元格的姓名作为查询值,以A:B为查找区域,在首列中找到与D2单元格相同的姓名,然后返回这个区域中与之对应的第二列(也就是配偶所在列)的配偶。只要输入我们就可以进行查询啦。
不过问题来了,如果我们以配偶作为查找值,需要在这个区域中查找和配偶对应的姓名,我们将如何使用公式呢?
由于VLOOKUP函数要求查询值必须处于查询区域的首列,再使用普通方法就无法完成要求了,所以今天小编要和大家讲解的就是关于逆向查询的几种方法,大家记好了哦!
首先我们来看第一个方法:通过使用IF函数来重新构建数组。
第一步我们需要在E4中输入一下函数:
=VLOOKUP(D4,IF({1,0},$B$2:$B$17,$A$2:$A$17),2,0)
这个公式的具体含义就是我们将会通过用IF({1,0},$B$2:$B$17,$A$2:$A$17),返回一个配偶在前,姓名在后的多行两列的内存数组,使其符合VLOOKUP函数的查询值处于查询区域首列的条件,之后再用VLOOKUP查询就可以完成啦!
总结:方法一的函数使用相对来说会比较复杂,而且运算效率低,但是运算原则比较复杂,也适用于大数据的查询。
下面我们一起来看一下第二个方法:通过使用CHOOSE函数重新构建数组,首先我们来看看具体的函数公式:
E6使用公式为:
=VLOOKUP(D6,CHOOSE({1,2},B2:B17,A2:A17),2,0)
这个公式的具体意思和方法一一样,也是重新构建一个内存数组,使其符合VLOOKUP函数的查询值处于查询区域首列的条件,不同的就是公式的内容有点不同而已。
总结:其实这个函数与方法一的思路相同,虽然同样是使用复杂,运算效率低,但是适合大数据的查询。
以下我们看看第三个方法:结合使用INDEX+MATCH两个函数:
E8使用公式为:
=INDEX(A:A,MATCH(D8,B:B,))
本公式的具体意思是:公式首先使用MATCH函数返回D8单元格姓名在B列单元格中的相对位置,也就是这个区域中所处第几行。
接下来就要以此作为INDEX函数的索引值,通过从A列单元格区域中返回对应位置的内容。
方法三这个公式是最常用的查询公式之一,虽然看似有点繁琐,但是实际查询应用时,因为其组合灵活,所以可以完成从左至右、从右到左、从下到上、从上到下等多个方向的查询,适用范围很宽广!
总结:值得注意的是本函数还是嵌套使用的方式,我们进行操作非常灵活,运算起来也很方便。
最后一个方法就是:所向披靡的LOOKUP函数,你不能错过!
E10使用公式为:
=LOOKUP(1,0/(D10=B2:B17),A2:A17)
首先大家需要清楚这是一个非常经典的LOOKUP用法。公式的具体意思是我们需要用D10=B2:B17得到一组逻辑值,最后我们可以用0除以这些逻辑值,然后得到由0和错误值组成的内存数组。再用1作为查询值,在内存数组中进行查询。具体操作步骤如下图所示:
在本例中假如大家在LOOKUP函数找不到查询值,那么就说明它与查询区域中小于或等于查询值的最大值匹配,我们将会以最后一个0进行匹配,并返回A2:A17中相同位置的值,是不是非常经典呢?
总结:由于该函数使用简便,功能强大,最关键的是公式书写也比较简洁,所以它是大众情人哦~建议大家记住这个公式!
以上就是本例的全部内容啦,四个方法不知道你最喜欢哪一个呢?不过大家还需要注意的是如果有多条符合条件的结果,前三个公式都是返回首个满足条件的值,而第四个公式则是返回最后一个满足条件的值,这一点大家在使用时还需要特别注意哦,希望本例对大家有所帮助!