學不完．教不停．用不盡

有網友問到，在 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-根據單條件和雙條件篩選資料(FILTER)

 Excel-列出指定星期幾的日期

 Excel-單一欄位篩選與跨欄位篩選

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-由資料清單中篩選一組

 Excel-利用FILTER函數模糊篩選

 Excel-從日期清單中區別平日和假日計算總和

 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用