【Excel函数篇】高难度！筛选状态下的函数计算套路大全
2017-02-28 22:16阅读：
更多Excel表格技巧 请关注：
@EXCELers
文：老祝
提示：本期难度系数稍高，建议先收藏。工作中如果遇到类似问题，找到相应功能的函数公式能够直接套用就好。
1、筛选后添加序号
D2单元格公式
=SUBTOTAL(3,E$1:E2)-1
简要说明：
1、SUBTOTAL函数只统计可见单元格内容。
2、第一参数使用3，表示执行CoUNTA函数的计算规则。
3、公式始终计算E列从第一行至公式所在行这个区域中，处于可见状态的非空单元格个数。用结果减1，实现序号效果。
2、筛选后相乘
E2单元格公式为：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)
简要说明：
1、OFFSET以E3单元格为基点，依次向下偏移1~13行，实现对E4~E16每个单元格的单独引用。
2、SUBTOTAL函数第一参数使用3，即依次统计E4~E16每个单元格的可见单元格个数，如果单元格处于显示状态，则对这个单元格的统计结果为1，否则统计结果为0。
得到类似以下效果：
{1;0;1;1;1;1;0;0;1;1;0;1;0}
3、再使用以上结果乘以F列的数量和G列的单价，如果单元格处于显示状态，则相当于1*数量*单价，否则相当于0*数量*单价。
4、最后使用SUMPRODUCT函数对乘积进行求和。
3、筛选后按条件计数
E2单元格公式为：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>5))
简要说明：
1、前半部分计算原理与示例2相同。
2、仅改变后半段的统计条件为(G4:G16>5)
4、筛选后自动更正标题
D1单元格公式为：
=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),D:D)&'统计表'
简要说明：
1、SUBTOTAL与OFFSET函数结合部分，计算原理与示例2相同。
得到由0和1组成的内存数组：
{0;1;0;1;0;0;0;1;0;0;0;1;0;0;0}
2、用0/这个内存数组，如果是1，得到0，如果是0，则为错误值。
{#DIV/0!;0;#DIV/0!;0;#DIV/0!;……;#DIV/0!;0;#DIV/0!;……}
3、LOOKUP函数以1作为查询值，在以上内存数组中查找最后一个0的位置，并返回对应位置的D列的内容。
最终目的就是实现筛选后，提取最后一个处于显示状态的单元格内容。
4、将提取到的内容与&'统计表'连接，变成可自动更新的表格标题。
好了，今天的内容就好大家分享到这里，祝各位一天好心情~~
打个响指，
如果文章对你的表格学习或工作有所帮助 ，就关注
@EXCELers,一起学习进步吧~
PS：另附我们这周的函数练习题帖子：
【Excel函数练习系列】第01期：批量单条件单结果查询
