【原】动态筛选后，生成动态连续序号！

Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能

Excel爱好者大本营

用1%的Excel基础搞定99%的职场问题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万Excel爱好者聚集地

SUPER EXCEL MAN

    

大家好，今天我们来简单讲讲“筛选状态下的连续序号”问题。要解决这个问题，就不得不学习SUBTOTAL函数了。

SUBTOTAL函数用于返回单元格区域引用或数据库中的合计值，能够使用求和、计数、求平均值、最大值、最小值等多种统计方式对可见单元格中的内容进行汇总。

SUBTOTAL函数语法：

=SUBTOTAL(功能代码，数值区域)

第一参数代表：使用数字指定使用哪种函数执行汇总计算。

如果第一参数是1~11的数字，汇总结果会忽略使用筛选命令所隐藏的行，但不会忽略“隐藏行”命令所隐藏的行。

如果第一参数是101~111的数字，汇总结果会忽略使用筛选命令所隐藏的行，同时也会忽略“隐藏行”命令所隐藏的行。

了解完了SUBTOTAL函数的基本概念后，我们来看一下例子。

如下图所示：

我们执行完成数据源中的筛选操作后，我们想要使C列的序号始终保持连续递增的状态。

我们由内向外一步一步试着输入函数公式。

在C2单元格输入公式：

=D$2:D2

并向下填充公式。

D$2:D2部分：首个D2对行进行绝对引用，第二个D2对行进行相对引用。当公式向下复制时，会依次变成如G列的效果，该区域起始单元格不变，结束单元格逐步扩展区域。

由于数组结果只会默认保留数组中的首个单元格内容。我们可以通过F9键将每个单元格中的数组结果展开，观察效果。

{"石家庄"}

{"石家庄";"济南"}

{"石家庄";"济南";"保定"}

{"石家庄";"济南";"保定";"德州"}

{"石家庄";"济南";"保定";"德州";"唐山"}

{"石家庄";"济南";"保定";"德州";"唐山";"日照"}

{"石家庄";"济南";"保定";"德州";"唐山";"日照";"大同"}

{"石家庄";"济南";"保定";"德州";"唐山";"日照";"大同";"广州"}

{"石家庄";"济南";"保定";"德州";"唐山";"日照";"大同";"广州";"太原"}

{"石家庄";"济南";"保定";"德州";"唐山";"日照";"大同";"广州";"太原";"珠海"}

在外面嵌套SUBTOTAL函数：

=SUBTOTAL(3,D$2:D2)

用D$2:D2作为SUBTOTAL的第二参数。

SUBTOTAL函数的第一参数使用数字3，表示使用COUNTA函数的计算规则，统计D$2:D2区间内数组元素的非空单元格的数量。

由于D$2:D2公式向下填充后，每个单元格数组的元素是逐次递增的，由1到10，所以COUNTA计算规则下计算出了每个数组中可见状态下的非空单元格的元素的数量为1~10，也就相当于为可见单元格加上了递增的序号。

由于SUBTOTAL函数的作用是计算数据的分类汇总，Excel会把数据表最后一行的SUBTOTAL函数作为汇总行，而非数据的一部分，因此在使用SUBTOTAL函数的工作表中执行筛选时，SUBTOTAL函数的最后一行不执行筛选，会始终显示，影响筛选结果的正确性，如下图所示，“珠海”所在行始终显示。

我们最后完善公式为：

=SUBTOTAL(3,D$2:D2)*1

在SUBTOTAL函数的计算结果的基础上执行一次数学运算，乘以1后，能够使Excel不再将最后一行SUBTOTAL函数作为汇总行。

回顾关键内容，善用图片表达，学会建立联系，拓展深度广度，浓缩关键概念，应用到行动中，善于归纳总结，尝试进行分享。