2014年3月5日 星期三

Excel-分別計算假日和非加日的加班時數

有網友想要解決一個問題:在一個加班的資料表中,希望能分別計算平日和假日的加班時數。而上班情形又分為二種狀況,一種是正常的星期六日為假日加班日,另一種是輪班之星期二四為加班日。

【準備工作】

選取儲存格A1:C24,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、時數。

一、星期六日為假日加班日

(1) 計算平日加班時數

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

使用 WEEKDAY 函數來取得日期為星期幾,參數「2」表示傳回值1代表星期一、傳回值 2 代表星期二、…、回值 7 代表星期日。

WEEKDAY(A2,2)<6:表示日期為星期一到星期五(為平日)。

image

(2) 計算假日加班時數

儲存格E2:=SUM(NOT((WEEKDAY(A2,2)<6))*C2)

選取儲存格D2:E2,往下各列貼上。

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

儲存格H2:=SUMPRODUCT((WEEKDAY(日期,2)<6)*時數)

透過 SUMPRODUCT 函數將平日加班的時數陣列加總。

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

儲存格H3:=SUMPRODUCT(NOT((WEEKDAY(日期,2)<6))*時數)

透過 SUMPRODUCT 函數將假日加班的時數陣列加總。其中 NOT 函數可以將條件結果 TRUE 轉成 FALSE,將 FALSE 轉成 TRUE

 

二、星期二四為假日加班日

原理說明與一之(1)(2)(3)(4)相同。

(1) 計算假日加班時數

儲存格E2:=SUM(OR(WEEKDAY(A2,2)=2,WEEKDAY(A2,2)=4)*C2)

找出日期的星期幾「二或(OR)四」者,乘以時數,再予以加總。

(2) 計算平日加班時數

儲存格D2:=SUM(NOT(OR(WEEKDAY(A2,2)=2,WEEKDAY(A2,2)=4))*C2)

找出日期的星期幾不是「二或(OR)四」者,乘以時數,再予以加總。(使用 NOT 函數)

選取儲存格D2:E2,往下各列貼上。

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

儲存格H3:=SUMPRODUCT((WEEKDAY(日期,2)=2)*時數)+SUMPRODUCT((WEEKDAY(日期,2)=4)*時數)

透過 SUMPRODUCT 函數將假日(星期二四)加班的時數陣列分別計算後加總。

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

儲存格H2:=SUM(時數)-(SUMPRODUCT((WEEKDAY(日期,2)=2)*時數)+SUMPRODUCT((WEEKDAY(日期,2)=4)*時數))

計算加班的總和來減掉假日(星期二四)加班時數總和,即為平日加班時數總和。

沒有留言:

張貼留言

好康東東