Skip to main content

比較兩列並在 Excel 中查找重複值(完整指南)

Author: Xiaoyang Last Modified: 2025-05-12

比較兩列以查找重複值

要查找兩列之間的重複值,根據您的需求有幾種方法可用,例如突出顯示重複值以獲得視覺概覽或提取它們進行深入分析。在本節中,我們將介紹一些快速技巧來完成此任務。

使用條件格式突出顯示兩列中的重複值

在 Excel 中突出顯示兩列中的重複值是一種識別重複數據的有效方法,特別是在手動檢查不切實際的大數據集中。在這種情況下,條件格式是一個有用的特性來解決這個問題。

步驟 1:選擇您要突出顯示重複值的列中的數據

在此示例中,我將選擇 A2:A10,請參見截圖:
A screenshot showing the selection of column A for highlighting duplicates in Excel

步驟 2:應用條件格式功能

  1. 點擊「開始」>「條件格式」>「新建規則」,請參見截圖:
    A screenshot showing the Conditional Formatting menu in Excel
  2. 在「新建格式規則」對話框中,請執行以下操作:
    • 2.1 從「選擇規則類型」列表框中選擇「使用公式確定要設置格式的單元格」;
    • 2.2 在「為符合此公式的值設置格式」文本框中輸入以下公式;
      =COUNTIF($B$2:$B$10, A2)>0
    • 注意:在上述公式中,"B2:B10" 代表您要比較的數據列表,"A2" 是您要突出顯示重複值的列的第一個單元格。該公式檢查 A2 單元格中的值是否在 B 列中的任何地方找到。根據您的數據修改單元格引用。
    • 2.3 然後,點擊「格式」按鈕。
    • A screenshot of the New Formatting Rule dialog box in Excel for highlighting duplicates
  3. 在彈出的「設置單元格格式」對話框中,指定一種顏色來突出顯示重複項。然後點擊「確定」。
    A screenshot of the Format Cells dialog box for choosing a highlight color in Excel
  4. 當返回到「新建格式規則」對話框時,點擊「確定」按鈕。

結果:

現在,A 列和 B 列中重複的值已在 A 列中突出顯示,如下截圖所示:
A screenshot showing duplicates highlighted in column A using Conditional Formatting in Excel

提示:
  • 條件格式中的重複規則是「不區分大小寫」。因此,Apple 和 apple 都會被標記為重複值。
  • 如果您想從 B 列中突出顯示重複值,只需先選擇 B 列,然後將以下公式應用於「條件格式」:
    =COUNTIF($A$2:$A$10, B2)>0

使用強大的工具 Kutools 選擇並突出顯示兩列中的重複值

有時候,您可能不僅需要突出顯示,還需要選擇重複值以便將其複製並粘貼到工作簿中的其他位置。在這種情況下,Kutools for Excel 的「選擇相同與不同單元格」功能是一個理想選擇。它可以通過突出顯示和選擇您需要的單元格來識別重複值或唯一值。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

點擊「Kutools」>「選擇」>「選擇相同與不同單元格」,在「選擇相同與不同單元格」對話框中,請執行以下操作:

  1. 分別在「查找值位於」和「根據」框中選擇源數據和比較數據;
  2. 在「基於」部分下選擇「按行」;
  3. 從「查找」部分選擇「相同值」;
  4. 在「對選中結果的處理」部分下指定一種背景色來突出顯示重複值;
  5. 最後,點擊「確定」按鈕。
    A screenshot of the Select Same & Different Cells dialog for identifying duplicates

結果:

現在,A 列和 B 列中重複的值已在 A 列中突出顯示並選擇,您可以隨時將其複製並粘貼到任何所需的單元格中。請參見截圖:
A screenshot showing duplicates highlighted and selected in Excel using Kutools

提示:
  • 當在「選擇相同與不同單元格」對話框中勾選「區分大小寫」復選框時,此功能支持區分大小寫的比較。
  • 如果您想選擇 B 列中的重複值,只需在「選擇相同與不同單元格」對話框的「查找值位於」和「根據」框中交換兩個選擇的列;
  • 要應用此功能,請下載並安裝 Kutools for Excel

使用公式查找並提取兩列中的重複值

要查找並提取兩列之間的重複值,您可以使用公式來定位並提取重複值。

請將以下公式複製並粘貼到您希望放置結果的空白單元格中,然後向下拖動填充柄以將此公式應用於其他單元格。

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

注意:在上述公式中,"A2" 是您要查找重複值的列的第一個單元格;"B2:B10" 代表您要比較的數據列表。

結果:

如您所見,如果 A 列中的數據存在於 B 列中,該值將被顯示;否則,單元格將保持空白。
A screenshot showing the use of a formula to find and extract duplicates between two columns in Excel

提示:此公式是「不區分大小寫」的。

使用 VBA 代碼選擇兩列中的重複值

本節將指導您完成創建一個 VBA 代碼的步驟,該代碼可以識別並選擇兩列之間的重複值。

步驟 1:打開 VBA 模塊編輯器並複製代碼

  1. 按下「Alt + F11」鍵打開「Microsoft Visual Basic for Applications」窗口。
  2. 在打開的窗口中,點擊「插入」>「模塊」以創建一個新的空白模塊。
  3. 然後,將以下代碼複製並粘貼到空白模塊中。
    VBA 代碼:查找並選擇兩列之間的重複值
    Sub Compare()
    'Update by Extendoffice
    Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng1 In Range1
        xValue = Rng1.Value
        For Each Rng2 In Range2
            If xValue = Rng2.Value Then
                If outRng Is Nothing Then
                    Set outRng = Rng1
                Else
                    Set outRng = Application.Union(outRng, Rng1)
                End If
            End If
        Next
    Next
    outRng.Select
    Application.ScreenUpdating = True
    End Sub
    

步驟 2:執行此 VBA 代碼

  1. 粘貼此代碼後,請按「F5」鍵運行此代碼。在第一個提示框中,選擇您要選擇重複值的數據列表。然後,點擊「確定」。
    A screenshot showing the first prompt to select a data list when running the VBA code to find duplicates in Excel
  2. 在第二個提示框中,選擇您要比較的數據列表,然後點擊「確定」,請參見截圖:
    A screenshot showing the second prompt to select the comparison data list when running the VBA code in Excel

結果:

現在,A 列和 B 列中的重複值已在 A 列中選擇,您可以根據需要填滿單元格顏色或複製並粘貼它們。
A screenshot showing duplicate values selected in Column A after running the VBA code in Excel

提示:
  • 此 VBA 代碼是「區分大小寫」的;
  • 如果您想選擇 B 列中的重複值,只需在選擇數據範圍時交換兩個選擇的列。

逐行比較兩列是否匹配

在 Excel 中,逐行比較兩列通常是必要的,以檢查匹配項,這有助於檢查記錄或分析數據趨勢等任務。Excel 有不同的方法來做到這一點,從簡單的公式到特殊功能,您可以根據數據的需求選擇最適合的方法。讓我們來看看一些直觀有效的方法來完成這項工作。

使用公式比較同一行的兩列

Excel 的公式提供了一種直接而強大的跨列數據比較方法。以下是使用它們的方法。假設您在 A 列和 B 列中有數據,要檢查兩列中的數據是否匹配,以下公式可能會幫助您:

提示:這些公式用途廣泛,不僅適用於文本,還適用於數字、日期和時間。
使用等於運算符 (=):

請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。如果 A 列和 B 列同一行的值相同,則返回 TRUE;如果不相同,則返回 FALSE。請參見截圖:

=A2=B2

A screenshot showing results of comparing two columns in Excel using the Equal To operator (=)

IF 函數:

如果您想使比較更具信息性,可以使用 IF 函數來顯示自定義消息。

請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。當值相同時返回「匹配」,當值不同時返回「未匹配」。請參見截圖:

=IF(A2=B2, "Match", "No Match")
提示:您可以根據需要更改「匹配」、「未匹配」為其他表達方式。

A screenshot showing results of comparing two columns in Excel with the IF function to display 'Match' or 'No Match'

EXACT 函數:

如果您需要區分大小寫的比較,「EXACT 函數」是最佳選擇。

請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。當值完全匹配時返回「匹配」,當值不同時返回「未匹配」。請參見截圖:

=IF(EXACT(A2,B2), "Match", "No match")   
提示:您可以根據需要更改「匹配」、「未匹配」為其他表達方式。

A screenshot showing results of comparing two columns in Excel using the case-sensitive EXACT function


使用實用工具 Kutools 選擇並突出顯示同一行中的匹配項

如果您需要選擇並著色兩列之間的匹配項,而不是在單獨的列中獲取結果,Kutools for Excel 的比較單元格功能將是一個很好的選擇。它使您能夠快速選擇並應用填充顏色到每行內匹配或不同的單元格。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

點擊「Kutools」>「比較單元格」,在「比較單元格」對話框中,請執行以下操作:

  1. 分別在「查找值位於」和「根據」框中選擇兩列的數據;
  2. 在「查找」部分下選擇「相同單元格」;
  3. 在「對選中結果的處理」部分下指定一種背景色來突出顯示匹配項;
  4. 最後,點擊「確定」按鈕。
    A screenshot of Kutools for Excel's Compare Cells dialog box for highlighting matches in the same row

結果:

現在,同一行中的匹配項已在 A 列中突出顯示並選擇,您可以隨時將其複製並粘貼到任何所需的單元格中。請參見截圖:
A screenshot showing matches highlighted in the same row using Kutools for Excel's Compare Cells feature

提示:
  • 如果在「比較單元格」對話框中勾選「區分大小寫」選項,此功能「支持區分大小寫」的比較;
  • 如果您想選擇 B 列中的匹配項,只需在「比較單元格」對話框的「查找值位於」和「根據」框中交換兩個選擇的列;
  • 要應用此功能,請下載並安裝 Kutools for Excel

比較兩列並突出顯示同一行中的匹配項

比較兩列並突出顯示同一行中的匹配項可以使用 Excel 中的條件格式有效地完成。以下是識別並突出顯示行匹配項的指南:

步驟 1:選擇數據範圍

選擇您要突出顯示行匹配項的數據範圍。

步驟 2:應用條件格式功能

  1. 點擊「開始」>「條件格式」>「新建規則」。在「新建格式規則」對話框中,請執行以下操作:
    • 2.1 從「選擇規則類型」列表框中選擇「使用公式確定要設置格式的單元格」;
    • 2.2 在「為符合此公式的值設置格式」文本框中輸入以下公式;
      =$B2=$A2
    • 2.3 然後,點擊「格式」按鈕。
    • A screenshot showing the New Formatting Rule dialog box for highlighting matches in Excel rows using Conditional Formatting
  2. 在彈出的「設置單元格格式」對話框中,指定一種顏色來突出顯示匹配項。然後點擊「確定」。
    A screenshot of the Format Cells dialog box for selecting a highlight color in Excel
  3. 當返回到「新建格式規則」對話框時,點擊「確定」按鈕。

結果:

現在,同一行中的匹配值已立即突出顯示,請參見截圖:
A screenshot showing row matches highlighted using Conditional Formatting in Excel

提示:
  • 條件格式中的公式是「不區分大小寫」的。
  • 如果您希望突出顯示具有不同值的單元格,請應用以下公式:
    =$B2<>$A2

比較兩列並提取匹配數據

當您處理 Excel 中的兩組數據並需要在一組列表中找到另一組列表中的共同項目時,查找公式是您獲取這些匹配項的首選解決方案。

在 Excel 中,如果您在 A 列中有一個水果列表,在 B 列中有其銷售數據,現在您想將這些數據與 D 列中的某些水果進行匹配以找到其相應的銷售數據。如何在 Excel 中返回 B 列中的相對值?
A screenshot showing two lists of data in Excel, used for comparing and retrieving matching data

請根據需要應用以下任意公式之一,然後向下拖動填充柄以將此公式應用於其餘單元格。

  • 所有 Excel 版本:
    =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
  • Excel 365 和 Excel 2021:
    =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

結果:

如果找到匹配項,所有相應的值將被顯示,否則將返回 #N/A 錯誤,請參見截圖:
A screenshot showing results of using VLOOKUP to pull matching data in Excel

提示:
  • 使用上述公式,如果 D 列中的某些水果在 A 列中沒有匹配項,它們將返回錯誤。為了使這些錯誤更容易理解,您可以使用 IFERROR 函數包裝您的公式:
    • 所有 Excel 版本:
      =IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
    • Excel 365 和 Excel 2021:
      =IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
  • 對於那些熱衷於高級查找公式的用戶,Kutools for Excel 提供了一套令人印象深刻的高級查找公式,將傳統的 VLOOKUP 函數提升到新的高度,為您的數據管理任務提供了無與倫比的精確性和效率。
    A screenshot showing Kutools for Excel's advanced lookup tools for pulling matching data
    Kutools for Excel 擁有超過 300 種便捷工具的集合,旨在提高您的生產力。體驗其全部功能並提升您的電子表格! 立即獲取!

相關文章:

  • 在範圍內查找並突出顯示重複行
  • 有時候,工作表的數據範圍中可能存在一些重複記錄,現在您想查找或突出顯示範圍內的重複行,如下截圖所示。當然,您可以逐一檢查每一行來找到它們。但如果有多達數百行,這不是一個好的選擇。這裡,我將介紹一些有用的方法來處理這個任務。
  • 以不同顏色突出顯示重複值
  • 在 Excel 中,我們可以輕鬆地使用條件格式以一種顏色突出顯示一列中的重複值,但有時候,我們需要以不同顏色突出顯示重複值,以便快速容易地識別重複項,如下截圖所示。如何在 Excel 中解決這個問題?
  • 查找、突出顯示、篩選、計數、刪除 Excel 中的重複值
  • 在 Excel 中,由於我們手動記錄數據、從其他來源複製數據或其他原因,重複數據經常出現。有時候,重複值是有必要且有用的。然而,有時候重複值會導致錯誤或誤解。這裡,本文將介紹方法,使用公式、條件格式規則、第三方插件等快速識別、突出顯示、篩選、計數、刪除 Excel 中的重複值。
  • 刪除重複值並替換為空白單元格
  • 通常,當您應用 Excel 中的「刪除重複值」命令時,它會刪除整個重複行。但有時候,您希望用空白單元格替換重複值,在這種情況下,「刪除重複值」命令將不起作用。本文將指導您在 Excel 中刪除重複值並將其替換為空白單元格。