有網友問到，在 Excel 中取得一個資料表(如下圖左)，如果想要在一個儲存格中選取某一個類別，即能篩選出所有該類別的資料(如下圖右)。
你可以試著使用「自動篩選」即可方便篩選出想要類別的資料，但是如果想要以公式形式呈現，方便改變類別後，即能馬上得到結果，該如何處理？
【準備工作】
本例需要使用陣列公式，為了方便說明，先定義各個資料名稱。
1. 選取儲存格A1:D21，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項。定義名稱：「項目、類別、編號、數量」
2. 選取儲存格A1:D21，由[公式/已定義名稱/名稱管理員]中定義名稱為：「資料」。
3. 選取儲存格F2，在［資料/資料工具/資料驗證］中設定儲存格內允許：清單，來源：「A,B,C,D,E」。
【輸入公式】
儲存格G2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}
這是陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。
儲存格H2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,4),"")}
這是陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。
複儲存格G2:H2，往下各列貼上即可。
【公式說明】
以儲存格G2為例。
儲存格G2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}
IF(類別=$F$2,項目,FALSE)
將「類別」中符合儲存格F2內容者對應「項目」，否則給予Fasle。以本例可得 { Fasle, Fasle, 2, 3, Fasle, Fasle, 7, … } 陣列。
SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))
取得前述陣列中的第1個、第2個、…最小值，本例為 2,3,7, … 。
INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3)
前一項所得的各個最小值，以 INDEX 函數在「資料」陣列中求得第3欄的資料。公式中的「+1」乃因為「資料」中的欄名也占掉一列。
IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")
利用 IFERROR 函數將 INDEX 查表後所產生的錯誤值以空白顯示。
儲存格H2的公式原理相同，只要將查表 INDEX 函式中設定為查詢第4欄即可。
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
留言列表