subtotal对筛选后的结果求不重复个数
小伙伴们,大家好!昨天群里一个同学问了一个问题,怎么样对筛选后的结果求不重复个数,这个问题把我难住了。不过群里的杨平老师给了一个公式,我也学习了一下。今天就来和小伙伴们分享一下。
先来看下数据源和计算后的结果。A列是数据源,有生产部,销售部,财务部3个部门,但是有重复。所以未筛选前不重复的个数是3个。然后把财务部筛选出去,剩下生产部和销售部,所以筛选后不重复的个数是2。
咱们先来说说未筛选前不重复个数的计算方法,其实之前说过很多次了。就是match=row的套路。公式为=SUMPRODUCT(N(MATCH(A2:A13,A2:A13,)=ROW(1:12)))。
MATCH(A2:A13,A2:A13,)这部分是match的数组用法,分别在A2:A13中查找A2:A13的每一个值,返回它们的位置,如上图B列所示;ROW(1:12)是一个序号数组,和match返回结果的尺寸大小要一样,如C列所示。MATCH(A2:A13,A2:A13,)=ROW(1:12)这部分是让两者比较,相等的返回true,不相等的返回false,如D列所示。而true的部分刚好对应A列不重复值第1次出现的位置。最后计算true的个数就是不重复值的个数。n函数是将true转为1,false转为0。最后用sumproduct求和,是为了不用按ctrl+shift+enter三键。
未筛选下不重复个数的计算方法理解了,就可以看看筛选后不重复个数的计算方法了。主要是在上一步的基础上,添加一个条件,就是把筛选后隐藏的去除掉。那就用到subtotal这个函数了,它可以忽略筛选后的隐藏行。但是直接用subtotal计算区域只计算未隐藏的,不能去重复。所以还要用到offset的多维引用。
下图是直接用subtotal对区域计算的结果,只能把隐藏的忽略,但不能去重复。因为它是对整个区域运算。
下图是offset多维引用和subtotal结合的用法。公式为=SUBTOTAL(3,OFFSET(A1,ROW(1:12),))。offset的第2参数ROW(1:12)是一个数组,offset返回的结果是A2:A13,但是它是一个多维引用,可以把每个单元格看做一个独立的区域;然后用subtotal分别对多维引用的每个区域(此时一个区域是一个单元格)非空计数,被隐藏的单元格就统计为0,未被隐藏的统计为1。
subtotal返回的结果为{1;1;1;0;1;1;0;1;0;0;1;1},0对应的位置就是被隐藏的单元格,比如A5对应的就是数组中第4个位置的0。
接下来用SUBTOTAL(3,OFFSET(A1,ROW(1:12),))和MATCH(A2:A13,A2:A13,)=ROW(1:12)相乘,那么既没有隐藏的又是第1次出现的就返回1,否则返回0。最后用sumproduct求和就算出筛选后不重复的个数。所以完整的公式为=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))*(MATCH(A2:A13,A2:A13,)=ROW(1:12)))。
还可以用countif,公式为=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))/COUNTIF(A2:A13,A2:A13))。关键的部分还是subtotal和offset的配合用法。
在这里也希望大家多加群提问,好的问题也可以有好的答案,让我们共同学习。再次感谢杨平老师。
文件链接:
https://pan.baidu.com/s/1CKKTiexmfr46J0GDa5REkg
提取码:v687