统计不重复个数
前言
我们知道:统计个数可以用COUNT,COUNTA函数,统计某个或多个条件的个数可以用COUNTIF或COUNTIFS函数,但如何统计一列数据中不重复的个数呢?今天就d带大家简单了解一下数组函数的魅力。
1实例
如下表所示,如何统计B列产品共有几个类别,也就是不统计重复内容:
公式非常简单:
B11=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))
公式包含两个函数SUMPRODUCT函数及COUNTIF函数,这两个函数解释如下:
SUMPRODUCT(数组1,数组2,…..):函数返回相应的数组或区域乘积之和。
COUNTIF(区域,条件):返回区域中符合条件的个数。
平时我们写函数,COUNTIF函数中的条件大都为单一条件,如统计产品种类中A的个数COUNTIF(B2:B9,B2)=3
而上面的公式COUNTIF(B2:B9,B2:B9)中条件区域使用了B2:B9如何理解呢?
其实它相当于下面的一组组合:
COUNTIF($B$2:$B$9,B2)
COUNTIF($B$2:$B$9,B3)
COUNTIF($B$2:$B$9,B4)
COUNTIF($B$2:$B$9,B5)
COUNTIF($B$2:$B$9,B6)
COUNTIF($B$2:$B$9,B7)
COUNTIF($B$2:$B$9,B8)
COUNTIF($B$2:$B$9,B9)
2分步演示
第一步:我们分别统计产品种类,符合条件的个数
D2 =COUNTIF($B$2:$B$9,B2),如A出现3次,则在3处均会被统计成3次。
第二步:对个数取倒数,即用1除以个数。如果出现3次,每个将会变成1/3,那么3个1/3相加正好是1。
第三步:将上述数据求和:
F2 =SUMPRODUCT(E2:E9)
当然这里也可以用SUM(E2:E9)
但SUM和SUMPRODUCT函数还是有一点点不同的。
如使用SUMPRODUCT函数,B11可以写成
B11=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))
如果用SUM函数表示,直接这样写是不对的
B11=SUM (1/COUNTIF(B2:B9,B2:B9))
需要在编辑栏同时按CTRL+SHIFT+ENTER三键,系统会自动在公式最外面加上一对大括号,才会出现正确结果,这就是所谓的数组函数,注意手动输入的大括号是无效的。
哪个更简单,是不是很明显。
结论
数组函数,写起来虽然简单,减少了辅助列,但理解起来还是有一定难度的。
对于复杂的函数增加适当的辅助列可以大大简化逻辑,更好理解和不易出错。
在编辑栏,任意点击数组公式,大括号会自动消失,可能带来错误的结果,请慎用!
END