【Excel技巧】1分钟！学会统计筛选、隐藏后的数据，秒杀90%的同事！
作者：明镜在心
比如我们经常使用的 SUM（求和）、COUNT（计数）、AVERAGE(平均值）等函数。
但在某些场景下，如果对数据进行了筛选或手动隐藏了数据，此时再想要汇总计算，上面这些函数就无能为力了。
下文揭晓答案，跟我一起来看看吧！（PS.下文中如果公式显示不全，请左右滑动公式查看）
现在我们想看下 2020 年研发项目预算费用总共是多少
然后，通过筛选【C】列的研发年份，筛选出 2020。
正确的数字应该是 260，但这里筛选前后的结果都是 805，显然不是我们想要的。
此时，我们必须使用专门处理隐藏数据的函数，比如：SUBTOTAL 函数，或者 AGGREGATE 函数。
用这两个函数计算，就会忽略隐藏的行数据，返回的结果也就是我们想要的了！
另外需要提醒大家的是：在没有筛选或者隐藏数据的情况下，其统计结果与 SUM 等函数是完全一样的。
作用是：在指定的单元格区域内根据指定的分类汇总函数进行计算。
它将 11 个函数的功能集于一身，特别强大，可以满足日常大部分统计需求。
如果使用 1-11，将包括手动隐藏的行；如果使用 101-111，则排除手动隐藏的行。
还是以上面的例子为例，筛选出 2020 年的研发项目，然后汇总预算费用总和。
另外：在没有隐藏数据的时候，两者返回的结果也是一样的。
❷ 两者的不同点在于：如果有手动隐藏行，其结果会有所不同。
还是以上面的例子为例，这次不通过筛选，而是手动把 2019 年和 2021 年研发项目所在的行都隐藏起来，结果如下：
大家可以看到，手动隐藏了 2 行-6 行和 12 行-16 行，两个公式的计算结果就不一样了。
此参数将会把手动隐藏的行数据也计算在内。返回结果：805。
此参数并不会把手动隐藏的行数据计算在内，它只计算显示出来的行数据。返回结果：260。
AGGREGATE 是 Excel 2010 及以上版本才有的函数。
作用是：在指定的单元格区域或者数组内根据指定的分类汇总函数，并且忽略哪些值进行计算。
-
AGGREGATE（function_num, options, ref1, [ref2], …）
第 1 参数 function_num 的取值范围如下图：
它一共有 19 个函数的功能。比上面 SUBTOTAL 多了 8 个函数功能，可见它的功能进一步扩展了。
第 2 参数 Options（选项）的取值范围如下图：
它的使用方法跟 SUBTOTAL 差不多，最常用的就是它可以忽略隐藏行和错误值计算。
❶ 在没有筛选或手动隐藏行的情况下，通过选用第 2 参数的值，来忽略错误值。
此时使用 SUM 和 SUBTOTAL 函数将会出错。
而使用 AGGREGATE 函数（=AGGREGATE(9，6，E:E)），第 2 参数输入 6，它会忽略错误值，将其他数值计算在内。
❷ 在有筛选或手动隐藏行的情况下，通过选用第 2 参数的值，来忽略隐藏行和错误值。
此时使用 SUM 和 SUBTOTAL 函数还是会出错。
将 AGGREGATE 函数的第 2 参数改为 7（=AGGREGATE(9，7，E:E)），就可以同时忽略隐藏行和错误值了，一举两得。
以上函数只针对隐藏行（无论是筛选条件下的隐藏行，还是手动隐藏行），但如果是隐藏列，又该如何解决呢？
下图是一张 2020 年按月分配的研发经费明细表。
现在我们把 1-6 月隐藏起来，只想看 2020 年下半的总预算是多少，结果如下：
可以看出三个函数的计算结果一样。并没有忽略隐藏的列。
答案是：对于隐藏列，Excel 并没有专门用于解决这类问题的函数。
但是，我们可以通过辅助行+函数组合，解决该类问题。
❸ 隐藏【D:I】列之后，按【F9】键（某些笔记本电脑需要按【Fn+F9】）刷新下公式。
利用 CELL 函数计算单元格宽度，如果列被隐藏了，那么宽度就为 0，再使用 SUMIF 条件求和函数，计算出大于 0 的宽度对应的值。
这里我们第一参数写入"width"，代表计算单元格的列宽，以默认字号的一个字符宽度为单位。
如果列宽有改动时，此函数不能实时更新，需要按下【F9】（某些笔记本电脑需要按【Fn+F9】）键刷新下。
❶ SUBTOTAL 和 AGGREGATE 函数汇总计算隐藏行或者非隐藏行数据。
学完今天这篇文章，想必大家对于隐藏数据的处理有了更深刻的认识吧
如果你在日常工作中处理隐藏数据还有其他的好方法，欢迎留言与我讨论噢！
|
