sxt66329的部落格

用EXCEL 函數寫自動排班表，發現真很少數樂意分享原始設計內容，對於人資、醫療、保全、運輸等朋友來說，卻是極痛苦的事。因此，分享條件比較簡單排班系統，希望，能拋磚引玉、畫龍點睛，解決朋友的排班問題。

案例：

某一家公司，每月為了排班苦擾，他們班別為假日班(星期六、日及國定假日)、正常班等二種班別，每人在當月班別是不可以異動，即上假日班就是假日班。每個班人數至少二人，每月每班是固定人數。

在不考慮人員休假、調班、每週/月總時數，是否有連續上班24小時以及人員班別之特殊規定(因為，我尚未設計到此)，想設計可以用EXCEL函數規劃自動排班表格。

步驟

一、萬年曆

因為，排班有二項重要因素，人員及日期，日期方面在之前我所發佈之萬年曆，大家可以參考，就可以做出一張萬年曆工作表了。

二、人員

首先，依人員代號、姓名、班別來建立工作表，並將各班別前以流水號排列，以便排班表可以找到人員之需。

在班別之值，我是用0與1來表示，其儲存格式內容設定，呈現為假正二項格式，方便檢核及閱讀。

三、排班表

首先將萬年曆之值，帶入排班表，以便日後公司臨時有要更動行事曆，直接修正萬年曆就可以帶入排班表了。

再來，設定正常班10位、假日班5位，並留下10位名單空間，以便等一下由EXCEL產生名單之用。

接著，將去年已輪值之最後之代號打上去，以便在生成人員名單時，依剛剛人員工作表流水號排序下去。

接下來，檢視今年度連續休假日最長之情形，在連續假日後之上班日，要判斷其前一位輪值代號為何，此時，運用OFFSET函數，把前一位最後輪值儲存格帶入此儲存格，以利該日可以生成下一位輪值的朋友。

因此，公式邏輯是連續假日後之上班日，判斷前一天是否有上班，有就帶入前一位最後輪值儲存格帶入此儲存格，不是，再往上找，直到第七天為止(因為，IF條件式最多帶七層，超過就出現異常情形)。

那麼，超出怎麼辦，只好用直接帶入來解決。

運用，此公式來帶入前位輪值代號之後，將此公式往上帶入時，會發現公式會有錯誤值，這是，公式會往上找七層，但是，工作表不足七層就會超出此工作表，造成錯誤，這時候將層數減少至沒有錯誤值就好。

但是，到了班別第一筆時，就用判斷式，先確認班別，再去找去年最後一位輪值人員代號了。

[註]本表故意留一處錯誤值，為了使大家能理解確認會修改才留下來此值，不然，日後又更動條件但不會改此處，那代表你仍然不會用此表。

接下來，人員要如何去處理，此處運用餘數手法來做，首先將最後一位輪值人員代號與該班別總人數相除，若是，上次輪到25，總人數為85，相除之之餘數即為25，再加1，即為下一位就是了。

第二位輪值人員就要判斷前一位輪值人員流水號，再接下來生成輪值代號

接著是將沒人輪值名單儲存格用灰色填滿，以便判斷之需，此處是運用了格式化儲存格條件規則

因此，全年度輪值人員代號都可以生成出來了，接下來，將人名儲存格用VLOOKUP來帶入人名。

若是人名為空值，即沒人輪值，也是利用格式化儲存格條件規則，將儲存格用灰色填滿。再帶入全年度就是輪值表完成了。

四、排班人數變動

假如，我們要將正常班改為12位值勤，本排班表也是可以增加人員名單的，首先，將人員11、人員12欄位畫好。

接著，將沒人輪值名單儲存格用灰色填滿

再將，人員11之輪值代號，用人員10烤貝過來，將原本N4，改為O4，就是人員11之前一輪值代號為人員10，即為O4儲存格，故公式為IF(D4=0,(IF(COUNT(F4:O4)>=$J$1,"",MOD(O4,COUNTIF(人員名單!$D$2:$D$110,$D$4))+1)),(IF(COUNT(F4:O4)=$G$1,"",MOD(O4,COUNTIF(人員名單!$D$2:$D$110,$D4))+1)))。相對，人員12就是將人員11之 O4，改為P4，就可以帶了。以下全年度輪值表就往下拉生成即可。

再將值班人數由10改成12，馬上正常班排班人員變成12位。

再將值班人數由5改成6，馬上假日班排班人員變成6位。

若是，想變更上一次輪值人員代號，發現此排班表是可以馬上異動

接下來，人名增加12位，首先增加人員11、人員12二欄。

也是要將沒人輪值改用灰色填滿

接著，用VLOOKUP帶入輪值人名。

再拉下自動帶入全年度，就可以自動生成人名

五、人員班別異動

有許多公司，因每隔一段時間，人員班別會異動，故本排班表是否可以因應此需求，首先，在人員之班別進行異動，請用0代碼為假日班、1為正常班進行修正。

接下來，再依班表及代號次序排序，若內容有需調整，就在此步驟來調整排班次序。

接著，排定次序代號，以便等會由排班表帶入之用。

最後，再去排班表之人名VLOOKUP函數，調整各班別之儲存格範圍，就可以帶入異動後人名了。

六、引伸應用

每家公司排排班狀況不同，本表在簡易規則下排班，是可以自動排定。若是，您是需要每月進行排班、每班人名經常輪調各班別且每月固定，那就就本案例改為每月，若是怕跨月在連續值勤，可以用不同月份進行人工檢核，一樣可以運用本排班表。

若是，府上排班規定多，切記要先整理規則，模稜兩可會增加排班邏輯困難。不要害怕，先整理架構後，再用本表初步先排排看，再用人工調整。

檔案 =>     https://mega.nz/#!sUcnnbhJ!pY1hHSW6tgcU6syyt4eJ7qxjc5yy-afkImUIcQQlwwA

2017/01/25 補充

班別設計會涉及一日2/3班、假/平日班、一例一休等因素，故若帶入班時，設計上考量會比較複雜。

以平/假日班別設計，要先界定假/平日，因為，涉及到國定假日、補休、補班等因素，以下為例，就先做好萬年曆及找出平/假日，才可以做到區分班別情形。