2015年12月8日 星期二

Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

被網友問過最多次的 Excel 問題,可以說是『從完整清單中分列不同項目的清單』這樣的題目,像是下圖中「工作」欄位中有四種項目類別:清潔、收銀、樓面、廚房,想要由這個總表裡篩選各類項目的清單。
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)
你可以使用排序工具,對「工作」欄位排序,即可將相同項目集合在一起,再分四次複製。
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)
也可以手動使用篩選工具,分四次篩選各個項目,再複製出來使用。
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)
如果你使用『樞紐分析』工具,可以將姓名置於『列』,將工作置於『篩選』,也是可以執行篩選動作。
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)

【使用公式】
除了上述手動方式,如果你想使用公式來處理,可以參考以下三種不同的方式。
首先,選取B欄中所有含有資料的儲存格,定義名稱:工作。

(1) 使用 SUMPRODUCT+LARGE
使用這個公式會將原始資料後出現者,重新排列時會先列出。(參考下圖)
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)
儲存格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+陣列公式
使用這個公式會將原始資料先出現者,重新排列時會先列出。(參考下圖)
Excel-從完整清單中分列不同項目的清單(SUMPRODUCT,OFFSET)
儲存格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,往下各列貼上。

沒有留言:

張貼留言

檢視其他文章

好康東東