比較兩列並在 Excel 中查找重複值(完整指南)
在 Excel 中識別兩列中的重複值是數據分析中的一項常見任務。這有助於查明數據輸入錯誤、重複記錄,或用於數據清理目的。本文將指導您如何高效且準確地識別兩列中的重複值或匹配項。
使用條件格式突出顯示兩列中的重複值
在 Excel 中突出顯示兩列中的重複值是一種識別重複數據的有效方法,特別是在手動檢查不切實際的大數據集中。在這種情況下,條件格式是一個有用的特性來解決這個問題。
步驟 1:選擇您要突出顯示重複值的列中的數據
在此示例中,我將選擇 A2:A10,請參見截圖:
步驟 2:應用條件格式功能
- 點擊「開始」>「條件格式」>「新建規則」,請參見截圖:
- 在「新建格式規則」對話框中,請執行以下操作:
- 2.1 從「選擇規則類型」列表框中選擇「使用公式確定要設置格式的單元格」;
- 2.2 在「為符合此公式的值設置格式」文本框中輸入以下公式;
=COUNTIF($B$2:$B$10, A2)>0
- 注意:在上述公式中,"B2:B10" 代表您要比較的數據列表,"A2" 是您要突出顯示重複值的列的第一個單元格。該公式檢查 A2 單元格中的值是否在 B 列中的任何地方找到。根據您的數據修改單元格引用。
- 2.3 然後,點擊「格式」按鈕。
- 在彈出的「設置單元格格式」對話框中,指定一種顏色來突出顯示重複項。然後點擊「確定」。
- 當返回到「新建格式規則」對話框時,點擊「確定」按鈕。
結果:
現在,A 列和 B 列中重複的值已在 A 列中突出顯示,如下截圖所示:
- 條件格式中的重複規則是「不區分大小寫」。因此,Apple 和 apple 都會被標記為重複值。
- 如果您想從 B 列中突出顯示重複值,只需先選擇 B 列,然後將以下公式應用於「條件格式」:
=COUNTIF($A$2:$A$10, B2)>0
使用強大的工具 Kutools 選擇並突出顯示兩列中的重複值
有時候,您可能不僅需要突出顯示,還需要選擇重複值以便將其複製並粘貼到工作簿中的其他位置。在這種情況下,Kutools for Excel 的「選擇相同與不同單元格」功能是一個理想選擇。它可以通過突出顯示和選擇您需要的單元格來識別重複值或唯一值。
點擊「Kutools」>「選擇」>「選擇相同與不同單元格」,在「選擇相同與不同單元格」對話框中,請執行以下操作:
- 分別在「查找值位於」和「根據」框中選擇源數據和比較數據;
- 在「基於」部分下選擇「按行」;
- 從「查找」部分選擇「相同值」;
- 在「對選中結果的處理」部分下指定一種背景色來突出顯示重複值;
- 最後,點擊「確定」按鈕。
結果:
現在,A 列和 B 列中重複的值已在 A 列中突出顯示並選擇,您可以隨時將其複製並粘貼到任何所需的單元格中。請參見截圖:
- 當在「選擇相同與不同單元格」對話框中勾選「區分大小寫」復選框時,此功能支持區分大小寫的比較。
- 如果您想選擇 B 列中的重複值,只需在「選擇相同與不同單元格」對話框的「查找值位於」和「根據」框中交換兩個選擇的列;
- 要應用此功能,請下載並安裝 Kutools for Excel。
使用公式查找並提取兩列中的重複值
要查找並提取兩列之間的重複值,您可以使用公式來定位並提取重複值。
請將以下公式複製並粘貼到您希望放置結果的空白單元格中,然後向下拖動填充柄以將此公式應用於其他單元格。
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
注意:在上述公式中,"A2" 是您要查找重複值的列的第一個單元格;"B2:B10" 代表您要比較的數據列表。
結果:
如您所見,如果 A 列中的數據存在於 B 列中,該值將被顯示;否則,單元格將保持空白。
使用 VBA 代碼選擇兩列中的重複值
本節將指導您完成創建一個 VBA 代碼的步驟,該代碼可以識別並選擇兩列之間的重複值。
步驟 1:打開 VBA 模塊編輯器並複製代碼
- 按下「Alt + F11」鍵打開「Microsoft Visual Basic for Applications」窗口。
- 在打開的窗口中,點擊「插入」>「模塊」以創建一個新的空白模塊。
- 然後,將以下代碼複製並粘貼到空白模塊中。
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 代碼
- 粘貼此代碼後,請按「F5」鍵運行此代碼。在第一個提示框中,選擇您要選擇重複值的數據列表。然後,點擊「確定」。
- 在第二個提示框中,選擇您要比較的數據列表,然後點擊「確定」,請參見截圖:
結果:
現在,A 列和 B 列中的重複值已在 A 列中選擇,您可以根據需要填滿單元格顏色或複製並粘貼它們。
- 此 VBA 代碼是「區分大小寫」的;
- 如果您想選擇 B 列中的重複值,只需在選擇數據範圍時交換兩個選擇的列。
逐行比較兩列是否匹配
在 Excel 中,逐行比較兩列通常是必要的,以檢查匹配項,這有助於檢查記錄或分析數據趨勢等任務。Excel 有不同的方法來做到這一點,從簡單的公式到特殊功能,您可以根據數據的需求選擇最適合的方法。讓我們來看看一些直觀有效的方法來完成這項工作。
使用公式比較同一行的兩列
Excel 的公式提供了一種直接而強大的跨列數據比較方法。以下是使用它們的方法。假設您在 A 列和 B 列中有數據,要檢查兩列中的數據是否匹配,以下公式可能會幫助您:
- 「使用等於運算符 (=)」:比較兩個單元格的最簡單方法
- 「IF 函數」:使您的比較更具信息性
- 「EXACT 函數」:區分大小寫的比較
使用等於運算符 (=):
請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。如果 A 列和 B 列同一行的值相同,則返回 TRUE;如果不相同,則返回 FALSE。請參見截圖:
=A2=B2
IF 函數:
如果您想使比較更具信息性,可以使用 IF 函數來顯示自定義消息。
請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。當值相同時返回「匹配」,當值不同時返回「未匹配」。請參見截圖:
=IF(A2=B2, "Match", "No Match")
EXACT 函數:
如果您需要區分大小寫的比較,「EXACT 函數」是最佳選擇。
請輸入或複製以下公式,按「Enter」鍵,然後向下拖動填充柄以獲取所有結果。當值完全匹配時返回「匹配」,當值不同時返回「未匹配」。請參見截圖:
=IF(EXACT(A2,B2), "Match", "No match")
使用實用工具 Kutools 選擇並突出顯示同一行中的匹配項
如果您需要選擇並著色兩列之間的匹配項,而不是在單獨的列中獲取結果,Kutools for Excel 的比較單元格功能將是一個很好的選擇。它使您能夠快速選擇並應用填充顏色到每行內匹配或不同的單元格。
點擊「Kutools」>「比較單元格」,在「比較單元格」對話框中,請執行以下操作:
- 分別在「查找值位於」和「根據」框中選擇兩列的數據;
- 在「查找」部分下選擇「相同單元格」;
- 在「對選中結果的處理」部分下指定一種背景色來突出顯示匹配項;
- 最後,點擊「確定」按鈕。
結果:
現在,同一行中的匹配項已在 A 列中突出顯示並選擇,您可以隨時將其複製並粘貼到任何所需的單元格中。請參見截圖:
- 如果在「比較單元格」對話框中勾選「區分大小寫」選項,此功能「支持區分大小寫」的比較;
- 如果您想選擇 B 列中的匹配項,只需在「比較單元格」對話框的「查找值位於」和「根據」框中交換兩個選擇的列;
- 要應用此功能,請下載並安裝 Kutools for Excel。
比較兩列並突出顯示同一行中的匹配項
比較兩列並突出顯示同一行中的匹配項可以使用 Excel 中的條件格式有效地完成。以下是識別並突出顯示行匹配項的指南:
步驟 1:選擇數據範圍
選擇您要突出顯示行匹配項的數據範圍。
步驟 2:應用條件格式功能
- 點擊「開始」>「條件格式」>「新建規則」。在「新建格式規則」對話框中,請執行以下操作:
- 2.1 從「選擇規則類型」列表框中選擇「使用公式確定要設置格式的單元格」;
- 2.2 在「為符合此公式的值設置格式」文本框中輸入以下公式;
=$B2=$A2
- 2.3 然後,點擊「格式」按鈕。
- 在彈出的「設置單元格格式」對話框中,指定一種顏色來突出顯示匹配項。然後點擊「確定」。
- 當返回到「新建格式規則」對話框時,點擊「確定」按鈕。
結果:
現在,同一行中的匹配值已立即突出顯示,請參見截圖:
- 條件格式中的公式是「不區分大小寫」的。
- 如果您希望突出顯示具有不同值的單元格,請應用以下公式:
=$B2<>$A2
比較兩列並提取匹配數據
當您處理 Excel 中的兩組數據並需要在一組列表中找到另一組列表中的共同項目時,查找公式是您獲取這些匹配項的首選解決方案。
在 Excel 中,如果您在 A 列中有一個水果列表,在 B 列中有其銷售數據,現在您想將這些數據與 D 列中的某些水果進行匹配以找到其相應的銷售數據。如何在 Excel 中返回 B 列中的相對值?
請根據需要應用以下任意公式之一,然後向下拖動填充柄以將此公式應用於其餘單元格。
- 所有 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 錯誤,請參見截圖:
- 使用上述公式,如果 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")
- 所有 Excel 版本:
- 對於那些熱衷於高級查找公式的用戶,Kutools for Excel 提供了一套令人印象深刻的高級查找公式,將傳統的 VLOOKUP 函數提升到新的高度,為您的數據管理任務提供了無與倫比的精確性和效率。
Kutools for Excel 擁有超過 300 種便捷工具的集合,旨在提高您的生產力。體驗其全部功能並提升您的電子表格! 立即獲取!
相關文章:
- 在範圍內查找並突出顯示重複行
- 有時候,工作表的數據範圍中可能存在一些重複記錄,現在您想查找或突出顯示範圍內的重複行,如下截圖所示。當然,您可以逐一檢查每一行來找到它們。但如果有多達數百行,這不是一個好的選擇。這裡,我將介紹一些有用的方法來處理這個任務。
- 以不同顏色突出顯示重複值
- 在 Excel 中,我們可以輕鬆地使用條件格式以一種顏色突出顯示一列中的重複值,但有時候,我們需要以不同顏色突出顯示重複值,以便快速容易地識別重複項,如下截圖所示。如何在 Excel 中解決這個問題?
- 查找、突出顯示、篩選、計數、刪除 Excel 中的重複值
- 在 Excel 中,由於我們手動記錄數據、從其他來源複製數據或其他原因,重複數據經常出現。有時候,重複值是有必要且有用的。然而,有時候重複值會導致錯誤或誤解。這裡,本文將介紹方法,使用公式、條件格式規則、第三方插件等快速識別、突出顯示、篩選、計數、刪除 Excel 中的重複值。
- 刪除重複值並替換為空白單元格
- 通常,當您應用 Excel 中的「刪除重複值」命令時,它會刪除整個重複行。但有時候,您希望用空白單元格替換重複值,在這種情況下,「刪除重複值」命令將不起作用。本文將指導您在 Excel 中刪除重複值並將其替換為空白單元格。
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!