看完昨天的文章《Excel筛选那些让人很头痛的问题详解》,读者提出在筛选状态用SUBTOTAL函数取代COUNTIF函数,这种做法是否可以?
动手是检验真理的唯一方法,一起来看看。
选择区域,输入公式,按Ctrl+Enter结束,直接在筛选状态下是可以的。
=INDEX($D$14:$D$17,SUBTOTAL(103,$A$2:A2))
取消筛选,会发现出现问题,行不通。
既然读者提到SUBTOTAL函数,卢子就详细说说这个函数的用法。
1.求和、计数、平均、最大、最小等等
Step 01 选择A1,插入表格,在弹出的创建表对话框确认是否已经勾选表包含标题,没问题就点击确定。
Step 02 在设计选项卡,勾选汇总行,就自动进行汇总。
除了求和,通过下拉菜单,可以选择各种常用的汇总方式。
2.根据筛选的值,获取连续序号
在A2输入公式,下拉填充,进行筛选,都能够保持连续序号。
=SUBTOTAL(103,$B$2:B2)*1
3.根据筛选的值,进行条件求和
通常情况下,条件求和用SUMIF函数,但这个函数只是对全部数据进行条件求和。如果要根据筛选的值,进行条件求和,SUMIF函数就无法实现。
这种难度非常大,建议将公式收藏起来以后备用。
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B$1,ROW($1:$8),0))*($B$2:$B$9=B15)*$D$2:$D$9)
作者:卢子,清华畅销书作者;个人公众号:Excel不加班(ID:Excelbujiaban)