1分钟！学会统计筛选、隐藏后的数据，秒杀90%的同事！

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进秋叶 Excel 读者快乐交流群！

本文作者：明镜在心

本文编辑：竺兰

在 Excel 中，函数是处理和汇总数据的利器。

比如我们经常使用的 SUM（求和）、COUNT（计数）、AVERAGE(平均值）等函数。

但在某些场景下，如果对数据进行了筛选或手动隐藏了数据，此时再想要汇总计算，上面这些函数就无能为力了。

这时我们需要使用专门的函数，用于处理此类问题。

下文揭晓答案，跟我一起来看看吧！（PS.下文中如果公式显示不全，请左右滑动公式查看）

问题描述

如下图，是一张公司研发项目明细表：

现在我们想看下 2020 年研发项目预算费用总共是多少

最简单的方法是对筛选之后的表格进行求和。

我们先在【F1】单元格输入公式：

=SUM(E2:E16)

然后，通过筛选【C】列的研发年份，筛选出 2020。

最终如下图：

正确的数字应该是 260，但这里筛选前后的结果都是 805，显然不是我们想要的。

这个问题该怎么解决呢？

解决方法

此时，我们必须使用专门处理隐藏数据的函数，比如：SUBTOTAL 函数，或者 AGGREGATE 函数。

如下图：

我们将 F1 单元格中的公式改写成：

=SUBTOTAL(9,E:E)

或者：

将公式改写成：

=AGGREGATE(9,5,E:E)

用这两个函数计算，就会忽略隐藏的行数据，返回的结果也就是我们想要的了！

另外需要提醒大家的是：在没有筛选或者隐藏数据的情况下，其统计结果与 SUM 等函数是完全一样的。

函数解读

下面我就来给大家简单介绍下，这两个强大的函数。

▋SUBTOTAL 函数

作用是：在指定的单元格区域内根据指定的分类汇总函数进行计算。

语法结构：

SUBTOTAL（function_num,ref1,[ref2],...）

▲ 左右滑动查看

通用套路是：

SUBTOTAL（指定分类汇总函数，单元格区域）

下图是第 1 参数取值范围：

它将 11 个函数的功能集于一身，特别强大，可以满足日常大部分统计需求。

这里需要跟大家说明的是：

如果使用 1-11，将包括手动隐藏的行；如果使用 101-111，则排除手动隐藏的行。

我们来看两个例子，就能区分出他们的不同了！

❶ 在筛选的情况下，两者返回的结果是一样的。

还是以上面的例子为例，筛选出 2020 年的研发项目，然后汇总预算费用总和。

【F1】公式：

=SUBTOTAL(9,E:E)

【F2】公式：

=SUBTOTAL(109,E:E)

可以看见他们的结果都是 260，完全一致。

另外：在没有隐藏数据的时候，两者返回的结果也是一样的。

❷ 两者的不同点在于：如果有手动隐藏行，其结果会有所不同。

还是以上面的例子为例，这次不通过筛选，而是手动把 2019 年和 2021 年研发项目所在的行都隐藏起来，结果如下：

大家可以看到，手动隐藏了 2 行-6 行和 12 行-16 行，两个公式的计算结果就不一样了。

【F1】单元格的公式是：

=SUBTOTAL(9,E:E)

此参数将会把手动隐藏的行数据也计算在内。返回结果：805。

【F7】单元格的公式是：

=SUBTOTAL(109,E:E)

此参数并不会把手动隐藏的行数据计算在内，它只计算显示出来的行数据。返回结果：260。

▋AGGREGATE 函数

AGGREGATE 是 Excel 2010 及以上版本才有的函数。

作用是：在指定的单元格区域或者数组内根据指定的分类汇总函数，并且忽略哪些值进行计算。

它有两种形式：

❶ 引用形式

AGGREGATE（function_num, options, ref1, [ref2], …）

通用套路：

AGGREGATE（指定汇总函数，忽略哪些值，单元格区域）

❷ 数组形式

AGGREGATE（function_num, options, array, [k]）

通用套路：

AGGREGATE（指定汇总函数，忽略哪些值，数组，第 K 个值）

第 1 参数 function_num 的取值范围如下图：

它一共有 19 个函数的功能。比上面 SUBTOTAL 多了 8 个函数功能，可见它的功能进一步扩展了。

第 2 参数 Options（选项）的取值范围如下图：

一共有 8 个可选参数。

它的使用方法跟 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 年下半的总预算是多少，结果如下：

【P 列】是 SUM 函数；

【Q 列】是 SUBTOTAL 函数；

【R 列】是 AGGREGATE 函数。

可以看出三个函数的计算结果一样。并没有忽略隐藏的列。

那这个问题该如何解决呢？

答案是：对于隐藏列，Excel 并没有专门用于解决这类问题的函数。

但是，我们可以通过辅助行+函数组合，解决该类问题。

❶ 添加辅助行。

在【D7】单元格输入公式：

=CELL("width",D6)

并向右拖动将公式复制到【O7】。

❷ 在【P2】单元格输入如下公式：

=SUMIF($D$7:$O$7,">0",D2:O2)

❸ 隐藏【D:I】列之后，按【F9】键（某些笔记本电脑需要按【Fn+F9】）刷新下公式。

此时公式就会得出正确结果了！

这里的原理是：

利用 CELL 函数计算单元格宽度，如果列被隐藏了，那么宽度就为 0，再使用 SUMIF 条件求和函数，计算出大于 0 的宽度对应的值。

👉 CELL 函数简介：

语法结构：

CELL(info_type, [reference])

这里我们第一参数写入"width"，代表计算单元格的列宽，以默认字号的一个字符宽度为单位。

如下图，我们想计算【B1】单元格的列宽：

可以在【A1】单元格写入如下公式：

=CELL("width",B1)

如果列宽有改动时，此函数不能实时更新，需要按下【F9】（某些笔记本电脑需要按【Fn+F9】）键刷新下。

总结一下

今天我们一起学习了处理隐藏数据的方法。

❶ SUBTOTAL 和 AGGREGATE 函数汇总计算隐藏行或者非隐藏行数据。

❷ 利用辅助列+函数组合汇总计算隐藏列数据。

学完今天这篇文章，想必大家对于隐藏数据的处理有了更深刻的认识吧

如果你在日常工作中处理隐藏数据还有其他的好方法，欢迎留言与我讨论噢！

如果你想学习更多 Excel 知识，想要快速掌握数据处理的常用方式，提高办公效率！

小 E 推荐你学习我们秋叶家的《Excel 新手到高手》这门课程~

不用担心看不懂、学不会、难上手，课题会根据实例为你讲解，循序渐进，让 Excel 技术一步达成！

原价 199 ，双 11 活动价 99，领券还可立减 10 元哦~到手只需 89 元！

还等什么？赶紧扫码抢购吧👇👇👇~

欢迎加入秋叶Excel专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

点个在看

干货不断 !

👇👇👇

动动小手

分享给朋友~

👇👇👇