ソルバーによるシフト表自動作成

エクセルのアドイン機能に、シミュレーションツールの一つ「ソルバー」があります。
従来、ソルバーは整数の扱いが不得手のようでしたが、エクセル２０１０からエボリューショナリー機能が追加されました。
これにより、魔方陣や数独（そして本例のシフト表作成）のような非線形の整数問題を解くことが可能になりました。

ここでは、ソルバー機能をフルに活用し（のちにはVBA機能も）、シフト表の自動作成を試みてみます。

　１．単一勤務種別の自動割り当て　－　日々の必要人数を個人単位で割り振る
　２．複数勤務種別の自動割り当て　－　日勤や夜勤のように複数の勤務種別を均等割振
　３．連続勤務不可条件　－　連続してはいけない勤務の組み合わせを指定
　４．不可条件　 　－　各人が出来ない勤務を日単位で指定する
　５．均等化－ソルバー　 　－　ソルバーを使用して勤務日数を均等化
　６．均等化－均等再配置アドイン　 　－　別のアドインを使用して勤務日数を均等化
　７．不可勤務を複数指定　 　－　早出および遅出が不可のように複数の不可勤務を指定
　８．勤務指定方法の改良　 －　数値ではなく、リストから勤務名として指定できるようにする
　
　以下、マクロ（VBA）使用
　
　９．自動化　－　 入力から実行までをマクロ等を利用してなるべく自動化
１０．不可勤務入力改良　－　リストから複数の組み合わせで入力可
１１．人数の可変化・全自動実行　－　要員氏名を任意に追加削除可能
１２．予約勤務　－　前もって、指定日に希望勤務を予約しておく
１４．連続休み日数の制限　－　休みが指定日数以上続くのを抑制
１５．連続勤務日数の制限　－　勤務が指定日数以上続くのを抑制
１６．前旬データを考慮　－　前旬末１週間のデータを作成に反映
１７．入力チェック　 －　重複チェック、勤務色自動反映
１８．実行速度改良＆汎用化　－　作成完了までの時間を短縮する工夫
１９．過去勤務状況を反映　－　過去の勤務日数を均等化に反映　途中
２０．２週間７人制拡張版　－　期間を２週間、人員を７人にしたシフト表

２１．予定勤務日数　－　勤務日数を指定し、不足分は自動調整
２２．手動調整　－　出来たシフト表を調整、または変更するための仕掛け

「均等再配置」アドイン　－　ソースコード 　

最終的には、ソルバー、 および、ＶＢＡを使用して、
データを入力しボタンをクリックするだけで、シフト表が全自動で作成されるようにな仕掛けを作成します。

下の例のように、日々の必要人数を勤務ごとに指定し、
不可条件を課して、要員を割り振ります。

本例では、２週間、３種類の勤務、７人体制

―――――――――――――――――――――――――

要員設定

不可条件

連続不可条件

予約勤務

出力と集計表

条件設定

「全自動実行」ボタンをクリックすると、
薄い黄色のセル内に、全ての条件を満たす要員配置ができました。
（３．３０Ｇｈｚ、５Gｂメモリ、６４ビットＰＣで約２分）

ダウンロード後実行して、コンパイルエラーメッセージが出る場合

要員の人数、作成するシフト表の期間
　　下図の例は２週間、ステップ別説明は１週間分
　　期間／人数は、行列を追加することで（ある程度）拡張可。（我慢できる時間内に解が求まるかの問題・・・）


入力部：黒枠内は入力データ。
出力部：勤務シフト表 が自動作成される。（黄色セル）
計算部：入 力データを計算できるように数値に変換

初期情報： 下図の黒枠内に入力

　・複数の勤務種別
　　　　日：日勤　　　早：早出　　遅：遅出　　の３種類
　　　　　　（勤務の種類数に制限は無し　－　ＰＣ／ソルバーの性能次第）

　・必要人数の指定
　　　　曜日（日）単位に必要な人数を指定
　　　　　　（人数の制限は無し　－　ソルバーの性能次第）

　・目的セル（過不足計＝必要人数と割当人数の差） が０になること。
　・分散計（勤務日数のばらつき）が最少となること。
　・連続休み個数を最少化－３連休、４連休などの発生を抑制
　・連続勤務個数を最小化－６連勤、７連勤などの発生を抑制

　・その他－順次条件を追加

予約勤務と不可勤務の重複
予約勤務と連続不可勤務との整合

出力部の青枠内に出力
入力情報と禁止条件を元に、勤務数のバラツキが最小となるように要員を配置
バラツキは、個人単位の日数合計、勤務種別ごとの日数合計の分散計を最小化

個別のボタンクリックで、段階を経て作成可能

勤務の背景色と文字の色は勤務テーブルで指定する。
指定された背景色と文字色がＶＢＡでシフト表に自動反映される。
「勤務色更新」ボタンで手動でも更新可

　１．単一単位種別の自動割り当て　－　日々の必要人数を個人単位で割り振る
　　　　SUM：合計算出
　　　　COUNTIF：条件を満たすセルの個数、ABS:絶対値に変換
　　　　ソルバー：線形問題解法（シンプレックスLP）

　２．複数勤務種別の自動割り当て　－　日勤や夜勤のように複数の勤務種別を均等割振
　　　　ソルバー：進化論的解法（エボリューショナリ）

　３．連続勤務不可条件　－　連続してはいけない勤務の組み合わせを指定
　　　　文字列の連結：「＆」演算子
　　　　COUNTIF：ワイルドカードを使った条件付き集計
　　　　　　　　　　　（文字列中に指定文字列が含まれるかを判定：連続不可勤務）

　４．不可条件　 　 －　各人が出来ない勤務を日単位で指定する
　　　　配列数式：勤務ごとの個数集計（クロス集計）
　　　　　　　　　　　（右側のセルが空白でなく、かつ、左側のセルと値が一致しているセルの個数）

　５．均等化－ソルバー　 －　ソルバーを使用して勤務日数を均等化
　　　　均等化：標準アドインの組み込み方法
　　　　　　　　　ソルバーアドインを使用して均等化を図る（失敗）

　６．均等化－均等再配置アドイン　－　別のアドインを使用して勤務日数を均等化
　　　　均等化：「均等再配置」アドインを使用して勤務日数のバラツキを少なくする
　　　　　　　　　ユーザーアドインの組み込み方
　　　　VARPA（分散値）：個人単位での勤務個数のバラツキを求める

　７．不可勤務を複数指定　 －　早出および遅出が不可のように複数の不可勤務を指定
　　　　入力チェック：カンマ区切りデータから重複チェック
　　　　配列数式を使用して不可勤務指定

　８．勤務指定方法の改良　 －　数値ではなく、リストから勤務名として指定できるようにする
　　　　文字列処理：文字列から計算用の数値に変換、文字列に復元して出力
　　　　ユーザー定義関数
　　　　　　MyConnectAll：範囲内のセル文字列を全て連結（区切り文字指定可）
　　　　　　MyReplaceAll：テーブルに従い範囲内の文字列を全て置換
　　　　入力規則：リストから選択して入力

　９．自動化　 －　 入力から実行までをマクロ等を利用してなるべく自動化
　　　　VBA：コマンドボタン、ソルバー関連関数（SolverOk）、均等再配置（ReAllocate）

１０．不可勤務入力改良　 －　リストから複数の組み合わせで入力できるようにする
　　　　フォーム：VBAを使用してユーザーフォーム＆リストボックス（複数項目選択可）を使用

１１．人数の可変化・全自動実行　 －　要員氏名を任意に追加削除可能とする
　　　　入力範囲を可変：Offset関数、 名前の動的定義
　　　　キー処理：入力されたキーを取得（GetAsyncKeyState）
　　　　ソルバーダイアログ表示関数：SolverOkDialog関数
　　　　条件付きコンパイル：３２／６４ビット版Ｏｆｆｉｃｅへの対応（#If Win64 Then ～ #Else ～ #End If）

１２．予約勤務　 －　前もって、指定日に希望勤務を予約しておく
　　　　配列数式による集計：文字列の中に含まれる指定文字列の個数を算出
　　　　転記：ＶＢＡによる範囲内文字列の全転記

１４．連続休み日数の制限　－　休みが指定日数以上続くのを抑制
　　　　連続文字列のブロックす個数：SUBSTITUTE関数を使用して指定文字列（連続休み）のブロック個数を求める
　　　　SUMPRODUCT関数による配列間積の総和　－　連続休み数に重みを付ける

１５．連続勤務日数の制限　－　勤務が指定日数以上続くのを 抑制
　　　　正規表現：指定文字列のブロック個数を求めるVBAユーザー定義関数（MyRegCount）作成

１６．前旬データを考慮　－　前旬末１週間のデータを作成に反映
　　　　フォーム：チェックボックス
　　　　列の非表示：VBAを使用して、指定範囲列の表示／非表示を切り替える（Range.EntireColum.Hidden=TRUE/FALSE）

１７．入力チェック　 －　予約／不可／連続不可勤務間の重複チェック、勤務色自動反映
　　　　条件付き書式：指定条件を満たす（または満たさない）セルの色を変える
　　　　セルの文字色／背景色変更：VBAを使用して、指定範囲のセル書式（Interior.Color/Color）を個別変更する

１８．実行速度改良＆汎用化　－　解に達するまでの時間を短縮する工夫
　　　　ソルバーパラメータの動的変更：SolverOptions関数を使用して、パラメータを変更

１９．過去勤務状況を反映　－　過去の勤務日数を均等化に反映

２０．２週間７人制拡張版　 －　期間を２週間、人員を７人にしたシフト表


２１．予定勤務日数　－　勤務日数を指定し、不足分は自動調整
　　　　あらかじめ予定勤務日数を表として指定する。要員数と予定日数の差は、自動で勤務を割り当てる
　　　　自動で割り当てる勤務と超過可能数は表で指定。

　　　　　　例：日数に満たない場合は「日勤」を割り振って予定勤務日数になるように調整

２２．手動調整　－　出来上がったシフト表を調整、または変更するための仕掛け
　　　　フォームから略称名の候補リストから選択して変更できるようにする。
　　　　候補は変更可能な条件により、色分けする。
　　　　また、誤って数式が破壊されないように、シートの保護機能を設定する。