Excel中最难的多条件查找公式,帮你整理好了

前面跟大家分享了Excel中多条件查询的20种方法,但是当我们查找的结果区域是不确定的时候怎么办?比如我们人事部汇总好的员工工资表,想要查询某个月的工资;因为每个人做表的习惯不同,导致的考试成绩科目不一致,想要查询任意一科成绩……今天就说一下这些常用的解决方法:

一、最简单常规用法:辅助列+Vlookup+match函数

相信有点函数基础的朋友都是从这个函数学起的,因为它比较入门而且实用性也非常强!即使不好查询的情况我们也要构造出环境让vlookup函数来实现,因此当用于多个条件来查询的时候我们是实用最简单的添加辅助列来查询

比如我需要查找二班王洪涛的数学成绩:

Excel中最难的多条件查找公式,帮你整理好了插图1

首先在a35中添加辅助列=B35&C35下拉公式即可在K35中输入公式=VLOOKUP(H35&I35,$A$35:$F$52,MATCH(J35,$34:$34,0),0)

Excel中最难的多条件查找公式,帮你整理好了插图3

根据前几天的分析改函数的语法结构相信你应该清楚了,唯一不同的是这里的表是二维表,因为我们查询的科目位置是变化的,所以需要使用一个match函数来确定科目的位置

Excel中最难的多条件查找公式,帮你整理好了插图5

方法二:LOOKUP+offset函数查找的固定套路:

Lookup函数用法之一,向量形式=lookup(查找值,条件区域,结果区域)

数组形式=lookup(查找值,结果区域)

Excel中最难的多条件查找公式,帮你整理好了插图7

公式解释:

Excel中最难的多条件查找公式,帮你整理好了插图9

方法三:index+match函数经典组合

函数语法=index(结果区域,行号,列号)

在K88中输入数组公式=INDEX($D$88:$F$105,MATCH(H88&I88,$B$88:$B$105&$C$88:$C$105,0),MATCH(J88,$D$87:$F$87,0))

Excel中最难的多条件查找公式,帮你整理好了插图11

第一个match确定班级+姓名整体在b的位置为行号,第二个match确定科目在首行的位置为列号

方法四:纵向查找hlookup+match数组公式

与vlookup函数查找区别就是纵向查找,第三参数返回的行数

在K114中输入数组公式=HLOOKUP(J114,$D$113:$F$131,MATCH(H114&I114,$B$113:$B$131&C113:C131,0),0)

与前面查找函数不同的是hlookup函数查找值是科目

公式解释:

查找值是:科目数学(可以根据下拉菜单选择任意一科)

查找区域是:$D$113:$F$131,是以查找值为首的整个区域

返回行数是:match确定班级+姓名作为整体在b113:c131区域的位置

第四参数:0为精确查找查找

Excel中最难的多条件查找公式,帮你整理好了插图13

方法五:offset+match数组公式或indirect+address+match数组公式

Excel中最难的多条件查找公式,帮你整理好了插图15

在K140中输入数组公式=OFFSET($B$139,MATCH(H140&I140,$B$140:$B$157&$C$140:$C$157,0),MATCH(J140,$C$139:$F$139,0))在K167中输入数组公式=INDIRECT(ADDRESS(MATCH(H167&I167,$B$167:$B$184&$C$167:$C$184,0)+166,MATCH(J167,$166:$166,0)))

Excel中最难的多条件查找公式,帮你整理好了插图17

那么以上都是可以归为一类:都使用了查找引用函数嵌套match函数定位,当你理解了每一项整个公式就变得更加简单了!

六、sum家族函数

用好一个函数就要知道它适用哪些条件,当没有重复记录求和函数就变为查找引用函数:

当SUMPRODUCT函数用于求和=SUMPRODUCT((条件1)*(条件2)*……*(条件n))*结果区域)

万能sumproduct函数

在K194中输入公式=SUMPRODUCT(($B$194:$B$211=H194)*(C194:C211=I194)*($D$193:$F$193=J194)*$D$194:$F$211)

Excel中最难的多条件查找公式,帮你整理好了插图19

sum数组函数

在K219中输入数组公式=SUM(($B$218:$B$235=H218)*($C$218:$C$235=I218)*($D$217:$F$217=J218)*($D$218:$F$235))

Excel中最难的多条件查找公式,帮你整理好了插图21

方法七:最大/小函数也可以查找

该类函数的使用也是有限制条件的,如同sum函数的原理一样,当有重复记录就会返回最大值或者最小值!

最大函数在K243中输入数组公式=MAX(($B$243:$B$260=H243)*(C243:C260=I243)*(D242:F242=J243)*(D243:F260))最小函数在K267中输入数组公式=MIN(IF((B267:B284=H267)*(C267:C284=I267)*(D266:F266=J267),($D$267:$F$284)))

Excel中最难的多条件查找公式,帮你整理好了插图23

关于多条件查询返回单列查询:20种方法你会几个

Excel中不用函数、VBA也可以轻松实现一对多的查询

Excel自适应的下拉查询菜单,超级简单

至此对于多条件查找返回列数不确定的用法大致就总结这么多,涉及到了excel中基本常用的所有查找引用函数,但不限于还有其他更适合的方法!无论使用哪种方法,只要是适合自己的或者能够解决实际问题的都是好函数!通过这两期的函数用法介绍,你对Excel中查找引用函数是不是有了更深的理解?如果你还有更加简单的方法欢迎在评论区留言互相学习交流一下!

Excel中最难的多条件查找公式,帮你整理好了插图25

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2305938578@qq.com 举报,一经查实,本站将立刻删除。本文网址:https://www.excelwordppt.com/word-excel-ppt/4161.html

发表评论

登录后才能评论