【Excel 比對兩欄資料】是否相同？7招抓差異重複值

Excel 比對兩欄資料是否相同,判斷「逐列相同」用 IF 函數、找出「差異」用條件格式化標色、揪出「重複值」用 COUNTIF——三種目的對應三個方法,不用死背。 本文完整教 5 種公式(IF、VLOOKUP、XLOOKUP、EXACT、COUNTIF)與 2 種免公式技巧,附情境對照表、公式速查表與 10 題常見問答。

Excel 比對兩欄資料的 4 種情境 ｜ 用決策表選對方法

在日常的專案管理、團隊協作或資料分析工作裡,「比對兩欄資料」幾乎是天天用得到的動作。問題是,很多人一遇到就直接套 VLOOKUP,結果不是跳 #N/A 就是比錯——因為沒先想清楚「你到底要比什麼」。比對兩欄資料常見有三個目的:判斷兩欄「是否相同」、找出兩份資料的「差異」、揪出「重複值」,各自對應不同方法。

先看四種最常見的情境:

• 名單比對:確認兩份名單(如報名名單與出席名單)是否一致,找出缺席或重複的人。
• 庫存盤點:比對系統庫存與實際盤點數據,揪出異常或遺漏的品項。
• 成績核對:比對學生原始成績與登錄成績,確保沒有打錯或錯置。
• 訂單核查:比對訂單資料與出貨紀錄,追蹤漏單或重複出貨。

這些情境該用哪個方法?下面這張決策表直接對照——先看資料量、再看要不要標色或交叉比對,就能選對工具。

想把這些函數的來龍去脈一次補齊,可以搭配這份Excel 函數完整指南。而如果你發現「同一份比對每週都要重做一次」,那 Excel 其實不是最好的工具——把兩份資料丟進像 monday.com 這樣的協作平台,設定一次比對規則,系統會在資料更新時自動標記新增與重複,省下每週手動核對的時間。這點我們在結語會再展開。

方法一 IF 函數 ｜ 快速判斷兩欄是否相同

要判斷兩欄資料「是否相同」,最直觀的就是 IF 函數——它逐列比對,回傳你看得懂的「相同／不相同」文字,而不是冷冰冰的 TRUE/FALSE。這也是「Excel 比對兩個欄位是否相同公式」最常被搜尋的答案。

基礎公式與操作步驟

假設 A 欄是「原始名單」、B 欄是「出席名單」:

1. 在 C1 輸入:=IF(A1=B1,"相同","不相同")
2. 按 Enter,C1 就會顯示這一列的比對結果。
3. 選取 C1,雙擊右下角的填滿把手(小綠點),整欄會自動向下填滿。

這條公式的邏輯很單純:A1 等於 B1 就回傳「相同」,否則回傳「不相同」。常見的應用案例就是比對員工名單是否正確登錄。想看更多變化用法,可以參考這篇IF 函數應用實例。

空白儲存格與資料不完整的處理

實務上兩欄常有一邊是空白的,這時基本公式會把「兩個空白」判成相同、或把「一邊空白」判成不相同,容易誤導。加一層判斷會更嚴謹:

=IF(AND(A1<>"",B1<>""),IF(A1=B1,"相同","不相同"),"資料不完整")

這樣只要任一欄空白,就明確標成「資料不完整」,不會混進「相同/不相同」的結果裡。

常見錯誤:格式不一致的排解

最常見的坑是「明明看起來一樣,卻判成不相同」,九成是格式問題:

• 數字 vs 文字:一欄是數字 1001、另一欄是文字「1001」,內容相同但型別不同。用 =VALUE(A1)=VALUE(B1) 統一成數字再比。
• 多餘空格:從系統匯出的資料常帶前後空白。用 TRIM 清掉:=IF(TRIM(A1)=TRIM(B1),"相同","不相同")。
• 看不見的字元:CLEAN 可以移除換行等非列印字元。

先把格式清乾淨再比對,準確率會提升很多,也能避免「明明一樣卻判不同」的誤判。

方法二 VLOOKUP／XLOOKUP ｜ 檢查資料是否存在

IF 是「同一列左右比」,但很多時候你要的是「A 欄這個值,到底有沒有出現在 B 欄裡?」——順序不一定對齊。這種「是否存在」的交叉比對,就是 VLOOKUP 和 XLOOKUP 的主場,也是搜尋「vlookup 比對兩個欄位」的人最想要的解法。

VLOOKUP 用法與 ISNA／IFERROR 排解 #N/A

在 C1 輸入:

=IF(ISNA(VLOOKUP(A1,B:B,1,FALSE)),"未找到","已找到")

拖曳填滿 C 欄,就能標出 A 欄每個值是否存在於 B 欄。這裡用 ISNA 包起來的原因是:VLOOKUP 找不到值時會回傳 #N/A,直接攤在報表上很難看,也會干擾後續統計。你也可以改用 IFERROR:

=IFERROR(VLOOKUP(A1,B:B,1,FALSE),"未找到")

如果比對還是找不到明明存在的值,先檢查 B 欄有沒有多餘空格或格式差異,用 TRIM、CLEAN 清理後再比。VLOOKUP 的完整參數與各種進階寫法,可以看這篇VLOOKUP 查表用法。

XLOOKUP 新用法與優點

新版 Excel(Microsoft 365)可用 XLOOKUP,語法更直覺,而且預設就是精確比對、還能向左查找:

=XLOOKUP(A1,B:B,"已找到","未找到")

它的第四個參數就是「找不到時要顯示什麼」,所以不必再額外包 IFERROR,一條就搞定。對於 Excel 大量資料比對,XLOOKUP 的效能與可讀性都比 VLOOKUP 好。

若你的工作牽涉到 QA 或 Bug 追蹤,把比對出的異常直接開成工作項目會更順——這在 Jira 議題追蹤教學裡有原生的做法。

實務案例:庫存核對(系統庫存 vs 實際盤點)

零售或倉儲最常見的場景:A 欄是系統庫存清單、B 欄是實際盤點清單,要找出「系統有、盤點卻不見」的品項。

1. 在 C 欄輸入 =XLOOKUP(A1,B:B,"帳實相符","盤點缺漏")。
2. 把「盤點缺漏」的品項篩出來,就是要重點追查的異常。
3. 反過來用 B 欄比對 A 欄,還能揪出「盤點有、系統沒建檔」的多餘品項。

這種雙向比對比人工逐筆核對更快、也不容易漏看。

方法三 EXACT 函數 ｜ 大小寫敏感的精確比對

一般的 =A1=B1 和 IF 比對,Excel 是「不分大小寫」的——”ABC” 和 “abc” 會被判成相同。但帳號、序號、密碼、優惠碼這些場景,大小寫是有意義的,這時就要用 EXACT。

基本用法與搭配 IF

=EXACT(A1,B1) 會回傳 TRUE(完全相同,含大小寫)或 FALSE(不同)。想要看得懂的文字,包一層 IF:

=IF(EXACT(A1,B1),"完全相同","不同")

忽略大小寫技巧

如果你剛好相反,只想比內容、不管大小寫,可以先用 LOWER 或 UPPER 把兩欄轉成同一種格式再比:

=IF(LOWER(A1)=LOWER(B1),"相同","不同")

一個記法:EXACT 是「連大小寫都要一樣」,LOWER/UPPER 是「假裝大小寫不存在」,看你的需求選邊站。

實務應用:帳號核對

比對使用者帳號、電子郵件或 API 金鑰時,部分系統區分大小寫,用 =A1=B1 會把 “User01” 和 “user01″ 誤判成同一個。改用 EXACT 就能精準抓出這種差異。同樣提醒:比對前先用 TRIM 去掉多餘空格,否則 ” user01″ 和 “user01” 還是會被判成不同。

方法四 條件格式化 ｜ 自動標色找出差異

前面幾種方法會多產生一欄結果,但如果你要的是「一眼看出哪裡不一樣」,把差異或重複直接標成顏色最直覺。這也是搜尋「Excel 比對兩欄資料顏色」「Excel 比對兩欄資料差異」的人要的效果。

操作步驟(COUNTIF 標色)

以「標出 A 欄中沒出現在 B 欄的值」為例:

1. 選取 A 欄資料範圍。
2. 點「開始」→「條件格式化」→「新增規則」→「使用公式來決定要格式化哪些儲存格」。
3. 輸入公式:=COUNTIF(B:B,A1)=0(代表 A 欄這個值在 B 欄出現 0 次,也就是差異)。
4. 按「格式」設定醒目的填滿顏色,確定即可。

想標「重複值」而不是差異,把公式改成 =COUNTIF(B:B,A1)>0 就會反過來把「兩欄都有」的標色。條件格式化的規則管理、優先順序等進階用法,可以參考條件格式化實戰教學。

實務案例:活動報名名單比對(未出席自動標紅)

某公司辦員工講座,要比對報名名單(A 欄)與實際簽到名單(B 欄),找出報名了卻沒到的人:

1. 選取 A 欄報名名單。
2. 用條件格式化套 =COUNTIF(B:B,A1)=0,設成紅色填滿。
3. 所有沒出現在簽到名單裡的人自動變紅,直接匯出就能後續聯繫。

大量資料效能注意事項

條件格式化在幾百列時很順,但整欄套用(B:B、A:A)加上幾萬列時,每次編輯都會重算,檔案容易卡頓。Excel 大量資料比對的建議做法:把範圍限縮成實際資料範圍(如 $B$2:$B$5000),而不是整欄;必要時先用「移除重複值」瘦身再標色。

若這種名單比對每週都要重跑一次,老實說 Excel 會愈用愈累。(推薦試試 monday.com 的免費方案,把兩份名單放進同一個看板,設定一次自動化規則就能持續標記新增與重複,免費版 2 人永久使用、不需信用卡。)

方法五 免公式技巧 ｜ Ctrl+\ 與並排檢視 10 秒比對

不是每次比對都值得寫公式。如果只是臨時想知道「這兩欄哪裡不一樣」,Excel 有兩個內建功能,零公式、10 秒完成。

Ctrl+\ 快速鍵標示差異列

這是最被低估的技巧:

1. 選取要比對的兩欄(例如相鄰的 A2:B20)。
2. 按 Ctrl+\(Ctrl 加反斜線)。
3. Excel 會自動選取「每一列中與第一欄不同」的儲存格。
4. 這時直接按「填滿顏色」,所有差異就被標出來了。

整個過程不用打任何公式,適合臨時核對、快速抓出兩欄不一致的地方。(註:Ctrl+\ 是以每一列最左欄為基準,比對右側欄位的差異。)

並排檢視／分割視窗目視比對

當兩份資料在不同工作表或距離很遠時:

• 並排檢視:開兩個視窗,點「檢視」→「並排檢視」,兩份資料左右對照,還能開「同步捲動」一起滑。
• 分割視窗:在同一張表點「檢視」→「分割」,把視窗切成上下或左右,方便對照離很遠的兩欄。

這兩招對小量資料的目視核對特別好用,也正好回答了「如何在 Excel 中比較兩欄的數據」這個常見問題——不是每次都得靠公式。

移除重複值與多欄比對 ｜ 複合條件實戰

比對到後面,常常需要「先清掉重複、再用多個欄位交叉確認」。這一節把移除重複值和多欄比對一次講清楚,也對應「Excel 比對兩欄資料重複」的需求。

移除重複值:操作步驟與注意事項

1. 先備份原始資料(這步不能省,移除是不可逆的)。
2. 選取要處理的欄位或範圍。
3. 點「資料」→「移除重複值」。
4. 勾選要比對的欄位,按確定。

要注意:移除重複值只會保留第一筆,後面的重複列會被直接刪掉。如果你需要保留最新一筆,得先排序好順序再移除。想更完整地找出、標示、移除重複,這篇找出重複資料技巧講得很細。

AND 函數多欄比對(姓名+電話)

有時單看一欄不夠,得同時比對多個欄位才算數。例如要確認兩筆客戶資料是不是同一人,姓名和電話都要一致:

=IF(AND(A2=C2,B2=D2),"完全相同","不同")

只要其中一欄不同,就會回傳「不同」,避免同名不同人的誤判。

COUNTIF 跨欄比對(訂單編號+金額)

如果資料沒有對齊、要在另一份表裡找「同時符合兩個條件」的紀錄,用 COUNTIFS 最順:

=IF(COUNTIFS(D:D,A2,E:E,B2)>0,"存在","不存在")

這條會去 D、E 兩欄裡找「訂單編號等於 A2、而且金額等於 B2」的紀錄,兩個條件同時成立才算「存在」。COUNTIF/COUNTIFS 的計數邏輯,可以參考COUNTIF 計數教學。

實務案例:訂單核查與成績單核對

• 訂單核查:用 COUNTIFS 同時比對訂單編號與金額,揪出「編號對、金額不對」的異常單。
• 成績單核對:用 AND 同時比對學號、姓名與分數,避免資料錯置到別人身上。

這些比對技巧只是 Excel 資料處理的起點。如果你想把 Excel 從「會套公式」練到「能做資料清理與儀表板分析」,系統性地補一套Excel 線上課程會比零散 Google 更有效率。

結語與延伸學習 ｜ 何時該轉用 PM 工具

回到最開頭那句:比對兩欄資料是否相同,判斷相同用 IF、找差異用條件格式化、抓重複用 COUNTIF、存在與否用 VLOOKUP/XLOOKUP、大小寫用 EXACT——先想清楚目的、再選方法,就不會每次都硬套一個公式。

快速回顧:

• 判斷是否相同:IF 函數逐列比對,回傳「相同／不相同」;記得處理空白與格式。
• 檢查是否存在:VLOOKUP 搭 ISNA/IFERROR,新版直接用 XLOOKUP 更省事。
• 找出差異、標色:條件格式化 + COUNTIF,或零公式的 Ctrl+\。
• 精確比對:EXACT 區分大小寫;LOWER/UPPER 反之忽略。
• 抓重複、多欄比對:移除重複值 + AND/COUNTIFS 複合條件。

什麼時候用 Excel 就夠、什麼時候該換工具? 判斷很簡單:單次、小量、比完就結束的核對,Excel 公式完全夠用;但如果同一份比對每週都要重跑、比對結果要跨團隊同步、或希望資料更新時自動標記差異,Excel 就會愈用愈吃力——版本容易亂、沒有自動化、多人同時改還會蓋掉彼此。

這種「持續性、協作型」的比對,常見的做法是改用 monday.com:把兩份資料放進同一個看板,設定一次比對與通知規則,之後每當有新增或重複,系統會自動標記並通知負責人,不用每週手動重跑。想知道它和 ClickUp、Notion 等工具怎麼選,可以看這份專案管理工具比較。

你是哪種情況?

• 一次性、小量核對 → 用本文的 Excel 公式就好。
• 每週固定要比對、跨部門同步 → 用 monday.com 設自動化(免費版 2 人永久使用、不需信用卡)。

再補兩個常接在比對後面的動作:找出重複的名稱或訂單後,通常要把對應金額加總,這篇SUMIF 相同名稱加總用 SUMIF 和樞紐分析兩條路交代清楚;而要從源頭減少手誤造成的比對錯誤,可以用下拉選單自動帶資料在輸入端就管住可選項。

下一步很簡單:先挑一個你手上正在核對的情境,對照本文開頭的決策表選好方法,10 分鐘內就能比完;如果你發現自己每週都在重跑同一份比對,那就是該把它搬進 monday.com 自動化的訊號。

Excel 比對兩欄資料常見問題 ｜ 10 題 FAQ

如何比對時忽略大小寫?

把兩欄都轉成同一種大小寫再比即可,例如 =IF(LOWER(A1)=LOWER(B1),"相同","不同"),UPPER 也可以。反過來若要嚴格區分大小寫,改用 EXACT。

比對時遇到空白儲存格怎麼辦?

在公式裡先判斷空白,避免誤判:=IF(OR(A1="",B1=""),"資料不完整",IF(A1=B1,"相同","不相同"))。這樣任一欄空白時會標成「資料不完整」,不會混進相同/不相同的結果。

舊版 Excel 沒有 XLOOKUP 怎麼辦?

用 VLOOKUP,或改用相容性更好、還能向左查找的 INDEX+MATCH 組合,例如 =INDEX(B:B,MATCH(A1,B:B,0))。完整做法見INDEX MATCH 雙向查找。

Google Sheets 可以用同樣方法比對嗎?

可以。IF、VLOOKUP、EXACT、COUNTIF 在 Google Sheets 幾乎完全通用,XLOOKUP 也已支援;另外還能用 UNIQUE、ARRAYFORMULA、QUERY 等函數做更彈性的比對。

在 Excel 中,如何比對兩欄資料以顯示重複的顏色?

選取要標色的欄位 →「開始」→「條件格式化」→「新增規則」→「使用公式」,輸入 =COUNTIF(B:B,A1)>0,設定填滿顏色即可。這樣 A 欄裡「B 欄也有」的重複值就會自動變色;想標差異(沒重複的),把公式改成 =0。

如何在 Excel 中比對兩欄資料以避免重複?

兩種做法:一是用條件格式化把重複值標色、人工檢視後刪除;二是用「資料 → 移除重複值」一次清掉。若要在輸入階段就防止重複,可用資料驗證搭配 COUNTIF 設限制。

如何使用 VLOOKUP 比對兩個欄位?

公式 =IF(ISNA(VLOOKUP(A1,B:B,1,FALSE)),"未找到","已找到"):它拿 A1 到 B 欄裡查,找得到就回「已找到」、找不到回「未找到」,用 ISNA 或 IFERROR 避免出現 #N/A。這就是最常用的「A 欄的值在不在 B 欄」判斷。

如何比對多欄資料?

用 AND 函數或多重條件公式,例如 =IF(AND(A1=B1,C1=D1),"完全相同","不同"),詳見本文「移除重複值與多欄比對」章節。

Excel 比對兩欄資料常用公式有哪些?

一張速查表對照各情境,可對照回本文各方法段落:

不同工作表的兩欄資料也能這樣比對嗎?

可以,把範圍改成含工作表名稱即可,例如 =IF(ISNA(VLOOKUP(A1,'工作表2'!B:B,1,FALSE)),"未找到","已找到")。跨工作表、跨檔案的兩份資料比對差異完整流程,可參考兩份資料比對重複做法。