sxt66329的部落格

一、前言

用excel排班是很多人用的工具，但是，許多朋友遇到萬年曆就蠻頭痛的事，因此，本文用函數就可以實現萬年曆的設計，同時，也可以因應國定假日、補上班等因素，加強判斷當天是否為正常班。

二、全年萬年曆製作

首先用將年度放置A1儲存格，以下再分成月/日儲存格來展開全年365天之萬年曆了。

接下來就是處理最煩人的閏年判斷，我用IFERROR(IF(DAY(DATEVALUE(($A$1+1911)&"/"&A34&"/"&29))=29,29),"")之公式判斷，其手法是DateValue及Day函數判斷，當出現錯誤，就帶空值。

為什麼先用DateValue?那是，因為EXCEL會利用DateValue判斷日期是否存在，若是用Date函數，遇到了閏年，EXCEL會帶到3/1，結果是會誤判的。另外，DateValue是用西元日期判斷，民國年是要轉換成西元年格式，否則會發生錯誤。

三、處理星期欄位

接下來是處理星期欄位，若是，直接帶WEEKDAY函數，會發現星期天之值為1，星期六為7，也不易讓其他人理解那天是星期幾。因此，再建星期天轉換表格，轉換大家看懂的內容。

所以，公式寫成了IFERROR(VLOOKUP(WEEKDAY(DATEVALUE(($A$1+1911)&"/"&$A47&"/"&B47)),$H$2:$I$8,2,FALSE),"")

為什麼，我會用IFERROR來判斷星期函數值出錯?唉，是平年時，2/29沒有那一天，故當天就用空值了。

四、處理國定假日補理

萬年曆最麻煩是判斷人事行政局之國定假日、補班，有些人是用農民曆公式來換算等手法，這方法對於初學者來說是太難了，因此，我改用查表法，而且，貴公司要新增那幾天休假去員工旅遊、尾牙等，都可以自行定義，也是靈活且彈性。注意，日期一定要用西元年，因為，DateValue判斷日期是否存在，同時，統一日期格式，有利日後排班表導入時，日期判斷依據。

如何去設定班別欄位呢?首先，我設假日為0正常班則值為1，因為，有些公司假日是不計執勤天數，比較好換算，再利用自定儲存格顯示方法，讓表格呈現"假"VS"正"的內容，易外人理解那天是什麼班別。另外，為易於閱讀及判斷，利用「判斷格式化的條件」之方式，將儲存格能在假日班有顏色視覺，方便核算假日班之情形。

五、判斷班別

接下來是判斷班別情形，設計邏輯順序為，先查表國定假日補班，若是找到就直接帶值，若是找不到再利星期函數為"六/日"做為判斷，最後在2/29多加判斷星期欄值為空值(平年的2/29)直接為空值，剩下是正常班了。

因此，公式我寫成這樣子，IFERROR(VLOOKUP(DATEVALUE(($A$1+1911)&"/"&$A3&"/"&$B3),$K$2:$L$18,2,0),(IF(C3="六",0,IF(C3="日",0,1))))

六、結論

簡易萬年曆判斷是做排班表的第一步，有上述方式之後，排班表考慮因素少了日期判斷困擾。另外，排班表設計上要考慮有人員判斷，連續值勤限制、總時數限制、排休等因素，非一次可以完成複雜排班表，希望，未來有空再整理，分享苦難排班朋友。

檔案 =>  https://mega.nz/#!IEs3FIBZ

KEY    =>  !2EzA30WFsiHGFMaanqALr_sgyd_uV6-RkqnOWx_vQGw