在 Excel 裡有一個日期清單，其中包含金額的數列，如何計算各個星期幾的平均和上班日及假日的平均？
以下提供二種不同的公式來處理。
選取儲存格A3:C31，按 Ctrl+Shift+F3 鍵，勾選「列端列」，定義名稱：日期、星期、金額。
【SUMPRODUCT函數】
1. 各星期幾的平均
儲存格F4：=SUMPRODUCT((WEEKDAY(日期,2)=ROW(A1))*金額)/SUMPRODUCT((WEEKDAY(日期,2)=ROW(A1))*1)
複製儲存格F4，貼至儲存格F4:F10。
(1) SUMPRODUCT((WEEKDAY(日期,2)=ROW(A1))*金額)
利用 WEEKDAY 函數設定參數2，讓傳回值1~7對應星期一~星期日。
當公式向下複製時，ROW(A1)=1→ROW(A2)=2→ROW(A3)=3→…。
本式可計算所有日期是星期一者的金額總和。
(2) SUMPRODUCT((WEEKDAY(日期,2)=ROW(A1))*1)
本式可計算所有日期是星期一者的「個數」。
因為條件 WEEKDAY(日期,2)=ROW(A1) 會傳回 TRUE／FALSE 陣列，經過「*1」運算後，可以轉換為 1／0 的陣列。
(3) 二者相除即為平均
2. 上班日的平均
儲存格F14：=SUMPRODUCT((WEEKDAY(日期,2)<6)*金額)/SUMPRODUCT((WEEKDAY(日期,2)<6)*1)
(1) SUMPRODUCT((WEEKDAY(日期,2)<6)*金額)
利用 WEEKDAY 函數設定參數2，傳回值1~5對應星期一~星期五為上班日。
本式可計算所有日期是星期一~星期五者的金額總和。
(2) SUMPRODUCT((WEEKDAY(日期,2)<6)*1)
條件判斷會傳回 TRUE／FALSE 陣列，經過「*1」運算後，可以轉換為 1／0 的陣列。
本式可計算所有日期是星期一~星期五者的個數。
(3) 二者相除即為平均
3. 假日的平均
儲存格F15：=SUMPRODUCT((WEEKDAY(日期,2)>5)*金額)/SUMPRODUCT((WEEKDAY(日期,2)>5)*1)
(1) SUMPRODUCT((WEEKDAY(日期,2)>5)*金額)
利用 WEEKDAY 函數設定參數2，傳回值6~7對應星期六~星期日為假日。
本式可計算所有日期是星期六~星期日者的總和。
(2) SUMPRODUCT((WEEKDAY(日期,2)>5)*1)
條件判斷會傳回 TRUE／FALSE 陣列，經過「*1」運算後，可以轉換為 1／0 的陣列。
本式可計算所有日期是星期六~星期日者的個數。
(3) 二者相除即為平均
【陣列公式】
1. 各星期幾的平均
儲存格F4：{=AVERAGE(IF(WEEKDAY(日期,2)=ROW(A1),金額,""))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格F4，貼至儲存格F4:F10。
判斷條件：WEEKDAY(日期,2)=ROW(A1)，符合者傳回對應的金額，再透過 AVERAGE 函數加以平均。
2. 上班日的平均
儲存格F14：{=AVERAGE(IF(WEEKDAY(日期,2)<6,金額,""))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
判斷條件：WEEKDAY(日期,2)<6，符合者傳回對應的金額，再透過 AVERAGE 函數加以平均。
3. 假日的平均
儲存格F15：{=AVERAGE(IF(WEEKDAY(日期,2)>5,金額,""))}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
判斷條件：WEEKDAY(日期,2)>5，符合者傳回對應的金額，再透過 AVERAGE 函數加以平均。
【參考資料】
留言列表