有没有一种方法可以在Excel中检查重复值，而不使用CountIf函数？

Question

很多在SO上的解决方案都涉及使用CountIf查找重复项。然而，当我有一个包含100,000个以上数值的列表时，CountIf搜索重复项通常需要几分钟时间。

是否有一种更快的方法在Excel列中搜索重复项，而不使用CountIf？

谢谢！ 编辑#1：
阅读评论和回复后，我意识到需要更详细地说明。假设我是一位观鸟者，观鸟之后我将看到的1至25或50只新鸟输入到我的“鸟类大师列表”中。这实际上是一个动态增长的列表，每次添加时，我想确保我没有重复已经存在于列表中的内容。

因此，在我的文件的A列中是鸟的名称。B-M列可能包含鸟的其他属性。我想知道我刚刚在最新的观鸟之后添加到A列中的鸟是否已经存在于我的列表的其他位置。如果是，我会手动合并2个条目的数据，并在仔细审查后丢弃一些并保留一些。我显然不想在我的数据库中有相同鸟的重复条目。

因此，最终我想要一些指示，告诉我是否存在重复项，如果有重复项，请告诉我在哪一行查找（或突出显示或着色两个重复项）。

- MrPatterns

---

在这种情况下，您可以使用“数据验证”来防止输入重复的鸟名。请参见此处。 - brettdj

Answer 1

我所知道的最快方式（如果您使用Excel 2007/2010/2011）是使用“数据”（在功能区中）|“删除重复项”来查找重复项的总数或删除重复项。在测试之前，您可能需要将数据移动到临时工作表中。

第二种最快的方法是使用Countif。现在可以使用Countif以多种方式查找重复项。以下是两种主要方法。

1）在数据旁边插入新列并放置公式，然后简单地将其复制下来。

2）在条件格式中使用Countif来突出显示重复的单元格。有关更多详细信息，请参见此链接。 用于查找单个列中重复项的宏建议 编辑：

我的歉意：）

Countif是第三快的方式！

第二快的方法是使用数据透视表;)

您查找重复项的主要目的是什么？您想要删除它们吗？还是您想要突出显示它们？还是其他什么？

跟进

看起来我在公式中打错了一个字。是的，对于大量行数，像你建议的那样，CountIf确实需要几分钟的时间。

让我看看能否编写一段VBA代码以满足您的确切需求。

Sid

- Siddharth Rout

---

@phan：那么你需要的是我上面提到的条件格式。请参考此链接以获取详细信息 :) https://dev59.com/FmDVa4cB1Zd3GeqPh-xM#9469740 我已经在上面的帖子中更新了此链接。 - Siddharth Rout

有没有一种方式可以将行“标记”为在鸟名重复的条目旁边的列中添加字母“X”，而不是突出显示？我刚意识到，如果我突出显示100,000+列表中的单元格，我将不得不滚动很多。但是，如果我使用标记器标记行，例如“X”，我只需使用“X”过滤器即可查看重复条目。 - MrPatterns

@phan：哇！你观察了很多鸟啊！:D 在这种情况下，选择第一种选项，在其中插入一列并添加countif公式，然后过滤值>1。 - Siddharth Rout

好的，你是说我必须使用CountIf吗？我的问题是是否可以避免使用它... - MrPatterns

@phan：请确认您正在使用的Excel版本？ - Siddharth Rout

Answer 2

你可以使用VBA - 下面的函数可以在不到一秒钟的时间内返回100,000个列表中唯一条目的列表。用法：选择一个范围，输入公式（=getUniqueListFromRange(YourRange)）并使用CTRL+SHIFT+ENTER进行验证。

Public Function getUniqueListFromRange(parRange As Range) As Variant
' Returns a (1 to n,1 to 1) array with all the values without duplicates

  Dim i As Long
  Dim j As Long
  Dim locKey As Variant
  Dim locData As Variant
  Dim locUniqueDict As Variant
  Dim locUniqueList As Variant

  On Error GoTo error_handler
  locData = Intersect(parRange.Parent.UsedRange, parRange)

  Set locUniqueDict = CreateObject("Scripting.Dictionary")

  On Error Resume Next
  For i = 1 To UBound(locData, 1)
    For j = 1 To UBound(locData, 2)
      locKey = UCase(locData(i, j))
      If locKey <> "" Then locUniqueDict.Add locKey, locData(i, j)
    Next j
  Next i

  If locUniqueDict.Count > 0 Then
    ReDim locUniqueList(1 To locUniqueDict.Count, 1 To 1) As Variant
    i = 1
    For Each locKey In locUniqueDict
      locUniqueList(i, 1) = locUniqueDict(locKey)
      i = i + 1
    Next
    getUniqueListFromRange = locUniqueList
  End If

error_handler:         'Empty range

End Function

- assylias

---

不错 - 字典方法也可以有效地用于标记重复项。这可能比其他方法更快 - 更像O(n)而不是O(n^2)。 - lori_m

Answer 3

如果使用Excel 2007或更高版本（从100,000个值中很可能是这样），您可以选择：

主页选项卡 | 条件格式化> 高亮显示单元格规则> 重复值...

右键单击突出显示的单元格，并按所选单元格颜色筛选，以仅显示重复项（但请注意，这可能会导致条件格式化变慢）。

或者运行此代码并过滤彩色单元格，只需1秒钟即可处理100,000个单元格：

Sub HighlightDupes()

Dim i As Long, dic As Variant, v As Variant

Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")

i = 1
For Each v In Selection.Value2
    If dic.exists(v) Then dic(v) = "" Else dic.Add v, i
    i = i + 1
Next v

Selection.Font.Color = 255
For Each v In dic
    If dic(v) <> "" Then Selection(dic(v)).Font.Color = 0
Next v

End Sub

附录:

如果不使用代码或公式，只选择重复值，我发现以下方法很有用：

数据选项卡 | 高级筛选... 筛选范围内，只显示唯一记录，确定。

现在选择唯一值的范围，按下Alt+; (转到 特殊项目... 仅显示可见单元格)。使用此选择清除筛选器，您将看到所有未选定的单元格都是重复项，然后可以按Ctrl+9 (隐藏行) 只显示重复项。如果需要，这些行可以复制到另一个工作表中或用“X”标记。

- lori_m

---

关于高级筛选功能的建议很好。不幸的是，当处理大量行时，高级筛选功能也需要花费大量时间。+1 - Siddharth Rout

谢谢，这是真的。当在大量选择上进行原地过滤时，高级筛选并不那么快，但至少在我的系统上（使用Excel 2010，在20000行上）它仍然比向下填充=COUNTIF($A$2:A2,A2)要快得多，大约为5秒对比9秒，并且在复制到另一个范围时速度更快。 - lori_m

Answer 4

您并没有提到当您找到它们时想要做什么。如果您仅仅想知道它们在哪里...

Sub HighLightCells()
   ActiveSheet.UsedRange.Cells.FormatConditions.Delete
   ActiveSheet.UsedRange.Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual,  Formula1:=ActiveCell
   ActiveSheet.UsedRange.Cells.FormatConditions(1).Interior.ColorIndex = 4
End Sub

- datatoo

---

我在我的问题中添加了更多信息来回答你的问题。 - MrPatterns

Answer 5

使用数据验证来防止重复输入
您可以使用数据验证来防止输入重复的鸟名。请看Debra Dalgelish的网站。

处理现有重复项
我的免费Duplicate Master插件将让您

• 选择
• 着色
• 列出
• 删除

重复项目。

但更重要的是，它将允许您运行比完全匹配更复杂的匹配，例如：

• 大小写不敏感/大小写敏感搜索（以下是示例）
• 修剪/清理数据
• 删除所有空格（包括CHAR（160））查看下面的“magpie”和“mapgie”的示例
• 运行正则表达式匹配 (例如，以下示例将s$替换为""以去除复数)
• 在任何组合的列上匹配（即列A、所有列、列A&B等）

- brettdj

Answer 6

我很惊讶没有人提到"RemoveDuplicates"方法。

ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1

这将简单地删除活动工作表中A列中的任何重复条目。它只需要毫秒级别的运行时间（测试了20万行）。请注意，这将严格删除所有重复条目。虽然原始问题没有这样表述，但我相信这仍然能够满足您的需求。

- user2573063

---

好东西，这是最近版本中实现的吗？ - ForceMagic

Answer 7

一种查找唯一值的简单方法是使用高级筛选器，并仅筛选唯一值，然后将它们复制并粘贴到其他工作表中。当透视表被删除时，您将获取带有重复数据的完整数据。

- Baala

Answer 8

对范围进行排序，并在下一列中输入`=if(a2=a1;1;if(a2=a3;1;0))`

重复项将显示为“1”。

- polaco