贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

網友問到,在 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<=24)*(B2-9))+(B2>24)*15

複製儲存格C2,貼至儲存格C2:C23。

(1) VLOOKUP(B2,$E$2:$F$7,2,TRUE)

在 VLOOKUP 函數中藉由查表,查出年資對應特休天數。

(2) INT((B2>=10)*(B2<=24)*(B2-9))

若年資在 10~24 之間,則每一年的特休加 1。(B2>=10)*(B2<=24)之中的『*』,乃相當於執行兩個條件(B2>=10 和 B2<=24)的邏輯 AND 運算,傳回 TRUE/FALSE 。當在執行『*(B2<=24)』的『*』,及在執行算術運算,會將 TRUE/FALSE 轉換為 1/0。

(3) (B2>24)*15

由第(2)式處理10年~24年,每一年增加 1 天,若年資大於 24 年者,要再加 15,以補足年休30天。


【補充說明】

若要在B欄中輸入年和月的資料,可以將儲存格的數值格式定為:?0 ??/12。

這是分數的格式,其中整數部分對齊 2 位數,而小數部分則以 12 為分母。輸入資料時,整數和分數之間要有一個空白鍵。

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

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(7) 人氣()


留言列表 (7)

發表留言
  • MAMA
  • 您好!!
    想請教B欄位的年資,要如何計算出來!!
    感謝您!
  • 文末有提到:
    若要在B欄中輸入年和月的資料,可以將儲存格的數值格式定為:?0 ??/12。
    這是分數的格式,其中整數部分對齊 2 位數,而小數部分則以 12 為分母。輸入資料時,整數和分數之間要有一個空白鍵。
    如果A日期輸入 102 6/12(表示102年6月),B日期輸入 105 8/12(表示105年8月),當 B-A 時,會得到 3 2/12(表示3年2月)。

    vincent 於 2016/12/23 14:01 回覆

  • FIREBOBER
  • 您好,感謝您提供的公式
    不過10年開始是不是有少一天啊?
    10年是16天,但是照著公式打是顯示15天
    如果是我自己搞錯了,先跟您說聲抱歉喔
  • 沒錯!已更正。因為誤以為第11年才開始加1。謝謝你。

    vincent 於 2017/01/04 20:20 回覆

  • adsl.cai
  • 原本公式試算表滿10年還是15天,反而要滿11年才是16天,這地方是錯的喔!
    INT((B2>=10)*(B2<=25)*(B2-10))
    版大我修正後公式為修正後
    INT((B2>=10)*(B2<=25)*(B2-9)

  • 沒錯!已更正。因為誤以為第11年才開始加1。謝謝你。

    vincent 於 2017/01/04 20:20 回覆

  • 感謝提供公式
  • 不過年資滿24年即享有30天特休了,請問該怎麼調整公式?
  • 這個例子中的公式即是以年資滿24,即得30天休假。
    (請看圖中說明(5)5年以上未滿10年:特休15天。和(6)滿(含)10年以上,每增一年加給1天,最多給至30天為止。)

    vincent 於 2017/02/13 19:50 回覆

  • 感謝!
  • 不過輸入公式那個段落的內容有點看不懂,
    公式寫道:INT((B2>=9)*(B2<=25)*(B2-10))
    說明(2)的內容則是: INT((B2>=10)*(B2<=25)*(B2-9))
    請問哪一個才是對的?

  • 我把圖文修改成一致了。以INT((B2>=10)*(B2<=25)*(B2-9))為準。

    vincent 於 2017/02/14 12:19 回覆

  • 抱歉,有個問題
  • 年資剛滿25年,公式得出的結果是31天,
    但是再多一個月變成 25 1/12之後,又變回30天了,
    請問這部分該如何修正?
  • (應該是年資10~24天者,每年多一天休假;若超過24天,因滿30天休假,應該不再增加。)
    公式的寫法有很多種,我重新調整了公式如下:
    儲存格C2:=VLOOKUP(B2,$E$2:$F$7,2,TRUE)+INT((B2>=10)*(B2<=24)*(B2-9))+(B2>24)*15
    你再看看。
    謝謝你檢查出bug。

    vincent 於 2017/02/15 15:38 回覆

  • 訪客
  • 你好,如果工作滿1年有7天假期,工作不滿3個月0天假期,工作滿3個月可以按比較得到7天的假期,應該怎麼去設計公式才可以只計算工作滿3個月的員工假期日數? 比例計算公式是 受僱天數/365天 * 7=得到的假期 (進位至整數)
    想了好久,可以教教我嗎🙏🏻
  • 請參考:http://isvincent.pixnet.net/blog/post/47280522

    vincent 於 2017/05/30 00:02 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼