close

2105

如何用Excel區分1.341.67的時數各多少?

實用性

●○○

難易度

●○○○

範本檔

2105.XLSX

錦子老師您好:

如果今天我要將一位員工一個月中,每日加班時數輸入後自動區分為(平日前兩小時1.34、兩小時後1.67、休息日前兩小時1.34、後二~六小時1.67、七~八小時2.67、例假日2)各是幾小時的細項,這樣函數怎麼弄..要怎麼做呢?

首先我們要先建立整佃度或當月的國定假日表,本例為全年度(M2儲存格開始~MXX)

再來建立各種狀況的加班加權指數表(0.08333332小時、0.256小時、0.250694446小時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儲存格指定的第幾欄,再乘以加班時數與時薪。


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()