贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友問到:在 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 的個數,即為分年分月的個數。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 訪客
  • 不好意思,我驗證了一下,我要的是區間內都有達成條件的~假設以C跟D成員來看,都是五月有認證通過的成員,可是統計的數量只有,有其它方法可以解決嗎?
  • 參考以下文章中的做法:http://isvincent.pixnet.net/blog/post/43972078

    vincent 於 2014/09/24 20:41 回覆

  • 悄悄話

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼