MENU
PCガジェットはいつでも安いAmazonで！
  1. ホーム
  2. Excelソルバー
  3. ソルバーをVBAで繰り返す【初期値を変えて複数解を自動探索】

ソルバーをVBAで繰り返す【初期値を変えて複数解を自動探索】

Excelソルバー
ソルバー 数理最適化

＼HP 秋の超得セール 実施中／

icon icon
HP公式オンラインストアはこちらから icon

今回はエクセルのソルバーをVBAで操作して、繰り返し処理でソルバーを繰り返し実行し、繰り返しごとに初期値を変えて複数解を自動探索するようにします。

最終的にはコマンドボタンと連携させて、ボタンを押したらソルバーが自動で起動して上記の処理を行うようにします。

今回の記事により、以下のようなお悩みが解決することを目標とします。

  • 複数解の探索で何度もソルバーのダイアログを操作する面倒さを解消したい
  • そもそもVBAでソルバーを動かす方法が知りたい
  • ボタンを押したらソルバーが自動起動して勝手に最適解を出力してほしい

ソルバーで複数解を求める方法については以下の記事で取り扱いました。

●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】

このとき複数解を求めるために何度も初期値を手動で変えて、ソルバーのダイアログを毎回設定する必要があります。これが面倒です。

またいつも同じ設定で実行するならコマンドボタンにマクロを連携させて、ボタンを押したらいつもの設定でソルバーを実行したいという需要もあるかと思います。

そういうときにVBAでソルバーが使えれば便利です。

今回はソルバーをVBAで繰り返し実行する例題を通して、これらの疑問を解消するヒントを得られるようにします。

なおそもそもソルバーをどう使うのかという基礎的な内容は以下をご覧ください。

●エクセルソルバーとは【使い方と数理最適化の基礎】

＼マウスコンピューターのセール！／

※LibreOffice Calcでのプログラムによる繰り返しについては以下で解説しています。

あわせて読みたい
Calcのソルバーをプログラムで繰り返す【初期値を変えて複数解を自動探索】 今回はLibreOffice Calcの非線形ソルバーをプログラムで繰り返して複数解を自動探索してみるという内容です。 マクロで自動的にソルバーを回したい 以前複数解を自動探...
目次

今回作成した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でソルバー関係の関数が使えるようにする

以下の設定をすることで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関数です。ソルバーでのユーザー定義関数の書き方は以下もご覧ください。

●【VBA入門】関数の作り方

この関数は出力セルとして採用した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最新版が常に使えます。楽天なら購入するとポイントも

当ブログ（シルルスのコードおきば）ではエクセルソルバー関係の記事を他にも執筆しています。参考になりましたら幸いです。

●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】

●エクセルソルバーと組み合わせ最適化問題【解法例と基礎知識】

●エクセルソルバーとは【使い方と数理最適化の基礎】

●ソルバーで高校の化学平衡の問題を解いてみる

●輪作作物をソルバーで決定

●Calcのソルバーでどこまでできるか実験

●巡回セールスマン問題をソルバーで解く

●Support Vector Regressionをエクセルのソルバーで作ってみた

●移動販売をどこに出店するかソルバーで求める

●ソルバーで正規分布の90%範囲を求める

●ソルバーとユーザー定義関数の連携

＼マウスコンピューターのセール！／

Excelソルバー
ソルバー 数理最適化
目次