筛选状态下的条件求和,你会吗?
1.求筛选状态下考核成绩的总和

2.求筛选状态下销售部的考核成绩总和

这时就要用到offset的多维引用以及subtotal忽略筛选状态下隐藏的行的特性。在F2单元格输入下面的公式:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,ROW(1:13),))*(C3:C15="销售部")*D3:D15)
OFFSET(C2,ROW(1:13),)这部分以C2单元格为基点,分别向下偏移1,2,3,···,13行,得到由C3、C4、···、C15组成的三维引用。三维引用的每个区域都是一个单元格。
SUBTOTAL(3,OFFSET(C2,ROW(1:13),))这部分用subtotal统计多维引用每个区域的非空单元格个数,而且会忽略隐藏行。
假如筛选的条件是“性别-女”,那么subtotal返回的结果为{1;1;0;1;0;1;1;0;0;1;0;1;1}。也就是筛选后,可见的单元格对应的是1,隐藏的单元格对应的是0。

SUBTOTAL(3,OFFSET(C2,ROW(1:13),))*(C3:C15="销售部")*D3:D15这三部分相乘,得到了一维数组{61;0;0;0;0;37;86;0;0;0;0;0;0}。
实际是同时满足两个条件,第1必须是可见单元格,第2必须是销售部。这两个条件同时满足的,返回对应的成绩。否则只要有一个条件不满足的返回0。最后用sumproduct求和。
=SUMPRODUCT(SUBTOTAL(4,OFFSET(D2,ROW(1:13),))*(C3:C15="销售部"))
最后留个思考题:
求筛选状态下不重复部门的个数,并且把不重复的部门提取出来。如下图所示。

https://pan.baidu.com/s/1epIlzlYua-T2uO2Z9Li6Tg