2015年2月10日 星期二

Excel-分假日與平日計算平均(SUMPRODUCT)

網友想要在 Excel 的一個含有日期和金額的清單中,分別計算例假日和平日之金額的平均,該如何處理呢?(參考下圖)

這個問題藉助 SUMPRODUCT 函數可以輕易達成。

【準備工作】

選取儲存格A1:C26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、金額。

 

【公式設計】

(1) 求週六和週日的平均

儲存格F2:=SUMPRODUCT((WEEKDAY(日期,2)>5)*金額)/
SUMPRODUCT(--(WEEKDAY(日期,2)>5))

WEEKDAY(日期,2):找出日期陣列中星期一至星期日對應傳回 1 至 7。

WEEKDAY(日期,2)>5:判斷日期陣列在 WEEKDAY 傳回傳值是否大於5的 TRUE/FALSE 陣列。(星期六為 5、星期日為 7 )

SUMPRODUCT(--(WEEKDAY(日期,2)>5)):在 SUMPRODUCT 函數中使用「--」,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列,即可計算出符合條件的「個數」。

SUMPRODUCT((WEEKDAY(日期,2)>5)*金額):計算符合條件的金額「總和」。

把上式來除以上上式,即可求得符合條件的平均。(總和/個數)

 

(2) 求週一至週五的平均

儲存格F3:=SUMPRODUCT((WEEKDAY(日期,2)<=5)*金額)/
SUMPRODUCT(--(WEEKDAY(日期,2)<=5))

原理同(1),差別在於 WEEKDAY(日期,2) 的傳回值若為 1 至 5 則為平日。

沒有留言:

張貼留言

好康東東