Excel 大量資料比對完整教學：6 種方法從公式到工具一次搞定（2026 更新）

Excel 大量資料比對的核心方法包括 XLOOKUP、VLOOKUP、COUNTIF、MATCH 函數，以及條件式格式設定與 Inquire 增益集。根據資料量與比對需求的不同，選擇正確的方法能將原本數小時的人工逐行比對，縮短到幾秒鐘完成。本文整理截至 2026 年仍適用的 6 種實戰方法，從最基礎的公式到進階的檔案比對工具，幫助你快速找出重複項目與資料差異。

可以參考 Excel 大量匯入照片攻略：一次性插入多張圖片，輕鬆管理！

Excel 大量資料比對方法總覽：6 種方法怎麼選？

依據資料量大小與比對目的，選擇最適合的方法能大幅提升效率。以下表格整理 6 種常用的 Excel 資料比對方法，方便你根據實際需求快速決定。

方法一：用 COUNTIF 快速找出兩欄重複資料

COUNTIF 函數能在一欄中計算某個值出現的次數，是判斷「有沒有重複」最直覺的方法。當你有兩份客戶名單，想快速知道哪些客戶同時出現在兩份名單中，COUNTIF 是最快的起手式。

假設 A 欄是舊客戶名單（A1:A100），C 欄是新客戶名單（C1:C80），在 B1 輸入以下公式並向下填滿：

=IF(COUNTIF($C$1:$C$80, A1)>0, "重複", "")

這個公式會逐一檢查 A 欄的每筆資料是否存在於 C 欄中。若存在，B 欄顯示「重複」；若不存在，則顯示空白。

操作步驟：

1. 在 B1 儲存格輸入上述公式
2. 按下 Enter 確認
3. 將 B1 儲存格向下拖曳填滿至 B100
4. B 欄中顯示「重複」的列，即為兩份名單中共同出現的客戶

根據 Microsoft 官方文件，COUNTIF 在處理數千筆資料時效能穩定，但當資料超過 5 萬筆時，建議改用 XLOOKUP 或 Power Query（Excel 內建的資料匯入與轉換工具，可處理大規模跨檔案合併比對）以避免計算速度變慢。

方法二：用 VLOOKUP 比對資料並回傳對應值

VLOOKUP（Vertical Lookup，垂直查閱）能根據指定的關鍵值，在另一個表格中查找並回傳對應欄位的資料。這是 Excel 中使用率最高的查找函數之一，適合「已知 A 欄位的值，要找出 B 欄位對應什麼」的場景。

VLOOKUP 的語法結構如下：

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

• lookup_value：用來比對的值（例如員工編號「E001」）
• table_array：要搜尋的資料範圍（例如 $H$1:$I$100）
• col_index_num：要回傳的欄位序號（從 table_array 最左欄算起第幾欄）
• range_lookup：填 FALSE 表示精確比對，填 TRUE 表示近似比對

實例：根據產品名稱查找售價

假設 D 欄是銷售產品名稱，H 欄到 I 欄是產品售價參考表。在 F2 輸入：

=VLOOKUP(D2, $H$2:$I$100, 2, FALSE)

向下拖曳後，每一列都會自動根據 D 欄的產品名稱，從售價表中找到對應價格。

VLOOKUP 比對文字的注意事項：

• VLOOKUP 可以比對文字，不限於數字。但文字比對採精確匹配（Exact Match），「王小明」與「王 小明」（含空格）會被視為不同值
• 建議在比對前使用 TRIM 函數清除多餘空格：=VLOOKUP(TRIM(D2), $H$2:$I$100, 2, FALSE)
• VLOOKUP 不區分英文大小寫，「Apple」與「apple」會被視為相同

實戰經驗：在處理上萬筆客戶資料比對時，我最常遇到的問題是「明明資料一樣，VLOOKUP 卻回傳 #N/A」。十之八九是因為其中一欄的資料尾端有看不見的空格或換行符號。養成習慣在比對前先用 TRIM 和 CLEAN 函數清理資料，能省下大量 debug 時間。

方法三：用 XLOOKUP 取代 VLOOKUP，更強大的比對函數

XLOOKUP 是 Microsoft 於 2019 年推出的新一代查找函數，能同時取代 VLOOKUP、HLOOKUP 和 INDEX+MATCH 的組合，是目前 Excel 大量資料比對的首選工具。根據 Microsoft 365 官方文件，XLOOKUP 在處理 10 萬筆以上資料時，效能較 VLOOKUP 提升約 20-30%。

XLOOKUP 的語法結構：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value：要搜尋的值
• lookup_array：要搜尋的範圍（單一欄或列）
• return_array：要回傳的範圍（單一欄或列）
• if_not_found（選填）：找不到時顯示的訊息，例如 “查無資料”
• match_mode（選填）：0 = 精確比對（預設）、-1 = 精確或次小、1 = 精確或次大
• search_mode（選填）：1 = 從頭搜尋（預設）、-1 = 從尾搜尋

XLOOKUP vs VLOOKUP 關鍵差異：

實例：用 XLOOKUP 查找業務員的銷售業績

假設 A 欄是業務員姓名（A2:A50），B 欄是銷售金額（B2:B50），你想在 E2 輸入某個業務員姓名後，自動顯示其業績：

=XLOOKUP(E2, $A$2:$A$50, $B$2:$B$50, "查無此人")

如果 E2 輸入「陳小華」，XLOOKUP 會在 A 欄中搜尋「陳小華」，找到後回傳對應的 B 欄銷售金額。若找不到，則顯示「查無此人」而非 #N/A 錯誤。

方法四：用 MATCH + INDEX 組合進行靈活比對

MATCH 函數回傳搜尋值在範圍中的位置序號，搭配 INDEX 函數則能根據位置序號取出對應的值，兩者組合的靈活度超越 VLOOKUP。這個組合特別適合查找欄不在最左欄、或需要同時比對多個條件的進階場景。

基本語法：

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

實例：在 B 欄中找出 A 欄值的對應資料

假設你要根據 A1 的產品編號，在 D 欄（D1:D200）找到該編號的位置，再從 E 欄（E1:E200）取出對應的庫存數量：

=INDEX($E$1:$E$200, MATCH(A1, $D$1:$D$200, 0))

MATCH 先在 D 欄找到 A1 值的位置（例如第 15 列），INDEX 再從 E 欄的第 15 列取出庫存數量。

何時選 INDEX+MATCH 而非 XLOOKUP？

• 你的 Excel 版本不支援 XLOOKUP（Excel 2019 以下）
• 需要同時進行橫向與縱向的交叉查找
• 需要搭配陣列公式進行多條件比對

方法五：用條件式格式設定，視覺化標記重複與差異

條件式格式設定（Conditional Formatting）不需要撰寫任何公式，透過滑鼠點選即可將重複資料自動標上顏色，是最適合非技術人員的比對方法。

操作步驟：

1. 選取資料範圍：用滑鼠框選要比對的欄位，例如 A1:A500
2. 開啟條件式格式：點選「常用」索引標籤 → 「條件式格式設定」
3. 選擇醒目提示規則：點選「醒目提示儲存格規則」→「重複的值」
4. 設定標記顏色：在彈出視窗中選擇「重複」，並挑選醒目顏色（例如淺紅色填滿搭配深紅色文字），點擊「確定」

完成後，所有在選取範圍內出現超過一次的值，都會被自動標上你指定的顏色。

進階用法：比對兩欄之間的差異

若要找出 A 欄有但 B 欄沒有的資料，可以使用自訂公式的條件式格式：

1. 選取 A1:A500
2. 「條件式格式設定」→「新增規則」→「使用公式來決定要格式化哪些儲存格」
3. 輸入公式：=COUNTIF($B$1:$B$500, A1)=0
4. 設定格式為黃色底色，點擊「確定」

這樣 A 欄中所有「B 欄找不到」的值都會被標為黃色，一目瞭然。

方法六：用 Inquire 增益集比對兩個 Excel 檔案

Inquire 增益集是 Excel 內建的檔案比對工具，能一鍵找出兩個活頁簿之間所有的差異，包括儲存格值、公式、格式設定與巨集。這個功能特別適合需要審核不同版本報表的財務、會計與稽核人員。

啟用 Inquire 增益集的步驟：

1. 點選「檔案」→「選項」→「增益集」
2. 在下方「管理」下拉選單中選擇「COM 增益集」，按「執行」
3. 勾選「Inquire」，按「確定」
4. 回到 Excel 主畫面，功能列會出現「Inquire」索引標籤

使用 Inquire 比對檔案：

1. 同時開啟兩個要比對的 Excel 檔案
2. 點選「Inquire」索引標籤 →「比較檔案」
3. 在「比較」欄位選擇舊版檔案，「對照」欄位選擇新版檔案
4. 按下「比較」，Excel 會以顏色標記所有差異

比對結果中，不同類型的差異會以不同顏色標示：綠色代表新增內容、紅色代表刪除內容、藍色代表修改內容。左下方的勾選框可以篩選要檢視的差異類型（公式、格式、值等）。

注意：Inquire 增益集僅在 Excel 專業增強版（Professional Plus）、Microsoft 365 企業版中提供。如果你使用的是家用版或學生版，可能無法啟用此功能。

Excel 大量資料比對的實戰操作指引

掌握以下 5 個步驟，能系統性地完成任何規模的 Excel 資料比對工作，避免遺漏與錯誤。

1. 清理資料格式：比對前先用 TRIM 函數去除多餘空格、CLEAN 函數去除不可見字元、TEXT 函數統一日期格式。格式不一致是比對失敗的第一大原因
2. 根據資料量選擇方法：5,000 筆以下用 COUNTIF 或條件式格式即可；5,000 至 50,000 筆建議用 VLOOKUP 或 XLOOKUP；50,000 筆以上考慮使用 Power Query 或 Inquire 增益集
3. 固定參照範圍：在公式中使用絕對參照（$A$1:$A$100），避免向下填滿時參照範圍跟著位移，導致比對結果錯誤
4. 驗證比對結果：隨機抽取 5-10 筆比對結果，手動確認正確性。特別留意 #N/A 錯誤是「真的找不到」還是「格式不一致導致的誤判」
5. 備份原始資料：比對完成後，將原始資料另存一份備份。若後續發現比對邏輯有誤，可以從原始資料重新開始，不必擔心資料被覆蓋

Excel 大量資料比對可以用哪些函數？

最常用的函數包括 COUNTIF（計算重複次數）、VLOOKUP（垂直查找對應值）、XLOOKUP（新一代雙向查找函數）、MATCH（回傳搜尋值的位置）、INDEX（根據位置取值）。其中 XLOOKUP 功能最全面，但需要 Microsoft 365 或 Excel 2021 以上版本。

XLOOKUP 和 VLOOKUP 哪個比較好？

如果你的 Excel 版本支援 XLOOKUP（Microsoft 365 或 Excel 2021 以上），建議優先使用 XLOOKUP。它能向左向右查找、預設精確比對、找不到時可自訂訊息，語法也更直覺。VLOOKUP 的優勢在於相容性，幾乎所有 Excel 版本都支援。

Excel 大量資料比對速度很慢怎麼辦？

當資料超過 5 萬筆時，COUNTIF 和 VLOOKUP 可能導致計算速度明顯變慢。建議改用 XLOOKUP（效能較佳）、將計算模式暫時切換為「手動」（公式 → 計算選項 → 手動），或使用 Power Query 進行合併比對。

如何比對兩個不同 Excel 檔案的資料？

有兩種方法：第一，將兩個檔案的資料複製到同一個活頁簿中，再使用 VLOOKUP 或 XLOOKUP 跨工作表比對；第二，使用 Inquire 增益集（Excel 專業版以上），同時開啟兩個檔案後一鍵比對所有差異。

VLOOKUP 比對文字出現 #N/A 錯誤怎麼解決？

最常見的原因是文字中存在看不見的空格或特殊字元。解決方法：在公式中加入 TRIM 函數清除空格，例如 =VLOOKUP(TRIM(A1), $D$1:$E$100, 2, FALSE)。若仍有問題，再加上 CLEAN 函數去除不可見字元。

不會寫公式，有沒有不用函數的比對方法？

有。Excel 的「條件式格式設定」功能不需要寫任何公式。選取資料範圍後，點選「常用」→「條件式格式設定」→「醒目提示儲存格規則」→「重複的值」，即可自動將重複資料標上顏色。

Excel 資料比對前需要做什麼準備？

比對前建議做 3 件事：第一，用 TRIM 和 CLEAN 函數清理資料中的多餘空格與不可見字元；第二，統一資料格式（例如日期統一為 YYYY/MM/DD）；第三，備份原始資料，避免比對操作意外覆蓋原始內容。