如何在 Excel 中比較兩個欄
若要逐列比較 Excel 中的兩個欄,請在第三欄輸入 =A2=B2 並向下填滿:TRUE 代表兩個儲存格相符。如果兩份清單的順序不同,請使用 COUNTIF 或 XLOOKUP,檢查每個值是否出現在另一份清單的任何位置。本指南說明這兩種方法,以及如何醒目提示差異。
1. 逐列比較
當每一列在兩欄中都應該是相同的值時,比較公式就足夠了。它會傳回 TRUE 或 FALSE。請注意,Excel 在這裡不區分英文大小寫,因此「Ana」與「ANA」會被視為相等。
範例:兩個儲存格是否相符?
| A | B | C | |
|---|---|---|---|
| 1 | 輸入值 | 預期值 | 是否相符? |
| 2 | Ana | ANA | TRUE |
此比較不區分大小寫,因此 Ana 與 ANA 相等。
2. 精確比對英文大小寫
當大小寫很重要時,例如產品代碼或密碼,請用 EXACT 包住兩個儲存格。只有在文字完全相同、每個字元都一樣時,它才會傳回 TRUE。
範例:精確的、區分大小寫的比較
| A | B | C | |
|---|---|---|---|
| 1 | 輸入值 | 預期值 | 是否完全相符? |
| 2 | Ana | ANA | FALSE |
Ana 與 ANA 的大小寫不同,因此 EXACT 傳回 FALSE。
3. 找出同時存在於兩份清單中的值
當清單的順序不同時,逐列比較就會失敗。相反地,請用 COUNTIF 計算第一份清單中每個值在第二份清單中出現的次數。計數大於零,代表該值同時存在於兩份清單中。向下填滿公式時,請用美元符號鎖定第二個範圍,讓它保持不動。
範例:同時在兩份清單中,還是只在第一份?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 清單 A | 清單 B | 檢查 | |
| 2 | Ben | Dee | 兩者皆有 | |
| 3 | Ana | Ben | ||
| 4 | Cam | Eve | ||
| 5 | Dee |
Ben 在清單 B 中出現一次,因此計數為 1,該列顯示「兩者皆有」。
4. 從另一份清單查找值
若要從第二份清單帶入某個資料,例如價格或狀態,請使用 XLOOKUP(舊版本請用 VLOOKUP)。它也能告訴你哪些值缺少,因為缺少的值會傳回 #N/A。以 IFERROR 包住公式,即可改為顯示「缺少」等文字。
5. 醒目提示差異
選取兩個欄,然後選擇「常用」>「條件式格式設定」>「醒目提示儲存格規則」>「重複的值」,再把「重複」改為「唯一」。只出現在單一位置的每個值都會被上色。若要逐列找出差異,請選取這兩欄,按 Ctrl+G,按一下「特殊」,然後選擇「列差異」。Excel 會選取每一列中與第一欄不同的儲存格。
常見錯誤
隱藏的空格。 「Ana」與結尾有空格的「Ana 」看起來一樣,但不會相符。
修正: 比較前,先用 TRIM 清理兩欄。
以文字儲存的數字。 數字 100 與文字「100」不相等。
修正: 將文字轉換為數字,或用 ISNUMBER 檢查哪些儲存格是文字。
未鎖定的範圍。 =COUNTIF(B2:B4,A2) 向下填滿時,範圍會跟著移動,開始漏掉列。
修正: 向下填滿前,將它鎖定為 $B$2:$B$4。
常見問題
如何比較兩個欄並傳回差異?
在 Microsoft 365 中,FILTER 可以傳回一份清單中缺少於另一份清單的值。舊版本中,請在輔助欄使用 COUNTIF,然後再篩選它。
我可以用這種方式比較兩個工作表嗎?
可以。在公式中參照另一個工作表,例如 Sheet2!B2:B100,同樣的方法就能使用。
為什麼外觀相同的值,比較結果卻是 FALSE?
通常是隱藏的空格,或以文字儲存的數字。請使用 TRIM 並檢查儲存格格式。
相關函數
- COUNTIFCOUNTIF 會計算範圍中符合一個條件的儲存格:文字、數字、像 ">100" 這樣的比較,或萬用字元。包含語法、範例與常見錯誤。.
- XLOOKUPXLOOKUP 會在一欄或一列中搜尋,並從另一欄傳回對應的值,可以朝任何方向查詢,並內建「找不到」的訊息。包含語法、範例與練習。.
- VLOOKUPVLOOKUP 會在表格的第一欄中找到一個值,並從同一列的另一欄傳回值。包含語法、範例、常見錯誤與練習。.
- IFIF 會檢查一個條件:為真時傳回一個值,為假時傳回另一個值。包含語法、文字與數字的範例、巢狀 IF,以及常見錯誤。.
- IFERRORIFERROR 會在公式產生錯誤(例如 #N/A 或 #DIV/0!)時傳回你指定的值,否則傳回正常的結果。包含語法、範例,以及何時不該使用它。.
- TRIMTRIM 會移除文字中多餘的空格:開頭、結尾,以及字詞之間的重複空格。包含語法、範例,以及為什麼它能修正傳回 #N/A 的查閱。.
更多關於 比較欄
本指南提供逐列比對、COUNTIF 檢查與醒目標示的方法。本文補充如何比較兩個工作表上的清單、如何取得只存在於其中一個清單的值,以及如何讓比較能容忍雜亂的資料。
位於兩個不同工作表的清單
當清單位於不同工作表時,方法相同。請在範圍前加上工作表名稱,接著是驚嘆號。如果工作表名稱含有空格,請用單引號把它括起來。
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"In both","Only here")取得只存在於其中一個清單的值
使用輔助欄標記每一列,並篩選「只在 A 中」,即可列出缺少的值。在 Microsoft 365 中,一個公式就能直接傳回這些值,而且資料變更時結果會自動更新。
=FILTER(A2:A5,COUNTIF(B2:B4,A2:A5)=0)交換兩個範圍即可得到相反的清單。結果會溢出到下方的儲存格,因此請保持它們為空白。
讓比較能容忍雜亂的資料
真實的清單常有多餘的空格、大小寫不一致,以及以文字儲存的數字。請先在輔助欄中清理每一欄,再比較清理後的欄位。TRIM 可以移除多餘的空格,活頁簿中的其他清單也能用同樣的方式比較。在輔助欄中完成一次清理後,讓每個比較都指向清理後的欄位,這樣只要在一處修正,就會套用到所有地方。如果清單很長,也建議先排序,因為這樣比較容易用眼睛看出差異。
若要用查閱把一個清單的值帶到另一個清單,請參閱[XLOOKUP 課程](/learn/xlookup)與[VLOOKUP 與 XLOOKUP 比較](/guides/vlookup-vs-xlookup)。