2016年5月6日 星期五

Excel-表格資料篩選(OFFSET,SMALL,ROW,COLUMN)

在 Excel 的工作表中有一個資料表格(如下圖左),其中有名稱(甲、、乙、...、癸)和項目(A、B、C),對應的內容填有 1 ~ 9 的代號。如何才能篩選出每個代號所列的名稱(如下圖右)。(註:其中每名稱中的代號不會重覆)
Excel-表格資料篩選(OFFSET,SMALL,ROW,COLUMN)

【公式設計與解析】
儲存格G2:{=IFERROR(OFFSET($A$2,SMALL(IF($B$2:$D$11=$F2,ROW
($B$2:$D$11),FALSE),COLUMN(A:A))-2,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動加上「{}」。
複製儲存格G2,貼至儲存格G2:K9。

(1) IF($B$2:$D$11=$F2,ROW($B$2:$D$11),FALSE)
於陣列公式中判斷儲存格B2:D11中和儲存格F2內容是否相符,若成立,則傳回儲存格列號;若不成立,則傳回 FALSE
(2) SMALL(第(1)式,COLUMN(A:A))
當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:BA)=2→ ... →COLUMN(E:E)=5。再透過 SMALL 函數將第(1)傳回的陣列依序取出最第1小的值、第2小的值、...。
(3) OFFSET($A$2,第(2)式-2,0)
將第(2)式的傳回值代入 OFFSET 函數,求得相對的儲存格位置中的內容。其中『-2』是因為資料由第2列開始。
(4) IFERROR(第(3)式,"")
最後使用 IFFERROR 函數,對於第(2)式若因 SMALL 函數傳回錯誤值時,可以將結果顯示為空字串「""」。

沒有留言:

張貼留言

好康東東