有網友問到:在 Excel 中,如果要從多個欄位中列出資料重覆的清單(資料交集),該如何處理?(參考下圖)

【準備工作】

選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A資料。

選取儲存格A2:E26,定義名稱:全部資料。

【輸入公式】

儲存格G2:=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))-1,),"")

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

【公式說明】

參考下圖,對照相關說明。

(1) COUNTIF(全部資料,A資料)

在陣列公式中找出每一個A資料在全部資料中的個數。

(2) IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE)

在陣列公式中找出A資料在全部資料中個數為5的列號陣列,例如:

{Fasle,Fasle,Fasle,Fasle,5,Fasle,Fasle,Fasle,9,Fasle,Fasle,12,Fasle,…}

(3) SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))

在陣列公式中將(2)的結果由最小至最大列出,參考下圖中的欄G。

(4) OFFSET($A$1,SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))-1,)

使用 OFFSET 函數,在陣列公式中將(3)的結果對應出儲存格內容,參考下圖中的欄G。

(5) 利用 IFERROR 函數,將出現錯誤訊息 #NUM! 的儲存格改以空白顯示。

image

 

【延伸閱讀】

Excel-資料篩選的好幫手-UNIQUE函數(2021版以上)

Excel-2021版新增函數的使用

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)

Excel-列出不重覆的姓氏並依筆劃由小至大排列

 

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

arrow
arrow
    全站熱搜

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