[PR]当サイトはアフィリエイト広告を利用しています。
今回はエクセルのソルバーをVBAで操作して、繰り返し処理でソルバーを繰り返し実行し、繰り返しごとに初期値を変えて複数解を自動探索するようにします。
最終的にはコマンドボタンと連携させて、ボタンを押したらソルバーが自動で起動して上記の処理を行うようにします。
今回の記事により、以下のようなお悩みが解決することを目標とします。
- 複数解の探索で何度もソルバーのダイアログを操作する面倒さを解消したい
- そもそもVBAでソルバーを動かす方法が知りたい
- ボタンを押したらソルバーが自動起動して勝手に最適解を出力してほしい
ソルバーで複数解を求める方法については以下の記事で取り扱いました。
●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】
このとき複数解を求めるために何度も初期値を手動で変えて、ソルバーのダイアログを毎回設定する必要があります。これが面倒です。
またいつも同じ設定で実行するならコマンドボタンにマクロを連携させて、ボタンを押したらいつもの設定でソルバーを実行したいという需要もあるかと思います。
そういうときにVBAでソルバーが使えれば便利です。
今回はソルバーをVBAで繰り返し実行する例題を通して、これらの疑問を解消するヒントを得られるようにします。
なおそもそもソルバーをどう使うのかという基礎的な内容は以下をご覧ください。
今回作成したVBAプログラム
まず今回作成した基礎となるVBAプログラムをお示しします。それについて一つずつ解説していくという内容とします。
なおVBAの基礎から学習したいというときはこちらもご覧いただければと思います。
●プログラマーの国家資格を持つ筆者が教えるVBAの独学ロードマップ
今回の要である繰り返し処理の基礎は以下をご覧ください。
それでは今回の基礎となるプログラムです。
Sub multiopt()
Dim i As Integer
Dim j As Integer
Dim judge As Boolean
Dim count As Integer
Dim x(100) As Double
Dim f(100) As Double
For i = 0 To 12
Worksheets("Sheet1").Activate
SolverReset
Range("B3").Value = i * 90
SolverOK SetCell:=Range("C3"), _
MaxMinVal:=2, _
ByChange:=Range("B3"), _
Engine:=1
SolverAdd CellRef:=Range("B3"), _
Relation:=1, _
FormulaText:=1080
SolverAdd CellRef:=Range("B3"), _
Relation:=3, _
FormulaText:=0
SolverSolve UserFinish:=True
x(i) = Range("B3")
f(i) = Range("C3")
Next i
count = 0
For i = 0 To 12
If count = 0 Then
judge = func1(x(i), count)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
Else
judge = func1(x(i), count - 1)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
End If
Next i
End Sub
Function func1(x As Double, count As Integer)
Dim xb(100) As Double
Dim i As Integer
Dim judge As Boolean
judge = False
For i = 0 To count
xb(i) = Cells(3 + i, 5)
Next i
For i = 0 To count
If Abs(xb(i) - x) < 0.001 Then
judge = True
Exit For
End If
Next i
func1 = judge
End Functionエクセルのシートに入力する内容は以下となります。
- B3：0
- C3：=COS(B3*PI()/180)
コサインカーブを0度から1080度まで、つまり3回転させて、そのうちの最小値-1が三回出てくる180度、540度、900度の3つの解を求める内容となります。最適化問題に定式化すると以下のようになります。
- 目的関数：\(y=\cos x\)→最小化
- 制約条件：\(0 \leq x \leq 1080\)
- 変数：\(x\)
それでは一つずつ解説していきます。
VBAでソルバーを実行する方法
基本的なことはすべてマイクロソフトが運営するページに書いてあります。今回扱った内容以外に細かい調整がしたい場合は以下のページから目的の項目をお読みいただければと思います。
まずVBAでソルバー関係の関数が使えるようにする
以下の設定をすることでVBAでソルバー用の関数を使えるようになります。ツールから参照設定をクリックします。
現れたダイアログで「Solver」にチェックを入れて「OK」を押します。
SolverReset関数
VBAでソルバーを実行するときはまずSolverReset関数を記述します。
この関数はこの関数が実行されるタイミングでソルバーの条件式などの設定をすべてリセットする関数です。
この関数を記述しない場合、条件式などを追加すると、前の設定条件に条件式を雪だるま式に追加していくことになり、ソルバーの挙動がどんどん不安定になっていきます。
繰り返し処理でこの関数を記述しないと致命的なので忘れずに記述するようにしてください。
SolverOK関数
目的セルや変数セル、それを最小化するか最大化するか、ソルバーのエンジンをどれにするかなどを設定します。
- SetCell：目的セルを記述します
- MaxMinVal：1で最大化、2で最小化、3で値と一致
- ByChange：変数セルを記述します
- Engine：1でGRG非線形、2でシンプレックスLP、3でエボリューショナリーです
SolverAdd関数
条件の追加に相当します。
- CellRef：条件を適用するセルを指定します
- Relation：1で「<=」、2で「=」、3で「>=」、4で整数、5でバイナリ、6でdifferentです
- FormulaText：条件の右側、Rerationで1から3を選択した場合は数値を指定します。4から6の場合は省略可です
SolverSolve関数
ソルバーを実行する関数です。ソルバーダイアログで「解決」ボタンを押すことに相当します。
VBAでソルバーを繰り返し実行するためには、結果ダイアログを解決のたびに表示していると処理が止まってしまうので、UserFinishをTrueに設定します。
- UserFinish：Trueで結果ダイアログを非表示、Falseで表示です
プログラムの解説
ここから最初に示したプログラムの解説をしていきます。
繰り返しのたびに初期値を変更する
For i = 0 To 12
Worksheets("Sheet1").Activate
SolverReset
Range("B3").Value = i * 90今回は初期点を12点、90度刻みで配置することにします。より細かい刻みにするときは適宜繰り返し回数や初期点の配置方法を工夫しましょう。
Range(“B3”).Value = i * 90
この部分で変数セルのB3に繰り返しのたびに90度の倍数を設定します。
この一文でB3の値が変わるので、この後にソルバーのSolverOK関数のByChangeを設定すれば、変更済みのB3の値を読み込むことができます。
結果を配列に格納する
SolverSolve UserFinish:=True
x(i) = Range("B3")
f(i) = Range("C3")SolverSolve関数の後に結果が入ったB3とC3セルの値を配列xとfに格納しておきます。今回は要素数100個の配列に入れましたが、動的に要素を追加してもよいですし、繰り返し数が12なので要素数は12個でもかまいません。問題に合わせて適宜工夫しましょう。
結果が入った配列から、同じ結果を除外して違う結果だけセルに出力する
ソルバーの結果はすべてxとfに格納されるので、あとはそれをどうセルに反映させるかという問題になります。
すべての結果をセルに出力して目視で違うか同じか判定しても良いですが、今回はそれを自動化します。
Function func1(x As Double, count As Integer)
Dim xb(100) As Double
Dim i As Integer
Dim judge As Boolean
judge = False
For i = 0 To count
xb(i) = Cells(3 + i, 5)
Next i
For i = 0 To count
If Abs(xb(i) - x) < 0.001 Then
judge = True
Exit For
End If
Next i
func1 = judge
End Function同じ値が出力セルに存在するかどうかという結果を返すfunc1関数です。ソルバーでのユーザー定義関数の書き方は以下もご覧ください。
この関数は出力セルとして採用したE列とF列のすべての最適解の中に引数xが存在すればTrue、存在しなければFalseを返します。
E列とF列に出力した最適解とそのときの目的関数の値の数を変数countで数えるのですが、最初の一回のときだけ挙動が違うのでそれ用に場合分けして関数を記述しています。
Abs(xb(i) – x) < 0.001
この部分は出力セルの情報が入ったxb配列の中の値と引数で比較しているxの値の差を求めています。差が小さい、つまり差の絶対値が小さいなら同じ解と考えます。
なぜ同値かどうかつまり「=」演算子を使わないかという部分ですが、GRG非線形の最適解はソルバーの停止条件が微分した値が0にかなり近いかどうかという基準となっています。
完全な0ではないので、出力される最適解は真の最適解（局所解）にそれなりに近いけど厳密解ではない値となります。
つまり同じ最適解で止まっているはずなのに、出力結果が微妙に違う解がVBAでのソルバーの繰り返しの中で多数現れます。
なので今回はある程度同じ値なら同じと判定することにしています。それがAbs(xb(i) – x) < 0.001の意味です。
得られた最適解の情報から実際にセルに出力する
For i = 0 To 12
If count = 0 Then
judge = func1(x(i), count)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
Else
judge = func1(x(i), count - 1)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
End If
Next iこの部分で実際に最適解をシートに出力します。関数func1で同じ解があったかどうかを判定して、あったらTrueで何もしない、Falseなら新しい解ありと判定してE列とF列に出力します。
コマンドボタンにVBAを記述する
ここからは上のVBAプログラムをコマンドボタンに移植して、コマンドボタンを押したら自動的に繰り返し処理が実行されるようにします。
コマンドボタンの配置
エクセルのシートに戻って、開発タブの「挿入」からActiveXコントロールのコマンドボタンをクリックします。フォームコントロールのボタンではないので注意しましょう。
シート上でドラッグするとボタンを描画できます。
配置したコマンドボタンで右クリックしてコマンドボタンオブジェクトの編集をクリックします。
ラベルを入力できるのでラベル名を入力します。今回は「開始」というラベルにしました。
ボタンにVBAを記述する
続いてこのコマンドボタンをダブルクリックします。ボタンのVBA記述モードになります。このダイアログに記述したVBAがボタンを押したときに実行されることになります。
ここに上で解説してきたVBAのコードを参考に次のように入力します。
Private Sub CommandButton1_Click()のところにSubの内容をコピペ、Functionは元と同じコードとなります。
Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim judge As Boolean
Dim count As Integer
Dim x(100) As Double
Dim f(100) As Double
For i = 0 To 12
Worksheets("Sheet1").Activate
SolverReset
Range("B3").Value = i * 90
SolverOK SetCell:=Range("C3"), _
MaxMinVal:=2, _
ByChange:=Range("B3"), _
Engine:=1
SolverAdd CellRef:=Range("B3"), _
Relation:=1, _
FormulaText:=1080
SolverAdd CellRef:=Range("B3"), _
Relation:=3, _
FormulaText:=0
SolverSolve UserFinish:=True
x(i) = Range("B3")
f(i) = Range("C3")
Next i
count = 0
For i = 0 To 12
If count = 0 Then
judge = func1(x(i), count)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
Else
judge = func1(x(i), count - 1)
If judge = False Then
Cells(3 + count, 5) = x(i)
Cells(3 + count, 6) = f(i)
count = count + 1
End If
End If
Next i
End Sub
Function func1(x As Double, count As Integer)
Dim xb(100) As Double
Dim i As Integer
Dim judge As Boolean
judge = False
For i = 0 To count
xb(i) = Cells(3 + i, 5)
Next i
For i = 0 To count
If Abs(xb(i) - x) < 0.001 Then
judge = True
Exit For
End If
Next i
func1 = judge
End Functionデザインモードを解除してボタンを押す
コピペしたらシートに戻って、ボタンを押してみましょう。ただしそのままだとデザインモードになっていてボタンが押せないので、開発タブの「デザインモード」ボタンを押して解除します。
コマンドボタンが押せるようになるので押してVBAを実行します。
3つの最適解が出力されます。
まとめ【VBAでソルバーの繰り返しを自動化できる】
今回はエクセルのソルバーをVBAで操作して、繰り返し処理でソルバーを繰り返し実行し、繰り返しごとに初期値を変えて複数解を自動探索するようにしました。
少しVBAが使えるならできると思います。
エボリューショナリーの場合もソルバー1回当たりの求解時間が長いので、何回も値を変えて実行するときなどに、今回の手法を使うことで、パソコンを稼働させておくだけで、何回もソルバーダイアログを手動設定する手間から解放されます。
VBAでソルバーを繰り返し実行したいという悩みを持つ方の参考になりましたら幸いです。
VBAでソルバーが使えると用途が広がります
Office最新版が常に使えます。楽天なら購入するとポイントも
当ブログ（シルルスのコードおきば）ではエクセルソルバー関係の記事を他にも執筆しています。参考になりましたら幸いです。
●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】
●エクセルソルバーと組み合わせ最適化問題【解法例と基礎知識】
