http://www.pptjcw.com

xcel表格制作教程入门:筛选状态下的计算套路,全网首发!

    提示:本期难度系数稍高,建议先收藏。工作中如果遇到类似问题,能够直接套用就好。

    一、筛选后添加序号

    D2单元格公式

    =SUBTOTAL(3,E$1:E2)-1


    xcel表格制作教程入门:筛选状态下的计算套路,全网首发!



    简要说明:

    1、SUBTOTAL函数只统计可见单元格内容。

    2、第一参数使用3,表示执行COUNTA函数的计算规则。

    3、公式始终计算E列从第一行至公式所在行这个区域中,处于可见状态的非空单元格个数。用结果减1,实现序号效果。


    二、筛选后相乘

    E2单元格公式为:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)


    xcel表格制作教程入门:筛选状态下的计算套路,全网首发!



    简要说明:

    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函数对乘积进行求和。

    三、筛选后按条件计数

    E2单元格公式为:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>5))


    xcel表格制作教程入门:筛选状态下的计算套路,全网首发!



    简要说明:

    1、前半部分计算原理与示例2相同。

    2、仅改变后半段的统计条件为(G4:G16>5)

    四、筛选后自动更正标题

    D1单元格公式为:

    =LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),D:D)&”统计表”


    xcel表格制作教程入门:筛选状态下的计算套路,全网首发!

    简要说明:

    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、将提取到的内容与&”统计表”连接,变成可自动更新的表格标题。

    提示:如果您觉得本文不错,请点击分享给您的好友!谢谢

    上一篇:excel表格制作教程:常用函数Top5,哪个才是你心中的No.1? 下一篇:excel数据分析:新手必会的十个Excel小技巧

    郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。