Excel 列比較：查找匹配項和差異！

逐行比較兩列

以下是一組數據（區域 B2:C8），我需要檢查 B 列中的名稱是否與同一行中的 C 列相同。

這部分提供了兩個示例來說明如何逐行比較兩列

• 示例1：比較同一行中的單元格
• 示例2：區分大小寫比較同一行中的單元格

示例1：比較同一行中的單元格

通常，如果您想逐行比較兩列以完全匹配，您可以使用以下公式：

=B2=C2

按 Enter 鍵並將填充柄拖到單元格 D8。如果公式返回 TRUE，則兩列同一行中的值完全相同；如果返回 FALSE，則值不同。

或者，您可以使用 IF 函數顯示特定文本來顯示匹配或不匹配，如下所示：

=IF(B2=C2,"Match","No match")

結果可能如下所示：

示例2：區分大小寫比較同一行中的單元格

如果您想逐行比較兩列以區分大小寫，可以使用結合 IF 和 EXACT 函數的公式。

IF(EXACT(B2,C2), "Match", "Mismatch")

按 Enter 鍵獲取第一個結果，然後將自動填充柄拖到單元格 D8。

在上述公式中，您可以將文本“Match”和“Mismatch”更改為您自己的描述。

比較同一行中的多列

有時，您可能想要比較同一行中的多於兩列，例如下圖所示的數據集（區域 B2:D7）。在本節中，列出了不同的方法來比較多列。

在這裡，它被分為兩部分，以提供詳細的說明，說明如何在同一行中比較多列。

• 示例1：比較多列並在同一行的所有單元格中找到匹配項
• 示例2：比較多列並在同一行的任意兩個單元格中找到匹配項

示例1：比較多列並在同一行的所有單元格中找到匹配項

要在同一行中找到跨列的完全匹配，以下公式可以幫助您。

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

按 Enter 鍵獲取第一個比較結果，然後將自動填充柄拖到單元格 E7。

示例2：比較多列並在同一行的任意兩個單元格中找到匹配項

有時，您想找出同一行中的任意兩列是否匹配，您可以使用以下 IF公式。

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

按 Enter 鍵，並將填充柄拖到單元格 E7。

逐行比較兩列或多列並選取項目的背景色匹配項目或差異

如果您想比較兩列或多列並選取項目的背景色匹配項目或差異，本節將介紹兩種方法來處理這些工作。

有兩個示例用於比較和選取項目的背景色匹配項目和差異

• 示例1：比較兩列並選取項目的背景色同一行中所有單元格或任意兩個單元格的完全匹配
• 示例2：比較兩列並選取項目的背景色同一行中的差異

示例1：比較兩列並選取項目的背景色同一行中所有單元格或任意兩個單元格的完全匹配

要在同一行的所有單元格或任意兩個單元格中選取項目的背景色匹配項目，您可以使用條件格式功能。

1. 選擇您使用的區域，然後點擊首頁 > 使用條件格式 > 新規則。

2. 在新格式規則對話框中

1. 從選擇規則類型部分選擇使用公式來確定要格式化的單元格
2. 在中使用以下公式 格式化此公式為真的值 文本框中。
   

   =COUNTIF($B2:$D2, $B2)=3

3. 點擊格式。

3. 在 格式化單元格對話框中，然後選擇一種填充顏色或其他單元格格式以突出顯示行。點擊確定 > 確定以關閉對話框。

現在只有所有單元格匹配的行將被選取項目的背景色。

示例2：比較兩列並選取項目的背景色同一行中的差異

如果您想選取項目的背景色同一行中的差異，這意味著它逐個比較列單元格，並根據第一列找到不同的單元格，您可以使用 Excel內建功能-Go To Special。

1. 選擇您想要選取項目的背景色行差異的區域，然後點擊首頁 > 尋找 & 選擇 > 定位至。

2. 在彈出的定位至對話框中，選擇行差異選項。點擊確定。

現在行差異已被選擇。

3 現在保持選擇的單元格，點擊首頁 > 填充顏色從下拉選單中選擇一種顏色。

在單元格中比較兩列以查找唯一和重複數據

在這部分中，數據集（區域 B2:C8）如下所示，您想要找到同時位於 B 列和 C 列中的所有值，或者，僅在 B 列中的值。

本節列出了4 種不同的方法來比較單元格中的兩列，您可以根據需要選擇以下之一。

• 示例1：在單元格中比較兩列並在另一列中顯示比較結果
• 示例2：在單元格中比較兩列並選擇或選取項目的背景色重複或唯一數據，使用方便工具
• 示例3：在單元格中比較兩列並選取項目的背景色重複或唯一數據
• 示例4：在單元格中比較兩列並在另一列中列出精確重複項

示例1：在單元格中比較兩列並在另一列中顯示比較結果

在這裡，您可以使用結合 IF 和 COUNTIF 函數的公式來比較兩列並找到 B 列中但不在 C 列中的值。

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

按 Enter 鍵並將自動填充柄拖到單元格 D8。

示例2：在單元格中比較兩列並選擇或選取項目的背景色重複或唯一數據，使用方便工具

有時，在比較兩列後，您可能會對匹配或差異進行其他操作，例如選擇、刪除、複製等。在這種情況下，一個方便的工具 – Kutools for Excel 的選擇相同 & 不同儲存格可以直接選擇匹配或差異以便更好地進行下一步操作，也可以直接選取項目的背景色值。

免費安裝 Kutools for Excel 後，點擊 Kutools > 選擇 > 選擇相同 & 不同儲存格。然後在選擇相同 & 不同儲存格對話框中，請按以下操作：

1. 在查找值和根據部分中，分別選擇用於比較的兩列。
2. 選擇按行選項。
3. 根據需要選擇相同值或不同值。
4. 指定是否為選擇的值著色，然後點擊確定。

彈出一個對話框提醒您找到的值的數量，點擊確定關閉對話框。與此同時，值已被選擇，現在您可以刪除或複製或執行其他操作。

如果您勾選填充背景色和填充字體顏色複選框，結果顯示如下：

示例3：在單元格中比較兩列並選取項目的背景色重複或唯一數據

Excel 中的條件格式功能非常強大，您可以使用它來比較單元格中的兩列，然後根據需要選取項目的背景色差異或匹配項。

1. 選擇您將要比較的兩列，然後點擊首頁 > 使用條件格式 > 選取項目的背景色單元格規則 > 重複值。

2. 在彈出的重複值對話框中，從值的下拉列表中選擇您需要的選取項目的背景色格式。

3. 點擊確定。然後兩列中的重複項已被選取項目的背景色。

示例4：在單元格中比較兩列並在另一列中列出精確重複項

如果您想在區分大小寫的情況下逐個單元格比較兩列後在另一列中列出匹配值，這裡的以下宏代碼可以幫助您。

1. 啟用您要比較兩列的工作表，然後按 Alt + F11 鍵顯示 Microsoft Visual Basic for Applications 視窗。

2. 在 Microsoft Visual Basic for Applications 視窗中點擊插入 > 模組。

3. 然後將以下代碼複製並粘貼到新的空白模組腳本中。

VBA：在比較兩列後在旁邊的列中列出重複項

Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
    For Each xRgF2 In xRgC2
        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
    Next xRgF2
Next xRgF1
End Sub

4. 按 F5 鍵運行代碼，會有兩個對話框彈出，讓您分別選擇兩列。然後點擊 確定 > 確定。

匹配項已自動列在兩列的右側列中。

比較兩個列表並提取匹配數據

在這裡，我們介紹了兩種不同的情況來比較兩個列表並提取數據。

• 示例1：比較兩列並提取精確匹配數據
• 示例2：比較兩列並提取部分匹配數據

示例1：比較兩列並提取精確匹配數據

例如，有兩個對照表，現在您想比較 B 列和 E 列，然後從 C 列中找到相對價格並將其返回到 F 列。

這裡介紹了一些有用的公式來解決這個問題。

在 F2 單元格中（您想放置返回值的單元格），使用以下公式之一：

=VLOOKUP(E2,$B$2:$C$8,2,0)

或者

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

按 Enter 鍵，第一個值已找到。然後將自動填充柄拖到單元格 F6，所有值已被提取。

示例2：比較兩列並提取部分匹配數據

如果兩個比較列之間存在一些細微差異，如下圖所示，則上述方法無法工作。

在 F2 單元格中（您想放置返回值的單元格），使用以下公式之一：

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

或者

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

按 Enter 鍵，第一個值已找到。然後將自動填充柄拖到單元格 F6，所有值已被提取。

比較兩列並查找缺失數據點

假設有兩列，B 列較長，而 C 列較短，如下圖所示。與 B 列相比，如何找出 C 列中的缺失數據？

• 示例1：比較兩列並查找缺失數據點
• 示例2：查找缺失數據點並將其列在另一列中（使用方便工具）
• 示例3：比較兩列並在下方列出缺失數據

示例1：比較兩列並查找缺失數據點

如果您只想在比較兩列後識別哪些數據丟失，您可以使用以下公式之一：

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

或者

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

按 Enter 鍵，然後將自動填充柄拖到單元格 D10。現在，如果數據同時存在於 B 列和 C 列中，則公式返回 FALSE；如果數據僅存在於 B 列中但在 C 列中丟失，則公式返回 TRUE。

示例2：查找缺失數據點並將其列在另一列中（使用方便工具）

如果您想在比較兩列後對缺失數據進行一些後續操作，例如將缺失數據列在另一列中或在較短列下補充缺失數據，您可以嘗試 Kutools for Excel 的選擇相同 & 不同儲存格 方便工具。

安裝 Kutools for Excel 後，點擊 Kutools > 選擇 > 選擇相同 & 不同儲存格。然後在選擇相同 & 不同儲存格對話框中，按以下操作：

1. 在查找值部分，選擇包含完整列表的較長列。
2. 在根據部分，選擇缺少一些數據的較短列。
3. 選擇按行選項。
4. 選擇不同值選項。點擊確定。

彈出一個對話框提醒您缺失數據的數量，點擊確定關閉它。然後缺失數據已被選擇。

現在您可以按 Ctrl + C 鍵複製選擇的缺失數據，然後按 Ctrl + V 鍵將其粘貼到較短列下方或其他新列中。

示例3：比較兩列並在下方列出缺失數據

如果您想在比較兩列後將缺失數據列在較短列下方，INDEX 陣列公式可以幫助您。

在較短列的下方單元格中，假設單元格 C7，輸入以下公式：

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

按 Shift + Ctrl + Enter 鍵獲取第一個缺失數據，然後將自動填充柄拖下直到返回錯誤值 #N/A。

然後您可以刪除錯誤值，所有缺失數據已列在較短列下方。

使用通配符比較兩列

假設這裡有一個 B 列中的數據列表，您想計算 D 列中包含“Apple”或“Candy”的單元格數量，如下圖所示：

要計算一個單元格是否包含一個或多個值，您可以使用帶有通配符的公式來解決此問題。

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

按 Shift + Ctrl + Enter 鍵獲取第一次檢查，然後將自動填充柄拖到單元格 F8。

如果您想計算包含 D 列中值的單元格總數，請在 F8 單元格下方使用公式：

比較兩列（日期）是否大於或小於

如果有兩列日期，如下圖所示，您可能想比較同一行中哪個日期較晚。

• 示例1：比較兩列是否大於或小於
• 示例2：比較兩列是否大於或小於然後格式化

示例1：比較兩列是否大於或小於

您可以使用簡單的公式快速找出每行中日期1 是否晚於日期2。

=IF(B2>C2,"Yes","No")

按 Enter 鍵獲取第一個比較結果，然後將自動填充柄拖到單元格 C6以獲取所有結果。

示例2：比較兩列是否大於或小於然後格式化

如果您想選取項目的背景色日期1 列中大於日期2 的單元格，您可以使用 Excel 中的條件格式功能。

1. 選擇 B 列（日期1）中的日期，然後點擊首頁 > 使用條件格式 > 新規則。

2. 在新格式規則對話框中，選擇使用公式來確定要格式化的單元格在選擇規則類型部分，然後輸入公式

 =$B2>$C2

到格式化此公式為真的值的文本框中。

=$B2<$C2.

3. 點擊格式按鈕打開格式化單元格對話框，然後選擇您需要的格式類型。點擊確定 > 確定。

4. 然後日期1 列中大於日期2 列的單元格已被選取項目的背景色。

比較兩列並計算匹配或差異

以下數據集是一個用於比較和計算匹配或差異的示例。

SUMPRODUCT公式可以快速計算兩列中的匹配項。

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

按 Enter 鍵獲取結果。

有關計算匹配和差異的更多方法，請訪問此頁面：計算 Excel 中兩列之間的所有匹配/重複項

比較兩個區域

閱讀上述方法後，您現在知道如何比較兩列。然而，在某些情況下，您可能想要比較兩個區域（多列的兩個系列）您可以使用上述方法（公式或條件格式）逐列比較它們，但這裡介紹了一個方便的工具 – Kutools for Excel 可以在不同情況下快速解決這個問題，無需公式。

• 示例1：按單元格比較兩個區域
• 示例2：如果數據順序相同則比較兩個區域

示例1：按單元格比較兩個區域

這裡有兩個需要按單元格比較的區域，您可以使用 Kutools for Excel 的選擇相同 & 不同儲存格 工具來處理它。

免費安裝 Kutools for Excel後，點擊Kutools > 選擇 > 選擇相同 & 不同儲存格。然後在彈出的選擇相同 & 不同儲存格對話框中，按以下操作：

1. 在查找值部分，選擇您想在比較兩個區域後找出匹配或差異的區域。
2. 在根據部分，選擇用於比較的另一個區域。
3. 在基於部分，選擇按單元格。
4. 在查找部分，選擇您想選擇或選取項目的背景色的單元格類型。
5. 在對選中結果的處理部分，您可以通過填充背景色或字體顏色來選取項目的背景色單元格，如果您不需要選取項目的背景色，請不要勾選複選框。點擊確定。

彈出一個對話框並提醒選擇了多少個單元格/行，點擊確定關閉它。

• 選擇並選取項目的背景色唯一值
  
• 選擇並選取項目的背景色重複值
  

示例2：如果數據順序相同則比較兩個區域

假設，區域 F2:H7 是一個模型，現在您想找出區域 B2:D7 中的數據是否按照區域 F2:H7 的正確順序排列。

在這種情況下，Kutools for Excel 的儲存格對比可以幫助您。

免費安裝 Kutools for Excel後，點擊Kutools > 儲存格對比。然後在儲存格對比對話框中，設置如下：

1. 分別將兩個區域選擇到 查找值 和根據框中。
2. 在查找部分選擇您想選取項目的背景色的單元格類型。
3. 在對選中結果的處理部分選擇選取項目的背景色類型。點擊確定。

彈出一個對話框並提醒選擇了多少個單元格，點擊確定關閉它。現在與另一個區域不同的單元格已被選擇並選取項目的背景色。

上述信息詳細說明了如何在 Excel 中比較列。希望您覺得它有價值且有益。欲獲取更多無價的 Excel 技巧和竅門，請深入了解這裡。