发布于 由linda

[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这个快捷键，强制让所有公式进行重新计算。