2014年2月24日 星期一

Excel-將不同類別資料篩選至另一個工作表(陣列公式)

有網友問到:如下圖左的資料,希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表),參考下圖右,該如何處理?

【準備工作】

選取所有的資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、區別、金額。

image image

以篩選「北區」為例來說明:

【輸入公式】

儲存格A2:{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1))-1,1),"")}

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

IF(區別="北區",ROW(區別),FALSE):找出「區別」中符合「北區」的 ROW() 陣列(例如 ROW(3:3)=3、ROW(4:4)=4),不符合者以 False 表示。

SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1)):在上述的陣列 ROW() 中,取出最小值 ROW(1:1)=1,如果往下複製公式時,可以取出第二小值 ROW(2:2)=2,依此類推得到符合北區的儲存格是第幾列的數值陣列。

本例可得陣列:{false,3,4,false,false7,false,…..}。

INDIRECT(A$1):利用 INDIRECT 函數將儲存格A1內容轉換為真實的位址。(儲存格內容已事先定義為名稱了)

接著使用 INDEX 函數,以查表方式找出上述符合北區的儲存格是第幾列的數值所對應的儲存格內容。例如:INDEX(北區,3-1,1)查到第一個北區者的姓名。

複製儲存格A2,貼至儲存格A2:C2。複製儲存格A2:C2,往下至列貼上。

由於往下複製公式時,可能會傳回錯誤訊息(找不到符合的內容),所以使用 IFERROR 函數,讓傳回錯誤訊息的儲存格顯示空白。

 

註:如果想要篩選「西區、南區、東區」時,只要將上述公式中的「北區」置換掉即可。

沒有留言:

張貼留言

檢視其他文章

好康東東