webp

前段时间到一家制造型企业培训Excel,下课休息的时候,学员小Z向我咨询实际工作中的一个问题,以下是小Z的问题:

表格有8000行数据(即8000个项目),其中一列的字段为合同编号,表格中部分项目有填写合同编号,部分项目没填。由于一个合同签订的项目包含若干个(即8000个项目中有重复的合同编号),如何知道这8000个项目里面有多少个合同(不重复)?

曾经尝试用显示重复值功能,然后通过筛选,能显示没重复的数据(包含合同编号那列为空白格),然后通过右下角的“计数”显示到最终想要统计的合同数。但想知道更方便准确的方法。

简单来讲,小Z就是要实现对一组包含重复项的数据进行非重复计数。按照小Z现在的方法,先筛选再查看右下角的计算结果,显然非常不智能。如果数据更新了,每次都要重复这个动作来查看计数的结果。想要动态得到最新的无重复的计数结果,可以利用以下的2个方法来实现。

1 函数法

比如下图所示的这个销售明细的表格中,“公司名称”中的数据包含有大量的重复的数据,如果现在我想统计客户数量以及每个地区的客户数量,就要进行非重复计数。

webp

场景1:统计客户数量 

使用公式:=SUMPRODUCT(1/COUNTIF(C2:C15,C2:C15))

先用COUNTIFS这个条件计数函数统计每一行的客户ID的出现次数,得到这个计算结果:

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

这个结果的含义是C2出现2次,C3出现了3次,C4出现了1次,以此类推。

之后再用1/{2;2;1;3;3;3;3;3;3;3;3;3;2;2},比如“OLDWO”这个客户ID出现了2次,有2个计算结果,就把单次的计算结果变成1/2。

最后再用SUMPRODUCT函数对1/{2;2;1;3;3;3;3;3;3;3;3;3;2;2}的结果进行求和,那么“OLDWO”这个客户ID有2个1/2、1/2,求和之后就变成了1,就意味着有1个名为“OLDWO”的客户。

这是非重复客户数量的计算结果:

webp

场景2:统计每个地区的客户数量 

使用公式:=SUMPRODUCT(($E$2:$E$15=M2)*(1/COUNTIF($C$2:$C$15,$C$2:$C$15)))


在场景1的基础上增加了必须地区相同的条件,也就是$E$2:$E$15=M2这个部分的条件SUMRPODUCT函数各个条件用()括起来,再用*相连。

这样就得到了非重复的每个地区的客户数量的计算结果:

webp

2 数据透视表法

使用函数的方法来实现非重复计数,理解起来略微复杂。如果你使用的是Excel 2013及以上的版本,还有一个更为简单的方法来实现非重复计数,那就是用数据透视表。

点击“插入”选项卡中的“数据透视表”,在弹出来的对话框中勾选“将此数据添加到数据模型”,生成数据透视表。

webp

将“地区”拖到行标签中,再将“客户名称”拖到值项中,就可以看到初始的统计结果中是包含重复值的计数。

webp

接着我们在汇总数字上点击鼠标的右键,选择“值字段设置”,在弹出来的对话框中选择“非重复计数”。

webp

就得到了非重复计数的结果,是不是比函数操作简单很多!

webp

这就是2种可以自动更新计算结果的非重复计数的方法,我们总结一下:

函数法:SUMPRODUCT+COUNTIFS,适合任何版本

数据透视表法:插入数据透视表时勾选“将此数据添加到数据模型”,计算方式使用“非重复计数”,适合Excel 2013及以上版本