糊涂了10年,这个Excel公式到底是怎么不重复计数的?

百万发注册地址

10年来,这个Excel公式怎么不重复计算?

遵循

实际上,通常需要计算不重复的值的数量,例如:

[示例]在下图所示的客户消费计划中,需要计算客户总数。

e2bb9de4d7b54e7c9e3e07e488cbd8bf.jpeg

我们知道所有的公式,它可以很容易地从百度找到:

=SUMPRODUCT((1/COUNTIF(B2: B10,B2: B10)))

但使用它多年后,有多少人理解这个公式的含义?为什么用1 /?

解决今天这个公式的神秘面纱,让你充分理解这个公式的含义。

分析和配方设置步骤:

件计算数字。例如,计算客户A的出现次数:

=COUNTIF(B2: B10,B2)

注:B2: 件

a39d3e407f11423d9e4b034659131be5.jpeg

如果我用上面的公式替换上面公式中Countif的第二个参数,会发生什么?

=COUNTIF(B2: B9,B2: B10)

结果是表中所有客户行的出现次数。选择公式,然后按F9查看计算结果。

={3; 2; 2; 3; 1; 2; 2; 1; 3}

6c19702b3568462ebd0f9ae3543a75a7.gif

为了更清楚地理解,计算结果直接标在表中:

abd1bceb884045f3b1337d25e222fe84.jpeg

为了允许重复客户只计数一次,不使用1 /方法重复公式:

=1/COUNTIF(B2: B9,B2: B10)

结果是A的出现次数变为1/3,B的出现次数变为1/2 .

={1/3; 1/2; 1/2; 1/3; 1; 1/2; 1/2; 1; 1/3}

c547bb0d215f44f4ad27aee873691691.jpeg

如果添加所有出现的A(3),则恰好是1

1/3 + 1/3 + 1/3=1

相同B的相同出现次数也是1

1/2 + 1/2=1

其他人都是一样的

换句话说,在1/sum之后,每个客户的总和结果是1

最终的公式也出来了:

=SUMPRODUCT((1/COUNTIF(B2: B10,B2: B10)))

注意:为什么在这里使用sumproduct而不是sum,因为countif的第二个参数是区域(1组号),所以公式是一个数组运算,sum不能直接保存数组运算,而sumproduct函数可以。

Excel Elite培训

Blean

看看更多