ExcelのソルバーをVBAで自動化する方法

2020.02.23

みなさん，こんにちは。
シンノユウキ（shinno1993）です。

Excelにはソルバーというとても便利な機能があります。

しかし，多くの制約条件を入力する際，手作業だとかなり大変な作業になりますし，何より誤入力が起こりやすくなります。

そこで今回は，Excelで利用できるマクロ言語であるVBAを利用して，自動化する方法を紹介します。

ExcelのソルバーをVBAで自動化するためには

ソルバー機能自体には複雑な設定は必要ありません。

基本的なパラメータの設定→解決までができればOKかと思います。
それを実行するためのVBA関数は以下の通りです：

基本的なソルバー機能と対応するVBA関数
  • SolverOk関数：変数セル・目的セルを設定する
  • SolverAdd関数：制約条件を追加す
  • SolverSolve 関数：問題を解決する

以下で，これらの関数の基本的な使い方について紹介していきます。

はじめに：ライブラリの参照設定を行う

ソルバーに関連するVBA関数を利用するためには，まずソルバーアドインへの参照を有効にする必要があります。

VBEを開き，「ツール」→「参照設定」からダイアログを開き，「Solver」にチェックを入れて「OK」をクリックしてください。

これでソルバーに関連したVBA関数が使用可能になります。

SolverOk関数：変数セル・目的セルを設定する

SolverOk関数を利用することで，変数セル・目的セルを設定することができます。

SolverOk setCell:=目的セル, _
         MaxMinVal:=目標値, _
         ByChange:=変数セル, _
         Engine:=解決方法
  • setCell：目的セルを指定します。Range("A1") のように単一のセルを指定してください。
  • MaxMinVal：目的セルの目標値を数値で指定します。最大値：1，最小値：2，指定値：3を指定します。もしここで指定値を選択した場合，別途引数として ValueOf も指定する必要がありますが，その解説は省略します。
  • ByChange：変数セルを指定します。Range("A1")単一のセルか，Range("A1:A3")のように複数のセル範囲を指定します。
  • EngineDesc：問題の解決に使用する解決方法を文字列で指定します。GRG非線形："GRG Nonlinear"，シンプレックスLP："Simplex LP"，エボリューショナリー："Evolutionary"を指定します。

SolverAdd関数：制約条件を追加する

SolverAdd関数を利用することで，変数セル・目的セルを設定することができます。

SolverAdd cellRef:=セル参照, _ 
          Relation:=比較方法, _
          FormulaText:=制約条件
  • cellRef：制約条件で参照するセルを指定します。単一のセルを選択します。
  • Relation：参照するセルと制約条件との関係を指定します。これには次の６つがあり，それぞれ関連付けられた整数で指定します。<=：1，=：2，>=：3，int（整数）：4，bin（バイナリ）：5，def（異なる）：6，を指定します。
  • FormulaText：制約条件を指定します。数値を直接指定します。

SolverSolve関数：問題を解決する

SolverSolve関数を利用することで，実際に問題を解くことができます。返り値として実行結果が返ってきますが，もし結果ダイアログを表示する場合は返り値を利用する必要性は薄いように思います。

SolverSolve UserFinish:=結果ダイアログを表示するか？
  • UserFinish：結果を示すダイアログを表示するかを選択できます。Trueで表示しない，Falseで表示します。

実際のサンプルコード

では，以下のシンプルなシートを参考に，実際にソルバーを解くサンプルコードを提示してみたいと思います。なお，下記のシートは，以下の記事からも参照・DL等が行えます：

Excelでソルバーを使って線形計画問題（栄養問題）を解く
Excelを用いて最適化問題を解く方法を解説する連載の２回目。前回はExcelにソルバーアドインを追加する方法を紹介しました。今回は実際に，最適化問題のうちの線形計画問題を栄養を例に紹介したいと思います。では行きましょう！前回やったこと前回...
www.y-shinno.com
2020.01.31

実際のコードは下記のようになります：

Enum eMaxMinVal
    Maximize = 1
    Minimize
    Specific
End Enum

Enum eCol
    Refer = 2
    Relation = 3
    Formula = 4
End Enum
Sub main()
    Dim ws As Worksheet: Set ws = ActiveSheet
    '↑ソルバーでのパラメータの設定はシートに依存するので，
    '   ActiveSheetを指定しておくのが望ましいように思います。
    
    '（１）まずは既存のソルバーをリセット
    SolverReset
    
    '（２）目的セル，変数セルの設定
    SolverOk setCell:=ws.Range("A14"), _
                   MaxMinVal:=eMaxMinVal.Minimize, _
                   ByChange:=ws.Range("F3:F5"), _
                   EngineDesc:="Simplex LP"
    
    '（３）制約条件の設定
    Const startRow As Long = 9
    Const endRow As Long = 11
    Dim i As Long
    For i = startRow To endRow
        SolverAdd CellRef:=ws.Cells(i, eCol.Refer), _
                         Relation:=ws.Cells(i, eCol.Relation).Value, _
                         FormulaText:=ws.Cells(i, eCol.Formula).Value
    Next i
    
    '（４）実際に解く
    SolverSolve UserFinish:=False
End Sub

まとめ

今回はVBAを利用してExcelのソルバーを自動化するための方法を紹介しました。

今回のサンプルくらいのシートでしたら，手作業でやったとしても大したことないかもしれませんが，特に制約条件がもっと増えてくると，その恩恵も比例して増えてくるかと思います。

連載目次

  1. Excelに「ソルバー」アドインを追加しよう！
  2. Excelでソルバーを使って線形計画問題（栄養問題）を解く
  3. Excelでソルバーを使って整数計画問題（献立問題）を解く
  4. Excelでソルバーを使って食事最適化法を試してみる
  5. ExcelのソルバーをVBAで自動化する方法現在のページ
タイトルとURLをコピーしました