八种方式实现多条件匹配

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

相关文章

来自专栏BestSDK

Excel必备神器,9个自动批处理,巨量表格工作克星!

01 批量求和 对于某个区域的汇总求和,一个个写公式再拖动填充并不是最简便的做法,批量求和,得看"Alt"+"="来秀一波! ? 02 批量添加单位 还在困惑怎...

28711
来自专栏我有一个梦想

Python 项目实践二(下载数据)第三篇

接着上节继续学习,在本章中,你将从网上下载数据,并对这些数据进行可视化。网上的数据多得难以置信,且大多未经过仔细检查。如果能够对这些数据进行分析,你就能发现别人...

2075
来自专栏欧阳大哥的轮子

常用的数学函数以及浮点数处理函数

在编程中我们总要进行一些数学运算以及数字处理,尤其是浮点数的运算和处理,这篇文章主要介绍C语言下的数学库。而其他语言中的数学库函数的定义以及最终实现也是通过对C...

642
来自专栏wym

HDU 6400 Parentheses Matrix(构造)

  每个位置只能是左括号或者右括号, “()”这算一个匹配,那么“(())”,“()()”也是匹配的。

702
来自专栏aCloudDeveloper

公司数据结构+算法面试100题

1.把二元查找树转变成排序的双向链表(树) 题目: 输入一棵二元查找树,将该二元查找树转换成一个排序的双向链表。 要求不能创建任何新的结点,只调整指针的指向。 ...

3719
来自专栏专知

Numpy教程第1部分 - 阵列简介(常用基础操作总结)

【导读】这里是numpy教程的基础部分,涵盖了使用numpy的ndarrays执行数据操作和分析的一些操作。众所周知,Numpy是Python中最基本和最强大的...

2604
来自专栏SeanCheney的专栏

《图解算法》总结第1章 算法简介第2章 选择排序第3章 递归第4章 快速排序第5章 散列表第6章 广度优先搜索第7章 狄克斯特拉算法第8章 贪婪算法第9章 动态规划

Grokking Algorithms: An illustrated guide for programmers and other curious peop...

3559
来自专栏IMWeb前端团队

css中如何做到容器按比例缩放

本文作者:IMWeb 结一 原文出处:IMWeb社区 未经同意,禁止转载 在说容易按比例缩放前,我们先说下图片按比例缩放。 对于图片,默认只设置图片的...

2069
来自专栏Crossin的编程教室

【Python 第28课】 字符串的分割

字符串和list之间有很多不得不说的事。比如有同学想要用python去自动抓取某个网页上的下载链接,那就需要对网页的代码进行处理。处理的过程中,免不了要在字符串...

3024
来自专栏深度学习之tensorflow实战篇

R语言之系统聚类(层次)分析之图谱形式完整版

读取数据常见错误: 在读取数据过程中可能遇到以下问题,参照上一篇博客: 可能遇到报错: 1、Error in if (is.na(n) || n > 65536...

4025

扫描关注云+社区