2011年10月29日 星期六

Excel-用公式篩選資料(陣列公式)

有網友問到,在 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欄即可。

2 則留言:

  1. yuji@chienti.com.tw2011年10月31日 上午8:55

    我試好久還是不行!後來發現我EXCEL內沒有IFERROR...還有其它方式嗎?

    回覆刪除
  2. yuji@chienti.com.tw2011年10月31日 上午9:04

    我先把IFERROR刪除了!
    另外我想問!我原本資料有設定下拉方式,回傳篩選的數值可以把下拉方式的設定一並回傳嗎?

    回覆刪除

檢視其他文章

好康東東