Excel 统计唯一不重复项数量

不重复唯一值,这是在 Excel 里处理数据经常能遇到的问题。例如,统计不重复数量、筛选不重复值、删除重复值、提取重复值等。今天我们来学习,写统计不重复数量的公式。

 

问题描述

如何统计一区域中以不重复项的数量?

 

思路分析

1.使用 COUNTIF 函数,在生源地列中,分别统计每一行生源地在整列中出现的次数,并以数组形式返回出现次数。

公式为:=COUNTIF(生源地,生源地)

2.针对某一个生源地,假设它出现了 n 次,那么在返回的出现次数数组中占用 n 个位置,并且每个位置的值为 n。

例如数据列为 A, B, C, C, A, A,则返回的数组为,{3, 1, 2, 2, 3, 3},即 A 出现在3 个位置,每个位置的值为 3。

3.一个生源地无论出现多少次,都应只记一次。根据上述描述,如果将返回的出现次数数组里的每一项被一除并相加,刚好的到不重复项的数量。

例如,{3, 1, 2, 2, 3, 3} → 1/3 + 1/1 + 1/2 + 1/2 + 1/3 + 1/3 = 3

方法步骤

1.输入 SUMPRODUCT函数

= SUMPRODUCT(

2.接着输入SUMPRODUCT 函数的参数,被一除的 COUNTIF 函数:

=SUMPRODUCT(1/COUNTIF(

3.最后输入 COUNTIF 函数的参数,统计区域为生源地列,统计条件为所有的生源地。最后回车完成输入公式。

=SUMPRODUCT(1/COUNTIF(B:B,B2:B19))

 

可能出现的错误

当区域中含有空白单元格时,公式会出现 #DIV/0 错误。这是因为在 COUNTIF函数中,空白单元格在其两个参数表示不同的含义。

当空白单元格在统计区域参数时,被当做空字符 “”;当在判断条件参数时,被当做零。因此当 COUNTIF 函数判断条件引用空白单元格时,其表示零,统计值为零。如果将其被一除,则返回 #DIV/0 错误。

为了避免错误的产生,可以将判断条件连接一空字符,这样有内容的单元格不受影响,空白单元格则变成空字符,与统计区域保持一致。这种情况,空白单元格也计算为一个不重复项。

=SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19&""))

 

总结

你可能已经有疑问了,如何想到用 1 除?这个严格来说不是 Excel 问题,是算法问题。Excel 用两个函数巧妙的实现了该算法,最后得到目标值。建议记住该算法,在 Excel 很多问题上,会用到「1 除」概念,可以快速解决一些复杂的问题。

转载注明:文章转载自「懒人Excel - www.lanrenexcel.com

您可能还喜欢...

订阅评论
提醒
13 评论
最新
最旧 最多投票
内联反馈
查看所有评论
哈哈哈哈哈哈酱
2 月 前

为什么用这个公式出来的数,和直接“删除重复项”出来的数不一样呢?

杨亚峰
3 月 前

数据源很多的情况下,计算太慢了

天涯倦客
5 月 前

怎样实现也数组形式返回

小熊coco茶
2 年 前

请问需要计算符合某一特定条件下不重复项数量该用什么公式

Norah
2 年 前

请问单元格格式是常规吗?

追草帽
3 年 前

很实用

yvon
4 年 前

谢谢,好用

13
0
希望看到您的想法,请您发表评论x