【excel怎么批量求差】发现excel隐藏功能的SUMIFS函数看到“多条件差异”并不后悔

在EXCEL中，SUMIFS函数是“多条件总和”函数，但我们必须完成多条件调整。EXCEL没有“查找多条件差异”函数，因此可以反向利用多条件求和函数SUMIFS来执行“查找多条件差异”。

首先介绍了SUMIFS函数的参数和基本案例。

参数：total(包含条件值1的条件区域，条件值1，)最多可以有127个条件区域和127个条件值。

基本案例1:如图1所示，每个产品的区域合计。

图1

两张表的第一行“地区”顺序从“第一区”到“第五区”是一致的，因此可以在J5单元格中输入公式，向右填写，向下填写，从而大量计算每种产品的总和。

图2

在储存格J5中输入公式=sumifs (c $ 2:c $11，$ b $23360 $ b $11，$ i5)，然后选取游标并向右下方拖曳即可。

引数1:要撷取总计的区域。「列绝对参照」是为了让资料区域从列2到列11结束。「栏相对参照」是为了让资料区域在向右拖曳时从栏c变更为栏d。参数2:包含条件值“a产品”的条件区域；“绝对引用”是条件区域锁定，不会发生偏移。参数3:要根据基准值“a产品”求和，请向下拖动以成为c产品、d产品、b产品。介绍SUMIFS“多条件求和”的用法，现在介绍如何使用SUMIFS函数完成“多条件差分”，完成减法功能。

图3

图3、寻找差异案例2:每个产品在每个区域还剩多少库存？(总签入-总签出)

事故分析：“A产品”的“区域”库存=“区域A产品的接收数量-区域A产品的出库数量”。

K4单元格输入=总计(sumifs ($ d $2: $ d $15、$ b $2: $ b $15、$ j4、$ c $23360 $ c)=SUMPRODUCT({1184，738}*{1，-1})更改为=SUMPRODUCT(1184*1 738*(-1))，并返回最终结果446。然后向右拖动并向下拖动想法分析照片版本：图4

图4

扩展思路：如果是“签入总数”、“签出总数”，请省略不写{1，-1}，因为SUMPRODUCT本身就是数组总和计算。

图5，案例3:按日期查找多条件总和或按多条件查找差异

图5

事故分析：根据“一月”、“二月”、“三月”等月份的条件和，首先插入辅助列，然后使用MONTH函数提取月份。

份数，再根据“月份数”条件求和。

思路解析图片版：如图6

图6

如果想求差值，3月份-1月份是多少？

输入公式=SUMPRODUCT(SUMIFS(C$2:C$15,$H$2:$H$15,{3,1})*{1,-1})，

按CTRL+Shift+回车，得出结果为-2178。3月少，1月多，所以结果为负数。

---

▶如图7、案例四：参数添加通配符*，数组模糊查找条件求和

如图7

在J2单元格输入=SUMPRODUCT(SUMIFS(C$2:C$15,$B$2:$B$15,{"*A*","*b*"}))，按CTRL+Shift+回车，完成数组求和。字母B和b是一样的，不区分大小写。

---

▶如图8、案例五：合并单元格怎么正确多条件求和？

图8

在A列有合并单元格下，直接在H3单元格输入公式=SUMIFS(B$2:B$8,$A$2:$A$8,$G3)，得出的结果"800"是错误的。

思路分析：我们需要对A列的合并单元格进行细节处理，才可以用SUMIFS条件求和。

1. 选中A2:A8区域——点“开始”选项卡的"格式刷",在随便一个区域点一下，我们选中O列O2单元格，这时O2:O8就会变出同样的合并单元格。
2. 选中A2:A8区域——点“开始”选项卡的“合并后居中”，就取消了合并单元格——按F5，定位条件，空值——直接输入公式=A2，按CTRL+回车，就填充好了。
3. 选中O2:O8区域——点“开始”选项卡的"格式刷"，在A2单元格点一下，就会重新出现合并单元格。
4. 这时再用公式计算，在H3单元格输入公式=SUMIFS(B$2:B$8,$A$2:$A$8,$G3)，得出结果1856是正确的。

如图9：合并单元格处理演示

图9：合并单元格细节处理

如图10：SUMIFS多条件求和演示

图10：多条件求和操作演示

---

▶如图11、案例六：“求和区域”顺序不一致，怎么自动变换求和区域？

图11

思路分析：我们通过“公式”选项卡下的“定义名称”，再配合INDIRECT函数来实现动图引用。

1. 选中B1:E8区域——点“公式”选项卡下的“根据所选内容创建”，选首行，确定。
2. 这时“名称管理器”里就有根据“一区”、“二区”、“三区”、“四区”建立的名称。
3. 在H3单元格输入=SUMIFS(INDIRECT(H$2),$A$2:$A$8,$G3)，完成。
4. INDIRECT(H$2)意思是引用H2单元格"二区"地址的内容，因为我们”定义了名称",所以会引用"二区"对应的数据区域。（关于INDIRECT函数的使用可以看我的前面文章）

如图12：“定义名称”动态演示

图12

如图13：嵌套INDIRECT函数，引用“定义名称”的内容，完成动态引用求和区域，操作演示

图13

以上就是EXCEL的SUMIFS多条件求和函数的相加，相减及数组加减的用法。喜欢的朋友点点关注，帮忙转发、评论、点赞、收藏，非常感谢！