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. 从下图可以看出,隐藏行以后,公式并没有变化,但是编号发生了变化,自动忽略了隐藏行重新连续编号
联系客服