筛选或隐藏后序号不连续？你差的是这个万能Excel公式

序号不对号，工作就乱套！

好不容易排好的序号，只要一筛选或者一隐藏就又乱了。

特别是面对成百上千条数据时…

真让人头大！

今天我们就来解决这个问题~

继续往下看吧~

01

筛选或隐藏数据之后，序号不连续

相信大家都有过这种经历，工作中，我们经常需要对数据，进行筛选。

但是，筛选之后，序号并不会连续编号。

就比如下面的案例，当筛选出华东地区的数据之后，左侧的序号是不连续的。

而对比下图，筛选之后，编号都是连续的。

甚至，当有数据隐藏时，编号也都是连续的。

面对这样的问题，我们要怎样去做呢？

02

SUBTOTAL 函数的语法讲解

这里，需要引入 SUBTOTAL 函数，来解决上面这个问题。

SUBTOTAL 函数的语法如下：

=SUBTOTAL(function_num,ref1,[ref2],…)

可以理解为：

=SUBTOTAL(汇总方式,汇总区域)

SUBTOTAL 的第一参数，对应的函数如下图：

如果使用 1-11，会忽略已筛选掉的单元格，但包括手动隐藏的行。

如果使用 101-111，不仅会忽略已筛选掉的单元格，还会忽略手动隐藏的行。

03

SUBTOTAL 函数的实例讲解

上一部分，我们只是讲解了 SUBTOTAL 的语法，但是还不知道如何使用 SUBTOTAL 。

下面，就通过几个例子讲解一下。

下图中，正常情况下，对 B 列计数，三个公式的结果一样，都是 10。

对于为什么 SUBTOTAL 第一参数分别是 3 和 103，请看 SUBTOTAL 第一参数对应的函数表。

另外，这个对应表无需记忆，因为在写公式的时候，会有函数屏幕提示。

对 B 列，筛选出华东地区的数据时，COUNTA 还是 10，但是 SUBTOTAL 会忽略已经筛选掉的数据。

当只手动隐藏第 5 行数据时，只有 SUBTOTAL 的第一参数是「103」的时候，计数结果是 9 。

对比上面三个例子，应该就能理解了 SUBTOTAL 第一参数的意思：

如果使用 1-11，会忽略已筛选掉的单元格，但包括手动隐藏的行。

如果使用 101-111，不仅会忽略已筛选掉的单元格，还会忽略手动隐藏的行。

04

筛选或隐藏数据之后，序号保持连续

到这里，很多小伙伴肯定会举手：我知道了如何实现序号保持连续了。

然后，在 A2 单元格输入以下公式，并双击填充得到序号列。

=SUBTOTAL(103,$B$2:B2) 

好的，下面，我们来验证一下，这个公式是否正确。

当隐藏第 4 行数据时，编号是连续的。

当筛选出「华东地区」的时候，序号也是连续的。

但仔细一看，奇怪，怎么多了一个「华中地区」？

我明明只筛选了「华东地区」。

为什么会这样呢？

因为，此函数相当于分类汇总，在筛选时，最后一行是不参与筛选的。

知道了原因，我们终于可以放大招，给出最后的解决方法了。

方法一：

在最后一行数据下的第一行（此例中是 A12:D12 ）中任意单元格输入一个空格，扩充筛选区域，就可以解决。

方法二：

在 A2 单元格输入以下公式，双击填充可以解决。

=SUBTOTAL(103,$B$2:B2)*1 

方法三：

在 A2 单元格输入以下公式，双击填充也可以解决。

=SUBTOTAL(103,$B$2:B2) 

好了，问题终于解决了！

从这个问题中，你是否能一探 SUBTOTAL 的奥秘呢？

那就留个小问题考考你吧。

下图中，如何在筛选或隐藏状态下，得到华东地区的销售额？

欢迎下方「留言」，分享你的答案哦~

别忘了还有练习，练习文件获取方式就在评论里哦~