在我们实际工作中,数据查询见的比较多的肯定是列与列之间的查询,那如果列与行交叉的部分你知道怎么去查询吗?
比如如下案例,如下是每个销售员每个月销售最多的产品表,如果我们想查询销售8第8个月销售最多的产品是什么?我们要怎么操作呢?
这个就是我们今天要分享的内容,交叉查询如何实现?
方法一、 VLOOKUP查询
说到查询,绕不过去的函数肯定是VLOOKUP函数
比如上面的案例我们销售员在第一列,8月在第9列,我们可以在C18输入公式:
但是如果我们月份变更的话,我们的公式也要跟踪变更,如果不想每次变更公式,我们可以把列的内容用MATCH函数替代,
MATCH函数定义:
MATCH(lookup_value, lookup_array, match_type)
简单来讲就是MATCH(查找的值,查找区域, 0为精确匹配),返回在区域的第几行或第几列
知道了这个我们就可以变更C18公式为:
扩展下,既然VLOOKUP列查询可以实现,那HLOOKUP行查询可以吗?当然也是可以的,只要在C18输入如下公式即可:
方法二、 INDEX+MATCH函数查找
INDEX+MATCH函数的组合可以说是非常强大的查找函数
INDEX函数定义:
INDEX(array,row_num,column_num)
Array:单元格区域或数组
Row_num:数组或单元格区域的行数
Column_num:数组或单元格区域的列数
其函数功能是返回数组或单元格区域中指定的单元格或数组的值
知道了INDEX函数及MATCH函数的定义,我们就可以形成公式了,在C18单元格输入公式:
INDEX查找区域为B2:M14
行数为:MATCH(A18,A2:A14,0),返回的行数为8
列数为:MATCH(B18,B1:M1,0),返回的列数为8
注意:此处的两个8不是工作表的第8行第8列
行数8为A2:A14的行数,第8行即为A9;同样列数8为B1:M1的列数,第8列即为I列
方法三、LOOKUP+OFFSET法
相比较前面两种方法,这种方式稍微难理解点,主要应用的是LOOKUP函数的功能
LOOKUP(1,0/(条件),返回区域)
其中返回区域内容是借用OFFSET函数实现的
OFFSET(基点,偏移行数,偏移列数)
我们在C18输入公式:
公式解释:
MATCH(B18,B1:M1,0)
匹配B18在行区域B1:M1的第几行,返回的结果为8
OFFSET(A2:A14,,8),我们可以看出行省略了,所以行不变,仅对列进行变动,即把A2:A14的区域向右移8列,即到了I列
LOOKUP公式即变为了
=LOOKUP(1,0/(A2:A14=A18),I2:I14)
我们再看下公式的运行步骤:
首先判断(A2:A14=A18),如果满足条件即为TRUE(代表1),否则为FALSE(代表0),可以看到第8个数据显示的为TRUE
当在用0进行除的时候,得到的是一组#DIV/0!和0组成的数,再使用Lookup以1作为查找值,最接近的值即为0,即得到了第8行,最终返回的即为工作表第9行第9列的数据,即I9,就是我们需要的结果了。
公式看起来比较复杂,但是当我们一点点的去拆解的时候,可以发现,都是每个函数比较常用的功能,不过还是需要大家多练习下。
以上就是给大家分享的3种实现交叉查询的方法,看懂不代表会用哦~~,还是需要大家多实际操作下。
如果觉得文章对你有帮助的话,希望大家帮忙点赞加分享哦~,谢谢
本文由彩虹Excel原创,欢迎关注,带你一起长知识!
领取专属 10元无门槛券
私享最新 技术干货