在范围内检查唯一单元格的数量

Question

我有一个Excel表格。

在E列下面，我有425个带有数据的单元格。我想要检查是否在剩余的424个E列单元格中重复出现了相同的数据（即单元格内的文本）。我该怎么做？

例如，在E54中我有
Hello Jack

我该如何检查这个值，看看它是否在其他任何一个单元格中出现过？

- user1501034

---

COUNTIF() 函数可以帮您计算列表中每个数值出现的次数。 - Tim Williams

Answer 1

您可以使用以下公式来统计 E1:E425 中唯一单元格的数量：

=SUMPRODUCT(1/COUNTIF(E1:E425,E1:E425))

如果结果为 425，则表示所有值都是唯一的。
如果结果为 421，则表示有 4 个值是其他值的重复。

- brettdj

---

这正好满足了提问者的需求。 - Siddharth Rout

+1 为什么这不是被采纳的答案？对我想做的事情也起作用了。 - Lou

我知道这有点晚了，但为什么这个能够工作呢？countif返回0，所以1/countif=#error，sumproduct应该会失败。将公式分解成几个部分，这正是发生的事情。 - bob

@Bob，COUNTIF 函数永远不会返回零，因为列表正在查找自身 - 即每个值必须至少出现一次。 - brettdj

Answer 2

使用条件格式化在所有单元格上，该格式化将根据以下公式进行突出显示：

COUNTIF(E:E,E1) <> 1

这是基于列E，从E1开始的，否则进行修改。

在Excel 2010中，更加简单，只需进入条件格式化并选择

仅格式化唯一或重复值

- Lance Roberts

---

是的，但仅仅获得计数也无法帮助您追踪它们。当然，有许多方法可以做到这一点，但我习惯于滚动查找高亮显示的内容。 - Lance Roberts

+1，我无法使用参数（E：E，E1）或作为条件格式（很可能是OE）在LibreOffice中实现此功能；但是，我成功地将其作为额外列进行了变化，并且效果非常好，=IF(COUNTIF($G$2:$G$725,G2)<>1,1,0)。 - xtian

Answer 3

如果您需要补偿空单元格，请使用@brettdj提供的公式，并进行以下操作：

1. 将计数唯一的分子调整为检查非空单元格。

2. 将零长度字符串添加到COUNTIFS的条件参数中。

   =SUMPRODUCT((E1:E425<>"")/COUNTIF(E1:E425,E1:E425&""))

在分子中检查非空单元格意味着任何空单元格都将返回零。任何分子中带有零的分数无论分母是多少都将为零。在COUNTIF的条件部分附加空字符串足以避免#DIV/0!错误。

更多信息请参见使用SUMPRODUCT()计算唯一值。

- user4039065

Answer 4

这个公式根据列值是否唯一来输出“unique”或“duplicates”：

{=IF(
    SUM(IF(ISBLANK(E1:E425),0,ROW(E1:E425)))
=
    SUM(IF(ISBLANK(E1:E425),0,MATCH(E1:E425,E1:E425,0)))
,"unique","duplicates")}

这是一个数组公式。你不需要显式地输入括号{}。相反，你先输入没有{}的公式，然后按下cmd-enter（如果不是Mac，则按其他键 - 去查找吧！）如果你想要在多行上拆分你的公式以便于阅读，则在Mac上使用cmd-ctrl-return。

这个公式通过比较两个SUM()函数的结果来工作。如果它们相等，则所有非空条目（数字或文本）都是唯一的。如果它们不相等，则存在一些重复项。该公式不会告诉您重复项的位置。

第一个SUM()函数得到的是把每个非空条目的行号相加所得到的结果。

第二个SUM()函数使用MATCH()函数查找每个非空条目。如果所有条目都是唯一的，MATCH()函数将在其自己的位置找到每个条目，并且结果与第一个SUM()函数相同。但是，如果有重复条目，则后面的重复条目将匹配前面的重复条目，并且后面的重复条目将向总和贡献一个不同的值，因此两个SUM()函数的结果将不匹配。

您可能需要调整此公式：

• 如果您希望包含 "" 的单元格被视为空白，则在 ISBLANK(...) 中使用 LEN(...)=0。我想您可以在其中放入其他测试，但我没有尝试过。

• 如果您想测试一个不从第一行开始的数组，则应从 ROW(...) 中减去一个常数。

• 如果您有一个巨大的单元格列，在计算此总和时可能会出现整数溢出。我没有解决方案。

很遗憾 Excel 没有 ISUNIQUE() 函数！

- Norman of Anstruther

Answer 5

在主页选项卡上，选择条件格式化 > 高亮单元格规则 > 重复值... 然后它将会突出显示所有重复的内容。

- Mike

Answer 6

这可能是一个更简单的解决方案。假设列A包含需要处理的数据。按该列排序。然后，从B2开始（或第一个非空单元格），使用以下公式： = IF（A2 = A1，1，0）。

然后在该列上求和。当总和= 0时，所有值都是唯一的。

- JDM