excel分类计算多列数据的公式在一对多的冗长

导读来自网友的求助：一对多的数据表，统计一对一唯一项的数量。案例：下图1是销售人员的各项产品销量表，有的销售可能在不同时期多次售出同样产品，比如下图2这样，于予....

来自网友的求助：一对多的数据表，统计一对一唯一项的数量。

案例：

下图 1 是销售人员的各项产品销量表，有的销售可能在不同时期多次售出同样产品，比如下图 2 这样，于予菊就两次售出过主机。

请统计出每位销售出售的唯一产品类目数，效果如下图 3 所示。

解决方案：

1. 在 F2 单元格中输入以下公式 --> 下拉复制公式：

=SUMPRODUCT(($A$2:$A$29=E2)/COUNTIFS($A$2:$A$29,$A$2:$A$29,$B$2:$B$29,$B$2:$B$29))

别被公式的长度吓坏，分解开来并不复杂。

公式释义：

• COUNTIFS($A$2:$A$29,$A$2:$A$29,$B$2:$B$29,$B$2:$B$29)：
• countifs 是多条件计数函数，语法为 COUNTIFS(区域1, 条件1, [区域2, 条件2],…)；
• 此处的公式旨在统计出区域内 A 和 B 列同时重复的次数，会生成一个由重复次数组成的数组；
• $A$2:$A$29=E2：将 A 列的每个单元格与 E2 单元格的姓名比对，得到一组由 1 或 0 组成的数组；
• ($A$2:$A$29=E2)/COUNTIFS(...)：
• 将上述两个结果相除，分子中为 0 的，即姓名不匹配的，自然不会统计在内；
• 如果姓名匹配，则看分母中的重复次数，例如，如果重复过 3 次，则相除后的数组中会出现 3 个 1/3；
• SUMPRODUCT(...)：最后用该函数将上述结果相加，重复多次的加起来结果也是 1，从而得到案例所需结果。

用 sumproduct 函数的好处是不必使用数组公式。

2. 给标题加上筛选功能，分别筛选一下“于予菊”和“诸葛钢铁”的名字，这样就能很清楚地核对结果，去除重复项，他们二位的产品类目数完全正确。

接下来再给大家介绍一个公式。

3. 在 F2 单元格中输入以下公式 --> 按 Ctrl Shift Enter 结束：

=COUNT(0/(MATCH($B$2:$B$29,IF($A$2:$A$29=E2,$B$2:$B$29),)=ROW(A:A)))

公式释义：

• IF($A$2:$A$29=E2,$B$2:$B$29)：如果 A 列的姓名与 E2 单元格相同，则返回对应的 B 列值；
• MATCH($B$2:$B$29,...,)：计算上述值在区域 $B$2:$B$29 的位置序号，遇到重复值也只返回第一次出现的位置序号，也就是变相起到了去重作用；
• ROW(A:A)：生成自 1 开始的递增自然数；
• MATCH(...)=ROW(A:A)：将位置序号与自然递增数相比，如果相同则返回 true；
• count(0/...)：只有当分母不为 0 时，即 true 时，被 0 除才有意义，计算出这些有意义的结果数量就是想要的答案

4. 下拉复制公式：

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。