在 Excel 的工作表中有一個資料表格(如下圖左)，其中有名稱(甲、、乙、...、癸)和項目(A、B、C)，對應的內容填有 1 ~ 9 的代號。如何才能篩選出每個代號所列的名稱(如下圖右)。(註：其中每名稱中的代號不會重覆)
【公式設計與解析】
儲存格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 函數傳回錯誤值時，可以將結果顯示為空字串「""」。
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表