75如何处理筛选状态下的数据查询与汇总？

今天给大家聊一下筛选状态下数据查询与汇总的各种套路化函数公式。

先说一下，筛选状态下的无条件数据汇总，这个比较简单，看一眼就会。

举个例子，如下图所示，无论数据列表筛选何值，E2单元格都显示已筛选的奖学金总和。

在E2单元格输入以下公式即可。

=SUBTOTAL(109,E5:E18) 

E5:E18是统计范围，109表示忽略手工和筛选隐藏的行。公式的意思也就是只统计非隐藏的E5:E18区域内的值。

是不是比较简单？打个响指，加1块假大洋，换个问题▼

有条件的...

数据筛选汇总求和

在筛选状态下，只统计B列年份为C2单元格指定值的奖学金总额。比如C2单元格为2019年，则不管筛选学校是哪所、日期有多少年，均只统计筛选学校2019年的奖学金总额。

辅助列解决方案如下▼

F5单元格输入以下公式，向下复制填充。

=SUBTOTAL(103,E5)+0 

公式的作用是判断E列单元格是否处于非隐藏状态，隐藏时公式返回0，非隐藏返回1.

在E2单元格输入以下公式即可动态获取C2单元格指定年份下的筛选金额。

=SUMIFS(E5:E18,B5:B18,C2,F5:F18,1) 

SUMIFS函数以E5:E18区域为求和范围，筛选同时满足两个条件的数据。一个是B5:B18满足C2单元格指定年，另一个是F5:F18单元格值为1（也就是非隐藏）。

……

打个响指，加10块假大洋，再换个问题：如何不用辅助列，只用一条函数公式获取同样的计算结果？

公式如下▼

公式如看不全可以左右拖动..▼

=SUMPRODUCT((B5:B18=C2)*SUBTOTAL(103,OFFSET(E4,ROW(1:14),0))*E5:E18) 

公式使用以下函数代替辅助列的作用。

SUBTOTAL(103,OFFSET(E4,ROW(1:14),0))

OFFSET函数的意思是以E4单元格为起点，向下移动1、2、3、4……14个单元格，也就是分别引用E5:E18单元格，再使用SUBTOTAL函数计算其是否非隐藏可见。

……

有条件的…

数据筛选合并查询

再打个响指，加100个假大洋：如何动态展示筛选状态下的非重复学校名单？

效果如下图所示，不论筛选何值，B2单元格均显示被筛选的不重复的学校名单。

如果你是MS 365版本，公式如下：

公式如看不全可以左右拖动..▼

=TEXTJOIN(",",1,UNIQUE(IF(SUBTOTAL(103,OFFSET(E4,ROW(1:14),0))>0,C5:C18,"")))

如果是2019版本公式如下：

公式如看不全可以左右拖动..▼

=TEXTJOIN(",",1,INDEX(C5:C180,IF(SUBTOTAL(103,OFFSET(E4,ROW(1:14),0))*(MATCH(C5:C18,C5:C18,0)=ROW(1:14)),ROW(1:14),99)))

365版使用新函数UNIQUE去重复获取筛选状态下唯一值列表，2019版就苦了些，需要借助INDEX+SMALL+IF的万金油套路：学Excel函数，怎能不会这个经典查询套路？

……

如果是其它Excel版本？用个辅助列，简单的要死。

F5输入以下公式，向下复制填充：

=IF(SUBTOTAL(103,E5)*(COUNTIF(C$5:C5,C5)=1),","&C5&F4,F4)

这思路是不是有点眼熟？这不就是VOOKUP应用大全里的合并查询套路吗？

SUBTOTAL(103,E5)*(COUNTIF(C$5:C5,C5)=1

判断E5单元格是否隐藏，同时C5单元格的值是否首次出现，如果条件成立，则按逗号为分隔符合并学校名字，否则返回原字符串。

最后，B2单元格输入以下公式即可获取筛选结果▼

=MID(LOOKUP("座",F:F),2,99)