2689 |
EXCEL工作日與休假統計 |
李宗盛:「錦子老師,請問要怎樣設定才能做到統計每個月的天數、上班天數及休假天數,在F1輸入年月後,自動帶出A欄的日期資訊(YYYY/MM/DD)?
另外,G2的天數如果遇到閏月,要怎麼判斷呢?」
錦子老師:「下面我們來一一說明各個儲存格的解答。
點取G2儲存格輸入公式:
=DAY(EOMONTH(F1,0))
如果F1儲存格輸入2020/02/01,變成2020年2月則G2儲存格會變成29天。
點取G3儲存格輸入公式:
=SUMPRODUCT((MONTH(A2:A367)=MONTH(F1))*(C2:C367="上班")*1)
按CTRL+SHIFT+ENTER鍵完成陣列公式的輸入。
【公式解說】
=SUMPRODUCT((MONTH(A2:A367)=MONTH(F1))*(C2:C367="上班")*1)
先判斷A2:A367儲存格中月份等於F1儲存格月份的資料(MONTH(A2:A367)=MONTH(F1)),再判斷C2:C367儲存格中內容為上班的記錄(C2:C367="上班"),然後乘以1從還輯值成數值後,統計符合二個條件的筆數總和SUMPRODUCT((MONTH(A2:A367)=MONTH(F1))*(C2:C367="上班")*1)。
點取G4儲存格輸入公式:
=SUMPRODUCT((MONTH(A2:A367)=MONTH(F1))*(C2:C367="休假")*1)
按CTRL+SHIFT+ENTER鍵完成陣列公式的輸入。
點取I2儲存格輸入公式:
=MONTH(A2)=MONTH($F$1)
點取A2儲存格,再點取「資料 > 進階」圖示。
在【進階篩選】對話方塊,點取「資料範圍」文字方塊,輸入「A1:D367」。
點取「準則範圍」文字方塊,輸入「I1:I2」。
【說明】
由於是要利用公式運算來篩選資料,故不能有標題,所以I1是空白儲存格,公式在I2。
點取「確定」鈕,結果如下圖所示。
其實進階篩選只要設定一次,Excel就會記得,所以非常方便,如果覺得每次都要設定太麻煩,也可將進階篩選動作錄成巨集並指定快速鍵。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
部落格相關範例
2021.11.03 |
|
2021.11.02 |
|
2021.11.01 |
|
2021.10.31 |
|
2021.10.30 |
|
2021.10.29 |
|
2021.10.28 |
|
2021.10.27 |
|
2021.10.26 |
|
2021.10.25 |
|
2021.10.24 |
|
2021.10.23 |
|
2021.10.22 |
|
2021.10.21 |
|
2021.10.20 |
|
2021.10.19 |
|
2021.10.18 |
|
2021.10.17 |
留言列表