专栏首页阿凯的Excel八种方式实现多条件匹配

八种方式实现多条件匹配

之前在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),作者:阿忠凯

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python读书笔记10(不可变列表-元组)

    今天和大家分享的内容较少,和大家介绍一个新朋友即可! 元组! 元组,何方神圣?他其实就是一个固执的列表而已。 一、元组的声明(小括号) ? 元组的声明是用小括...

    用户1332619
  • 小小查找键、大大大乐趣

    你是不是在高速路口堵车的时候看到本文章呢? 不要问我在干嘛! 今天和大家愉快的分享的是查找键!! 神马?查找键还需要分享? 查找键其实分两类,一种是Ctr...

    用户1332619
  • Python读书笔记(Python软件的安装)

    小编最近一直在思考如何解决Excel的大数据分析的问题,为了解决该问题,小编决定开始学习Python!会在每周看心情分享自己的所学~ 是学习笔记哦!不是教学文...

    用户1332619
  • 并蒂花开,共筑未来

    ? 快 讯 腾讯数据中心基础设施认证培训 2020年9月11日,北京光环新网和中金云网腾讯数据中心基础设施认证培训专班纷纷迎来了结业认证仪式。两个专班共计30...

    腾讯数据中心
  • 简谈快速排序

    Jacklin
  • 智能隐形眼镜,即将实现的黑科技

    2019年,整个科技领域的焦点在5G将为整个科技领域带来什么样的全新动力。其中XR(VR/AR/MR)更是热火朝天。而XR类科技产品目前的主要形态为头戴式显示器...

    VRPinea
  • CSDN-markdown基本语法说明

    MathJax是一款运行在浏览器中的开源的数学符号渲染引擎,使用MathJax可以方便的在浏览器中显示数学公式,不需要使用图片。这篇文章介绍如何使用LaTeX...

    bear_fish
  • 2020年Java基础高频面试题汇总(1.4W字详细解析)

    面向过程:是分析解决问题的步骤,然后用函数把这些步骤一步一步地实现,然后在使用的时候一一调用则可。性能较高,所以单片机、嵌入式开发等一般采用面向过程开发

    程序员追风
  • 谷歌等巨头打击极端主义不力 英国威胁征收新税

    华莱士指责科技公司乐于销售人们的数据,却不愿意把它们提供给政府,迫使政府在去激进化项目、监控和其他反恐措施上花费了大量资金。 “如果他们继续不合作,我们应该考虑...

    企鹅号小编
  • 2020年1月编程语言排行榜:【2019年度最佳编程语言】公布!

    在每个人都认为Python将连续第二年成为TIOBE的年度编程语言的时候,老编程语言C语言凭借2.4%的年增长率,获得了奖项。

    老九君

扫码关注云+社区

领取腾讯云代金券