2016年12月17日 星期六

Excel-依年資計算特休天數(VLOOKUP,INT)

網友問到,在 Excel 中如何設計依年資算出特休天數?計算規則如下:(參考下圖)
(1) 6個月以上未滿1年:特休3天
(2) 1年以上未滿2年:特休7天
(3) 2年以上未滿3年:特休10天
(4) 3年以上未滿5年:特休14天
(5) 5年以上未滿10年:特休15天
(6) 滿(含)10年以上,每增一年加給1天,最多給至30天為止
Excel-依年資計算特休天數(VLOOKUP,INT)
【公式設計與解析】
首先,你要先建立如儲存格E1:F7的資料清單,目的是要給 VLOOKUP 函數查表之用。
Excel-依年資計算特休天數(VLOOKUP,INT)
接著,輸入公式:
儲存格C2:=VLOOKUP(B2,$E$2:$F$7,2,TRUE)+INT((B2>=10)*(B2<=25)*
(B2-10))+INT((B2>25)*15)
複製儲存格C2,貼至儲存格C2:C23。
(1) VLOOKUP(B2,$E$2:$F$7,2,TRUE)
在 VLOOKUP 函數中藉由查表,查出年資對應特休天數。
(2) INT((B2>=10)*(B2<=25)*(B2-10))
若年資在 10~25 之間,則每一年的特休加 1。(B2>=10)*(B2<=25)之中的『*』,乃相當於執行兩個條件(B2>=10 和 B2<=25)的邏輯 AND 運算,傳回 TRUE/FALSE 。當在執行『*(B2<=25)』的『*』,及在執行算術運算,會將 TRUE/FALSE 轉換為 1/0。
(3) INT((B2>25)*15)
若年資超過 15 年,則都以最高 30 年,即加15+15=30。

【補充說明】
若要在B欄中輸入年和月的資料,可以將儲存格的數值格式定為:?0 ??/12。
這是分數的格式,其中整數部分對齊 2 位數,而小數部分則以 12 為分母。輸入資料時,整數和分數之間要有一個空白鍵。
Excel-依年資計算特休天數(VLOOKUP,INT)

沒有留言:

張貼留言

檢視其他文章

好康東東