对于使用Excel的上班族来说,经常需要对数据进行查询调用,VLOOKUP是使用率较高的查询函数之一,单条件查询比较简单,直接套用该函数的语法即可,如果是两个条件甚至三个条件应该怎样使用该函数呢?
案例如下图所示:
根据第13行的车型和配置,从上面表中查找“厂方指导价”
方法1:用Sumifs函数 (多条件求和)
思路:在被查区域中车型和配置两个条件合一起就是唯一条件,用多条件求和就是自已加自已,就把结果查出来了。
公式:=SUMIFS(C2:C8,A2:A8,A13,B2:B8,B13)
注:如果车型和配置中有相同的多组数,就不能用这个函数了,他会直接求和。
方法2:用Sumproduct函数 (数组公式,函数本意是求多组数乘积之和)
思路:构建条件表达式,并将逻辑值转换为数值,符合条件为1,不符合条件为0,一一对应相乘,得出想要的结果
公式:=SUMPRODUCT((A2:A8=A13)*1,(B2:B8=B13)*1,C2:C8)
方法3:用Sum函数(需要用到数组,CTRL+SHIFT+ENTER三键)
思路:跟sumproduct函数用法相同,差异只是一个需要按数组三键,一个不需要
公式:{=SUM((A2:A8=A13)*(B2:B8=B13)*C2:C8)}
方法4:用Lookup函数
思路:lookup函数根据查找值,在被查找的数组中查找与其相同的值,如果该值不存在,则返回最后一个小于其本身的可用数据位置.并根据这个位置的索引,返回最后的返回值数组中“C2:C8”对应的索引位置的数据。
公式:=LOOKUP(1,(0/(A2:A8=A13)*(B2:B8=B13)),C2:C8)
注:0/的目的就是把符合条件的变成0,其他的变成错误值,利用lookup查找忽略错误值的特征查找到符合条件的值。
方法5:用If({1,0}和Vlookup函数
思路:VLOOKUP(条件1&条件2,if({1,0},条件1范围&条件2范围,结果范围),2,0),IF({1,0},相当于IF({True,False},用来构造查找范围的数据的。
公式:{=VLOOKUP(A13&B13,IF({1,0},A2:A8&B2:B8,C2:C8),2,0)}
使用注意:1、同时按住CTRL+SHIFT+ENTER 三键结束,因为这是数组公式;2、条件、结果范围大小要保持一致;
如果大家对这个用法不熟,实际上我们可以做一列辅助列,把车型和配置链接在一起,再使用Vlookup函数来查找,就比较简单了。
方法6:用Index函数和Match函数
思路:用Match函数定位,再用Index函数来取数,但Match函数定位需要把车型和配置链接成唯一条件,这就需要用到数组,所以公式输完后需要CTRL+SHIFT+ENTER 三键一起按下
公式:=INDEX(C2:C8,MATCH(A13&B13,A2:A8&B2:B8,0))
更多学习请关注数据匠人直播间 (加微:xiaoweiw108)
閱讀更多 數據匠人 的文章