根據上一篇文章：Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT)
要來修改統計方式。參考下圖，要在日期區間數列中小計分年分月的人數，但是要在期間中的每一月份分開統計，例如：A 成員在 5, 6, 7, 8, 9, 10 月都要被加計 1，B 成員在 10, 11, 12, 1, 2 月被加計 1，依此類推。
【準備工作】
選取欄B中所有資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：期間。
【輸入公式】
儲存格F2：=SUMPRODUCT((DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1))*
(DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1)))
(1) DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1)
MID(期間,1,4)：取期間陣列中的日期第 1 ~ 4 個字元，以儲存格B2為例，結果為 2014。
MID(期間,6,2),1)：取期間陣列中的日期第 6 ~ 7 個字元，以儲存格B2為例，結果為 05。
DATE(D2,E2,1)：以儲存格D2和儲存格E2代入日期，以儲存格F2為例，結果為 2014/1/1。
DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1)：由儲存格B2取得一個年／月，判斷是否「小於或等於」由儲存格D2(年)和儲存格E2(月)和1(日)組成的日期。
(2) DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1)
MID(期間,9,4)：取期間陣列中的日期第 9 ~ 12 個字元，以儲存格B2為例，結果為 2014。
MID(期間,14,2),1)：取期間陣列中的日期第 14 ~ 15 個字元，以儲存格B2為例，結果為 10。
DATE(D2,E2,1)：以儲存格D2和儲存格E2代入日期，以儲存格F2為例，結果為 2014/1/1。
MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1)：由儲存格B2取得一個年／月，判斷是否「大於或等於」由儲存格D2(年)和儲存格E2(月)和1(日)組成的日期。
若符合 (1) 和 (2) 條件者以 SUMPRODUCT 函數計算總和。公式中的「*」運算，相當於執行邏輯 AND 運算。所以在運算過程中，TRUE／FALSE 陣列會轉換為 1／0 陣列，SUMPRODUCT 函數會執行「乘積和」，因此計算最後 1 的個數，即為分年分月的個數。
留言列表