2014年3月6日 星期四

Excel-列出多欄位中重覆的資料(陣列公式,COUNTIF,OFFSET,SMALL)

有網友問到:在 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

沒有留言:

張貼留言

檢視其他文章

好康東東