有網友問到：如下圖左的資料，希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表)，參考下圖右，該如何處理？
【準備工作】
選取所有的資料範圍，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、區別、金額。
 |
 |
以篩選「北區」為例來說明：
【輸入公式】
儲存格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 函數，讓傳回錯誤訊息的儲存格顯示空白。
註：如果想要篩選「西區、南區、東區」時，只要將上述公式中的「北區」置換掉即可。
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
留言列表