在 Excel 裡,如果要比較工作表裡的兩組清單內容,那些內容是重複的、那些內容是唯一的,該如何處理?

 

1. 使用條件式格式設定

利用標示不同色彩(儲存格格式),來顯示重複的值或是唯一的值。

Excel-比較兩組內容的重覆項目和唯一項目

藉由 Excel 裡的條件式格式設定,自動標示重複的項目和唯一的項目。選取「條件式格式設定/醒目提示儲存格規則」,再選取:重複的值。

Excel-比較兩組內容的重覆項目和唯一項目

此時,可以選取重複的值或是唯一的值。

Excel-比較兩組內容的重覆項目和唯一項目

Excel 會自動建立格式化的條件規則:

Excel-比較兩組內容的重覆項目和唯一項目

 

2. 使用 COUNTIF 函數

利用 COUNTIF 函數計算第1組裡的每一個儲存格內容在第2組儲存格裡出理的次數,若是0,表示無重複的項目,若不是0,表示有重複的項目。

Excel-比較兩組內容的重覆項目和唯一項目

第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 函數中使用陣列公式

Excel-比較兩組內容的重覆項目和唯一項目

第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 函數來排序。在此中文字的排序是筆劃為順序,可以設定遞增和遞減。

Excel-比較兩組內容的重覆項目和唯一項目

第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 函數來讓錯誤訊息顯示為空白(空字串)。

Excel-比較兩組內容的重覆項目和唯一項目

第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 函數,也可以將結果集合在一起。

 

【參考資料】

 SORT 函數參考微軟提供的說明網頁:SORT 函數
 COUNTIF 函數參考微軟提供的說明網頁:COUNTIF 函數
 VLOOKUP 函數參考微軟提供的說明網頁:VLOOKUP 函數

學不完.教不停.用不盡文章列表

arrow
arrow

    vincent 發表在 痞客邦 留言(0) 人氣()