比較兩列以在 Excel 中尋找重複項(完整指南)
識別 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 然後,點擊 格式 按鈕。
- 在彈出的 單元格格式 對話方塊中,指定一種想要反白顯示重複項的顏色。 然後單擊 OK.
- 當它返回 新格式規則 對話框,單擊 OK 按鈕。
結果:
現在,A 列和 B 列中重複的值現在在 A 列中突出顯示,如下圖所示:
- 中的重複規則 條件格式 is 不區分大小寫。 因此,Apple 和 apple 都會被標記為重複項。
- 如果要反白 B 列中的重複項,只需先選擇 B 列,然後將以下公式套用到 條件格式:
=COUNTIF($A$2:$A$10, B2)>0
使用強大的工具 - Kutools 選擇並反白顯示兩列中的重複項
有時,您可能不僅需要突出顯示,還需要選擇重複以將其複製並貼上到工作簿中的其他位置。 在這種情況下, Excel的Kutools“ 選擇相同和不同的單元格 功能是一個理想的選擇。 它可以透過突出顯示並選擇您需要的儲存格來識別重複或唯一的值。
點擊 庫工具 > 選擇 > 選擇相同和不同的單元格在 選擇相同和不同的單元格 對話框,請執行以下操作:
- 選擇來源資料和比較數據 在中查找值 和 根據 分開的盒子;
- 選擇 每一行 在 基於 部分;
- 選擇 相同的值 來自 發現 部分;
- 指定背景顏色以反白重複值 結果處理 部分;
- 最後點擊 OK 按鈕。
結果:
現在,A 列和 B 列中重複的值在 A 列中被反白並選擇,以便您複製並貼上到任何所需的儲存格中。 看截圖:
- 此功能 支援區分大小寫 勾選時比較 區分大小寫 中的複選框 選擇相同和不同的單元格 對話;
- 如果要從 B 列中選擇重複項,只需交換 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 鍵來運行此程式碼。 在第一個提示方塊中,選擇要從中選擇重複項目的資料清單。 然後,點擊 OK.
- 在第二個提示框中,選擇要比較的資料列表,然後按一下 OK,請參見屏幕截圖:
結果:
現在,在 A 列中選擇 A 列和 B 列中的重複值,讓您可以使用顏色填充儲存格或根據需要複製並貼上它們。
- 這段VBA程式碼是 區分大小寫;
- 如果要從B列中選擇重複項,只需在選擇資料範圍時交換所選的兩個列即可。
逐行比較兩列的匹配項
在 Excel 中,通常需要逐行比較兩列來檢查匹配項,這有助於檢查記錄或分析資料趨勢等任務。 Excel 有不同的方法來執行此操作,從簡單的公式到特殊功能,因此您可以選擇最適合您的資料需求的方法。 讓我們來看看一些有效完成這項工作的簡單方法。
使用公式比較同一行中的兩列
Excel 的公式為跨列資料比較提供了一種簡單而有效的方法。 以下是如何使用它們。 假設您的 A 列和 B 列中有數據,要檢查兩列中的數據是否匹配,以下公式可能會幫助您:
- 使用等於運算子 (=):比較兩個單元格最簡單的方法
- 中頻功能:讓您的比較提供更多信息
- 精確功能:區分大小寫的比較
使用等於運算子 (=):
請輸入或複製以下公式,按 Enter 鍵,然後向下拖曳填充手柄以獲得所有結果。 如果同一行的 A 列和 B 列的值相同,則傳回 TRUE,否則傳回 FALSE。 看截圖:
=A2=B2
如果函數:
如果您想讓比較提供更多信息,您可以使用 IF功能 顯示自訂訊息。
請輸入或複製下面的公式,按 Enter 鍵,然後向下拖曳填充手柄以獲得所有結果。 當值相同時,它將傳回 Match;當值不同時,它將傳回 No Match。 看截圖:
=IF(A2=B2, "Match", "No Match")
確切的功能:
如果您需要區分大小寫的比較, 精確函數 是要走的路。
請輸入或複製以下公式,按 Enter 鍵,然後向下拖曳填充手柄以獲得所有結果。 當值完全匹配時,它將傳回 Match;當值不同時,它將傳回 No Match。 看截圖:
=IF(EXACT(A2,B2), "Match", "No match")
使用有用的工具選擇並突出顯示同一行中的匹配項 - Kutools
如果您需要逐行選擇並著色兩列之間的匹配項,而不是在單獨的列中獲取結果,Kutools for Excel 的比較單元格功能將是一個很好的選擇。它使您能夠快速選擇填充顏色並將其應用到每行中值匹配或不同的單元格。
點擊 庫工具 > 比較單元格在 比較單元格 對話框,請執行以下操作:
- 從兩列中選擇數據 在中查找值 和 根據 分開的盒子;
- 選擇 相同的細胞 在 發現 部分;
- 指定背景顏色以突出顯示下面的匹配項 結果處理 部分;
- 最後點擊 OK 按鈕。
結果:
現在,同一行中的符合項目在 A 列中被反白並選擇,使您可以將它們複製並貼上到任何所需的儲存格中。 看截圖:
- 此功能 支援區分大小寫 比較如果你檢查 區分大小寫 在選項 比較單元格 對話框;
- 如果要從B列中選擇匹配項,只需交換B列中選定的兩個列即可 在中查找值 和 根據 盒子的 比較單元格 對話框;
- 要應用此功能,請 下載並安裝 Kutools for Excel 第一。
比較兩列並突出顯示同一行中的匹配項
使用 Excel 中的條件格式可以有效地比較兩列並反白顯示同一行中的符合項目。 以下是識別和突出顯示行匹配的指南:
步驟一:選擇資料範圍
選擇要反白顯示行匹配的資料範圍。
步驟 2:套用條件格式功能
- 點擊 首頁
> 條件格式 > 新規則。 在 新格式規則 對話框,請執行以下操作:
- 2.1選擇 使用公式來確定要格式化的單元格 來自 選擇規則類型 列錶框;
- 2.2 在下面的公式中輸入 格式化此公式為真的值 文本框;
=$B2=$A2
- 2.3 然後,點擊 格式 按鈕。
- 在彈出的 單元格格式 對話方塊中,指定一種想要反白顯示重複項的顏色。 然後單擊 OK.
- 當它返回 新格式規則 對話框,單擊 OK 按鈕。
結果:
現在,同一行中的匹配值會立即被反白顯示,請參閱螢幕截圖:
- 條件格式中的公式是 不區分大小寫.
- 如果您的目標是反白顯示具有不同值的儲存格,請套用下列公式:
=$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 版本:
- 對於那些熱衷於高級查找公式的人來說, Excel的Kutools 提供了一套令人印象深刻的高級查找公式,將傳統的 VLOOKUP 函數提升到新的高度,為您的資料管理任務提供無與倫比的精確度和效率。
Excel的Kutools 擁有超過 300 種方便的工具,旨在提高您的工作效率。 立即體驗 30 天免費試用的全部功能並提升您的電子表格! 立即獲取!
相關文章:
- 尋找並突出顯示某個範圍內的重複行
- 有時,工作表的數據范圍中可能有一些重複的記錄,現在您想要查找或突出顯示該範圍中的重複行,如下面的屏幕快照所示。 當然,您可以通過檢查行來一個接一個地找到它們。 但是,如果有數百行,這不是一個好選擇。 在這裡,我將討論一些有用的方法來幫助您處理此任務。
- 用不同的顏色突出顯示重複的值
- 在Excel中,通過使用條件格式設置,我們可以輕鬆地用一種顏色突出顯示一列中的重複值,但是有時,我們需要突出顯示不同顏色的重複值,以便快速,輕鬆地識別重複項,如下面的屏幕快照所示。 您如何在Excel中解決此任務?
- 在Excel中查找,突出顯示,過濾,計數,刪除重複項
- 在Excel中,當我們手動記錄數據,從其他來源複製數據或出於其他原因而重複出現數據。 有時,重複是必要且有用的。 但是,有時重複的值會導致錯誤或誤解。 在這裡,本文將介紹在Excel中通過公式,條件格式設置規則,第三方加載項等快速識別,突出顯示,篩選,計數,刪除重複項的方法。
- 刪除重複項並替換為空白單元格
- 通常,當您在 Excel 中應用刪除重複項命令時,它會刪除整個重複行。 但有時,您希望空白單元格替換重複值,在這種情況下,刪除重複項命令將不起作用。 本文將指導您刪除重複項並將其替換為 Excel 中的空白單元格。
最佳辦公生產力工具
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超級查詢: 多條件VLookup | 多值VLookup | 跨多個工作表的 VLookup | 模糊查詢 .... | |
高級下拉列表: 快速建立下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 比較範圍和列 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 資源庫 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符,...) | 50+ 圖表 類型 (甘特圖,...) | 40+ 實用 公式 (根據生日計算年齡,...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片,...) | 12 轉化 工具 (數字到單詞, 貨幣兌換,...) | 7 合併與拆分 工具 (高級合併行, 分裂細胞,...) | ... 和更多 |
使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。 點擊此處獲取您最需要的功能...
Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!