2014年9月24日 星期三

Excel-在日期區間數列中小計分年分月的人數2(SUMPRODUCT)

根據上一篇文章: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 的個數,即為分年分月的個數。

沒有留言:

張貼留言

好康東東