在EXCEL中,SUMIFS函数是"多条件求和"函数,但是我们工作需要完成多条件求差,EXCEL里是没有"多条件求差值"函数的,所以我们可以逆向利用多条件求和函数SUMIFS来完成"多条件求差值"。
▌先介绍下SUMIFS函数的参数和基础案例:
▶参数:SUMIFS(提取求和的区域,包含条件值1的条件区域,条件值1,……)最多可以有127个条件区域和127个条件值。
① 基础案例一:对每个产品分地区求和,如图1
图1
② 因为这两个表的首行"地区"顺序是一致的,都是从"一区"到"五区",所以我们只要在J5单元格输入公式,向右填充,向下填充就可以批量计算完每个产品的合计值。
图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函数来完成"多条件求差值",完成求减法功能。
图3
▶如图3、求差值案例二:求每个产品在每个区还剩多少库存?(总入库-总出库)
思路分析:"A产品"在"一区"的库存 = "一区A产品的入库数 - 一区A产品的出库数"。
- K4单元格输入=SUM(SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入库","出库"})*{1,-1}),因为是数组计算,所以输入完公式后要按CTRL Shift 回车才可以。
- SUMIFS($D$2:$D$15,$B$2:$B$15,$J4,$C$2:$C$15,{"入库","出库"})得到结果{1184,738},因为SUMIFS无法对数组求和,所以要在最前面加一个数组求和函数SUMPRODUCT。
- =SUMPRODUCT({1184,738}*{1,-1})变成=SUMPRODUCT(1184*1 738*(-1)),返回最终结果446。
- 然后向右拖动,向下拖动就行。
思路解析图片版:如图4