[PR]当サイトはアフィリエイト広告を利用しています。
VBAプログラム上でソルバーを動かすことはできるのですが、ソルバーの試行一回ごとにVBAプログラムを動かすにはどうすればいいのかと思っていた時期があります。
しかしユーザー定義関数というのを覚えることで、この問題がかなり改善されました。
ユーザー定義関数の引数にソルバーの変数セルを指定して、ユーザー定義関数の処理にVBAプログラムを書いて、ユーザー定義関数をエクセルのシートで呼び出して、ソルバーを実行。
こう書くと何やら面倒な感じですが、解説していきます。
この方法を使えば複雑な計算はVBAで処理して、最適化の部分はソルバーに任せるということがかなりの部分でできるようになるので、個人的には結構な発見でした。
例題として以前SPF材の最小切り出し問題で取り上げた最適化問題を使います。
部品は
① 20cm×2
② 40cm×2
③ 60cm×2
④ 80cm×2
⑤ 94cm×2
⑥ 60cm×12
の6種類22個です。
B1セルからB22セルまで、部品の長さを入力します。
A1セルからA22セルまでは適当に1から22まで番号を振ります。
このA列の数値を変数セルにしてソルバーを実行します。
以前はIf関数などを駆使して目的セルの値を計算していましたが、ここをユーザー定義関数を利用してVBAで計算していきます。
作成したユーザー定義関数が次のようになります。
Option Explicit
Function CalcLength(arg As Range, Material As Range)
Dim materialList() As Integer
Dim materialNum As Integer
materialNum = arg.Rows.count
ReDim materialList(materialNum - 1)
Dim i As Integer
For i = 0 To materialNum - 1
materialList(i) = Material(arg(i + 1, 1), 1)
Next i
Dim count As Integer
count = 0
Dim countLength As Integer
countLength = 0
For i = 0 To materialNum - 1
countLength = countLength + materialList(i)
If countLength > 180 Then
count = count + 1
countLength = materialList(i)
End If
Next i
CalcLength = count * 180 + countLength
End Function解説していきます。
Function CalcLength(arg As Range, Material As Range)
Dim materialList() As Integer
Dim materialNum As Integer
materialNum = arg.Rows.count
ReDim materialList(materialNum - 1)3行目がユーザー定義関数のCalcLength関数の宣言です。引数は二つ。
変数セルA列のargと材料セルB列のMaterialです。
Rangeオブジェクトとして受け取ります。
4行目が材料の並びを格納する配列materialListです。
5行目と6行目で材料の個数を格納する変数materialNumに、材料の個数を格納します。
7行目でmaterialListの配列数を設定しています。materialNum個の配列になります。
Dim i As Integer
For i = 0 To materialNum - 1
materialList(i) = Material(arg(i + 1, 1), 1)
Next i8行目でカウント用変数iを定義しています。
9行目から11行目で並びを算出しています。
例えば変数セルのA1が3なら、materialList(0)はMaterial(3,1)となり40が入ります。
Dim count As Integer
count = 0
Dim countLength As Integer
countLength = 0
For i = 0 To materialNum - 1
countLength = countLength + materialList(i)
If countLength > 180 Then
count = count + 1
countLength = materialList(i)
End If
Next i12行目から15行目でSPF材の本数を格納する変数countと材木数を数えるときに使う変数countLengthを0で初期化しています。
16行目から22行目で現在の材木の長さが180を超えたらcountを一つ足すという処理をしています。
例えばmaterialList(0)が80でmaterialList(1)が94だとcountLengthは174でcountは増えませんが、次のmaterialList(2)が60だと、countLengthは234となり、If文が実行されてcountが増えて、countLengthが60で再設定されます。
この処理をすべての変数セルで実行します。
CalcLength = count * 180 + countLength
End Function最後にユーザー定義関数の出力として、使用したSPF材の全体の長さを指定しています。
使った本数×材木の元の長さ+最後の材木の長さで求めています。
これが最小になれば最小の切り出し方が求まります。
問題はソルバーが試行するときにその都度ユーザー定義関数を実行してくれるかでした。
やってみたらこうなりました。
目的セルの値が1320となり、前回関数を駆使したときと同じ値となりました。
以下やり方です。
D1セルに
=CalcLength(A1:A22,B1:B22)
と入力します。
引数のセル指定のところは、セルをドラッグして指定できます。
ソルバーの設定は以下です。
目的セルの設定：$D$1
目標値：最小値
変数セルの設定：$A$1:$A$22
制約条件の対象：
$A$1:$A$22 <= 22 $A$1:$A$22 >= 1
$A$1:$A$22 = AllDifferent
$A$1:$A$22 = 整数
解決方法の選択：エボリューショナリー
これ、結構すごいなと思いました。
今まではセル上で関数を駆使してしかソルバー用の色々な計算はできないんじゃないかと思っていて、複雑な計算をするときは最適化アルゴリズムを自分で作成するとか、Pythonの最適化ライブラリを使って計算は自作プログラムとかしかないかなと思っていたのですが、ソルバーでかなりできるんじゃないかなと。
まあPythonのライブラリみたいに、複雑な処理を簡単に求めるというのはどうだろとは思います。ライブラリの内容を自分でプログラミングしないといけないかもしれませんし。
でも個人的には結構すごいなという感想です。
以上「ソルバーとユーザー定義関数の連携」でした。
当ブログ（シルルスのコードおきば）ではエクセルソルバー関係の記事を他にも執筆しています。参考になりましたら幸いです。
●ソルバーをVBAで繰り返す【初期値を変えて複数解を自動探索】
●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】
●エクセルソルバーと組み合わせ最適化問題【解法例と基礎知識】
