分享

【公式解惑】计算不重复个数

 Purefact 2015-05-23

COUNTIF函数的第二参数和第一参数的单元格区域相同时,会依次计算每个元素出现的次数。运用COUNTIF函数,可以统计数据区域中的不重复个数。

公式为:

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))


这是一个十分常用的统计不重复数据个数的公式。其中包含了一个简单的数学逻辑:任意一个数据重复出现N次,N个1/N的和值为1。

***********

公式中“COUNTIF(A1:A10,A1:A10)”部分是数组计算,运算过程相当于:

=COUNTIF(A1:A10,A2)

=COUNTIF(A1:A10,A3)

……

=COUNTIF(A2:A10,A10)

COUNTIF函数分别统计A2:A10单元格区域中每个元素出现的次数。返回内存数组结果为:

{4;4;2;4;4;4;4;2;4;4}

再使用1除以返回的内存数组,得到以下结果:

{0.25;0.25;0.5;0.25;0.25;0.25;0.25;0.5;0.25;0.25}

***********

用1除,即相当于计算COUNTIF函数所返回内存数组的倒数。为便于理解,把这一步的结果中的小数部分使用分数代替,结果为:

{1/4;1/4;1/2;1/4;1/4;1/4;1/4;1/2;1/4;1/4}

***********

如果单元格的值在区域中是唯一值,这一步的结果是1。

如果重复出现两次,这一步的结果就有两个1/2。

如果单元格的值在区域中重复出现3次,结果就有3个1/3…

即每个元素对应的倒数合计起来结果仍是1。

最后用SUMPRODUCT函数求和,得出不重复的人员总数。

***********

这种解法有一个副作用,就是使用1/N的除法运算会产生浮点误差,有时候会产生莫名错误,可以使用ROUND函数进行修约。

***********

最后再给一个MATCH函数的用法:

=SUMPRODUCT(1*(MATCH(A1:A10,A1:A10,0)=ROW(1:10)))

你能试着解释一下吗?

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多