2011年1月15日 星期六

Excel-計算分月分星期的小計摘要

在 Excel 的工作表中有全年1月到12月中每一天的資料(如下圖),如果想要分月且分星期幾的方式來計算收益欄位的和,該如何處理呢?

(1) 使用SUMPRODUCT函數

儲存格G2:=SUMPRODUCT(--(MONTH($A$2:$A$366)=ROW(1:1)),--(WEEKDAY($A$2:$A$366,1)=COLUMN(A:A)),$D$2:$D$366)

--(MONTH($A$2:$A$366)=ROW(1:1)的意義:

找出A欄中的月份(MONTH函數)為ROW(1:1)=1,即為1月者,ROW(1:1)向下複製 –> ROW(2:2)=2 –> ROW(3:3)=3 …,可找出合於1~12月的資料。而「--」乃為將True/Fasle陣列改為1/0陣列。

--(WEEKDAY($A$2:$A$366,1)=COLUMN(A:A)的意義:

WEEKDAY函數可找出每天為星期幾,參數1表示「為星期日:1、星期一:2、…」,COLUMN(A:A)=1,向右複製 –> COLUMN(B:B)=2 –> COLUMN(C:C)=3 …,可找出合於星期日到星期六的資料。

將儲存格G2複製到儲存格G2:M13。

(2) 使用陣列公式

儲存格G2:{=SUM(IF(MONTH($A$2:$A$366)=ROW(1:1),IF(WEEKDAY($A$2:$A$366,1)=COLUMN(A:A),$D$2:$D$366,0)))}

輸入完成後,要按 Ctrl+Shift+Enter 鍵。

將儲存格G2複製到儲存格G2:M13。

你可以試著練習如何計算平均的摘要?

沒有留言:

張貼留言

好康東東