sxt66329的部落格

前言

本文因太多人來求助我，可能當初沒說明如何規劃排班系統的方式，就用前言先將排班系統規劃前，要做那些事才比較好進行排班。

一、排班因素要明確且簡單化

人員：可上什麼班、那幾天休假，總時數

班別：正常班、假日班，連續上有沒有限制，並與日期做明確區分，不能太模糊

二、先做行事曆出來，將全年(含國定假日)一次出來，再分正常班、假日班

三、再將人員、休假日、限制班也用在一張表整來出來

四、再用人員塞班表，或是班表塞人員

若是想自動帶出來，就比較麻煩，有人些是用01010(之數列串)來帶排班表，0代表休假，1代表上班，好處是COUNT總上班數

調班、人員異動、特殊限制、班別A可代表班別B等問題是加重EXCEL判斷及速度，這已經涉及VBA、系統架構設計層次了。

總之一句話，一定要將手上排班規則整理明確，太多不明確就會讓班表更雜亂且不易處理。

 

本文

日前，朋友問我，如何利用Excel做出月份排班報表，他想做出報表如下圖。

 

而且排班資料來源為下圖，方便他日後維護用，因此，我開始著手進行此份排班報表。

 

以下是我進行的步驟。

一、首先，在A1儲存格建立年份資料，此欄位為了方便日後使用，採用資料驗證的方式，資料驗證設定如下圖，設定好了在A1儲存格右側就多了下拉式鍵來代替輸入值。

 

二、再來於B1儲存格建立各月份資料，此處為直接輸入方式，並運用儲存格格式設定技巧，設定內容如下圖，讓此儲存格呈現5月的外觀。

 

三、再接下來，建立日期，因為29~31日需要判斷月份(待兒解說處理方式)，故1~28日就直接帶值(如下圖)。

 

四、29~31日，利用DATEVALUE及DAY函數設定(設定如下)，判斷該值是否呈現。

 

五、接下來，建立星期對照表(如下圖)，再將AK1:AQ2範圍設定名稱管理員為WEEK，方使帶公式使用。

 

六、接下來處理星期，此處運用WEEKDAY及HLOOKUP(設定如下圖)，其中HLOOKUP是利用WEEK名稱管理員來帶入值。

  

七、因29~31日之日期會因月份不同，產生不一樣的值，較29~31日之星期判斷，我是用以下公式來判斷。

 

八、接下來是用設定格式化/管理規則的技巧來設定星期六、星期天填滿顏色技巧，

 

九、進入了管理規則，我設定星期六的技巧如下，其中公式C$2是取其上方星期數的儲存格之值，欲設定星期六、星期天填滿顏色區塊。

 

十、再至格式，進入儲存格式設定，如下圖。

 

十一、設定好了格式化，再選定星期六、星期天填滿顏色儲存區塊即如下圖，再去設定星期日。

 

十二、再來去排班資料全選所有排班人員(員編及姓名)，再用移除重複方式，將人名表建立。

 

十三、再複製已移除重複之人員名單。

 

十四、再貼上報表人員上，如下圖。

 

十五、在排班資料多增加二檔，其中VLOOKUP判斷值下公式如下，以利VLOOKUP查詢。

  

十六、再將新增二欄設定名稱管理員，方便VLOOKUP使用。

 

十七、最後用VLOOKUP來帶入值，公式如下圖，即可完成。