sumifs函数多条件匹配

sumifs函数多条件匹配

  sumifs函数多条件匹配，SumIfs函数是 Excel 中多个求和函数之一，SumIfs函数的多条件用数组表示。下面小编为大家分享sumifs函数多条件匹配。

  sumifs函数多条件匹配1

  方法一：增加辅助列法

  常见的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函数多条件匹配2

  方法/步骤

  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函数多条件匹配3

  一、Excel Sumifs 语法

  1、表达式：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  中文表达式：SUMIFS(求和区域，条件区域1，条件1，[条件区域2，条件2]，...)

  2、说明：

  A、表达式中，前三个参数是必需的，括号([])中的参数是可选的，省略号(...)表示继续构建[条件区域3，条件3]、[条件区域4，条件4]、...、[条件区域n，条件n]。

  B、可以在条件中使用通配符“问号 (?) 和星号 (*)”，问号匹配任意单个字符，星号匹配任意一个或一串字符；果要找“? 和 *”，需要在它们前面加转义字符 ~，例如要查找 ?，需要这样写 ~?。

  C、如果在条件中使用文本条件、含有逻辑或数学符号的条件都必须用双引号 (") 括起来；例如使用大于号，应该这样写：">50" 或 ">"&50。

  D、SumIfs 只对数值求和，文本则忽略，如果选中的`求和区域全为文本，则返回 0；如果既有文本又有数值，则只取数值求和。

  二、Excel Sumifs函数的使用方法举例

  (一)单条件

  1、假如要统计在广州销售的所有服装的销量之和。选中 H2 单元格，把公式 =SUMIFS(F2:F10,D2:D10,"广州") 复制到 H2，按回车，则返回在广州销售的所有服装销量之和，如图1所示：

  图1

  2、公式说明

  公式 =SUMIFS(F2:F10,D2:D10,"广州") 中，F2:F10 是求和区域，D2:D10 是条件区域，“广州”是条件。

  (二)多条件

  1、双条件

  A、假如要统计在广州销售的且为“衬衫”的所有服装销量之和。把公式 =SUMIFS(F2:F10,D2:D10,"广州",C2:C10,"衬衫") 复制到 H2 单元格，按回车，则统计出所有满足条件销量之和，操作过程步骤，如图2所示：

  B、公式说明

  公式 =SUMIFS(F2:F10,D2:D10,"广州",C2:C10,"衬衫") 的求和区域为 F2:F10；条件区域1为 D2:D10，条件1为“广州”；条件区域2为 C2：C10，条件2为“衬衫”。

  2、数组条件且与Sum函数结合

  A、假如要统计在广州和深圳销售的衬衫销量之和。选中 H2 单元格，把公式 =SUM(SUMIFS(F2:F10,D2:D10,{"广州","深圳"},C2:C10,"衬衫")) 复制到 H2，按回车，则统计出满足的衬衫销量之和，操作过程步骤，如图3所示：

  B、公式说明

  公式 =SUM(SUMIFS(F2:F10,D2:D10,{"广州","深圳"},C2:C10,"衬衫")) 由 Sum 和 SumIfs 两个函数组成，其中 SumIfs 函数用来分别统计在“广州”和“深圳”销售的“衬衫”销量之和，Sum函数用来把 SumIfs 求出的在“广州”和“深圳”销售的“衬衫”销量之和加起来。SumIfs 的条件1“{"广州","深圳"}”为数组，数组中只有两个条件，如果还要加条件，可以在后面添加。

  提示：如果不用 Sum 函数，仅统计“广州”的“衬衫”销量。

  3、多数组条件

  A、假如要统计在广州和杭州销售的价格为 86、80 或 65 元的服装销量之和。把公式 =SUM(SUMIFS(F2:F10,D2:D10,{"广州","杭州"},E2:E10,{86;80;65})) 复制到 H2 单元格，如图4所示：

  B、按回车，则统计出满足条件的服装销量之和，如图5所示：

  注意：条件2 {86;80;65} 中数字之间用半角分号(;)，如果用半角逗号(,)，只会返回第一条满足条件的销量；结果返回 329，它正是第一条记录“长袖白衬衫”的销量，如图6所示：

  (三)用通配符组合条件

  1、假如要统计产品名称为四个字、销售地区含有“州”字、价格大于60元的全部服装销量之和。把公式 =SUM(SUMIFS(F2:F10,B2:B10,"",D2:D10,"*州*",E2:E10,">60")) 复制到 H2 单元格，按回车，则统计所有满足条件的服装销量之和，如图7所示：

  2、公式说明

  公式 =SUM(SUMIFS(F2:F10,B2:B10,"",D2:D10,"*州*",E2:E10,">60")) 中的 SumIfs 由三组“条件区域和条件”组成；第一组(B2:B10,"")是从 B2:B10 中找出名称为四个字的服装；第二组(D2:D10,"*州*")是从 D2:D10 中找出含有“州”字的服装；第三组(E2:E10,">60")是从 E2：E10 中找出“价格”大于 60 元的服装。最后把三组统计出的服装销量用 Sum 求和。