我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在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),然后选中光标,向右,向下拖动就可以了。
▌介绍完用SUMIFS"多条件求和"的用法,现在讲解下怎么利用SUMIFS函数来完成"多条件求差值",完成求减法功能。
图3
▶如图3、求差值案例二:求每个产品在每个区还剩多少库存?(总入库-总出库)
思路分析:"A产品"在"一区"的库存 = "一区A产品的入库数 - 一区A产品的出库数"。
思路解析图片版:如图4
图4
思路拓展:如果是“入库总数”+“出库总数”,就省略不写{1,-1},因为SUMPRODUCT本身就是数组求和计算。
▶如图5、案例三:按日期多条件求和或多条件求差值
图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条件求和。
如图9:合并单元格处理演示
图9:合并单元格细节处理
如图10:SUMIFS多条件求和演示
图10:多条件求和操作演示
▶如图11、案例六:“求和区域”顺序不一致,怎么自动变换求和区域?
图11
思路分析:我们通过“公式”选项卡下的“定义名称”,再配合INDIRECT函数来实现动图引用。
如图12:“定义名称”动态演示
图12
如图13:嵌套INDIRECT函数,引用“定义名称”的内容,完成动态引用求和区域,操作演示
图13
以上就是EXCEL的SUMIFS多条件求和函数的相加,相减及数组加减的用法。
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08