Excel不同工作表重複資料比對教學總覽
在專案管理、團隊協作或日常辦公中，常常需要比對不同Excel工作表之間的資料是否重複，例如：比對兩份產品清單、員工名單或客戶資料，找出重複或僅存在於某一表格的項目。這不僅有助於資料清理，也能避免重複作業與錯誤決策。
無論你是專案經理、團隊領導，還是希望提升效率的知識工作者，掌握Excel不同工作表重複資料比對的方法，都是提升生產力的關鍵技能。
常見比對方法與適用情境
不同的比對方法各有優缺點，選擇合適的工具能大幅提升工作效率。以下將分別介紹VLOOKUP、COUNTIF、條件格式與XLOOKUP等方法，並說明各自的適用場合。
VLOOKUP函數比對
原理與適用情境
VLOOKUP可用於在一個工作表查找另一工作表的資料，適合需要返回對應欄位值的情境。例如：比對產品代碼，並帶出對應價格或庫存數量。
限制
– 只能向右查找（查找值必須在查找範圍的最左欄）
– 若資料格式不一致，容易出現#N/A錯誤
– 無法直接比對多欄位
實例說明
假設Sheet1與Sheet2都有「產品名稱」與「價格」欄，需比對兩表中哪些產品名稱重複，並帶出Sheet2的價格。
公式範例
在Sheet1的C2輸入：
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
若查無資料，會顯示#N/A，代表該產品未在Sheet2出現。
COUNTIF函數比對
原理與適用情境
COUNTIF適合用於單一欄位的重複資料檢查，能快速判斷某資料是否在另一工作表出現，常用於名單比對、重複檢查。
優點
– 公式簡單，適合大批量資料
– 可直接用於條件格式
公式範例
在Sheet1的C2輸入：
=COUNTIF(Sheet2!A:A, A2)
結果大於0表示A2在Sheet2有重複。
與VLOOKUP差異
COUNTIF僅能判斷有無重複，無法帶出對應欄位資料。
條件格式高亮重複資料
原理與適用情境
條件格式可用於視覺化標示重複資料，適合需要快速檢視哪些資料重複的情境，常見於名單審查、資料清理。
操作步驟
1. 選取Sheet1需比對的資料範圍
2. 點選「開始」>「條件格式」>「新規則」
3. 選擇「使用公式來決定要格式化的儲存格」
4. 輸入公式：=COUNTIF(Sheet2!$A$2:$A$100, A2)>0
5. 設定填色或字體格式
優缺點
– 優點：直觀、易於操作
– 缺點：僅能標示，無法帶出對應資料；跨多表時需手動調整範圍
XLOOKUP與新函數應用（進階）
XLOOKUP優勢
XLOOKUP為新版Excel提供的強大函數，突破VLOOKUP只能向右查找的限制，支援向左、向右查找，且能自訂找不到時的回傳值。
公式範例
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "未找到")
FILTER函數
FILTER可用於篩選出所有重複或唯一資料，適合進階用戶進行自動化資料整理。
適用場合
– 需同時比對多欄位
– 需自訂查無資料時的顯示內容
– 大量資料自動化處理
實作案例：兩個工作表比對重複資料
以「產品清單」為例，示範如何用三種方法比對Sheet1與Sheet2的重複產品。
情境說明
Sheet1：公司A的產品清單
Sheet2：公司B的產品清單
目標：找出兩公司都有販售的產品
步驟一：準備資料
- Sheet1與Sheet2皆有「產品名稱」欄
- 確認資料格式一致（避免空格、全形半形差異）
步驟二：VLOOKUP應用
- 在Sheet1新增「B公司有無」欄
- 輸入：
=IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "否", "是") - 拖曳填滿，即可判斷每個產品是否在公司B出現
步驟三：COUNTIF應用
- 在Sheet1新增「B公司重複次數」欄
- 輸入：
=COUNTIF(Sheet2!A:A, A2) - 結果大於0即為重複
步驟四：條件格式應用
- 選取Sheet1「產品名稱」欄
- 設定條件格式，公式同上
- 重複資料自動高亮
常見問題與錯誤排解
#N/A錯誤
原因：查找值在目標範圍不存在，或資料格式不一致
解決方式：
– 檢查資料有無多餘空格、格式差異
– 使用TRIM、CLEAN函數清理資料
資料格式不一致
常見情境：一方為數字、一方為文字，或有隱藏字元
解決方式：
– 統一格式（如全部轉為文字或數字）
– 使用VALUE、TEXT函數轉換
公式錯誤
常見情境：範圍設定錯誤、漏加絕對參照（$）
解決方式：
– 檢查公式範圍
– 需要固定範圍時加上$符號
進階技巧與自動化
多表/多檔案比對
- 可將多個工作表資料合併到一張表，再用上述方法比對
- 若需跨多檔案比對，可利用Power Query整合資料來源
巨集自動化簡介
- 透過VBA巨集可自動化重複資料比對流程，適合大量或定期作業
- 需具備VBA基礎知識，或可考慮使用現成協作工具
資料同步建議
- 若多位同仁需協同維護資料，建議使用如Monday.com、ClickUp等專業協作平台，支援多人即時編輯、資料同步與權限管理，降低重複資料風險。
專案協作與資料比對工具推薦
在團隊協作或跨部門專案中，僅靠Excel手動比對容易出現遺漏或版本混亂。建議可搭配Monday.com等專案管理工具，將資料比對、進度追蹤與協作流程整合於同一平台，提升透明度與效率。
這類工具支援自動化提醒、權限分級與多檔案管理，特別適合需要多人協作或定期資料比對的情境。
常見FAQ
Q1：如何只找出A表有、B表沒有的資料？
可用VLOOKUP或COUNTIF判斷，若結果為#N/A或0，即表示僅存在A表。
Q2：比對時資料格式不同怎麼辦？
建議先用TRIM、CLEAN、VALUE等函數統一格式，再進行比對。
Q3：能否一次比對多個欄位（如姓名+電話）？
可將多欄位合併為一欄（如=A2&B2），再用上述方法比對。
Q4：如何標示只在一方出現的資料？
可用條件格式或輔助欄位，設定公式判斷另一表是否有對應資料，並標示出來。
Q5：Excel有無更自動化的比對工具？
進階用戶可考慮Power Query、巨集，或導入如Monday.com等協作平台自動化資料同步。
總結與行動建議
Excel提供多種方法協助不同工作表間的重複資料比對，從基礎的VLOOKUP、COUNTIF、條件格式，到進階的XLOOKUP、FILTER與自動化巨集，皆能因應不同規模與需求。
選擇合適的方法，並搭配專業協作工具，能大幅提升資料處理效率、降低錯誤風險。建議讀者依據實際工作情境，靈活運用上述技巧，並嘗試導入如Monday.com等平台，優化團隊資料管理流程。
