|
围观的市民刘先生表示：我活了二十多年，看见斗图的比较多，这么无聊斗Excel使用技巧的第一次见！ 为了更好的装逼，哦！不！是为了更好的分享多条件匹配的技巧，今天和大家分享如何用八种方式实现多条件匹配！ 上原始素材！ 看到广州新垣结衣有种中国梅西、中国郜林斯曼的感觉！ 不管那么多了，反正就是原表是名称、城市、评分表。 现在需要根据两个条件得到评分！ 我是轻度强迫症患者，想到什么就想能穷尽解决方案，所以写这个文章即为分享，也为自己收藏存档，部分实现方式较难理解。 大大的提醒：1、4、6、7方法比较简单，只是想了解解决方案的，看这四个中任意一个就好啦，想拓展编程思路的，可以都看看！ 方法一：增加辅助列法 常见的Vlookup匹配应用只能查找一个单元格，针对多条件的，就是把多个条件都放到一个单元格即可。 原表插入一列作为辅助列，然后输入=，用本文连接符&连接不同的单元格，合并到一个单元格即可! 查询列表同理! 最后编写Vlookup就可以实现！ 方法二：Vlookup函数与数组重构第一式 其实有了第一个方法的思路，第二个方法就是由插入一列辅助列变成使用数组函数构建一个虚拟的表而已。 公式：{=VLOOKUP(G2&H2,IF({1,0},B1:B9&C1:C9,D1:D9),2,)} 公式两边用大括号包裹，说明什么？说明输入函数后是同时按住Ctrl Shift Enter结束的！ 为虾米需要这么复杂呢？因为我们用到了数组函数，今天很多公式都是三键结束的。 先解释一下Vlookup的第一个参数 G2&H2就是两个单元格的合并，结果就是石原里美茂名，和刚刚创建辅助列的效果一样! Vlookup第二个参数是要引用一个区域，我们在这里是用IF函数实现搭建一个区域。 先回想一下IF函数的用法 IF（判断条件，为真的时候返回什么，为假的时候返回什么） {1,0}啥意思呢？其实通俗理解这个就是两列，第一列的数字都是1，第二列的数字都是0。 翻译成Excel的语言就是将一列变成了两列 变身后 第一列是：=IF(1,B1:B9&C1:C9,D1:D9) 第二列是：=IF(0,B1:B9&C1:C9,D1:D9) 所以Excel重新帮我们构建了一个新的表，这个表的第一列就是名字和城市的组合，第二列是评分。和第一种方法创建辅助列的方式其实是一样的。 唯一的区别是方法一是人工实实在在的创建了一个新表，而方法二是通过IF加上数组函数虚拟创建了一个表。 方法三：Vlookup函数与数组重构第二式 本方法和方法二类似，但是构建数组辅助表的时候换了一种形式。 公式：{=VLOOKUP(1,IF({1,0},(B1:B9=G2)*(C1:C9=H2),D1:D9),2,)} 本方法的辅助表变成了每个列等于条件，然后两个条件相乘。 B1:B9=G2得到的是True和False的数组 C1:C9=H2得到的同样是True和False的数组 True等同于1，False等同于0 当多条件同时满足的时候就变成了1，否则就是0
第一列变成了如果两者均相等才显示为1，如果有其中任意一个不等都是0，则最终结果就是0 第二列就是心中评分。 然后Vlookup根据1查找，则新的辅助表只有两个条件都相等的时候才是1，否则是0 那只有一个返回值就是6啦！ 本案例的精髓在于深刻理解数组是如何重构及重构后的表是什么样子的！ 方法四：Lookup大叔实现 Lookup和Vlookup是表亲关系，Lookup虽然使用频率没有Vlookup高，但是很多场合Lookup可以更巧妙的解决问题！ Lookup（找什么，在哪里找，如果找到了返回什么）
公式：=LOOKUP(1,0/((B2:B9=G2)*(C2:C9=H2)),D2:D9) 这个公式没有大括号哦，普通Enter键结束公式编写即可！ 重要说明一个第二个参数0/(B2:B9=G2)*(C2:C9=H2) 某列等于某个单元格得到的是True、False数组，两个数组相乘是1、0数组。 因为数字0不可以作为分母，如果是分母会报错！ (B2:B9=G2)*(C2:C9=H2)返回值：{0;0;0;0;0;0;1;0} 0/(B2:B9=G2)*(C2:C9=H2)返回值：{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!} 则Lookup第二个参数的辅助表只有倒数第二个有有效数字，所以只有唯一的返回值了！ 备注：本案例最不好理解的是为什么第一个参数是1，第二个参数的分子是0！其实第一个参数可以是任意的数字，只要大于第二个参数的分子即可！ 为什么一定要这样呢？ 因为Lookup的实现原理是返回辅助表中小于等于第一个参数数字对应的返回值！ 比较拗口！但是是真谛！给你个眼神自行体会！
方法五：Match Index大法！ match和index匹配可以完全实现Vlookup的应用，还可以实现反查等Vlookup本身实现不了的匹配功能。 基础函数介绍 =Match（查找什么，在哪个列找，0）返回第一个参数在第二个参数中的位置 =Index（列，返回该列第几个值）返回某个列中第N个值 两个组合就是Vlookup的应用咯！
公式：{=INDEX(D2:D9,MATCH(G2&H2,B2:B9&C2:C9,0))} 思路：先获取查找的内容在新的列中属于第几位，然后返回评分列对应位置的值！ 完美！ 重点是Match函数的应用，Match第一个参数就是两个条件合并，第二个参数本来应该接一个列，本案例我用两个列相乘，实现了每个列相同位置用文本连接符链接在一起，和创建辅助列是一样的！有上文的铺垫，我不再累述了！ 大大的提醒：下面的方法只适用于返回值是数字的！如果是返回值是文本，只能用上面的哦！ 方法六：Sumifs实现 Sumifs是Sumif的大哥，Sumif只能实现单条件统计求和，Sumifs可以实现N条件统计求和！ =Sumifs（要求和的列，要判断的列1，判断条件1，要判断的列2，判断条件2......）
公式：=SUMIFS(D2:D9,B2:B9,G2,C2:C9,H2) 比较简单，不过多解释咯！ 方法七：Sumproduct函数实现 Sumproduct曾经我单独写过一篇文章，感兴趣可以看一下！ 点我查看！
公式：=SUMPRODUCT((B2:B9=G2)*(C2:C9=H2)*D2:D9) Sumproduct是数组乘积求和，也不解释啦，其实看我上面的那个文章就理解了，不懂的留言交流！ 方法八：Sum的判断求和，数组函数盲的噩梦！
公式：{=SUM((B2:B9=G2)*(C2:C9=H2)*D2:D9)} 提问：你这个方法和刚才的有咩区别吗？ 回答：没有，还是数组乘积求和！ 提问：那你为什么要分享 回答：我就是想凑齐八个！你打我呀！！！！！
今天就到这里！祝各位明年昨日可以不用自己过七夕！ 感谢各位支持！ |
|
|
