前言我们知道:统计个数可以用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 |
|