SUM、SUMIF、SUMIFS、SUMPRODUCT等求和函数的使用方法及实际应用分享过啦(详细见以下链接),今天分享个稍微冷门但很好用,也同样拥有求和功能的函数。
EXCEL条件求和mp.EXCEL求和王--SUMPRODUCT函数mp.萌二笔记分类目录及书单mp.
用过EXCEL“数据”→“分类汇总”的小伙伴应该见过SUBTOTAL这个函数,不过估计没深入研究过具体用法。
一、SUBTOTAL函数
官方说明:SUBTOTAL函数返回一个数据列表或数据库的分类汇总。
用人话说,就是用于计算分类汇总,可以求平均值,求最大、最小值,计数,求和等等。
函数语法:SUBTOTAL(function_num,ref1,[ref2],...)
第一参数function_num指明函数功能,可选择值的范围在1到11之间,和101到111之间。
第二参数开始,是数据区域引用,最少必须有1个(即ref1),最多可254个命名区域或引用(ref2,ref3,...,ref254)。
SUBTOTAL函数忽略任何不包括在筛选结果中的行,不论使用什么function_num值。现在就先来分享EXCEL筛选状态下的求和及乘积后求和。
二、筛选状态下的求和
如下图,总计数量C7单元格公式=SUM(C2:C6),右拉到I7单元格得到各列对应总计数量:
现希望在不同筛选条件下,自动更新筛选后的数量求和结果。那增加一行,C8单元格输入公式=SUBTOTAL(9,C2:C6)后右拉到I8单元格:
三、筛选状态下乘积求和
还是上例,增加一列单价,求筛选后每行数量*对应单价的乘积后求和。
C10单元格输入以下公式后右拉到I10单元格:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(C1,ROW($1:$5),))*$J$2:$J$6*C2:C6)
选中公式中有疑惑的部分,按F9键拆解计算过程,帮助理解:
不在筛选结果的第2行、第6行,对应的C2、C6单元格,subtotal运行结果为0,对应的乘积结果也为0,也就不参与求和了。
【原文链接:EXCEL筛选状态下的求和问题】