2010年11月27日 星期六

Excel-用公式取代樞紐分析(二)

在 Excel 中取得一個資料表(如下圖左)(資料範圍:儲存格A2:C141),以樞紐分析方式得到各年級的各種請假時數分析(如下圖右)。如果想要以月份來分析可能不太容易,如何以公式完成這個要求呢?

現在要以公式來建立分析表(如下圖左),依年級、月份和假別分別來分析。你可以使用SUMPRODUCT函數、SUMIFS函數和陣列公式等三種方式來做,以下以SUMPRODUCT為例:

儲存格M3:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$3),--($D$2:$D$141=O$2),$E$2:$E$141)

將儲存格M3複製到儲存格O3:Q8。

儲存格M9:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$9),--($D$2:$D$141=O$2),$E$2:$E$141)

將儲存格M9複製到儲存格O9:Q14。

儲存格M15:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$15),--($D$2:$D$141=O$2),$E$2:$E$141)

將儲存格M15複製到儲存格O15:Q20。

你也可以改良成上圖右的方式,利用資料驗證方式,將儲存格T1中設定為清單:一年級,二年級,三年級。

儲存格U3:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$T$1),--($D$2:$D$141=U$2),$E$2:$E$141)

將儲存格U3複製到儲存格U3:W8。

如此只要由儲存格T1中選取不同年級,則可以得到該年級的分析表。

沒有留言:

張貼留言

好康東東