八种方式实现多条件匹配

之前在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 条评论
登录 后参与评论

相关文章

来自专栏前端儿

Web 前端颜色值--字体--使用,整理整理

颜色值 CSS 颜色使用组合了红绿蓝颜色值 (RGB) 的十六进制 (hex) 表示法进行定义。对光源进行设置的最低值可以是 0(十六进制 00)。最高值是 2...

2332
来自专栏搞前端的李蚊子

Html5模拟通讯录人员排序(sen.js)

// JavaScript Document  var PY_Json_Str = ""; var PY_Str_1 = ""; var PY_Str_...

5976
来自专栏码匠的流水账

聊聊spring cloud的HystrixAutoConfiguration

本文主要研究一下spring cloud的HystrixAutoConfiguration

992
来自专栏我和未来有约会

简练的视图模型 ViewModel

patterns & practices Developer Center 发布了 Unity Application Block 1.2 for Silver...

2199
来自专栏MelonTeam专栏

Bitmap 源码阅读笔记

导语: Android 系统上的图片的处理,跟Bitmap 这个类脱不了关系,我们有必要去深入阅读里面的源码,以便在工作中能更好的处理Bitmap相关的问题...

2518
来自专栏Petrichor的专栏

Dataset 列表:机器学习研究

In computer vision, face images have been used extensively to develop face recog...

1541
来自专栏JMCui

MongoDB系列五(地理空间索引与查询).

Volvo Today, Volvo announced i...

2722
来自专栏linux驱动个人学习

高通Audio中ASOC的machine驱动

ASoC被分为Machine、Platform和Codec三大部分,其中的Machine驱动负责Platform和Codec之间的耦合以及部分和设备或板子特定的...

9784
来自专栏c#开发者

XML Encryption in .Net

XML Encryption in .Net One of the new features being introduced with the Whidbey...

4387
来自专栏一个会写诗的程序员的博客

java.sql.SQLException: connection holder is null

java.sql.SQLException: connection holder is null

1341

扫码关注云+社区