網友根據這篇:Excel-COUNTIF+SUMIF練習

延伸問到:如果在清單中指定某段日期區間,並且要區隔平日和六日的營業額分別為多少,該如何處理?

參考下圖,本例要改用 SUMPRODUCT 函數來處理。

Excel-在清單中指定日期區間計算假日和非假日的小計(SUMPRODUCT)

【公式設計與解析】

選取B2:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、營業額。

1. 平日營業額

=SUMPRODUCT((日期>=DATE(2019,2,10))*(日期<=DATE(2019,4,15))*
(WEEKDAY(日期,2)<=5)*營業額)

SUMPRODUCT 函數中使用三個條件:

(1) 日期>=DATE(2019,2,10),判斷日期是否大於或等於2019/2/10。

(2) 日期<=DATE(2019,4,15),判斷日期是否小於或等於2019/4/15。

(3) WEEKDAY(日期,2)<=5

WEEKDAY 函數中使用參數「2」,若是星期一到星期五會傳回 1 到 5;若是星期六會傳回 6;若是星期日會傳回 7。

因此判斷 WEEKDAY(日期,2)<=5,若是成立表示日期是「平日」。

Excel-在清單中指定日期區間計算假日和非假日的小計(SUMPRODUCT)

三個條件之間使用「*」,相當於執行邏輯 AND 運算。傳回值 TRUE/FALSE 在運算過程中會轉換為 1/0。再將結果乘上「營業額」,透過 SUMPRODUCT 函數予以加總。

Excel-在清單中指定日期區間計算假日和非假日的小計(SUMPRODUCT)

 

2. 六日營業額

=SUMPRODUCT((日期>=DATE(2019,2,10))*(日期<=DATE(2019,4,15))*
(WEEKDAY(日期,2)>5)*營業額)

原理同「1.」,唯一不同的地方是 WEEKDAY(日期,2)>5,用以判斷日期是否為星期六、星期日。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()