今天我们来对求和做一下总结,把各种场景下如何使用相应的函数来求和! 1、Ctrl+A,可以选择光标所在位置的当前区域(VBA中CurrentRegion)2、ALT+=,是快速求和的快捷键,同 【开始】- 【自动求和】一致!=SUMPRODUCT(MOD(COLUMN($B3:$I3),2)*$B3:$I3) 1、MOD功能取余,MOD(被除数,除数),这里处于2,那么结果余数只有0或者1,实际 数据都在计数列,所以余数都是0,预算在偶数列,余数是0,这样合计正好就是实际的!2、COLUMN函数是获取给定区域的列号,比如案例中COLUMN($B3:$I3) = {2,3,4,5,6,7,8,9}3、如果我们每组有很多列,那么我们就要指定MOD的值作为判断条件 比如我们有三列,在预算和实际的基础上增加一列,差额!
=SUMPRODUCT((MOD(COLUMN($B3:$J3),3)=1)*$B3:$J3) 本案例由于有实际和预算表头,所以我们也可以使用SUMIF来处理,唯一不推荐的就是 “闷头+”!
=SUMIF($B$2:$I$2,"实际",B3:I3) 这种情况下,SUMIF更简单,但是MOD方法更加灵活通用!先看一下正常求和的方式,结果还是错误值,无法正常求和!=SUMPRODUCT(IFERROR(C3:C11,0)) =SUM(D3:$D$12)-SUM(E4:$E$12) 1、合并单元格,尤其大小不同的合并单元格中,公式无法下拉,所以需要选中全部合并单元格,Ctrl+Enter一次性录入2、合并单元格中的值,在合并区域的左上角单元格中,比如柑橘类合计对应的值在E3,合并单元格区域(E3:E5)!3、利用错位思路,全部合计-扣除自己本身剩下的合计
=SUMIF(B3:B10,"*瓜*",C3:C10) 1、SUMIF及SUMIFS、COUNTIF等都支持通配符2、* 表示任意0个或者多个字符,如果要表示一个字符使用 ?(问号)=SUMIFS(F:F,A:A,I2,B:B,J2) 要点说明:SUMIFS函数第一参数是求和区域,SUMIF最后一个参数是求和区域!,如果记忆,SUMIFS多条件,不确定条件组个数,所以先定下求和区域! SUBTOTAL 中的参数9表示求和,但是这种求和只是针对筛选产生的可见区域求和,手动隐藏的,不影响结果,如果想要手动隐藏的也忽略,把9修改为109即可!=SUMIF($A$2:A2,A2,$B$2:B2) 要点说明:注意相对和绝对引用方式,都是锁定了区域的开始,下拉区域逐步扩大,从而达到累计效果!=SUMIFS(C:C,A:A,">=2019-7-1",A:A,"<="&EOMONTH(DATE(2019,7,1),0)) 要点说明:这种方式处理有一个好处就是,使用EOMONTH可以获取到指定日期当月最后一天的日期,这种我们可以针对指定的任意年月快速求和
=SUMPRODUCT((MONTH(A2:A14)=7)*(C2:C14)) 要点说明:SUMPRODUCT的写法虽然看上去更易懂和简洁,但是他的计算量要高出SUMIF方式太多,所以一般数据量大 ,首先要排除使用SUMPRODUCT来处理,取而代之的SUMIF或者SUBTOTAL等!=INDEX($A$2:$A$13,MAX((MMULT((ROW($C$2:$C$13)>=TRANSPOSE(ROW($C$2:$C$13)))*1,$C$2:$C$13)<=300)*ROW($A$2:$A$13))) 要点说明:作为压轴,MMULT矩阵相乘方式,还是有一点的难度的!这里的MMULT函数主要在内存中完成累计,这样就不用辅助列处理了!有兴趣的同学可以研究一下!没有MMULT函数基础的同学!可以先读一下拓展的基础知识!关于求和的各种套路我们就先总结这么多,知识多多总结才是你的!
|