刚入门的小伙伴总喜欢抱怨“Excel的函数太多了,老是记不住,有没有一个能汇总很多函数功能的函数呢?”不瞒你说,还真有!今天咱们要说的这个函数,其最大的功能就是可以替代11种函数使用。除此之外,它还可以根据不同的筛选结果,变更最后的计算结果!怎么样?是不是光听起来就很厉害呢?废话不多说,赶紧和小编一起来看看吧!

忽略筛选行求和

“苗老师,我碰到了个问题”,小白一上班就来找我,她说:“我有一张合计的表,打印的时候不想打印某些人的内容,就把它们用筛选隐藏了,但是每次求和都要更改求和区域,老麻烦了。”

我说:“那好办,换个求和函数就行。别用SUM了,试试SUBTOTAL。”

小白:“这是什么函数,没用过。”

我说:“这函数可比SUM函数厉害多了,能应对好几种求和场景呢!”

小白:“这么厉害,那你可得教教我。”

“那就听我细细给你道来~首先,来解决你表格的问题。”说着,我就打开了她的表格,如图1所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

“现在,你的表格使用的是SUM函数求和,我们把它换成SUBTOTAL函数,你再看看”。说完,我在单元格A7中输入了公式。

=SUBTOTAL(9,A2:A6)

学会这个Office Excel“万能”函数,节省你一半的时间

 

“真的变了!”接着小白又筛选了一些别的行,发现都可以得到她想要的结果,十分高兴。不过随后她又发现了新大陆,“那这个9是什么意思呢?”

我:“这个9呀,表示忽略未筛选出的数据,仅对筛选后的结果进行求和”。

小白:“听你这说法,还有其他数字代表其他的含义咯?”

我:“当然,那我就再跟你说说其他数字的含义吧!”

忽略隐藏行求和

我们有时候会碰到这种情况,有一列数字,需要隐藏几个不进行运算的数据。如果是直接使用SUM,是无法得到正确结果的,如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

即使用上刚学的SUBTOTAL函数的参数“9”,也是无法实现的,如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

这时候我们就要考虑换一个参数了。

下面有请参数“109”,登场!

公式:=SUBTOTAL(109,A1:A5)

如下图所示,将SUBTOTAL函数第一参数变为“109”后,就能轻松得到忽略隐藏行后的求和结果!如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

参数“109”的作用是对可见数值进行求和,它既可以对隐藏后的数据求和,也可以对筛选后的数据求和。而参数“9”只能使用在筛选行,对隐藏行则无效。

SUBTOTAL其他参数的应用

SUBTOTAL不仅仅局限在求和领域,平均值、最大值、标准差、方差,都能求,只需改变它的第一参数即可。例如,现在我们要统计忽略隐藏行的最大值,如图所示。

公式:=SUBTOTAL(104,A1:A5)

学会这个Office Excel“万能”函数,节省你一半的时间

 

(隐藏前)

学会这个Office Excel“万能”函数,节省你一半的时间

 

(隐藏后)

隐藏了最大值“8”后,直接在单元格A6中得到了当前可见的最大值“7”。

那为什么是104呢?其实SUBTOTAL函数里面有一套数字代表规则,今天咱们就把其他的参数都说一说,包括求平均值、最大值、最小值、标准差、方差等11种功能。有的常用,有的不常用,大家结合自己的需求来选择。下面是11种参数的对照表。

学会这个Office Excel“万能”函数,节省你一半的时间

 

拓展部分1:只统计分类汇总

我们在制表的时候,经常会碰到这样一种汇总情况,在同表内进行分项汇总,如图

学会这个Office Excel“万能”函数,节省你一半的时间

 

如果使用SUM进行汇总,则会统计出所有的数据,如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

可是我们只想合计各个小计的内容呀!别慌,只需把SUM换成SUBTOTAL就可以得到我们想要的答案。如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

这是为什么呢?其实SUBTOTAL除了能忽略掉被隐藏、筛选的行外,还会忽略掉包含SUBTOTAL,以及AGGREGATE函数的单元格。单元格B3、B6、B10都是用SUBTOTAL函数计算的小计,自然在最后用SUBTOTAL函数求和时,会被忽略掉。

学会这个Office Excel“万能”函数,节省你一半的时间

 

拓展部分2:不间断序号

“我们了解了SUBTOTAL函数的特性之后,就可以用它来做一些什么,比如给列表编号。”

“什么,列表编号不是用鼠标拉一下就好了吗?”

“不一样~我的编号,可是自动的哦!无论是删除行还是隐藏行,编号都能自动重新排列!”

“这么神奇,那我可要好好学学。”

其实它非常简单,假设我有一张列表,目前序号列是空的,如图11所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

在A2单元格输入公式:=SUBTOTAL(103,B$2:B2),然后下拉填充,就能得到我们想要的序号。如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

我们试着来隐藏一行,就会发现,序号仍然是按照顺序排列的,并没有中断,如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

现在我们来逐步解释一下公式=SUBTOTAL(103,B$2:B2)

103:查看上述参数对照表可以得知,103的作用是忽略隐藏行和被筛选值,统计非空单元格数。

B$2:B2:A2单元格内的区域是B$2:B2,目的是,统计出B2:B2区域中非空单元格数,结果为1。在公式下拉后,A3单元格内的区域变成了B$2:B3,那么统计的非空单元格数就变成了两个,得到的结果为 2。如图所示。

学会这个Office Excel“万能”函数,节省你一半的时间

 

以此类推,随着公式的下拉,我们就可以得到一组连续的序号。再结合SUBTOTAL函数第一参数只计算可见数值的特性,就可以得到一组不间断的序号!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注