2021.10.14 追記あり
はじめに
表計算ソフトのソルバーを使った方法です。
LibreOffice Calcのソルバーを使ってシフト表を作ってみます。このソルバーは式を保存できないようで、実際に使うことはオススメしません。
最初は簡単なシフト表から始め、順次機能を追加していきます。
EXCELの場合は、「解決方法の選択」で「シンプレックスLP」を選択してください。
ソルバーの条件を保存できますので、小規模な計算には使えると思います。
最適化の条件1 (2021.10.13 追記あり)
- １週間分の必要出勤人数とスタッフ別の出勤日数から、条件合うようにシフト表を作成します。
まず文字や数値を次のように書き込みます。
G2に=SUM(B2:F2)と式を入れ、縦方向にG8まで式をコピーします。
B9に=SUM(B2:B8)と式を入れ、横方向にF9まで式をコピーします。
次に、メニューからソルバーを選択し、次のように入力します。
「ターゲットセル」は空白ではダメで、影響の無さそうな式の入ったセルを指定しています。
「変更させるセル」に結果が書き込まれます。それらは0か１(１が出勤、０が休み)ですので、「制限条件」でバイナリに指定します。
B9:F9は「変更させるセル」の縦方向の合計が計算され(先に式を入力している)、下の行の B10:F10の値と一致するように「制限条件」を設定します。
G2:G8は「変更させるセル」の横方向の合計が計算され、右の行の H2:H8の値と一致するように「制限条件」を設定します。
「解決」ボタンを押すと、計算してくれます。
結果は次のようになりましたが、条件を満たす結果はいくつもありますので、全く同じにはならないとはずです。
スタッフ別出勤日数を幅を持たせるのであれば、
10行目を最小出勤日数にし、11行目を最大出勤日数にして、ソルバーの２行目を
$B$9:$F$9 >= $B$10:$F$10
$B$9:$F$9 <= $B$11:$F$11
とすることもできます。
最適化の条件2
- スタッフの休日希望を反映させるように機能を追加
B13:F19の間に休みを希望する日に0を入れ、出勤可能日には1を入れておきます。
ソルバーは次のように設定します。
最後の行が追加されています。
「解決」を実行すると、条件を満たす結果が返ってきます。
B2:F9が休日希望のB13:F19より大きくならない、つまり、0の場合は「変更させるセル」の値が強制的に0になります。
最適化の条件3
- 連続勤務日数の制限 連続5日を限度とする機能を追加
21行から下を追加します。
B23には=SUM(B$2:B2)+MIN(B$21,5)
B24には=SUM(B$2:B3)+MIN(B$21,4)
B25には=SUM(B$2:B4)+MIN(B$21,3)
B26には=SUM(B$2:B5)+MIN(B$21,2)
B27には=SUM(B$2:B6)+MIN(B$21,1)
B28には=SUM(B2:B7)
B29には=SUM(B3:B8)
を入力し,
B23:B29をF列までコピーします。
例えばMIN(B$21,5)はB21の値のうち過去5日分を連続出勤日数に反映させています。
これらの式で当日を含めて過去6日間の出勤日数が計算されます。その間に休みがあれば5以下になります。
「制限条件」が全部表示させていませんので、最後の１行のみ追加してください。
B2:F8の勤務日数が5以下になるようにしています。
最適化の条件4 (2021.10.13 追記)
- スタッフの出勤希望を反映させるように機能を追加
上のスタッフの休日希望と同じような考え方でできます。
出勤希望日を1, それ以外の休んでも良い日には0を入れます。
ソルバーには$B$2:$F$8 >= $B$32:$F$38 を追加します。
つまり１を入れた所は必ず１(出勤)になります。
最適化の条件5 (2021.10.14 追記)
- 業務を2に分けて、それぞれ１名ずつ割り当てる
この記事の下の方にあるコメント蘭を見ていただけると経緯が理解できると思います。
結論から言うとLibreOffice Calcのソルバーが10分経っても終了しませんでした。EXCELなら大丈夫なのかな？？
上の条件3は必要がないので省略してます。
参考にされる方もいるかもしれませんので、やり方を書いておきます。
GLPKで書いた同じ機能のプログラムを勤務シフト表の作成 基礎(3)に書いてありますので、興味のある方はご覧ください。数秒以内で答えが出ます。
ソルバーの変数領域(変更させるセル)は、１つの矩形でないとダメなので、業務Aと業務Bの領域を縦に並べています(B2:H15)。それと、業務Aと業務Bが同時に割り当てられないように、B22:H28で業務Aと業務Bと同じ日に割り当てられないようにしています。
後は上述の方法とほぼ同じ考え方です。
I2に=SUM(B2:H2)と式を入れ、縦方向にI15まで式をコピーします。
B16に=SUM(B2:B8)と式を入れ、横方向にH16まで式をコピーします。 業務Aだけの合計です。
B17に=SUM(B9:B15)と式を入れ、横方向にH17まで式をコピーします。
B22に=B2+B9と式を入れて、矩形のB22:H28の領域に式をコピーします。業務Aと業務Bに同時に割り当てられた時に2となります。
後は、下のソルバーの画像を参考にしてください。
ソルバーの設定です。
