2015年10月16日 星期五

Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)

有網友問到:如下圖左半部的資料表,其中是每個學生在不同日期所選修的課,如何根據這個資料表摘要出每門課的學生(參考下圖右半部)?
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)

以下數個部分慢慢來分解。
1. 使用 SUMPRDOCUT+LARGE 函數
儲存格E2:
=SUMPRODUCT(LARGE(($B$2:$C$23=E$1)*ROW($B$2:$C$23),ROW(1:1)))
公式得到的結果是姓名的「列號」,這個做法會以原順序『相反』的方法呈現。
如果你要用陣列公式來處理,則公式:
儲存格E2:{=LARGE(($B$2:$C$23=I$1)*ROW($B$2:$C$23),ROW(1:1))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:G23。
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)
利用上述公式,依據『列號』查到對應的儲存格內容。
儲存格E2:=IFERROR(OFFSET($A$1,SUMPRODUCT(LARGE(($B$2:$C$23=E$1)*
ROW($B$2:$C$23),ROW(1:1)))-1,,,),"")
儲存格E2:{=IFERROR(OFFSET($A$1,LARGE(($B$2:$C$23=I$1)*ROW
($B$2:$C$23),ROW(1:1))-1,,,),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:G23。
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)

2. 利用陣列公式和 SMALL 函數
儲存格E2:{=SMALL(IF($B$2:$C$23=E$1,ROW($B$2:$C$23),999),ROW(1:1))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
其中參數 999,只是任意的一個很大的值。
公式得到的結果是姓名的「列號」,這個做法會以原順序『相同』的方法呈現。
複製儲存格E2,貼至儲存格E2:G23。
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)
利用上述公式,依據『列號』查到對應的儲存格內容。
儲存格E2:{=OFFSET($A$1,SMALL(IF($B$2:$C$23=E$1,
ROW($B$2:$C$23),999),ROW(1:1))-1,,,)}
複製儲存格E2,貼至儲存格E2:G23。
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)

3. 在每個姓名中將各課程標示那一天上課
儲存格F2:=IFERROR(OFFSET($B$1,,MATCH(F$1,$B2:$C2,0)-1,,),"")
複製儲存格F2,貼至儲存格F2:H23。
Excel-表格資料重組(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,陣列公式)

沒有留言:

張貼留言

檢視其他文章

好康東東