学会这个Excel“万能”函数，节省你一半的时间
发布时间：2023-02-21 19:22:52
刚入门的小伙伴总喜欢抱怨“Excel的函数太多了，老是记不住，有没有一个能汇总很多函数功能的函数呢？”不瞒你说，还真有！今天咱们要说的这个函数，其最大的功能就是可以替代11种函数使用。除此之外，它还可以根据不同的筛选结果，变更最后的计算结果！怎么样？是不是光听起来就很厉害呢？废话不多说，赶紧和小编一起来看看吧！
忽略筛选行求和
“苗老师，我碰到了个问题”，小白一上班就来找我，她说：“我有一张合计的表，打印的时候不想打印某些人的内容，就把它们用筛选隐藏了，但是每次求和都要更改求和区域，老麻烦了。”
我说：“那好办，换个求和函数就行。别用SUM了，试试SUBTOTAL。”
小白：“这是什么函数，没用过。”
我说：“这函数可比SUM函数厉害多了，能应对好几种求和场景呢！”
小白：“这么厉害，那你可得教教我。”
“那就听我细细给你道来~首先，来解决你表格的问题。”说着，我就打开了她的表格，如图1所示。
“现在，你的表格使用的是SUM函数求和，我们把它换成SUBTOTAL函数，你再看看”。说完，我在单元格A7中输入了公式。
=SUBTOTAL(9,A2:A6)
“真的变了！”接着小白又筛选了一些别的行，发现都可以得到她想要的结果，十分高兴。不过随后她又发现了新大陆，“那这个9是什么意思呢？”
我：“这个9呀，表示忽略未筛选出的数据，仅对筛选后的结果进行求和”。
小白：“听你这说法，还有其他数字代表其他的含义咯？”
我：“当然，那我就再跟你说说其他数字的含义吧！”
忽略隐藏行求和
我们有时候会碰到这种情况，有一列数字，需要隐藏几个不进行运算的数据。如果是直接使用SUM，是无法得到正确结果的，如图所示。
即使用上刚学的SUBTOTAL函数的参数“9”，也是无法实现的，如图所示。
这时候我们就要考虑换一个参数了。
下面有请参数“109”，登场！
公式：=SUBTOTAL(109,A1:A5)
如下图所示，将SUBTOTAL函数第一参数变为“109”后，就能轻松得到忽略隐藏行后的求和结果！如图所示。
参数“109”的作用是对可见数值进行求和，它既可以对隐藏后的数据求和，也可以对筛选后的数据求和。而参数“9”只能使用在筛选行，对隐藏行则无效。
SUBTOTAL其他参数的应用
SUBTOTAL不仅仅局限在求和领域，平均值、最大值、标准差、方差，都能求，只需改变它的第一参数即可。例如，现在我们要统计忽略隐藏行的最大值，如图所示。
公式：=SUBTOTAL(104,A1:A5)
（隐藏前）
（隐藏后）
隐藏了最大值“8”后，直接在单元格A6中得到了当前可见的最大值“7”。
那为什么是104呢？其实SUBTOTAL函数里面有一套数字代表规则，今天咱们就把其他的参数都说一说，包括求平均值、最大值、最小值、标准差、方差等11种功能。有的常用，有的不常用，大家结合自己的需求来选择。下面是11种参数的对照表。
拓展部分1：只统计分类汇总
我们在制表的时候，经常会碰到这样一种汇总情况，在同表内进行分项汇总，如图
如果使用SUM进行汇总，则会统计出所有的数据，如图所示。
可是我们只想合计各个小计的内容呀！别慌，只需把SUM换成SUBTOTAL就可以得到我们想要的答案。如图所示。
这是为什么呢？其实SUBTOTAL除了能忽略掉被隐藏、筛选的行外，还会忽略掉包含SUBTOTAL，以及AGGREGATE函数的单元格。单元格B3、B6、B10都是用SUBTOTAL函数计算的小计，自然在最后用SUBTOTAL函数求和时，会被忽略掉。
拓展部分2：不间断序号
“我们了解了SUBTOTAL函数的特性之后，就可以用它来做一些什么，比如给列表编号。”
“什么，列表编号不是用鼠标拉一下就好了吗？”
“不一样~我的编号，可是自动的哦！无论是删除行还是隐藏行，编号都能自动重新排列!”
“这么神奇，那我可要好好学学。”
其实它非常简单，假设我有一张列表，目前序号列是空的，如图11所示。
在A2单元格输入公式：=SUBTOTAL(103,B$2:B2)，然后下拉填充，就能得到我们想要的序号。如图所示。
我们试着来隐藏一行，就会发现，序号仍然是按照顺序排列的，并没有中断，如图所示。
现在我们来逐步解释一下公式=SUBTOTAL(103,B$2:B2)
103：查看上述参数对照表可以得知，103的作用是忽略隐藏行和被筛选值，统计非空单元格数。
B$2:B2：A2单元格内的区域是B$2:B2，目的是，统计出B2:B2区域中非空单元格数，结果为1。在公式下拉后，A3单元格内的区域变成了B$2:B3，那么统计的非空单元格数就变成了两个，得到的结果为 2。如图所示。
以此类推，随着公式的下拉，我们就可以得到一组连续的序号。再结合SUBTOTAL函数第一参数只计算可见数值的特性，就可以得到一组不间断的序号！
0
投稿猜你喜欢
1.打开Word文档，点击工具栏的“插入”插入图片 2.插入图片后我们会发现之显示图片空白框 &nb- 插入分节符您可以使用分节符改变文档中一个或多个页面的版式或格式。例如，可以将单列页面的一部分设置为双列页面。您可以分隔文档中的各章，以便每一
- 我只想打印其中的一行，或者从第m行到第n行的数据，这如何实现呢？示例数据工作表、打印样式和要打印的表格模板工作表分别如下、和所示。将“数据”
- Word文档可能因为演示的需要，需在添加一段网络或者自己制作的视频文件，来帮助我们要说明的内容，那么下面就由小编给大家分享下word中插入视
- 们在Word中插入页眉页脚后，有时需要重新设置页眉页脚，以更加符合我们的实际要求，那么具体要怎么做呢?下面小编马上就告诉大家word设置奇偶
- 偶尔删除表格方法不正确，删除后可能还留下一条线;这条线如果所使用的方法也不正确同样无法删除，至于怎么删除，文章将探讨具体的方法。那么下面就由
- 无页边距打印：文件——打印——打印设置，在弹出窗体中选择“页边距”，把“上、下、左、右”边距全部改为0.
- excel工作簿中两个表格怎么只打印一个？excel工作簿中有2个表格，但是只需要打印出一个表格，该怎么操作呢？下面我们就来看看详细的教程，
- 这是在知乎上看到的一个问题，我试着用VBA来解决。欢迎大家就自已使用Excel中遇到的问题或想要的解决方案提问，我将尽力解答。问题：怎么用E
- 一、两端对齐所谓的两对对齐，指的就是左右两边都同时对齐的意思。你的word文档中的文字，至少要有满满的两行或者更多，这样，才能发现两端对齐的
- 在Excel中录入好数据以后就需要导入到oracle数据库，或许有些朋友不知道该如何做。下面是小编带来的关于如何将excel导入oracle
- 柏拉图也叫排列图，想要通过excel表格来绘制，应该怎么做?下面随小编一起来看看吧。excel绘制柏拉图的步骤首先把我们要制图数据整理成表格
- 介绍一种给力的方法。F5定位数字法。首先我们选中整张表，点红框位置就选中了整张表。rpen22:09:20然后点F5定位，弹出接着确定。我们
- 您的经理，客户和其他VIP会更加关注外观经过专业设计的图表。这是如何使图表具有专业风格的基础。当您进行报告时，您无法使用电缆将数据连接到读者
- 不能错过的36个excel使用技巧1、一列数据同时除以10000复制10000所在单元格，选取数据区域 - 选择粘性粘贴 - 除
- Excel中经常需要创建自定义函数，函数具体该如何自定义呢?接下来是小编为大家带来的excel自行创建自定义函数的教程，供大家参考。exce
- 在我们日常工作中，有关查找的问题有非常多，一说到到查找，首先想到的肯定是VLOOKUP函数，如果能学好VLOOKUP函数，工作中的大部分的查
- word2013怎么打开宏呢？很多用户对此还不是很清楚，小编这里就给大家带来有关word2013怎么打开宏的回答，希望能够对大家有所帮助。1
- 本文介绍Microsoft Excel中IMPOWER函数的语法和用法。函数说明IMPOWER函数的主要作用是返回以 x+yi 或 x+yj
- 今天，CoolStar团队给大家发布了iOS 13 - iOS 13.5 越狱公测版。很多正在使用 iPhone 的网友，对 “ 越狱 ”都
