根据指定条件,求唯一值的个数(建议收藏备用)

一、无条件统计唯一值个数

公式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))

基础较差的表弟表妹,如果理解不了上面的公式,那就收藏起来,要用的时候拿出来套用就是了。


(0)

相关推荐