![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
Excel多条件匹配6种方法及思路
1个回答
展开全部
在做多条件匹配作业的时候发现了很多坑。多亏了几位圈友的指点,加上参考了小蚊子老师的博客,总结成了这篇文章和大家分享6种方法和其中的思路。文中一些概念是我自己的话总结的,可能并不严谨,如果看不懂,详细原理还是百度吧-。=
先从VLOOKUP讲起。用字段合并的方法,把多条件变为单一条件,如下图,把公司、部门合在一起。查找A:E区域,参考到第4行即可。注:辅助列加在哪都行,道理一样。
前面的方法需要辅助列,如果不用辅助列,那么需要在函数中将A列B列进行合并,简单&的合并实际操作中批量会出现#N/A的结果,所以需要数组计算。函数的编写见上图。函数写完,最后不是按Enter,而是 Shift+Ctrl+Enter ,这样就是数组计算了。最后可以看到,函数最外面有个大括号“{}”。这个VLOOKUP函数中嵌套了一个IF函数。
【什么是IF({1,0},...)?】IF函数,1就是true,0就是false。与后面的真值和假值相对应。而{1,0}是数组,就是把真值、假值分别计算,1返回的值是A2:A25。0返回的值是D2:D25。两个结果结合成一个数组,也就是这里面的VLOOKUP的数据表区域。
LOOKUP法的逻辑就是找到唯一对的那个值,在此借用“挫人”的解释会更清晰:
LOOKUP(lookup_value,lookup_vector,[result_vector])
当Lookup的Lookup Value永远大于lookup vector时,返回最后一个小于lookup vector对应的result
=LOOKUP(1,0/((A2:A3=G2)*(B2:B3=H2)),C2:C3)
lookup_value为1lookup_vector,为0/逻辑值
逻辑值(A2:A3=G2)*(B2:B3=H2)结果有两种,TRUE和FALSE。在公式计算中:TRUE看作是1,FALSE看做0
上面公式就变成了0/({TRUE;TRUE}*{TRUE;FALSE})-->0/{1;0}-->{0/1;0/0}-->{0;#DIV/0!}
整个公式就变成了=LOOKUP(1,{0;#DIV/0!},{100;200})因为0/0-->#DIV/0!为错误值,而LOOKUP要找的,是非错误值。所以,第二参数只有0,0<1,0对应的result是100,所以最后的值是100
P.S.除了1,0/……,还可以写2,1或者3,2或者100,0只要第一参数永远大于第二参数中的值就可以^_^
SUM是求和,SUMIFS是对条件指定的单元格求和,利用了条件筛选功能。求和区域就是要利用里面的值求和,当只有一个加数时,这个值就是我们想要的值。我们想知道员工数,就选C:C。然后是条件筛选,先选第一条件区域比如公司A:A,在选条件也就是对应的G2。后面的部门也是如此。这样就通过条件筛选出了唯一值。
SUMPRODUCT是数组或各区域的乘积之和,这里用的不是乘积和而是多条件属性=SUMPRODUCT((条件1)*(条件2)*...(条件n))。同时满足多条件,返值。
利用高级筛选复制到别的区域,缺点是需要手动更新。
后面3种方法是在小蚊子老师博客里找到的,这里给出简单的原理,具体操作请看博客里面有动图。同时,感谢“挫人”、“没脸的小白脸”、“Starnight”、“米果”的指点和建议。
以上就是总结的各种方法,有些地方表达不清,如果看不懂请百度。希望能帮到大家。
先从VLOOKUP讲起。用字段合并的方法,把多条件变为单一条件,如下图,把公司、部门合在一起。查找A:E区域,参考到第4行即可。注:辅助列加在哪都行,道理一样。
前面的方法需要辅助列,如果不用辅助列,那么需要在函数中将A列B列进行合并,简单&的合并实际操作中批量会出现#N/A的结果,所以需要数组计算。函数的编写见上图。函数写完,最后不是按Enter,而是 Shift+Ctrl+Enter ,这样就是数组计算了。最后可以看到,函数最外面有个大括号“{}”。这个VLOOKUP函数中嵌套了一个IF函数。
【什么是IF({1,0},...)?】IF函数,1就是true,0就是false。与后面的真值和假值相对应。而{1,0}是数组,就是把真值、假值分别计算,1返回的值是A2:A25。0返回的值是D2:D25。两个结果结合成一个数组,也就是这里面的VLOOKUP的数据表区域。
LOOKUP法的逻辑就是找到唯一对的那个值,在此借用“挫人”的解释会更清晰:
LOOKUP(lookup_value,lookup_vector,[result_vector])
当Lookup的Lookup Value永远大于lookup vector时,返回最后一个小于lookup vector对应的result
=LOOKUP(1,0/((A2:A3=G2)*(B2:B3=H2)),C2:C3)
lookup_value为1lookup_vector,为0/逻辑值
逻辑值(A2:A3=G2)*(B2:B3=H2)结果有两种,TRUE和FALSE。在公式计算中:TRUE看作是1,FALSE看做0
上面公式就变成了0/({TRUE;TRUE}*{TRUE;FALSE})-->0/{1;0}-->{0/1;0/0}-->{0;#DIV/0!}
整个公式就变成了=LOOKUP(1,{0;#DIV/0!},{100;200})因为0/0-->#DIV/0!为错误值,而LOOKUP要找的,是非错误值。所以,第二参数只有0,0<1,0对应的result是100,所以最后的值是100
P.S.除了1,0/……,还可以写2,1或者3,2或者100,0只要第一参数永远大于第二参数中的值就可以^_^
SUM是求和,SUMIFS是对条件指定的单元格求和,利用了条件筛选功能。求和区域就是要利用里面的值求和,当只有一个加数时,这个值就是我们想要的值。我们想知道员工数,就选C:C。然后是条件筛选,先选第一条件区域比如公司A:A,在选条件也就是对应的G2。后面的部门也是如此。这样就通过条件筛选出了唯一值。
SUMPRODUCT是数组或各区域的乘积之和,这里用的不是乘积和而是多条件属性=SUMPRODUCT((条件1)*(条件2)*...(条件n))。同时满足多条件,返值。
利用高级筛选复制到别的区域,缺点是需要手动更新。
后面3种方法是在小蚊子老师博客里找到的,这里给出简单的原理,具体操作请看博客里面有动图。同时,感谢“挫人”、“没脸的小白脸”、“Starnight”、“米果”的指点和建议。
以上就是总结的各种方法,有些地方表达不清,如果看不懂请百度。希望能帮到大家。
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询