2014年6月10日 星期二

Excel-依平時/假日分開加總(SUMPRODUCT,WEEKDAY)

有網友問到在以下的日期清單中(參考下圖),如果工時超過 8 小時的部分算為加班,如何分開計算平時和假日的加班?又如何在一個儲存格中即算出加班的總和?

【計算方式一】

(1) 計算平時加班時數

儲存格D2:=IF(WEEKDAY(A2,2)<6,C2-8,0)

WEEKDAY(A2,2)<6:判斷日期是否為「平時」,其中 WEEKDAY 函數的參數 2,代表星期一傳回 1,星期二傳回 2,…,星期六傳回 6,星期日傳回 7。

image

(2) 計算假日加班時數

儲存格E2:=IF(WEEKDAY(A2,2)>5,C2-8,0)

(3) 計算平時加班時數總和

儲存格G2:=SUMPRODUCT((WEEKDAY(A2:A32,2)<6)*(C2:C32-8))

(4) 計算假日加班時數總和

儲存格H2:=SUMPRODUCT((WEEKDAY(A2:A32,2)>5)*(C2:C32-8))

 

【計算方式二】

(1) 計算平時加班時數

儲存格D2:=SUMPRODUCT((WEEKDAY(A2,2)<6)*(C2-8))

WEEKDAY(A2,2)<6:判斷日期是否為「平時」,其中 WEEKDAY 函數的參數 2,代表星期一傳回 1,星期二傳回 2,…,星期六傳回 6,星期日傳回 7。

(2) 計算假日加班時數

儲存格E2:=SUMPRODUCT((WEEKDAY(A2,2)>5)*(C2-8))

(3) 計算平時加班時數總和

儲存格G2:{=SUM(IF(WEEKDAY(A2:A32,2)<6,C2:C32-8,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(4) 計算假日加班時數總和

儲存格H2:{=SUM(IF(WEEKDAY(A2:A32,2)>5,C2:C32-8,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

沒有留言:

張貼留言

檢視其他文章

好康東東