在EXCEL中,SUMIFS函数是"多条件求和"函数,但是我们工作需要完成多条件求差,EXCEL里是没有"多条件求差值"函数的,所以我们可以逆向利用多条件求和函数SUMIFS来完成"多条件求差值"。

▌先介绍下SUMIFS函数的参数和基础案例:

▶参数:SUMIFS(提取求和的区域,包含条件值1的条件区域,条件值1,……)最多可以有127个条件区域和127个条件值。

① 基础案例一:对每个产品分地区求和,如图1

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图1

② 因为这两个表的首行"地区"顺序是一致的,都是从"一区"到"五区",所以我们只要在J5单元格输入公式,向右填充,向下填充就可以批量计算完每个产品的合计值。

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图2

③ J5单元格输入公式=SUMIFS(C$2:C$11,$B$2:$B$11,$I5),然后选中光标,向右,向下拖动就可以了。

  • 参数1:要提取求和的区域,"行绝对引用"是为了保证数据区域是从第2行开始到第11行结束,"列相对引用"是为了保证向右拖动时,数据区域会从第C列变成第D列。
  • 参数2:包含条件值"A产品"的条件区域,"绝对引用"是条件区域锁定,不会发生偏移。
  • 参数3:表示要根据条件值"A产品"求合计,向下拖动就会变成C产品、D产品、B产品。

▌介绍完用SUMIFS"多条件求和"的用法,现在讲解下怎么利用SUMIFS函数来完成"多条件求差值",完成求减法功能。

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图3

▶如图3、求差值案例二:求每个产品在每个区还剩多少库存?(总入库-总出库)

思路分析:"A产品"在"一区"的库存 = "一区A产品的入库数 – 一区A产品的出库数"。

  1. K4单元格输入=SUM(SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入库","出库"})*{1,-1}),因为是数组计算,所以输入完公式后要按CTRL+Shift+回车才可以。
  2. SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入库","出库"})得到结果{1184,738},因为SUMIFS无法对数组求和,所以要在最前面加一个数组求和函数SUMPRODUCT。
  3. =SUMPRODUCT({1184,738}*{1,-1})变成=SUMPRODUCT(1184*1+738*(-1)),返回最终结果446。
  4. 然后向右拖动,向下拖动就行。

思路解析图片版:如图4

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图4

思路拓展:如果是“入库总数”+“出库总数”,就省略不写{1,-1},因为SUMPRODUCT本身就是数组求和计算。


▶如图5、案例三:按日期多条件求和或多条件求差值

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图5

思路分析:按“一月份”、“二月份”或“三月份”等月份的条件求和,首先插入一列辅助列,用MONTH函数提取月份数,再根据“月份数”条件求和。

思路解析图片版:如图6

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图6

如果想求差值,3月份-1月份是多少?

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

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


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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

如图7

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


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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图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:合并单元格处理演示

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

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

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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

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


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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图11

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

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

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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图12

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

如何发现Office Excel隐藏功能,SUMIFS函数居然可以"多条件求差值"看了不后悔

图13

以上就是EXCEL的SUMIFS多条件求和函数的相加,相减及数组加减的用法。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注