Excel-多条件去重计数

昨天帮同事写了几个Excel公式，解决了他工作上的一个频繁统计的难题。其中用到了SUMPRODUCT函数和数组公式，来进行多条件计数和多条件去重统计。这些都是我以前不太会的部分，通过这一天的查找、思考、学习，有一些来之不易的浅见。决定赶紧记录下来，免得明天再看忘记了。

需求描述

原始表格如下表：

地区	人员
北京	张三
北京	李四
北京	王五
北京	张三
北京	李四
上海	张三
上海	李四
上海	李四
上海	麻六

最终的统计数据为：

地区	地区个数	人员个数	重复人员个数	重复人员次数
北京	5	3	2	4
上海	4	3	1	2

需求分析

先详细介绍一下最终统计结果中每列字段的含义

地区列就是原始表中地区列去重
地区个数列是原始表中每个地区共有多少条记录
人员个数列是原始表中每个地区共出现了多少个人（去重）
重复人员个数是原始表中每个地区出现1次以上人员个数（去重）
重复人员次数是原始表中每个地区出现1次以上人员个数（不去重）

Excel基础

如果已经有Excel结构化引用的基础，对SUMPRODUCT函数也有了解，这部分可以跳过。

结构化引用

在Excel中选中原始数据表，使用CTRL+T快捷键，可以将原始表转变为结构表，这时表的名称为表1，由于汉字的表名称不方便输入公式，我们在表设计中将名称修改为table1。

结构表有一个优点，通过结构化引用，能够在增加或减少计算数据时，不用修改公式。举例来说：我用COUNTIF 公式来计算北京地区共有多少条记录可以写成下面两种方式：

第一种是=COUNTIF(A:A, "北京") ，公式的计算范围是整个A列，有点太大了，可能会影响计算速度。
第二种是=COUNTIF(A2:A10, "北京") ，这样当数据增加删除时，要频繁修改公式内的数据范围。

结构化引用就是通过将数据区域命名，来完美解决上面两个问题，写出来是这样的 =COUNTIF(table1[地区], "北京")，其中table1[地区] 会随着数据区域的更新而自动更新范围。

SUMPRODUCT函数

SUMPRODUCT函数返回对应的区域或数组的乘积之和。默认运算是乘法，但加、减和除也可能。
语法:
= SUMPRODUCT （array1，[array2]，[array3]，...）
对数组参数中相应元素需要进行相乘并求和

详细介绍参见SUMPRODUCT函数

解决结果

我们按需求分析中的问题顺序来依次介绍公式。

地区列

地区列是原始表中地区列表去重，Excel中有专门的删除重复项功能，比较简单。这里重点要介绍的是 Office 365 的一个专属的函数，也可以实现去重功能。
=UNIQUE(table1[地区]) 这个公式属于比较新的功能了，只是除了office 365 外，其他版本都不支持，移植性差。

地区列个数

分别统计原始表中北京和上海出现的次数，有三种方法：

使用COUNTIF函数完成： =COUNTIF(table1[地区], D2) (其中D列就是上面的地区列)
使用SUMPRODUCT函数完成： =SUMPRODUCT((table1[地区]=D2)*1)
使用FILTER函数完成： =COUNTA(FILTER(table1[地区], table1[地区]=D2))

其中第二个方法中，(table1[地区]=D2) 是一个只包含True和False的数组，必须再乘以1或加上0才能转化为1和0值的数组进行求和

第三个方法中FILTER函数也是Office 365的新特性，其他版本不支持。

人员个数

人员个数列中，首先的条件是地区，其次是人员去重的个数。如北京有5条记录，但涉及人员去重后只有3个人。这个中间的难点在于条件去重。

这里也有两个方法，我们先来看第一种。第一种方法用到了FILTER和UNIQUE的组合。公式为: =COUNTA(UNIQUE(FILTER(table1[人员], table1[地区]=D2))) 我们从里到外逐层来介绍，首先是FILTER(table1[人员], table1[地区]=D2) 用来筛选地区是北京的人员，得到的结果是一个数组{张三, 李四, 王五, 张三, 李四} 再通过UNIQUE函数对上面的数组进行去重，得到{张三, 李四, 王五} 最后用COUNTA函数来数一下数组中剩余的个数。

看到这里不得不称赞一下Office 365新特性中这个动态数组，真的是很好用呀！

第二种方法，就是用SUMPRODUCT函数来求解。具体的公式为：
=SUMPRODUCT(IFERROR(1/(COUNTIFS(table1[人员],table1[人员],table1[地区],D2)*(table1[地区]=D2)),0)) 这个公式非常的复杂，而且是一个数组公式，我们还是分步来说明。

首先来看COUNTIFS(table1[人员],table1[人员],table1[地区],D2)这一部分，这一部分非常关键，用的是COUNTIFS函数的数组模式，计算北京地区的人员的计数的个数。结果是一个数组，为{2, 2, 1, 2, 2, 2, 2, 2, 0}，这里详细讲一下数字的含义，一共有9个数字，说明我们这个数组中是按人员列进行的汇总，前5个数字分别是北京地区按人员列{张三，李四，王五，张三，李四}的计数结果关键是后四个数字，后四个数字是对应上海的{张三，李四，李四，麻六}，由于我们的条件计数中有地区属性，所以计算这几个人员分别在北京地区的出现个数 张三和李四在北京地区出现了两次，麻六一次也没有，所以是0。

因为我们后续的计算都是针对这个{2, 2, 1, 2, 2, 2, 2, 2, 0}数组展开的所以理解这个数组中每个数字的来历非常重要

接下来，我们来继续处理这个数组，这个数组中我们只需要北京的数据，所以要乘以一个数组(table1[地区]=D2)来将把上海部分过滤掉。
得到数组{2, 2, 1, 2, 2, 0, 0, 0, 0} 这就是北京区域人员的计数

我们需要的是去重后的计数，只保留一个张三、李四。可以用1来除以数组，比如张三是出现了两个2，那除完后就是0.5, 但有两个张三，和仍是1 王五只有1个，1/1后还是1，求和仍是1。

这个思路是从网上借鉴过来的，(搜索 “Excel 去重统计” 关键字就能看到）， =SUMPRODUCT(1/COUNTIF(列范围，列范围))原理是：每个数据出现的次数有多少，然后用1去除。假设A列数组中出了3次A，每出现一次A，它的比例是1/3，1/3+1/3+1/3=1，求和之后的1，这样A出现了多少次，我们就只统计一次而已，相当于去重统计。

由于我们的数组中存在0，所以用1去除的时候会出现被零除的错误，所以要用 IFERROR()函数来消错，将出错的值置0，得到数组为： {0.5, 0.5, 1, 0.5, 0.5, 0, 0, 0, 0}，对该数组用SUMPRODUCT求和，得到最终结果3，涵义是北京地区的人员去重后一共有三个，张三、李四、王五。

重复人员个数

重复人员个数是指各地区内重复出现的人去重求和。只计算出现次数大于1的人员。如北京地区张三和李四分别出现两次，那么重复人员个数就是2。上海地区李四出现次数大于1，那么重复人员个数就是1。这里需要注意的是，重复的定义是地区内的，上海的张三不与北京张三不算重复。

这个公式直接写感觉非常难，重点是对数组公式的理解，我在这里卡了几个小时，查阅了好多数组公式的资料，才求解出来。公式如下： =SUMPRODUCT(IFERROR(1/COUNTIFS(table1[人员],table1[人员],table1[地区],D2),0)*(table1[地区]=D2)*(COUNTIFS(table1[人员],table1[人员],table1[地区],D2)>1))

这个公式比较长，但其中的原理也非常简单，仔细观察的前半部分和人员个数列的部分是一样的。 IFERROR(1/COUNTIFS(table1[人员],table1[人员],table1[地区],D2),0)*(table1[地区]=D2) 这里计算的是第三步中北京地区人员去重计数，但我们的要求是只计算出现次数大于1的。那就再添加一个条件(COUNTIFS(table1[人员],table1[人员],table1[地区],D2)>1)

用分步求值来看一下， IFERROR(1/COUNTIFS(table1[人员],table1[人员],table1[地区],D2),0)*(table1[地区]=D2) 的计算结果是{0.5, 0.5, 1, 0.5, 0.5, 0, 0, 0, 0} 其中的1是指出现1次的王五，我们需要重新设置条件将王五去掉。

(COUNTIFS(table1[人员],table1[人员],table1[地区],D2)>1) 计算的结果是{2, 2, 1, 2, 2, 2, 2, 2, 0}，因为只有王五是1次，再加一个”>1”的判断，将出现1次和过滤掉，得到{1, 1, 0, 1, 1, 1, 1, 1, 0}，将这个数组与{0.5, 0.5, 1, 0.5, 0.5, 0, 0, 0, 0}相乘求和，得到最终结果为2。

重复人员次数

地区内出现重复人员的总次数。这个重点是不去重。公式如下： =SUMPRODUCT((COUNTIFS(table1[人员],table1[人员],table1[地区],D2)>1)*(table1[地区]=D2))

这里的思路是将把地区内大于1的滤出来，直接求和。分步如下： (COUNTIFS(table1[人员],table1[人员],table1[地区],D2)>1) 得到{1, 1, 0, 1, 1, 1, 1, 1, 0}，此处过滤掉了北京的王五，再使用地区条件(table1[地区]=D2)，过滤掉上海的张三和李四，最后得到{1, 1, 0, 1, 1, 0, 0, 0, 0}，求和即为北京地区张三的次数+李四的次数，为4次。

总结

以上就是SUMPRODUCT函数关于多条件统计、去重的心得。还有一个比较关键的缺点不得不提， COUNTIFS函数的效率非常低，我们在公式中使用这个函数做条件，导致对电脑的算力要求比较高，计算时间延迟非常明显，实际测试数据量超过5000行，表格会明显卡顿，建议取消自动计算功能，只在需要时再计算。

通过本次几个公式的求解，终于对数组公式初窥门径，理解了SUMPRODUCT函数的强大。个人认为，Excel的函数都是一个个灵活强大的工具，在面对具体问题时，如何将这些单个工具组合起来，凑出我们需要的结果，是运用公式的一个关键，这其中离不开对这些单个函数的熟悉和理解。

Excel-多条件去重计数 SUMPRODUCT函数