比如下面这个表格,有2016年和2017年的进货日期,只有数量和单价,没有各项目的小计。如果要筛选出2017年来项目形成一张报表来进行求和,直接使用SUMPRODUCT是不能成功的。有人说可以选择区域啊,那么如果筛选出2016的不是又要更改求和区域吗?其实EXCEL提供了一个处理筛选的函数。请看分析。
数据源示例
首先:利用SUBTOTAL参数102,对隐藏单元格进行计数。SUBTOTAL(102,OFFSET(A1,ROW(1:13),)),得到一个{0;0;0;0;0;0;1;1;1;1;1;1;0}的内存数组,从这里就可以看到,当所在行筛选隐藏后,函数返回结果为0,而显示的行则会返回1,然后就可以利用SUMPRODUCT一一对应乘积求和了。
完整公式:SUMPRODUCT(SUBTOTAL(102,OFFSET(A1,ROW(1:13),)),C2:C14,D2:D14)
效果示例
(辅助列是本例效果示例,并没有采用辅助列求和)
联系客服