如何比較兩個 Excel 工作表的重複項：5 種快速方法

在 Excel 中處理大型數據集時，通常會有多個包含相似數據的工作表。比較這些工作表中的重複記錄可能是一個相當大的挑戰，尤其是當數據分散在不同的列和行中時。

在兩個 Excel 工作表中查找重複項的最常用方法是使用：

1. VLOOKUP、COUNTIF 或 EXACT 函數

2. 條件格式

3. 電源查詢

4. 外部工具和插件

5. 目視檢查重複項

本文逐步介紹了這五種方法，以查明和處理跨多個工作表的重複項。到最後，您將看到管理重複值是多麼容易。

讓我們開始吧！

目錄

• 樣本工作表和數據
• 1. 使用 VLOOKUP、COUNTIF 或 EXACT 函數查找重複項
  • A. 如何使用 VLOOKUP 函數在兩個工作表中查找重複項？
    • 如果您處理不同的工作簿怎麼辦？
  • B. 如何使用 COUNTIF 函數查找工作表中的重複項？
  • C. 如何使用 EXACT 函數查找工作表中的重複項？
• 2.如何對重複行使用條件格式
  • 如何使用條件格式規則管理器
• 3. 如何使用 Power Query 跨工作表查找重複項
• 4. 識別工作表重複項的工具和插件
• 5. 如何目視檢查兩張紙中的重複項
• 準備 Excel 工作表的 3 個技巧
• 如何處理錯誤和不一致
• 最後的想法

樣本工作表和數據

按照本文中的示例，使用第一個工作表的 A 列中的此數據創建一個新的 Excel 工作簿：

• 蘋果

• 橙子

• 梨

• 草莓

將此數據放入第二張表的 A 列：

• 梨

• 草莓

• 蘋果

• 菠蘿

您的工作表如下所示：

設置好工作表後，我們現在可以了解在兩個工作表中查找重複項的不同方法，從 VLOOKUP、COUNTIF 和 EXACT 函數開始。

我們走吧！

1. 使用 VLOOKUP、COUNTIF 或 EXACT 函數查找重複項

Excel 具有三個可以輕而易舉地查找重複項的內置函數：VLOOKUP、COUNTIF和EXACT。

這些功能旨在幫助您查找、計算和比較電子表格中的數據，使其成為查找重複條目的理想工具。

每個功能都有獨特的用途，了解如何使用它們可以顯著簡化您的數據分析過程。

在以下部分中，我們將探討如何使用 VLOOKUP、COUNTIF 和 EXACT 函數有效識別 Excel 工作表中的重複項。

A. 如何使用 VLOOKUP 函數在兩個工作表中查找重複項？

VLOOKUP 代表垂直查找。您可以使用此函數查找兩列之間的重複值。這是語法：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：要在 table_array 的第一列中搜索的值。

• table_array：包含要在其中搜索的數據的單元格範圍。

• col_index_num：要從中返回值的 table_array 中的列號。

• range_lookup：可選。它要么是 TRUE（近似匹配），要么是 FALSE（完全匹配）。默認值為 TRUE。

要跨 Excel 文件中的兩個工作表使用該函數，您需要知道如何在公式中引用單獨的工作表。為此，請鍵入工作表名稱，後跟感嘆號 (!) 和您的單元格或單元格範圍。

例如，這將引用同一工作簿的工作表 2 中的單元格 A2 到 A5：工作表 2!$A$2:$A$5。

要在示例電子表格中使用 VLOOKUP 函數，請執行以下步驟：

1. 選擇單元格 B2 以顯示第一個比較結果。

2. 鍵入此公式：=VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE)。

3. 按回車鍵顯示比較結果。

4. 填寫公式以比較第一個工作表中其餘行的值。

結果將如下圖所示：

當找不到重複項時，您可以通過顯示用戶友好的消息而不是錯誤來整理顯示。

例如，此公式將分別為找到的值和未找到的值顯示“是”或“否”：

=IF(ISNA(VLOOKUP(A2, Sheet 2!$A$2:$A$5, 1, FALSE)), “否”, “是”)

在我們關於使用 VLOOKUP 比較兩列的文章中有更多關於此函數的示例。

如果您處理不同的工作簿怎麼辦？

如果您的工作表位於不同的工作簿中，則函數用法與兩個 Excel 文件相同。但是，引用第二個工作表有點複雜。

你需要：

• 將 Excel 工作簿的名稱括在括號中

• 跟隨工作表的名稱

• 將工作簿和工作表用引號引起來。

例如，如果單元格位於名為“WB 2.xlsx”的工作簿中名為 Sheet2 的工作表中，則格式將如下所示：

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

在輸入公式之前，關閉第二個工作簿。否則，你會得到一個錯誤。

B. 如何使用 COUNTIF 函數查找工作表中的重複項？

Excel 中的 COUNTIF 函數用於計算指定範圍內滿足給定條件的單元格的數量。

要比較多個工作表，您可以計算第二個工作表中與第一個工作表中的單元格匹配的單元格數。

這是函數的語法：

=COUNTIF（範圍，標準）

• Range：要根據指定條件計數的單元格範圍。

• Criteria：細胞計數必須滿足的條件。

要將函數與示例數據一起使用，請執行以下步驟：

1. 選擇單元格 B2 以顯示第一個比較結果。

2. 鍵入此公式：=COUNTIF(Sheet2!$A$2:$A$5, A2)

3. 按回車鍵顯示比較結果。

4. 填寫公式以比較第一個工作表中其餘行的值。

該函數將為某些單元格找到一個匹配項，而不會為其他單元格找到一個匹配項。比較單元格顯示計數。以下是示例數據的結果：

COUNTIF 函數可用於其他有用的任務，例如計算 Excel 中的非空白單元格。

C. 如何使用 EXACT 函數查找工作表中的重複項？

Excel 中的 EXACT 函數還可用於查找兩個不同 Excel 工作表中相同單元格內的重複項。語法是：

=EXACT（文本 1，文本 2）

• text1是要比較的第一個文本字符串。

• text2是要比較的第二個文本字符串。

按著這些次序：

1. 選擇單元格 B2。

2. 輸入公式=EXACT(A2, Sheet2!A2)

3. 按回車鍵顯示比較結果。如果兩個值相同，則公式將返回 TRUE，否則返回 FALSE。

4. 填寫公式以比較第一個工作表中其餘行的值。

請注意，此方法不會在單元格範圍內搜索重複項。相反，您只是在不同工作表中查找基於相同單元格的匹配項。

這對於您只希望有少數例外的有序數據很有用。

VLOOKUP、COUNTIF 和 EXACT 函數對於查找重複項很有用，但 Excel 是一個多功能程序，還有其他選項。在下一節中，我們將了解如何使用條件格式來識別兩個工作表中的重複項。

2.如何對重複行使用條件格式

在本節中，您將學習如何使用條件格式來查找和突出顯示兩個 Excel 工作表中的重複行。

要創建條件格式規則，請按照下列步驟操作：

1. 選擇包含數據的單元格範圍（在本例中為 A2:A5）。

2. 單擊 Excel 功能區中的“主頁”選項卡。

3. 單擊“樣式”組中的“條件格式”。

4. 從下拉菜單中選擇“新規則”。

下一個任務是為您的規則提供一個公式以供使用。按著這些次序：

1. 在對話框中選擇“使用公式確定要設置格式的單元格”

2. 輸入以下公式：=COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

最後，您對重複單元格應用您喜歡的格式。

1. 單擊“格式”按鈕以打開“設置單元格格式”對話框。

2. 選擇一種格式，例如用黃色背景色填充重複項。

3. 單擊確定。

您的重複數據現在以黃色突出顯示。

如何使用條件格式規則管理器

創建條件格式規則後，您可以使用條件格式規則管理器對其進行管理。

要訪問管理器：

1. 轉到主頁選項卡。

2. 單擊條件格式。

3. 選擇“管理規則”。

您將看到應用於所選工作表的所有條件格式規則的列表。您可以通過選擇規則並單擊相應的按鈕來編輯、刪除或更改規則的順序。

要將相同的規則應用於另一張紙，請按照下列步驟操作：

1. 在第二張表中選擇要比較的範圍。

2. 轉到條件格式規則管理器。

3. 選擇規則，單擊複製規則，然後單擊編輯規則。

4. 將“Sheet2”替換為要比較的第一個工作表的名稱。

現在您已將條件格式規則應用於兩個工作表，重複項將根據您選擇的格式突出顯示。

確保根據需要調整公式中的範圍和單元格引用，以涵蓋要比較的所有數據。

條件格式可能看起來有點原始。如果您想要更精細的控制，那麼 Power Query 可能就是答案！在下一節中，我們將介紹如何使用 Power Query 查找重複項。

3. 如何使用 Power Query 跨工作表查找重複項

Power Query 是 Microsoft Excel 中的數據轉換和數據準備工具。識別相同的值只是您可以使用該工具執行的眾多分析任務之一。

為此，您應該首先將兩個工作表中的數據導入到單獨的表中。在每個工作表中執行以下步驟：

1. 右鍵單擊單元格區域。

2. 選擇“從表/範圍獲取數據”。

3. 將表名修改為適當的名稱。

導入兩個工作表後，第一個任務是合併數據：

1. 轉到“數據”選項卡。

2. 單擊“獲取數據”。

3. 選擇“合併查詢”。

4. 選擇“合併”並選擇兩個表。

5. 單擊兩個鍵列。

6. 選擇“內部”作為“加入類型”，然後單擊“確定”。

Power Query 編輯器將打開，其中包含 Excel 工作表中兩個表的組合數據。您將看到兩列，每列一列。由於您只對重複值感興趣，因此可以刪除第二列。

您可以在 Power Query 編輯器中單擊“關閉並加載”以將重複項加載到新工作表。

要探索此強大功能的更多方面，請按照我們關於如何在 Excel 中使用 Power Query 的文章中的示例進行操作。

Excel 還具有第三方工具和加載項，它們添加了無縫查找重複項的功能，因此讓我們在下一節中了解其中的一些工具。

4. 識別工作表重複項的工具和插件

外部工具和加載項可以提供本機 Excel 功能中可能不可用的高級功能。這些工具可以進一步簡化比較重複工作表的過程。

Spreadsheet Compare 是一款 Microsoft 工具，可讓您並排比較兩個工作簿，突出差異並輕鬆識別重複項。您可以從 Microsoft 網站下載它。

您可以安裝多個插件來自動執行查找重複項的過程。一個例子是“ Duplicate Remover ”。安裝插件：

1. 轉到“插入”選項卡。

2. 單擊“獲取加載項”。

3. 搜索“重複”。

4. 在您選擇的工具上單擊“添加”。

5. 如何目視檢查兩張紙中的重複項

如果一切都失敗了，用你的眼睛！Excel 中的“排列窗口”對話框允許您並排查看多個工作表或工作簿。

雖然它不能直接找到重複項，但它可以幫助您直觀地比較工作表或工作簿中的數據以發現重複項。按著這些次序：

1. 單擊 Excel 功能區中的“查看”選項卡。

2. 單擊“窗口”組中的“全部排列”。

3. 選擇一個排列選項，例如“垂直”或“水平”。

這將並排顯示兩張紙或一張在另一張上方。現在您可以手動比較每個工作表中的數據以識別重複項。

您需要滾動瀏覽數據並目視檢查每個值以找到匹配項。

請注意，此方法對於大型數據集效率不高，因為它需要手動比較。使用本文中的其他方法將更有效地在更大的數據集中查找重複項。

這就是我們在 Excel 工作表中查找重複值的最後一種常用方法！在下一節中，我們將為您提供一些準備工作表的技巧。

準備 Excel 工作表的 3 個技巧

在開始比較多張工作表之前，請確保數據集的列和行已正確排列。

確保兩個Excel 工作表具有相同的結構和相同的標題名稱。如果需要，您可以重新排列兩個工作表中的列以相互匹配。

以下是確保准確比較的三個建議：

1. 在兩個工作表中以相同的順序排列您的數據。這使得 Excel 函數更容易有效地工作。

2. 使用一致的格式、大小寫和數據類型規範化您的數據。這將防止由於細微差異而導致的不匹配條目。

3. 刪除不必要的空白行或列，因為它們可能會干擾比較過程。

您可以通過計算唯一值來評估數據集中有多少重複項。該視頻介紹了幾種方法：

如何處理錯誤和不一致

數據中的不一致會影響比較過程。以下是解決不一致的四個技巧：

1. 檢查數據類型是否存在差異，例如在同一列中混合文本和數值。

2. 確保對日期、數字和其他數據類型使用一致的格式。

3. 檢查您的數據是否存在缺失或不正確的條目，並在必要時進行更新。

4. 標準化數據集中的縮寫或不一致的命名約定。

最後的想法

在兩個 Excel 工作表中查找重複項是數據管理和分析的一項基本任務，可確保數據的完整性和準確性。Excel 提供了多種識別重複項的技術，每種技術都有自己的優點和局限性。

方法的選擇取決於用戶的需求、數據集的大小和復雜性以及期望的結果。對於較小的數據集和直接比較，使用VLOOKUP、COUNTIF 或條件格式可能就足夠了。

對於更大的數據集或更複雜的數據轉換，Power Query 是一個強大而靈活的工具，可以處理範圍廣泛的數據準備任務，包括查找重複項。

總而言之，比較Excel 工作表的重複項是您工具箱中的一項非常方便的技能。借助本文中的技巧，您可以發現那些討厭的重複項並讓您的數據乾淨利落。

相信我們，隨著您在這方面做得越來越好，您將輕鬆完成數據任務並給周圍的每個人留下深刻印象！