在 Excel 裡,如果要比較工作表裡的兩組清單內容,那些內容是重複的、那些內容是唯一的,該如何處理?
1. 使用條件式格式設定
利用標示不同色彩(儲存格格式),來顯示重複的值或是唯一的值。
藉由 Excel 裡的條件式格式設定,自動標示重複的項目和唯一的項目。選取「條件式格式設定/醒目提示儲存格規則」,再選取:重複的值。
此時,可以選取重複的值或是唯一的值。
Excel 會自動建立格式化的條件規則:
2. 使用 COUNTIF 函數
利用 COUNTIF 函數計算第1組裡的每一個儲存格內容在第2組儲存格裡出理的次數,若是0,表示無重複的項目,若不是0,表示有重複的項目。
第1組/重複的值,儲存格D4:=IF(COUNTIF($B$4:$B$18,A4),A4,"")
第2組/重複的值,儲存格E4:=IF(COUNTIF($A$4:$A$18,B4),B4,"")
複製儲存格D4:E4,貼至儲存格D4:E18。
第1組/唯一的值,儲存格G4:=IF(COUNTIF($B$4:$B$18,A4),"",A4)
第2組/唯一的值,儲存格H4:=IF(COUNTIF($A$4:$A$18,B4),"",B4)
複製儲存格G4:H4,貼至儲存格G4:H18。
3. 在 COUNTIF 函數中使用陣列公式
第1組/重複的值,儲存格D4:=IF(COUNTIF(B4:B18,A4:A18),A4:A18,"")
第2組/重複的值,儲存格E4:=IF(COUNTIF(A4:A18,B4:B18),B4:B18,"")
第1組/唯一的值,儲存格G4:=IF(COUNTIF(B4:B18,A4:A18),"",A4:A18)
第2組/唯一的值,儲存格H4:=IF(COUNTIF(A4:A18,B4:B18),"",B4:B18)
由於公式的寫法是陣列公式,所以不需複製儲存格D4、儲存格E4、儲存格G4、儲存格H4。
4. 在 COUNTIF 函數中使用陣列公式且使用 SORT 函數排序
將前面的內容,再以 SORT 函數來排序。在此中文字的排序是筆劃為順序,可以設定遞增和遞減。
第1組/重複的值,儲存格D4:=SORT(IF(COUNTIF(B4:B18,A4:A18),A4:A18,""),,-1)
第2組/重複的值,儲存格E4:=SORT(IF(COUNTIF(A4:A18,B4:B18),B4:B18,""),,-1)
第1組/唯一的值,儲存格G4:=SORT(IF(COUNTIF(B4:B18,A4:A18),"",A4:A18),,-1)
第2組/唯一的值,儲存格H4:=SORT(IF(COUNTIF(A4:A18,B4:B18),"",B4:B18),,-1)
5. 使用 VLOOKUP 函數和陣列公式
改用 VLOOKUP 函數以查詢第1組的每一個儲存格在第2組中的查詢結果,若是「沒有」傳回錯誤訊息,表示有重複,如果「有」傳回錯誤訊息,表示沒重複。
在此會輔以 IFERROR 函數來讓錯誤訊息顯示為空白(空字串)。
第1組/重複的值,儲存格D4:=IFERROR(VLOOKUP(A4:A18,B4:B18,1,FALSE),"")
第2組/重複的值,儲存格E4:=IFERROR(VLOOKUP(B4:B18,A4:A18,1,FALSE),"")
第1組/唯一的值,儲存格G4:=IF(ISERROR(VLOOKUP(A4:A18,$B$4:$B$18,1,FALSE)),A4:A18,"")
第2組/唯一的值,儲存格H4:=IF(ISERROR(VLOOKUP(B4:B18,A4:A18,1,FALSE)),B4:B18,"")
如果再使用 SORT 函數,也可以將結果集合在一起。
【參考資料】
![](https://pic.pimg.tw/isvincent/1654417637-1933265109-g.png)
![](https://pic.pimg.tw/isvincent/1654417637-1933265109-g.png)
![](https://pic.pimg.tw/isvincent/1654417637-1933265109-g.png)