subtotal 函数是 Excel 中一个功能非常全面的函数，它不仅仅可以求和，还可以计算乘积、平均值、最大最小值、计数、计算方差等。

可以说，这是一个包罗万象的函数，只要通过调整参数设置，就能以一代多实现不同函数的效果。

不仅如此，subtotal 函数还允许设置是否要忽略隐藏值。

今天我们就以一个案例来学习一下：如何用 subtotal 函数忽略隐藏值自动顺序编号。

函数作用：

  • 返回列表或数据库中的分类汇总。

语法：

  • SUBTOTAL(function_num,ref1,[ref2],...)

参数：

  • function_num ：必需，数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。1-11 包括手动隐藏的行；101-111，排除手动隐藏的行；始终排除已筛选掉的单元格。
  • ref1：必需，要对其进行分类汇总计算的第一个命名区域或引用。
  • [ref2],...：可选，要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。

案例：

对下图 1 的 B 列进行顺序编号，当手动隐藏部分行时，要求自动跳过隐藏行连续编号。效果如下图 2、3。

解决方案：

1. 在 A2 单元格输入以下公式：

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

公式释义：

  • 103：从上面的参数表可以看出，103 相当于 counta 函数，且忽略隐藏值
  • counta 函数的作用是计算范围中不为空的单元格的个数
  • 计算区域的首行需要绝对引用；末行相对引用，随着公式下拉自动增加，从而实现编号递增

2. 向下拖动 A2 单元格复制公式，就会自动顺序编号

3. 现在，我们选中红框范围的行 --> 右键单击，选择“隐藏”

4. 从下图可以看出，隐藏行以后，公式并没有变化，但是编号发生了变化，自动忽略了隐藏行重新连续编号

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。