当前位置：首页 > 科技 > 正文

Excel中最牛逼的函数，一个顶11个，简直就是百变神君

没想到要学SUBTOTAL函数的粉丝挺多的，那就来个全面的讲解吧。当了那么多年的配角，今天终于当回主角。

1.汇总行的妙用

选择区域，插入表格，点设计，勾选汇总行。

汇总行，除了可以求和，还可以下拉选择计数，最大值等。

当然，插入表格和勾选汇总行这些步骤也可以省略，直接写公式也行。

求和就用：

=SUBTOTAL(109,D2:D11)

=SUBTOTAL(9,D2:D11)

计数就用：

=SUBTOTAL(103,D2:D11)

=SUBTOTAL(3,D2:D11)

SUBTOTAL一共可以代表11个函数，不过平常用得最多的是计数COUNTA和求和SUM。

2.筛选的时获取连续序号

正常情况下，用ROW、COUNTIF之类获取的序号，只要进行筛选就乱了。而SUBTOTAL刚好能解决这个问题。

=SUBTOTAL(3,B$2:B2)*1


区域采用混合引用，下拉的时候就逐渐变大，从而起到累计的作用。后面*1的作用，是防止最后一行当成汇总，导致筛选的时候出错。不加不一定会错，加了肯定没错。

现在筛选的时候，序号就是连续的，最后一行的汇总也跟着改变数据。

3.对筛选的结果进行条件求和、计数

正常情况下，SUMIFS、COUNTIFS不管有没筛选结果都一样，不能直接对筛选的结果进行判断。

不过可以利用SUBTOTAL可以对可见单元格生成序号。

=SUBTOTAL(3,D2)


现在要求大于200元的班级个数，就可以用这样的公式：

=COUNTIFS(F:F,1,D:D,">200")


对价格进行筛选，统计结果会自动改变。

同理，统计金额大于200元的班级的总金额，就可以这样设置公式。

=SUMIFS(D:D,F:F,1,D:D,">200")

核心点就是利用SUBTOTAL作为辅助列，生成数字1，再根据辅助列作为新的条件进行判断。

4.将筛选的结果合并在一个单元格内

这个跟案例3一样，也是用辅助列生成数字1，再借助这个判断。

输入公式，按Ctrl+Shift+Enter结束。IF部分的作用是让符合F列为1，D列大于200的，返回C列的值，否则返回空，再用TEXTJOIN将内容合并起来。

=TEXTJOIN("、",1,IF((F2:F11=1)*(D2:D11>200),C2:C11,""))


要将SUBTOTAL用好，还得学会很多函数才行。

推荐：VLOOKUP与LOOKUP的1,0详解

上篇：VLOOKUP与LOOKUP的1,0详解（通俗版）

还想知道什么用法？

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）

你可能想看：

Excel中最牛逼的函数，一个顶7个，简直就是百变神君

=VLOOKUP(G2:=SUMPRODUCT(($A$2:$A$10=G2)*($B$1，=SUMIF(A:=SUMIFS($F:$A$20=$H2)*($B$2:$A$20=H2)*1),=COU...

Excel中最牛逼的函数，一个顶7个，简直就是百变神君

=VLOOKUP(G2:=SUMPRODUCT(($A$2:$A$10=G2)*($B$1，=SUMIF(A:=SUMIFS($F:$A$20=$H2)*($B$2:$A$20=H2)*1),=COU...

Excel2022版来了，新增Xtext、PZ等5个超牛逼的函数！

Excel内置函数很难完成的时候。开发函数其实并不难。先来回顾一下Excel的SUM函数，想实现跟Text一样的分隔效果。Format的作用就跟Text一样。中间的代码按照Text的写法操作。输入刚刚...

Excel2022版来了，新增Xtext、PZ等5个超牛逼的函数！

Excel内置函数很难完成的时候。开发函数其实并不难。先来回顾一下Excel的SUM函数，想实现跟Text一样的分隔效果。Format的作用就跟Text一样。中间的代码按照Text的写法操作。输入刚刚...

终于，Excel数据处理的方式来到了一个十字路口：Excel中的函数式编程简介

是继续沿用的方式还是使用函数式编程的思路。今天我们简单介绍一下Excel中的函数式编程。因为它全部是使用Excel的函数创建公式而已。你需要做的是调整在Excel中使用函数处理数据的思路。Excel与...

终于，Excel数据处理的方式来到了一个十字路口：Excel中的函数式编程简介

是继续沿用的方式还是使用函数式编程的思路。今天我们简单介绍一下Excel中的函数式编程。因为它全部是使用Excel的函数创建公式而已。你需要做的是调整在Excel中使用函数处理数据的思路。Excel与...

Excel教程：excel字符串函数--SUBSTITUTE函数和REPLACE函数讲解

请在C列输入公式，将B列的凭证字号，修改为C列所示的内容。您会写公式吗，C2单元格输入下面任意一个公式。=SUBSTITUTE(B2？=REPLACE(B2,上面两个函数的语法，请参考下面的介绍：SU...

Excel教程：excel字符串函数--SUBSTITUTE函数和REPLACE函数讲解

请在C列输入公式，将B列的凭证字号，修改为C列所示的内容。您会写公式吗，C2单元格输入下面任意一个公式。=SUBSTITUTE(B2？=REPLACE(B2,上面两个函数的语法，请参考下面的介绍：SU...

Excel VBA 7.19 Excel跨工作薄多工作表数据合并之Excel表格多行表头

所以我们今天针对多行表头的数据汇总来分析下方法场景模拟假设我们要统计的Excel中每个表格都有表头，1) End If Next sth End If ActiveWorkbook.Close Fal...

Excel技巧应用篇：【上班族必看】Excel 基础技巧轻松学，善用 Excel 提升工作效率

利用折线图绘制实际成绩／预测图表，利用时间轴呈现值的趋势的「折线图」，折线图能清楚呈现「数据的变化」。折线图是依照「数年、数月、数日这种时间轴来观察值的变化」的图表。是无法绘制出具有意义的折线图。此为...

Excel VBA 7.19 Excel跨工作薄多工作表数据合并之Excel表格多行表头

所以我们今天针对多行表头的数据汇总来分析下方法场景模拟假设我们要统计的Excel中每个表格都有表头，1) End If Next sth End If ActiveWorkbook.Close Fal...

Excel技巧应用篇：【上班族必看】Excel 基础技巧轻松学，善用 Excel 提升工作效率

利用折线图绘制实际成绩／预测图表，利用时间轴呈现值的趋势的「折线图」，折线图能清楚呈现「数据的变化」。折线图是依照「数年、数月、数日这种时间轴来观察值的变化」的图表。是无法绘制出具有意义的折线图。此为...

Excel教程：excel中len函数嵌套工作案例

SUBSTITUTE函数，先观看柳之老师为大家录制的函数视频教程。请在C列编写公式计算每组成员的平均奖金：=ROUND(B2/(LEN(A2)-LEN(SUBSTITUTE(A2,=SUBSTITUT...

Excel教程：excel中len函数嵌套工作案例

SUBSTITUTE函数，先观看柳之老师为大家录制的函数视频教程。请在C列编写公式计算每组成员的平均奖金：=ROUND(B2/(LEN(A2)-LEN(SUBSTITUTE(A2,=SUBSTITUT...

Excel教程：excel筛选后自动编号、连续编号必用函数：SUBTOTAL函数

今天的Excel筛选后自动连续编号，其中涉及到N函数嵌套的巧妙使用，记录了每个使用团队的明细数据，实现A列的编号保持连续性。请在A2单元格写公式实现筛选状态下的连续编号。下面是输入公式之后的筛选效果预...

Excel教程：excel筛选后自动编号、连续编号必用函数：SUBTOTAL函数

今天的Excel筛选后自动连续编号，其中涉及到N函数嵌套的巧妙使用，记录了每个使用团队的明细数据，实现A列的编号保持连续性。请在A2单元格写公式实现筛选状态下的连续编号。下面是输入公式之后的筛选效果预...

Excel中特别有用的函数之Filter

今天继续介绍Excel中的新函数系列：今天介绍的FILTER在内的Excel新函数，数组这个参数就是准备进行筛选的整个数据区域（或数组），这个参数是一个返回逻辑值TRUE/FALSE的判断”参数中的值...

Excel中特别有用的函数之Filter

今天继续介绍Excel中的新函数系列：今天介绍的FILTER在内的Excel新函数，数组这个参数就是准备进行筛选的整个数据区域（或数组），这个参数是一个返回逻辑值TRUE/FALSE的判断”参数中的值...

Excel中隐藏的3个神器，1个顶10个，很多人居然不知道！！

你知道Excel里有哪些非常实用的小工具吗，Excel表格数据太多，一秒等比放大表格局部内容，Excel照相机可是个非常实用的功能，当我们再返回Excel表格：我们只需要选中表格区域？我们随意的更改原...

SUBTOTAL公式序号函数区域

上一篇
学渣如何秒解几何难题？必看初中数学几何题辅助线经典方法

下一篇
神奇的中成药，乌鸡白凤丸的7种妙用

有话要说...

最新文章

取消
扫码支持 支付码