一、案例
如下图所示,为某公司员工信息表。要求在A列填充连续序号,且当按部门筛选数据时,A列序号仍保持连续。
如下图所示,当未筛选数据时,A列序号是连续的。
当筛选出“营销部”的员工数据时,序号仍然连续,如下图所示:
二、解决方法
在A2单元格输入公式 =SUBTOTAL(3,$B$2:$B2)*1
拖动填充柄向下复制公式。
公式解析:
(1)SUBTOTAL函数用于对一列数据进行分类汇总,第一个参数用于指定汇总类型,包括求和、求平均值、计数等。本例中SUBTOTAL第一个参数为“3”,指统计非空单元格的个数。
Excel中的COUNTA函数也可以用于统计非空单元格的个数。两个函数的区别在于SUBTOTAL函数可以排除筛选掉的单元格。
如下图所示,分别使用SUBTOTAL和COUNTA函数对C2:C11单元格计数。当未筛选数据时,两个函数返回的值均为“10”。
当筛选“研发部”时,结果如下图所示。
SUBTOTAL返回“4”,COUNTA的返回值不变。这是由于SUBTOTAL不统计筛选掉的值。
(2)本例中SUBTOA公式后需要乘以1(即“*1”),否则会出错。如下图所示,当公式中没有“*1”时,筛选“财务部”,我们会发现最后一行的“营销部”的数据也出现在筛选数据中。
仔细观察,我们会发现“财务部”对应的两行数据的行号是蓝色的,而“营销部”对应的数据的行号是黑色的。
我们知道,当在Excel中进行筛选时,被筛选出的数据行号会变成蓝色。这说明当我们在进行筛选时,表格的最后一行“营销部”的数据并不会纳入筛选范围,而是独立于数据之外,无论我们筛选哪个部门的数据,这一行数据始终都会出现。为了避免这种情况的出现,需要在SUBTOTAL之后加上“*1”。
END
联系客服