Excel兩份資料比對重複項目總覽
在專案管理、名單整理或訂單核對等日常工作中，經常需要比對兩份資料，找出重複或相同的項目。Excel提供多種工具與函數，能協助用戶高效完成比對任務。以下將依據不同需求，介紹各種比對方法、適用情境與常見應用，並以實際案例說明操作細節。
方法比較與適用情境
| 方法 | 適用情境 | 優點 | 可能限制 |
|---|---|---|---|
| 條件格式 | 需快速視覺化重複項目 | 操作直觀、即時高亮 | 跨表、複雜比對有限 |
| VLOOKUP/COUNTIF | 需標記、篩選重複，或進一步處理 | 公式彈性、易於自動化 | 公式易錯、跨表需調整 |
| XLOOKUP | 新版Excel，需更彈性查找 | 支援雙向查找、參數靈活 | 舊版Excel不支援 |
| Power Query | 大量資料、需自動化或跨檔案比對 | 處理大量資料效能佳 | 學習曲線較高 |
資料準備與前置處理
開始比對前，建議先檢查資料格式是否一致（如去除多餘空白、統一大小寫、移除重複值），以避免比對出現誤差。例如，若要比對兩份員工名單，應確保姓名欄位格式統一，並去除多餘空格。
常見錯誤：
– 資料有隱藏空白或格式不一致，導致比對失敗。
– 欄位名稱不同，公式引用錯誤。
方法一：條件格式高亮重複項目
條件格式適合需要快速視覺化重複項目的情境，例如比對兩份名單，直接高亮顯示重複者。
操作步驟
-
選取比對範圍
假設第一份資料在A欄，第二份資料在B欄，先選取A欄所有資料（如A2:A100）。 -
設定條件格式
點選「常用」>「條件格式」>「新增規則」>「使用公式決定要格式化的儲存格」。 -
輸入公式
在公式欄輸入：
=COUNTIF($B$2:$B$100, A2)>0
這代表A欄的每個值，只要在B欄出現過就會被高亮。 -
設定格式
選擇填色或字體顏色，點擊「確定」。 -
檢查結果
A欄中與B欄重複的項目即會自動高亮。
延伸應用
- 跨工作表比對：公式可改為
=COUNTIF(工作表2!$B$2:$B$100, A2)>0。 - 多欄位比對：可將比對範圍擴大，如
=COUNTIF($C$2:$D$100, A2)>0。
常見錯誤與排查
- 公式範圍未加絕對參照（$），導致比對錯誤。
- 比對範圍包含空白列，出現誤判。
實務案例
應用情境：
人資部門需比對新進員工名單與現有員工名單，快速找出重複錄用者。透過條件格式高亮，立即辨識重複名單，避免重複入職。
方法二：VLOOKUP、COUNTIF與XLOOKUP比對重複
當需要標記重複項目、進行進一步篩選或自動化處理時，公式法更具彈性。
COUNTIF公式比對
公式範例：
在C2輸入：
=IF(COUNTIF($B$2:$B$100, A2)>0, "重複", "不重複")
向下填滿，即可標記A欄每筆資料是否在B欄出現。
VLOOKUP公式比對
公式範例：
=IF(ISNA(VLOOKUP(A2, $B$2:$B$100, 1, FALSE)), "不重複", "重複")
VLOOKUP僅能從左往右查找，且若B欄有重複值，僅返回第一個結果。
XLOOKUP（新版Excel）
公式範例：
=IF(ISNUMBER(XLOOKUP(A2, $B$2:$B$100, $B$2:$B$100, , 0)), "重複", "不重複")
XLOOKUP支援雙向查找，參數更靈活，建議使用新版Excel者優先考慮。
跨工作表/跨檔案比對
公式中的範圍可引用其他工作表或檔案，如：
=COUNTIF([名單B.xlsx]Sheet1!$B$2:$B$100, A2)>0
常見錯誤與排查
- COUNTIF/VLOOKUP範圍錯誤，導致結果全為「不重複」。
- 資料格式不一致（如數字與文字混用）造成比對失敗。
- VLOOKUP查找值在查找範圍左側，導致查找不到。
實務案例
應用情境：
行銷團隊需比對活動報名名單與歷史參加者，標記重複參與者以進行專屬回饋。利用COUNTIF公式批次標記，快速完成篩選。
方法三：Power Query自動化比對（進階）
Power Query適合處理大量資料、跨檔案比對或需自動化流程的場景。
操作步驟
-
載入資料
點擊「資料」>「取得資料」>「自Excel活頁簿」，分別載入兩份資料。 -
合併查詢
在Power Query編輯器中，選擇「合併查詢」，以主要欄位（如姓名或ID）為依據合併。 -
選擇合併方式
可選「內部合併」（僅保留重複）、或「左外部合併」（保留主資料表全部，標記重複）。 -
展開合併結果
展開新欄位，即可看到哪些資料有重複。
優缺點
- 優點：適合大量資料、可自動化、支援跨檔案。
- 限制：需學習Power Query操作，初學者需適應。
實務案例
應用情境：
採購部門需比對數千筆供應商訂單與歷史訂單，找出重複下單。利用Power Query合併查詢，大幅提升比對效率，並可定期自動更新。
多欄位/多條件比對技巧
若需同時比對多個欄位（如姓名+電話），可將多欄位合併後再比對。
公式範例：
在C2輸入：
=A2&B2
將A欄與B欄內容合併，再用COUNTIF比對：
=IF(COUNTIF($D$2:$D$100, C2)>0, "重複", "不重複")
其中D欄為另一份資料的合併欄。
應用情境：
人資需比對同名同姓但電話不同的員工，避免誤判。
比對後的資料處理建議
比對完成後，常見的後續處理包括：
- 自動標記重複項目：利用公式或條件格式標記，便於後續篩選。
- 篩選重複或唯一項目：利用篩選功能，快速分離重複與不重複資料。
- 移除重複：選取資料範圍，點選「資料」>「移除重複」，可依多欄位設定。
自動化建議：
若經常需進行資料比對與協作，可考慮導入如Monday.com、ClickUp等專案協作平台，結合自動化流程與權限管理，提升團隊效率。
常見問題與錯誤排查
- Q：比對結果全為「不重複」，怎麼辦？
A：檢查比對範圍、資料格式（文字/數字）、是否有隱藏空白。 - Q：如何比對不同工作表的資料？
A：公式中的範圍加上工作表名稱即可，如=COUNTIF(工作表2!$B$2:$B$100, A2)>0。 - Q：VLOOKUP找不到值但明明有？
A：檢查查找值與範圍格式是否一致，避免多餘空白或格式錯誤。 - Q：如何比對多欄位資料？
A：可先合併多欄位內容，再用COUNTIF或VLOOKUP比對。
實務案例：名單比對與重複檢查
案例一：活動報名重複檢查
行銷部需比對新活動報名名單與歷史參加者，避免重複發送邀請。透過COUNTIF公式標記重複者，並用篩選功能快速整理。
案例二：訂單重複核對
採購專員需比對本月訂單與過去訂單，找出重複下單。利用Power Query合併查詢，批次處理數千筆資料，顯著提升效率。
進階協作與自動化工具應用
當比對需求頻繁或涉及多部門協作時，建議結合自動化與協作平台。例如，Monday.com可將Excel資料自動匯入，設定自動比對與通知流程，適合專案團隊進行跨部門名單核對或訂單審查。
ClickUp與Notion也支援資料庫管理與自動化提醒，適合知識工作者與團隊協作。
常見FAQ
Q1：Excel舊版沒有XLOOKUP怎麼辦？
A：可用VLOOKUP或COUNTIF替代，或考慮升級Excel版本。
Q2：如何比對跨檔案的資料？
A：在公式中引用完整檔案路徑，如=[名單B.xlsx]Sheet1!$B$2:$B$100，或用Power Query載入多檔案。
Q3：比對大量資料時Excel很慢，有建議嗎？
A：建議用Power Query處理，或將資料分批比對，減少即時運算壓力。
Q4：如何自動化比對流程？
A：可結合Excel巨集、Power Query或協作平台（如Monday.com）設定自動比對與通知。
結論與選擇建議
Excel比對兩份資料重複項目有多種方法，從基礎條件格式、公式法到進階Power Query，皆可依據資料量、複雜度與自動化需求選擇最適合的方案。若僅需快速視覺化，條件格式最便捷；需批次標記或篩選，建議用COUNTIF或VLOOKUP；大量或跨檔案比對，則推薦Power Query。
如需團隊協作與自動化，建議結合Monday.com等平台，打造高效資料管理與協作流程。
