Excel多条件查找方法
发布时间:2023-07-17 19:33:13
对于Excel中多条件匹配的问题,永远是一个热点话题,而根据匹配结果是数字还是非数字的时候,有些查找方法又不能通用,今天就对多条件匹配的常见方法做一次梳理,并对每种方法的适用情况做出说明,希望对大家的工作能有所帮助。
先来看看今天用到的案例:
需要从数据源(A-D列)中按照机构和姓名分别匹配出职级(非数字内容)和基本工资(数字内容),以下就结合本案例来介绍多种常用的公式套路。
1、SUMIFS函数实现多条件匹配
SUMIFS函数本来是一个多条件求和的函数,格式为:
SUMIFS(求和列,条件列1,条件1,条件列2,条件2……)
但是当同时满足多个条件的结果只有一个时,多条件求和就等同于多条件匹配。
公式为:=SUMIFS(D:D,A:A,F2,B:B,G2)
要引用的列就是实际求和列,本例中是D列,而条件列分别就是A列和B列。
优势:使用SUMIFS多条件匹配时,比较方便高效,公式简单易用,再增加条件也比较容易。
不足:当要匹配的结果不是数字时,就不能使用这个方法了,同时当满足多个条件的数据不是唯一值时,结果可能错误。
推荐指数:★★★★
2、SUMPRODUCT函数实现多条件匹配
SUMPRODUCT函数的功能是计算多个数组或区域的数据乘积之和,利用比较运算和逻辑值的特性,也可以实现多条件求和的功能,用来处理多条件匹配,其实和SUMIFS的原理差不多,公式为:
=SUMPRODUCT(($A$2:$A$ =F2)*($B$2:$B$ =G2)*$D$2:$D$ )
优势:再增加条件时比较容易。
不足:数组计算,数据量大时比较卡,对函数的用法不理解容易出错,并只对数字的匹配结果适用。
推荐指数:★★★
3、辅助列+VLOOKUP函数实现多条件匹配
VLOOKUP函数本来是按单条件匹配的,这个函数很多朋友都应该熟悉,之前也多次分享过相关教程。
这里简单说一下函数的结构:
VLOOKUP(查找值,查找区域,第几列找,精确查找)
本例中的公式为:=VLOOKUP(G2&”-“&H2,$A$1:$E$ ,4,0)
在这个公式中,查找条件不是一个单元格,而是使用&将机构和姓名两个条件做了连接G2&”-“&H2。
在连接两个条件时,建议在中间添加一个分隔符,这在某些情况下可以避免出现错误。
因为我们使用了自己构造的条件,而这个条件在数据源中并不存在,因此需要在数据源的最左侧添加一个辅助列,如上中A列所示。
辅助列的公式为:=B2&”-“&C2
优势:公式对于匹配结果为数字或者非数字的时候都可以使用,适用面比SUMIFS更广泛。
不足:要增加辅助列才能使用,虽然难度指数降低了,但是会略显繁琐。
推荐指数:★★★★
4、VLOOKUP+IF函数实现多条件匹配
这是VLOOKUP较有难度的一种套路,公式为:
=VLOOKUP(F2&G2,IF({1,0},$A$2:$A$ &$B$2:$B$ ,$C$2:$C$ ),2,0)
对于这个公式套路,关键是IF这部分,篇幅所限,不对公式原理做过多解释,只要掌握IF函数的第二参数是将数据源中的两列进行合并,第三参数是数据源中需要匹配结果的列,同时公式需要按CTRL+SHIFT+ENTER键完成输入,遇到问题能够套用就行了。
优势:不用辅助列,一步到位。
不足:不适于新手使用,数据量较大时计算缓慢。
推荐指数:★★★
5、VLOOKUP+CHOOSE函数实现多条件匹配
这是将前一个公式中的IF换成了CHOOSE函数,公式为:
=VLOOKUP(F2&G2,CHOOSE({1,2},$A$2:$A$ &$B$2:$B$ ,$C$2:$C$ ),2,0)
这个公式套路的核心是CHOOSE,同样是构造了一个合并后的查找列,对于具体原理,需要了解的可以留言,一般能够套用公式解决问题就足够了,这个公式同样需要三键输入。
优势:不用辅助列,一步到位。
不足:不适于新手使用,数据量较大时计算缓慢。
推荐指数:★★★
6、LOOKUP函数实现多条件匹配
在常用的引用函数里,LOOKUP无疑是最受高手喜欢的一个函数了,这个函数充满了很多套路,例如多条件匹配时,公式套路就是:
=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域)
就本例而言,公式为:
=LOOKUP(1,0/(($A$2:$A$ =F2)*($B$2:$B$ =G2)),$C$2:$C$ )
优势:不使用辅助列一步到位,增加条件也方便,适用场合广泛,不管数字还是文本都可以使用这个套路。
不足:会让新手有一点畏惧心态。
推荐指数:★★★★★
7、更多实现多条件匹配的方法
就公式而言,除了上面分享的五种套路,其实还有一些,这里只列举公式套路和适用条件,其它的就不一一举例了,例如:
=MAX((条件区域1=条件1)*(条件区域2=条件2)*结果区域),这个公式是一个数组公式,只适合结果为数字的情况。
=INDEX(结果区域,MATCH(条件1&条件2,条件区域1&条件区域2,0)),数组公式,量大时比较卡。
=XLOOKUP(条件1&条件2,条件区域1&条件区域2,结果区域),新函数,只有office365版本可以用。
=FILTER(结果区域,(条件区域1=条件1)*(条件区域2=条件2)),新函数,只有office365版本可以用。
小结:对于多条件匹配问题,推荐使用第1、3和6三种方法,其实如果不用公式的话,数据透视表和高级筛选是更好的选择,这些方法会在今后的教程中分享给大家。
猜你喜欢
- 当微软为win10系统更新了KB4493474的补丁之后,我们再来看一看这个补丁中还有什么新的内容:解决在节点操作过程中引发异常时,可以引起
- Win10键盘一直自动输入文字怎么办?许多小伙伴最近一直在向我提问,自己没有使用键盘输入任何文字,键盘却无线的输入一些奇怪字符,这要怎么解决
- 所谓图片文件,就是个人电脑中存放的图形与图像。第一步:打开要编辑的文本。第二步:在要放置“图片文件”的位置单击,选定插入点。第三步:移动鼠标
- 一、菜单插入法在文档中,通过操作如下菜单“插入”→“特殊符号”。 会弹出如下图的对话框。切换到“数学符号”选项卡,
- 有人认为使用笔记本电脑的时候,外接显示器屏幕更大更加方便工作,但是也有人认为外接显示器眼睛看不过来,工作效率都下降了。最近正好有笔记本win
- win10怎么安装语言包?当我们使用的电脑系统为win10系统,那如何安装语言包呢,win10系统怎么安装语言包,win10怎么安装语言包,
- 1、我们把鼠标切换到文档左上角的“文件”按钮,在弹出的下拉菜单中选择“Word选项”命令。 2、在跳出的“Word
- word转换pdf格式有各种好处,首先有的电脑上不支持word文档,或者只支持一部分word格式,比如只支持doc而不支持docx,转换成p
- 最近有用户咨询小编说自己win10电脑的桌面调不出来了,不知道要怎么办?小编发现出现这个情况的用户不在少数,今天小编就教教大家怎么把win1
- 我们在编辑Word文档时,尤其是与数学相关的内容是有时候需要插入与根号相关的式子等,下面为大家介绍word2007公式编辑器添加根式方法,不
- Word文档中怎么自动对图片进行编号其实,只要每个文档编写者按照如下步骤为图片编号,就可以令合并到一起的文档自动对图片进行编号:在图片下方添
- word2010中,超链接在“插入”菜单的“超链接”选项下进行操作,也可以从右键下来选项中来操作。两种方法基本相同,都是在需要插入超链接处打
- Microsoft试图创建世界上最简单,最直观的程序集合。然而,对于大多数普通人来说,程序仍然过于复杂,以至于无法使用和理解。因此,花点时间
- 1.打开需要打印的Word文档 2.点击工具栏左上角的“文件”,然后点击“选项” 3.在
- Win10电脑怎么禁止安装软件?许多用户在使用电脑的时候经常会把电脑设置成禁止安装任何软件,这样就可以有效地防止一些垃圾流氓软件安装,那么我
- 工作中发现,文字或者字母与数字在同一行的时候,如果想要分开,一般都是空格空格,当数据多的时候,有没有更简单的方法呢,下面就给小伙伴们介绍一下
- win10系统已经慢慢的普及了,大部分小伙伴使用的时候都想要在上面使用自己习惯的字体,但是还不知道该怎么安装,今天就为你们带来了安装的方法。
- win10如何开启使用自带创建虚拟机功能?说到虚拟机,大家就知道它的用处有多大了,比如程序员用来测试一些木马病毒,或者用来模拟模拟安装系统等
- 最近家里的win10电脑用蓝牙连接耳机时,发现蓝牙耳机出来的声音会有卡顿现象,对使用体验很不友好,于是就来手动设置下,今天把解决方法制作成教
- 如果您使用Win10系统完游戏的时候会网络卡顿、游戏延迟严重,不妨试试禁用TCP协议中的Nagle算法来解决这个问题。Nagle算法主要用户