百科生活 投稿
关于【多条件匹配函数怎么用】，sumifs函数多条件匹配，今天小编给您分享一下，如果对您有所帮助别忘了关注本站哦。
- 内容导航：
- 1、Excel函数组合(四) - INDEX + SMALL + IF组合
- 2、多条件匹配函数怎么用：sumifs函数多条件匹配
1、Excel函数组合(四) - INDEX + SMALL + IF组合
大家好，我是永不止步的老牛。
Excel有很多功能强大的函数，如果函数间能组合使用，那就是强强联手，今天我们先看第3组强强联手“INDEX+SMALL+IF”组合。
我们先看一下效果：
这3个函数的组合主要是为了解决一对多的问题，根据一个条件查询，返回多个结果，是个常用组合。
思路是：
- 用IF函数在某列中根据条件匹配数据，满足条件的返回所在行号，不满足条件的就返回一个比较大的数值，IF函数返回的是一个数组；
- SMALL函数在数组中按1，2，3...顺序取出对应的行号，把比较大的数值(超出数据区域)过滤掉；
- INDEX按SMALL返回的行号在区域内获取数据。
分别看一下3个函数的语法和含义：
动图中M2的公式为：
=INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8),ROW(A1)))
我们解析一下这个公式，公式中IF函数部分：
IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8)
是用IF函数在区域$A$2:$A$9中查找满足条件(=$L$2)的数据，满足条件就返回行号(减掉表头的1行)，不满足就给一个比较大的数字(4^8)，返回的是一个数组{1;65536;3;65536;65536;65536;7;65536}；
用这个数组替代SMALL函数的第一个参数，那么公式中SMALL部分就变成：
SMALL({1;65536;3;65536;65536;65536;7;65536},ROW(A1))
第二个参数用行号返回取第几个数据，往下填充时会每行加1，为的是产生1，2，3...这样的序列，那么M2返回数组中最小的那个是1，M3返回第2小的是3...；
M2最后的公式就是INDEX($C$2:$C$9,1)，就是“张三”；
M3最后的公式就是INDEX($C$2:$C$9,3)，就是“王五”；
因为函数返回的是数组，所以写好公式后，需要按Ctrl+Shift+Enter结束。
我们扩展一下，根据一个条件查询，可以返回任意列多个数据，INDEX函数可以根据行列获取数据，刚才的公式我们只用了INDEX函数的行，列需要呼叫MATCH函数来动态获取，我们把刚才的公式修改一下：
INDEX($B$2:$J$9,SMALL(IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8),ROW(A1)),MATCH(M$1,$B$1:$J$1,0))
红色部分是改动的地方，一是把INDEX函数去区域扩大，包含多行多列，二是用MATCH函数给INDEX函数当第3个参数(返回数据的列)，MATCH(M$1,$B$1:$J$1,0)表示根据选择的列M1，返回在区域中的位置(第几列)，我们看效果：
完美，至于根据一个条件返回多行多列并生成图表，因为牵扯到行数的变化，不同版本的Excel，图表功能略有不同，可以这样实现：
这样处理有个瑕疵，就是选3组时，图表的第3个序列是个空白序列，虽然没有线，但是图例中没有消失掉，可以用VBA来解决，因为篇幅原因，单独开一篇去介绍如何处理。
Excel函数组合持续更新中，如果对你有帮助，请关注点赞支持一下。
Excel函数组合(一) - VLOOKUP和MATCH组合
Excel函数组合(二) - INDEX和MATCH组合
Excel函数组合(三) - SUM和SUMIF组合
2、多条件匹配函数怎么用：sumifs函数多条件匹配
sumifs函数多条件匹配
方法一：增加辅助列法
常见的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(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((B2:B9=G2)*(C2:C9=H2)*D2:D9)
Sumproduct是数组乘积求和，
方法八：Sum的判断求和，数组函数
公式：{=SUM((B2:B9=G2)*(C2:C9=H2)*D2:D9)}
sumifs函数多条件匹配
方法/步骤
1
sumifs函数就是对多个条件进行求和的函数。
2
打开数据表。
3
输入好要求和的条件。如果条件量大还是提前输入在表格里清楚。
4
输入公式=sumifs(C2:C23,，要计算的数据区。
5
继续输入公式=sumifs(C2:C23,A2:A23，加入条件1区。
6
继续输入公式=sumifs(C2:C23,A2:A23,F2，加入条件1。
7
继续输入公式=sumifs(C2:C23,A2:A23,F2,B2:B23，加入条件2区。
8
继续输入公式=sumifs(C2:C23,A2:A23,F2,B2:B23,G2)，加入条件2，如果还有条件可以继续加入。
9
回车之后，得到结果。
sumifs函数多条件匹配
方法一：增加辅助列法
常见的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(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((B2:B9=G2)*(C2:C9=H2)*D2:D9)
Sumproduct是数组乘积求和，
方法八：Sum的判断求和，数组函数
公式：{=SUM((B2:B9=G2)*(C2:C9=H2)*D2:D9)}
本文关键词：多条件求和sumifs函数使用方法，sumifs多条件求和，sumifs两个条件匹配，多个条件的sumif函数，sumifs函数匹配的使用方法。这就是关于《多条件匹配函数怎么用，sumifs函数多条件匹配（Excel函数组合<四>）》的所有内容，希望对您能有所帮助！
- 最近发表