【原】忽略隐藏行、隐藏列的汇总，你都会吗？

你好，我是刘卓。欢迎来到我的公号，excel函数解析。在工作中，我们经常会用到忽略隐藏行（包括筛选后）的汇总，偶尔也会用到忽略隐藏列的汇总。今天就来分享下这两种用法。

-01-

忽略隐藏行的汇总

下图是某公司不同业务员在1~6月份的销售额，其中第13行是对销售额的合计。现在的要求是以忽略隐藏行的方式查看不同业务员的合计数。只需在B13单元格输入公式=SUBTOTAL(109,B3:B12)，右拉填充即可。

如下图所示，当隐藏第3~第12行的任意几行，第13行就会得到忽略隐藏行的汇总结果。这个用法我相信很多小伙伴都会用，主要用到subtotal函数。

subtotal函数返回列表或数据库的分类汇总。语法如下：

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

Function_num ：是1~11或101~111的数字，用来指定分类汇总所采用的函数。如果使用1~11，则不忽略手动隐藏的行。如果使用101~111，则忽略手动隐藏的行。我上面就是用109忽略手动隐藏的行来求和。

如果筛选后汇总，不管是用1~11，还是用101~111，都会忽略筛选后被隐藏的行。

Ref1 ：是要对其进行分类汇总的第1个区域。

Ref2,...：是要对其进行分类汇总的第2到第254个区域。

注意：

如果ref1，ref2中有其它小计，则将忽略这些嵌套小计以避免重复计数。subtotal函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num值。

对于注意事项的第1点，我解释一下。在A1:A6随意输入几个数字，然后在A7单元格输入公式=SUBTOTAL(9,A1:A6)，得到A1:A6的和。再在A10单元格输入公式=SUBTOTAL(9,A1:A7)，还是得到A1:A6的和，因为它会忽略A7中嵌套的subtotal。

另外也可以用aggregate函数进行忽略隐藏行的汇总。今天由于时间关系，暂不分享。

-02-

忽略隐藏列的汇总

还是同一份数据，其中H列是对销售额的合计。现在的要求是以忽略隐藏列的方式查看不同月份的合计数。效果如下图所示，当隐藏B~G列的任意列时，H列不会自动得到忽略隐藏列的汇总结果，还需按一下F9。

excel没有提供忽略隐藏列汇总的函数，那这个效果是怎么做到的？是利用cell函数通过辅助行的方法实现的。

在B38单元格输入公式=CELL("width",B38)，向右填充到G38单元格。

在H27单元格输入公式=SUMIF(B$38:G$38,">0",B27:G27)，下拉填充。隐藏B~G列任意列，并按F9键重新计算，H列即可得到最后的结果。

cell函数用于获取单元格的信息。可以返回引用区域左上角单元格的格式、位置或内容的有关信息。语法如下：

CELL(info_type, [reference])

info_type ：一个文本值，指定要返回的单元格信息的类型。有很多类型，比如address，col，row等。这里只说一下width的含义，其它的大家自己看下帮助的。width返回取整后的单元格的列宽。

reference ：是需要得到相关信息的单元格。如果省略该参数，则返回最后更改的单元格的信息。如果该参数是一个区域，则返回左上角单元格区域的信息。

CELL("width",B38)返回B38单元格的列宽，当时的列宽为7。当公式所在列隐藏时，cell函数结果返回0。利用这个特性，可以用sumif函数计算忽略隐藏列的汇总求和。

链接：

https://pan.baidu.com/s/1TsJ2zzZDPgzYYjUyeNhqrQ

提取码：dzpe