根据指定条件,求唯一值的个数(建议收藏备用)
一、无条件统计唯一值个数
公式1:SUMPRODUCT+COUNTIF
上图中要统计营业员的个数,也就是统计A2:A7唯一值的个数,其公式为:
=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
上面公式中SUMPRODUCT会进行数组运算,实际上就是依次进行下面的运算:
=1/COUNTIF($A$2:$A$7,A2)
=1/COUNTIF($A$2:$A$7,A3)
。。。。。。
=1/COUNTIF($A$2:$A$7,A7)
然后将上面的公式结果进行求和。
如下图C列所示:
2、SUMPRODUCT+MATCH
公式:
=SUMPRODUCT(--(MATCH(A2:A7,A2:A7,0)=ROW(A1:A6)))
上面的公式相当于对下图的C列求和,
C3单元格公式意思是在A2:A7区域中查找A3,如果A3是第一次出现,那么公式计算结果为1,否则为0。对C列求和就是唯一值的个数。
由于MATCH找到的始终是第一次出现的位置,故在A2:A7中查找A3(张三),找到的是A2那个“张三”,其位置数是1,而ROW(A2)结果为2,二者不等,计算结果为FALSE,用二个负号(负负得正)将FALSE转化为0(如果是TRUE,就转化为1)
二、单条件求唯一值个数
下图统计营业员”张三“对应的购货单位个数
公式1:
=SUMPRODUCT(($A$2:$A$7=E2)*(MATCH($B$2:$B$7,$B$2:$B$7,0)=ROW($B$1:$B$6)))
公式2:
=SUMPRODUCT(($A$2:$A$7=E2)/COUNTIFS(A$2:A$7,A$2:A$7,$B$2:$B$7,$B$2:$B$7))
不详细解释,请参照无条件求唯一值个数的公式来理解。
三、双条件求唯一值个数
公式1:
=SUMPRODUCT(($A$2:$A$7=E2)*($C$2:$C$7=F2)*(MATCH($B$2:$B$7,$B$2:$B$7,0)=ROW($B$1:$B$6)))
公式2:
=SUMPRODUCT((($A$2:$A$7=E2)*(C2:C7=F2))/COUNTIFS(A$2:A$7,A$2:A$7,$B$2:$B$7,$B$2:$B$7))
基础较差的表弟表妹,如果理解不了上面的公式,那就收藏起来,要用的时候拿出来套用就是了。