ツールバーからアドインとして呼び出していたソルバーをマクロ化します。
また、均等再配置もアドインではなく、VBAとして直接取り込んで実行。
操作手順
1.必要なデータを入力 太黒枠内
勤務種別:現在は4種類。必要に応じて行を追加して拡張可(1~9まで)
氏名:現在は5名固定、後に任意の人数を設定できるように改良
必要人数:日々に必要な人数を入力-プルダウンメニュー
不可勤務:出来ない勤務をカンマで区切って入力。
次項10でチェックボックス(リストボックス)から選択可。
連続不可勤務:連続してはいけない勤務を設定、3個以内。
下図は、上記を入力済みの状態
3.ソルバー実行
ボタンをクリックするとソルバーが実行されます。
フォームは表示されません。
画面左下ステータスバー左側を見て試行状況を観察してください。
動きが止まったらソルバー実行終了です。
目的セルが「0」に収束しない場合は、再度、2から試してみてください。
コンパイルエラーが出る場合:ソルバーアドインが組み込まれていません。右記を参考-コンパイルエラー発生
分散「重み」について
現在、分散は、各勤務ごとの日数分散と総勤務日数の分散の総和としています。
総勤務日数を重点的に均一にしたい場合は、各勤務の重みを、1→0 とします。
特定の勤務の日数を均一にしたい場合は、その勤務の重み以外を0とします。
マクロ記述上の注意点
・アドレス部は即値で書かない実行ボタン処理のVBAコード
Private Sub cmdGoSolver_Click() ' ' ソルバー実行 ' SolverOk SetCell:="目的セル", MaxMinVal:=2, ValueOf:=0, ByChange:="変化させるセル", _ Engine:=3, EngineDesc:="Evolutionary" SolverSolve End Sub Private Sub cmdInit_Click() 'ソルバー実行 Range("変化させるセル").ClearContents Call 予約転記 End Sub Private Sub cmdReallocate_Click() '均等再配置実行 If Range("目的セル") > 0 Then Range("目的セル").Activate MsgBox "再度、初期化→ソルバー実行、を行ってみて下さい。", vbOKOnly, "過不足が0になっていません。" Exit Sub End If cmdStop.Enabled = True Call reAllocate("変化させるセル", "条件セル", "最小化セル") '均等再配置 cmdStop.Enabled = False End Sub
以下、SolverOk関数についての説明-ヘルプ からの転記
SolverOk 関数ソルバーの基本的なモデルを設定します。
マウスを使った操作では、[データ] | [分析] グループの [ソルバー] をクリックした後、[ソルバー : パラメーター設定] ダイアログ ボックスのオプションを指定することに相当します。
SolverOk(SetCell, MaxMinVal, ValueOf, ByChange,Engine, EngineDesc)
SetCell 省略可能です。バリアント型 (Variant) の値を使用します。作業中のワークシートの単一セルへの参照を指定します。[ソルバー : パラメーター設定] ダイアログ ボックスの [目的セル] ボックスに相当します。
MaxMinVal 省略可能です。バリアント型 (Variant) の値を使用します。[ソルバー : パラメーター設定] ダイアログ ボックスの [最大値]、[最小値]、[値] に相当します。
MaxMinVa 設定 1 最大化 2 最小化 3 値と一致 ValueOf 省略可能です。バリアント型 (Variant) の値を使用します。引数 MaxMinVal に 3 を指定した場合は、目的セルの値の目標値を必ず指定してください。
ByChange 省略可能です。バリアント型 (Variant) の値を使用します。目的セルの値が目標値に到達するまで変化させる、セルまたはセル範囲への参照を指定します。
[ソルバー : パラメーター設定] ダイアログ ボックスの [変化させるセル] に相当します。Engine 省略可能です。バリアント型 (Variant) の値を使用します。問題の解決に使用する解決方法として、Simplex LP 方法の場合は 1、GRG Nonlinear 方法の場合は 2、Evolutionary 方法の場合は 3 を指定します。
[ソルバーのパラメーター] ダイアログ ボックスの [解決方法の選択] ボックスの一覧に相当します。ByChange 省略可能です。バリアント型 (Variant) の値を使用します。問題の解決に使用する解決方法を文字列 ("Simplex LP"、"GRG Nonlinear"、または "Evolutionary") として指定する代替方法です。
[ソルバーのパラメーター] ダイアログ ボックスの [解決方法の選択] ボックスの一覧に相当します。
戻り値 SolverSolve関数の戻り値
0 ソルバーによって解が見つかりました。すべての制約条件と最適化条件を満たしています。 1 ソルバーによって現在の解に収束されました。すべての制約条件を満たしています。 2 現在の解の精度を上げることはできません。すべての制約条件を満たしています。 3 反復回数が上限に達し、処理が中止されました。 4 目的セルの値が収束しません。 5 実行可能解が見つかりませんでした。 6 ユーザーの要求によってソルバーが停止しました。 7 この LP ソルバーに必要な線形条件が満たされていません。 8 問題が大きすぎるため、ソルバーで処理できません。 9 目的セルまたは制約条件セルの値でエラーが発生しました。 10 指定された制限時間に達し、処理が中止されました。 11 メモリ不足のため、問題を解決できません。 13 モデルでエラーが発生しました。すべてのセルと制約条件が正しく指定されていることを確認してください。 14 ソルバーによって公差内で整数解が見つかりました。すべての制約条件を満たしています。 15 実行可能 [整数] 解の数が上限に達し、処理が中止されました。 16 実行可能 [整数] 子問題の数が上限に達し、処理が中止されました。 17 大域解に確率収束しました。 18 すべての変数に上限と下限が必要です。 19 バイナリ制約条件または alldifferent 制約条件で、変数の範囲が競合しています。 20 変数の上限と下限には、実行可能解を使用できません。
SolverGet関数
ソルバーの現在の設定についての情報を返します。
各設定は、[ソルバー : パラメーター設定] ダイアログ ボックスや、[ソルバー : オプション設定] ダイアログ ボックスで指定されたものです。
SolverOptions関数
ソルバーの動作の詳細なオプションを設定します。
マウスを使った操作では、[ソルバー : オプション設定] ダイアログ ボックスで各オプションを指定することに相当します。
均等再配置処理 VBAコード
Option Explicit Const 収束判定値 = 0.01 Dim flg中止 As Boolean 'flg中止判定用の変数 Sub reAllocate(範囲セル As String, 条件セル As String, 最小化セル As String) ' 均等再配置 '---------------------- '変数 '---------------------- Dim rngShift As Range '選択範囲 Dim SigmaSum As Double '分散計 Set rngShift = Range(範囲セル) '---------------------- '開始 '---------------------- SigmaSum = 99999 '分散初期値 '---------------------- '作業変数 '---------------------- Dim r, c, r2 As Long Dim n As Long Dim tmpSigmaSum As Double tmpSigmaSum = SigmaSum For n = 1 To 10 '繰り返し回数 For c = 1 To rngShift.Columns.Count '選択範囲の列数 For r = 1 To rngShift.Rows.Count '選択範囲の行数 For r2 = r + 1 To rngShift.Rows.Count '----------------------------- DoEvents If flg中止 Then GoTo MyAbort End If '----------------------------- Call MySwap(rngShift(r, c), rngShift(r2, c)) 'セル入れ替え If Range(条件セル).Value <> 0 Then '条件を満たすか? Call MySwap(rngShift(r, c), rngShift(r2, c)) '満たさない→戻す Else Dim tmp tmp = Range(最小化セル).Value '最小値取り出し If tmp < SigmaSum Then '値更新? SigmaSum = tmp '新たな最小値とする Else Call MySwap(rngShift(r, c), rngShift(r2, c)) '戻す End If End If Next r2 Next r Next c If Abs(tmpSigmaSum - SigmaSum) < 収束判定値 Then Exit For '収束 tmpSigmaSum = SigmaSum Next n '-------------------------------------- '完了 '-------------------------------------- GoTo MyEnd MyAbort: MyEnd: End Sub Private Sub MySwap(Rng1 As Range, Rng2 As Range) '----------------- 'セルデータ入替 '----------------- Dim tmp As Variant tmp = Rng1.Value Rng1.Value = Rng2.Value Rng2.Value = tmp End Sub Private Function IsRange(RangeName As String) As Boolean ' 範囲名の正当性チェック On Error GoTo MyEnd IsRange = False If Range(RangeName).Address <> "" Then IsRange = True 'アドレスを取得できたら正しい! MyEnd: On Error GoTo 0 End Function
定義した名前
次項では、不可勤務をリスボックスから任意の組み合わせで入力できるようにします。