2014年9月23日 星期二

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

有網友問到:在 Excel 的工作表中有一個認證期間的日期數列,而這個日期數列是由兩個日期組成的文字(參考下圖右),現在希望能在這個數列中分年分月的計數人數,該如何處理?(參考下圖左)(認證完成日以期間最後的年月份為準)

 

【準備工作】

選取欄B中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:期間。

 

【輸入公式】

(1)

儲存格F2:=SUMPRODUCT((VALUE(LEFT(期間,4))=D2)*(VALUE(RIGHT(期間,2))=E2))

LEFT(期間,4):取出「期間」陣列的每個期間文字最左邊四個字元。(此為年份文字)

VALUE(LEFT(期間,4)):將上式中取得的四個字元文字,轉換為數字。(此為年份數字)

VALUE(LEFT(期間,4))=D2:判斷上式中的數字是否等於儲存格D2的內容,傳回 TRUE/FALSE 的陣列。

(2)

RIGHT(期間,2):取出「期間」陣列的每個期間文字最右邊二個字元。(此為月份文字)

VALUE(RIGHT(期間,2)):將上式中取得的二個字元文字,轉換為數字。(此為月份數字)

VALUE(RIGHT(期間,2))=E2:判斷上式中的數字是否等於儲存格E2的內容,傳回 TRUE/FALSE 的陣列。

(3)

最後透過 SUMPRODUCT 函數將 (1) 和 (2) 相乘,公式中的「*」運算,相當於執行邏輯 AND 運算。所以在運算過程中,TRUE/FALSE 陣列會轉換為 1/0 陣列,SUMPRODUCT 函數會執行「乘積和」,因此計算最後 1 的個數,即為分年分月的個數。

沒有留言:

張貼留言

檢視其他文章

好康東東