[Excel]制作一个隐藏行/列的提示计数器

说起Excel中各种各样致命的错误,那就不得不提到隐藏的行或者列所带来的数据歧义。为了帮助我们尽可能得避免这样的失误,让我们讨论下如何制作一个隐藏行列的提示器。

01提示被隐藏的行数

先考虑制作一个辅助列,将其的数值标为1。

然后再B2中输入这样一个公式命令:

=COUNT(Table1[订单计数]) – AGGREGATE(2,5,Table1[订单计数])

一起来解读下这个公式的含义:

  • COUNT(Table1[订单计数])

合计辅助数列,也就是统计整个表格行数量

  • AGGREGATE(2,5,Table1[订单计数])

Aggregate作为一个功能强大的函数,它能够实现对非隐藏行的求和。在这里参数1设置为2,代表求和;参数2设置为5,则代表忽略隐藏行。

而两者的差值,就是被隐藏掉的行的数量。

02 提示被隐藏的列数

有了AGGREGATE函数,我们照着上文,再写一个对被隐藏列数的计算公式,可好?

很不幸的是,AGGREGATE函数只对行有效,对于计算被隐藏的列数,我们得另寻出路了。

可以尝试如此改造表格,

  • 添加一个辅助行,并输入对应的函数公式 =IF(CELL(“width”,A1)>1,1,0) 利用CELL函数中求单元格宽度的命令,可以将被隐藏的单元格标注为0值,而未被隐藏的单元格标注为1值。

  • 在B3单元格中输入 =COUNT(A1:J1)-SUM(A1:J1) 从而计算出实际列数量与未被隐藏的列数间差异,从而得到被隐藏的列数。

不过要注意的是,这并不是一个完美的公式,因为当设置隐藏列或者取消隐藏列时,这个公式无法自动重新计算:

比如在上表基础上取消对于E、F列的隐藏,辅助行并没有重新计算,因而最终计算的隐藏行数出现了错误。

因此,你需要做的是,在每次隐藏或者取消隐藏后,都应该使用F9这个快捷键,强制让所有公式进行重新计算。