2016年2月19日 星期五

Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

網友問到 Excel 的問題:如何在一個日期區間中計算工作天數、放假天數?
參考下圖,因為每個人遇到的狀況不同,當要計算工作天數時,你可能要考量到星期六、星期日和放假日,也要考量到補班日,該如何處理?
Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

【公式設計與解析】
1. 不含六日的工作天數
儲存格C2:=NETWORKDAYS(A2,B2)
利用 NETWORKDAYS 函數,只要給予起始和終止的日期,即可計算日期區間中扣除星期六日的天數。
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
儲存格C2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 p="">
"A" & N(A2) & ":A" & N(B2):將二個日期的數值組成一個儲存格範圍,例如:A42370:A42552。其中 N 函數可以將一個日期傳回其代表的數值。
INDIRECT("A" & N(A2) & ":A" & N(B2):利用 INDIRECT 函數將上式轉換為真實的儲存格參照位址。
ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))):將上式置入 ROW 函數,轉換為列號範圍,例如:A42370:A42552→ROW(A42370:A42552),在 SUMPRODUCT 函數中可以表示為 42370, 42371, 42372, ..., 42551, 42552 組成的陣列。
WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 nbsp="" strong="">WEEKDAY
函數中利用參數『2』,得到傳回值小於 6 者(表示星期一至星期五)的 TRUE/FALSE 陣列。
Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)
1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 nbsp="" strong="">TRUE/FALSE
 陣列轉換為 1/0 陣列。
最後,透過 SUMPRODUCT 函數加總,即為所求。

2. 不含六日、不含假日的工作天數
儲存格D2:=NETWORKDAYS(A2,B2,$G$3:$G$16)
在 NETWORKDAYS 函數置入第 3 個參數,其為放假日的儲存格範圍。
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2)):
求在儲存格G3:G16的放假日中,介於儲存格B2和儲存格A2兩個日期之間的個數。
儲存格D2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" 
& N(B2))),2)<6>=A2))

3. 不含六日、不含假日、含補班日的工作天數
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2)):
求在儲存格G19:G21的補班日中,介於儲存格B2和儲存格A2兩個日期之間的個數。
儲存格E2:=NETWORKDAYS(A2,B2,$G$3:$G$16)+SUMPRODUCT
(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
儲存格E2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" &
N(B2))),2)<6>=A2))+
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))

沒有留言:

張貼留言

好康東東