被網友問過最多次的 Excel 問題，可以說是『從完整清單中分列不同項目的清單』這樣的題目，像是下圖中「工作」欄位中有四種項目類別：清潔、收銀、樓面、廚房，想要由這個總表裡篩選各類項目的清單。
你可以使用排序工具，對「工作」欄位排序，即可將相同項目集合在一起，再分四次複製。
也可以手動使用篩選工具，分四次篩選各個項目，再複製出來使用。
如果你使用『樞紐分析』工具，可以將姓名置於『列』，將工作置於『篩選』，也是可以執行篩選動作。
【使用公式】
除了上述手動方式，如果你想使用公式來處理，可以參考以下三種不同的方式。
首先，選取B欄中所有含有資料的儲存格，定義名稱：工作。
(1) 使用 SUMPRODUCT+LARGE
使用這個公式會將原始資料後出現者，重新排列時會先列出。(參考下圖)
儲存格D2：=IFERROR(OFFSET($A$1,SUMPRODUCT(LARGE((工作=D$1)*
ROW(工作),ROW(1:1)))-1,),"")
SUMPRODUCT(LARGE((工作=D$1)*ROW(工作),ROW(1:1)))：找出『工作』陣列中所有和儲存格D1相同者的列號，再由 LARGE 函數找出最大第 1 個值。
ROW(1:1)=1，向下複製公式時，會產生 ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=4→…。因此可以由 LARGE 函數找出最大第 2、3、4、… 個值。
透過 OFFSET 函數代入上式的列號，找到相對於儲存格A1的位置。
而 IFERROR 函數用以將 OFFSET 函數查詢得到錯誤訊息者，修正為空白。
複製儲存格D2，貼至儲存格D2:G2，複製儲存格D2:G2，往下各列貼上。
(2) 使用 SUMPRODUCT+SMALL+陣列公式
使用這個公式會將原始資料先出現者，重新排列時會先列出。(參考下圖)
儲存格D2：{=IFERROR(OFFSET($A$1,SMALL(IF(工作=D$1,ROW(工作),
9999),ROW(1:1))-1,),"")}
輸入完成，要按 Ctrl+Shift+Enter 鍵，Excel 自動產生「｛｝」。
複製儲存格D2，貼至儲存格D2:G2，複製儲存格D2:G2，往下各列貼上。
(3) 使用 SUMPRODUCT+SMALL+不使用陣列公式
使用這個公式會將原始資料先出現者，重新排列時會先列出。(參考上圖)
儲存格D2：=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((工作=D$1)*
ROW(工作),ROW(1:1)+COUNTA(工作)-SUM(1*(工作=D$1))))-1,),"")
這個公式比(2)複雜，但是不使用陣列公式。
複製儲存格D2，貼至儲存格D2:G2，複製儲存格D2:G2，往下各列貼上。
留言列表