【原】听说正在评比万能函数，SUBTOTAL函数800里加急正在赶来

点击上方

蓝色

文字  关注我们吧！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

前面有一篇文章简单介绍了SUBTATAL函数的基本语法结构和简单的用法，具体请参看【SUBTOTAL函数】和【OFFSET函数】动态求最值。其实对于SUBTALTAL函数而言，它的功能还远不止于此...

01

筛选后更新序号

不知道大家有没有注意到这样一个情况：当你进行筛选后，数据的序号并不是按照从1开是递增的，而是现实可见单元格的序号。有时候，为了使文件更美观，或者其它特殊的要求，需要每次筛选后序号都是从1开始递增。该如何操作呢？

在单元格A1中输入“=SUBTOTAL(3,C$1:C2)-1”并向下拖曳即可。

思路：

• SUBTOTAL函数只统计可见单元格内容。参数3表示执行COUNTA函数的功能

• 函数始终计算C列（选择其它列也可以）第一行至当前行的区域中可见的非空单元格数量。减去1后就是序号了
  

TIPs：上图右侧显示了部分数字所代表的含义

02

筛选后和SUMPRODUCT函数配合

在单元格A12中输入“=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:8),))*($B$2:$B$9)*($C$2:$C$9))”即可实现动态计算筛选数据的总值了。

思路：

• 利用OFFSET函数以A1单元格为基点，向下偏移1~8行，以覆盖到A列整个数据区域

• 利用SUBTOTAL函数统计可见单元格个数。可见，统计为1；不可见，统计为0

• 最后利用SUBPRODUCT函数求和
  

03

筛选后按条件计数

这里和上一例类似，只需要将条件更新为“($C$2:$C$9>800)”即可。

04

自动更新标题

在单元格D1中输入“=LOOKUP(1,0/SUBTOTAL(3,OFFSET(A1,ROW(1:18)-1,)),A:A)&"人物清单"”即可。

思路：

• 原理和前几例一样，利用SUBTOTAL函数和OFFSET函数构建内存数组

• 利用LOOKUP函数查找最后一个0的位置
  

文章推荐理由：

SUBTOTAL函数由于内置了其它函数功能，是真正的“万能函数”，并且在和其它函数的配合使用上有强大的功能。

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

推荐阅读：

你知道每个月你家交多少电费吗？

高级筛选？公式表示不服！

总结篇-IF函数实用终极帖

重大突破：满血恢复未保存文件，死机不再可怕！

插入滚动区域，让数据核对变得简单轻松，眼睛不疲劳！

戳原文，更有料！免费模板文档！