八种方式实现多条件匹配

之前在Excel内部的分享交流群和别的讲师探讨了多条件匹配有哪些实现方式。

围观的市民刘先生表示:我活了二十多年,看见斗图的比较多,这么无聊斗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)}

提问:你这个方法和刚才的有咩区别吗?

回答:没有,还是数组乘积求和!

提问:那你为什么要分享

回答:我就是想凑齐八个!你打我呀!!!!!

今天就到这里!祝各位明年昨日可以不用自己过七夕!

感谢各位支持!

原文发布于微信公众号 - 阿凯的Excel(akexcel)

原文发表时间:2017-08-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数说工作室

【SAS Says】基础篇:读取数据(中)

特别说明:本节【SAS Says】基础篇:读取数据(上),用的是数说君学习《The little SAS book》时的中文笔记,我们认为这是打基础的最好选择。...

3385
来自专栏CDA数据分析师

数据分析师(技术编程类)常见的10道面试题解答

1、海量日志数据,提取出某日访问百度次数最多的那个IP。   首先是这一天,并且是访问百度的日志中的IP取出来,逐个写入到一个大文件中。注意到IP是32位的,最...

2258
来自专栏猿人谷

100阶乘末尾有多少个零

题目:1*2*3*……*100 求结果末尾有多少个零 分析:一般类似的题目都会蕴含某种规律或简便方法的,阶乘末尾一个零表示一个进位,则相当于乘以10而10 是...

2155
来自专栏编程之旅

数据结构——无权图的路径问题(C++和java实现)

好像又是接近半个月没有更新,这半个月忙着结婚的各项事情,本来预计的学习任务也拖拖拉拉,进度缓慢。吐槽一句,拍婚纱照真的是最非常非常累的一件事情,不想再有下次了。

792
来自专栏杨建荣的学习笔记

重温快速排序(r4笔记第73天)

说起排序,总是会想起大名鼎鼎的快速排序,等自己再次翻开快速排序时,感觉是很陌生的,从这个对比也能看出自己确实是已经忘记了曾经重要的日子。 快速排序使用了分治思想...

3477
来自专栏决胜机器学习

有趣的算法(三)——Hash算法

有趣的算法(三)——Hash算法 (原创内容,转载请注明来源,谢谢) 一、Hash算法 近期看到用hash实现基于hash的简单的小型数据库(传统大型数据...

3347
来自专栏大数据挖掘DT机器学习

【面试】数据分析师常见的10道面试题解答

1、海量日志数据,提取出某日访问百度次数最多的那个IP   首先是这一天,并且是访问百度的日志中的IP取出来,逐个写入到一个大文件中。注意到IP是32位的,最...

4986
来自专栏海天一树

NOIP 2013初赛普及组C/C++答案详解

试题和答案: https://wenku.baidu.com/view/aa2bc10b5022aaea988f0f77.html?re=view

654
来自专栏iKcamp

翻译连载 | 附录 A:Transducing(下)-《JavaScript轻量级函数式编程》 |《你不知道的JS》姊妹篇

原文地址:Functional-Light-JS 原文作者:Kyle Simpson-《You-Dont-Know-JS》作者 JavaScript 轻量级函数...

1818
来自专栏cnblogs

Javascript继承,再谈

     说到Javascript的继承,相信只要是前端开发者都有所了解或应用,因为这是太基础的知识了。但不知各位有没有深入去理解其中的玄机与奥秘。今本人不才,...

33011

扫码关注云+社区