5分鐘學會用Excel Solver排班表

Peiluniek

Follow

--

用Excel 最佳化輪班表，例用Excel>資料>規劃求解，求得每時段員工需求人數與最低成本。

以下例子會詳細說明如何用簡單的步驟排出既省時又省力的班表

首先，一個好的表格可以使得規劃求解更順利。Ａ列為每一時段的開始時間， 以兩小時為一單位; B列為每一時段的員工需求人數; C列為此時段可支援的員工人數，E列為員工上班時間，F列為員工下班時間，G列則是每一時段上班的員工人數，例如G4為12:00AM-8:00這段時間要打卡上班的人數。

黃色的部份：填入排班者需要員工數，以實際現場需求調整。以兩個小時為一單位填入員工需求數，以B4為例，這一格要填12:00AM-2:00AM這段時間的員工需求人數，在B4填入14，表示在12:00AM-2:00AM這段期間，共需要14位員工。

表二中B4 - B15空格裡，填入每個時段的最低員工需求數，B16則是用SUM公式將12個數字相加＊2，這個例子的總和是194小時 （每一時段是兩小時，員工總人數乘上兩小時）

綠色的部分則需要運用SUM公式做計算，若想計算C4這一格的實際人數，需知道G列中哪些打卡時段有涵蓋到12:00AM-2:00AM這個時段（表三）。

表三以下這四個時段打卡上班的員工可以支援Ｃ4（12:00–2:00）時段：

G4（12:00AM-2:00AM）

G13（6:00 PM-2:00AM）

G14（8:00 PM - 4:00 AM）

G15（10:00 PM - 6:00 AM）

C5-C15都是依此類推。

特別注意，當C7=SUM(G4:G7)可以直接拉出連續四格之後，直接把游標放置C7表格右下方，當出現黑色十字時，點兩下之後，C8-C15就會自動填滿，C8=SUM(G5:G8)（表四）

此時因為G列還沒有任何數字，所以就算C列已用SUM公式做計算，還是只會得到一排0的結果，但因為C列公式與G列數值有關聯，規劃求解後，可求得C列與G列數值。C16=SUM(C4:C15)*2，計算方式與B16相同，此時游標可以放置B16右下方出現黑色十字後，向右拉至C16即會出現=SUM(C4:C15)*2的公式。

打開資料>規劃求解（圖一）

設定目標：$C$6，選取最小值，排班者希望人力可以等於或微高於需求，但不希望高出太多閒置人力，所以應選取最小。

藉由變更變數儲存格：$G$4:$G$15，希望透過規劃求解來得到這12格的值，同時也就是求得多少員工需要在每一個時刻上班打卡，才足夠因應人力需求。

新增限定限制式需按新增，儲存格參照、限制式可以直接選取儲存格，中間選單有≤, =, ≥, int, bin, dif，六個選項

限制式1: 為提供足夠人力，C列的每一時段的人力需要大於或等於B列。

限制式2: 因為員工上班人數不得為0.5人，G列的每一格都需要是整數。

選取求解方式: 選擇單純LP(simple linear programming)

按下求解鍵

接著會跳出圖三這個視窗，選擇保留規劃求解解答，按下確定

即可求得圖四，從G列的答案可得知：6位員工需要在12:00上午打卡上班，0位員工需要在2:00上午打卡上班，以此類推。

同時C列也得到最佳解，足以支援員工需求，但同時總時數又是最小值，只比B列的員工需求時數，高出14小時。

生產線人員、軍事人員、護理師、消防站、客服中心排班表都適用這樣的表格，如何在最精簡的人力下達成人力需求，如果並非採用兩小時為一單位計算人力需求或8小時輪班制的話，也可以在A、E、F列輸入符合公司的輪班制度。