2105 |
如何用Excel區分1.34、1.67的時數各多少? |
||||
實用性 |
●○○ |
難易度 |
●○○○ |
範本檔 |
2105.XLSX |
錦子老師您好:
如果今天我要將一位員工一個月中,每日加班時數輸入後自動區分為(平日前兩小時1.34、兩小時後1.67、休息日前兩小時1.34、後二~六小時1.67、七~八小時2.67、例假日2)各是幾小時的細項,這樣函數怎麼弄..要怎麼做呢?
首先我們要先建立整佃度或當月的國定假日表,本例為全年度(M2儲存格開始~MXX)。
再來建立各種狀況的加班加權指數表(0.0833333為2小時、0.25為6小時、0.25069444為6小時1分)。
在A欄輸入要計算加班費的日期。
在B2儲存欄輸入公式:
「=IF(LOOKUP(A2,$M$2:$M$27,M2:M27)>0,3,IF(WEEKDAY(A2)>5,2,1))」
再將B2儲存格公式複製到B3:B32儲存格。
【公式解析】
LOOKUP(A2,$M$2:$M$27,M2:M27) 傳回A2儲存格內容到M欄搜尋的結果,如果找到會傳回所在列的列號。
WEEKDAY(A2) 傳回A2儲存格是星期幾(數字)
IF(LOOKUP(A2,$M$2:$M$27,M2:M27)>0,3,IF(WEEKDAY(A2)>5,2,1)) 如果LOOKUP傳回的是數值,則B2儲存格輸入3,表示為國定假日,否則若A2儲存格星期為六或日,則輸入2,表示為休息日,否則輸入1,表示即為工作日。
(3為國定假日,2為休息日報星期六或星期天,1為一般工作日)
在C欄輸入每日加班時數。
在D欄輸入時薪。
在E2儲存格輸入公式:
「=VLOOKUP(C2,$G$2:$J$4,B2)*D2*C2」
再將E2儲存格公式複製到E3:E32儲存格,即可算出每日加班費。
【公式解析】
VLOOKUP(C2,$G$2:$J$4,B2)*D2*C2
將C2儲存格內容與G欄作比對,傳回其位於第幾列,再抓取B2儲存格指定的第幾欄,再乘以加班時數與時薪。
留言列表