马震安
在Excel中进行数据筛选时,有时需要为筛选后的数据添加序号、有时需要对符合条件的筛选数据进行汇总计算、还有时则需要根据筛选字段显示不同的标题。这些用Excel自带的自动筛选或高级筛选很难实现,但利用好SUBTOTAL函数,一切就变得容易多了。
为了能够更形象地说明SUBTOTAL函数对筛选起到的作用,我们构造了如图所示的基础数据工作表(图1)。
实例1:按部门筛选数据,并为筛选后的数据添加序号。
在A2单元格输入公式:=SUBTOTAL(3,B$1:B2)-1,向下填充;选中B2单元格,添加筛选。这样,筛选后的数据序号也是由小到大依次排列的(图2)。
實例2:按部门统计实发工资在7000元以上的人数。
在需要显示统计人数的单元格输入公式:
=SUMPRODUCT(SUBTOTAL(3,OFF SET(B2,ROW(1:10),))*(H3:H12>=7000))
这样,就实现了统计出按部门筛选实发工资在7000元以上的人数(图3)。
实例3:自动更正筛选后的标题。
在A1单元格输入:
= L O O K U P ( 1 , 0 / S U B T O T A L(3,OFFSET(B1,ROW(1:10)-1,)),B:B)&"实发工资在7000以上人数"
这样,当按部门筛选时,标题就会按筛选部门发生相应变化(图4)。