2014年4月9日 星期三

Excel-多條件查表篩選資料(INDEX,陣列公式)

回答網友提問:在 Excel 中有一個如下圖左的資料表,如何篩選出符合二個條件的資料(如下圖右)?

【準備工作】

選取儲存格A1:E26,按 Ctrl+Shift+F3 鍵,定義名稱:項目、類別、編號、數量、狀態。

選取儲存格A2:E26,進入名稱管理員中,定義名稱:資料。

【題目要求】

要篩選資料的條件置於儲存格G2和儲存格G4,必須符合二個條件者,才能被篩選。

 

【輸入公式】

儲存格H2:{=IFERROR(INDEX(資料,SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)),3),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(類別=$G$2)*(狀態=$G$4):當你有二個條件時,可以使用運算子「*」來執行邏輯 AND 的動作。

IF((類別=$G$2)*(狀態=$G$4),項目,FALSE):若二個條件都成立時(AND結果為TRUE),則傳回項目的編號,例如第 7 列符合以上二個條件,則傳回項目 6。否則,傳回 FALSE。此結果形成了一些編號和 FALSE 的陣列。

SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)):傳回上述項目中最小的編號,此即為二個條件都符合者的項目編號。其中 ROW(1:1)=1,當公式往下複製時,會產生 ROW(2:2)=2、ROW(3:3)=3、…,可以分別得第2小的值和第3的值…。

透過 INDEX 函數在「資料」的陣列中找到對應的值。

IFFERROR 函數乃在當公式查不到資料時會傳回錯誤訊息,將這個錯誤訊息改以空白顯示。

儲存格I2:{=IFERROR(INDEX(資料,SMALL(IF((類別=$G$2)*(狀態=$G$4),項目,FALSE),ROW(1:1)),4),"")}

本公式的原理同上。

複製儲存格H2:I2,往下各列貼上。

 

【延伸思考】

如果要篩選的條件變為三個、四個時,該如何處理呢?

只要將條件改為「(條件一)*(條件二)*(條件三)*(條件四)」即可。

沒有留言:

張貼留言

檢視其他文章

好康東東