有網友問到：在 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! 的儲存格改以空白顯示。
【延伸閱讀】
* Excel-資料篩選的好幫手-UNIQUE函數(2021版以上)
* Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
* Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)
留言列表