数据透视表+函数搞定Excel中的非重复计数 96

2024-03-22 22:36:52

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

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

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

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

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”的客户。

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

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

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

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

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

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

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

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

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

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

函数法：SUMPRODUCT+COUNTIFS，适合任何版本

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