學不完．教不停．用不盡

網友想要在 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 則為平日。