2017年12月16日 星期六

Excel-根據每日上、下班時間計算整月薪資(SUMPRODUCT,INT)

(網友提問)在 Excel 中有一個每日上班和下班的打卡記錄,以每30分採計(未達30分不採計),如何根據這些數據計算薪資總額?
參考下圖,上班時刻10:11、下班時刻10:57,以每1分計是46分,若以每30分計是1。
Excel-根據每日上、下班時間計算整月薪資(SUMPRODUCT,INT)

【公式設計與解析】
儲存格I2:=SUMPRODUCT(INT((C2:C32-B2:B32)*24*60/30))*I1
(1) INT((C2:C32-B2:B32)*24*60/30)
在 Excel 中是以 1 來表示 1 天,所以:
1 小時是 1/24、1 分是 1/24/60、1 秒是 1/24/60/60
運算式:*24*60/30,可以將分鐘數換算為幾個30分鐘,再利用 INT 函數取除法的『商』,即可將未滿30分鐘者不採計。

(2) SUMPRODUCT(INT((C2:C32-B2:B32)*24*60/30))
在 SUMPRODUCT 函數中可以計算 C2:C32-B2:B32 換算為幾個30分鐘的總和
(即(C2-B2)換算為30分+(C3-B3)換算為30分+…+(C32-B32)換算為30分)。

(3) SUMPRODUCT(INT((C2:C32-B2:B32)*24*60/30))*I1
將 SUMPRODUCT 函數的傳回值乘上薪資,即為薪資總額。

【額外說明】
上圖中的E欄和F欄利用數值格式設定為「[m]」,可以顯示為「分」,但此數值只能拿來參考,無法取來運算。(例如:SUM(F2:F32)的結果並不是真的人眼看到的F欄數值的總和)

【延伸練習】
如果你是要取工作時間總和(累計),再以每30分採計(未達30分不採計)來計算薪資總額?
Excel-根據每日上、下班時間計算整月薪資(SUMPRODUCT,INT)
公式:
儲存格I2:=INT(SUMPRODUCT((C2:C32-B2:B32))*24*60/30)*I1

沒有留言:

張貼留言

檢視其他文章

好康東東