close

2689

EXCEL工作日與休假統計

李宗盛:「錦子老師,請問要怎樣設定才能做到統計每個月的天數、上班天數及休假天數,在F1輸入年月後,自動帶出A欄的日期資訊(YYYY/MM/DD)?

另外,G2的天數如果遇到閏月,要怎麼判斷呢?

image

錦子老師:「下面我們來一一說明各個儲存格的解答。

點取G2儲存格輸入公式:

=DAY(EOMONTH(F1,0))

image

如果F1儲存格輸入2020/02/01,變成20202月則G2儲存格會變成29天。

image

點取G3儲存格輸入公式:

=SUMPRODUCT((MONTH(A2:A367)=MONTH(F1))*(C2:C367="上班")*1)

CTRL+SHIFT+ENTER鍵完成陣列公式的輸入。

image

公式解說

=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鍵完成陣列公式的輸入。

image

點取I2儲存格輸入公式:

=MONTH(A2)=MONTH($F$1)

點取A2儲存格,再點取「資料 > 進階」圖示。

image

在【進階篩選】對話方塊,點取「資料範圍」文字方塊,輸入「A1:D367」。

點取「準則範圍」文字方塊,輸入「I1:I2」。

image

【說明】

由於是要利用公式運算來篩選資料,故不能有標題,所以I1是空白儲存格,公式在I2

點取「確定」鈕,結果如下圖所示。

image

其實進階篩選只要設定一次,Excel就會記得,所以非常方便,如果覺得每次都要設定太麻煩,也可將進階篩選動作錄成巨集並指定快速鍵。

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

image

部落格相關範例

2021.11.03

2650讓自動校正輸入統一的文字

2021.11.02

2649Excel中自訂梯形面積函數

2021.11.01

2648把資料徹底隱藏起來

2021.10.31

2644關於歐元的轉換

2021.10.30

2643Excel中攝影功能的妙用

2021.10.29

2642計算儲存格中的總字數

2021.10.28

2640建立文字型長條圖

2021.10.27

2641用特殊符號補齊字元數

2021.10.26

2630使用Excel數組公式自製日曆

2021.10.25

2639Excel找出計算結果的位置

2021.10.24

2638如果要將一個數值除指定的數,並能自動分配在不同格子上要怎麼做...

2021.10.23

2632欄位字串替換

2021.10.22

2629Excel查表對照值

2021.10.21

2628VBA 將字串改為函數

2021.10.20

2627相對位址

2021.10.19

2626絕對與相對的儲存格位址

2021.10.18

2624Excel秘密功能-顯示儲存格公式

2021.10.17

2623Excel秘密功能-固定小數位數

 

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()