タグ別アーカイブ: 最適解

Solverによるシミュレーション5~マクロ編・設定~

 今回からはマクロによりソルバーの設定・解決自体の自動化に取り組んでいきましょう!

 前回までの4回の解説では、ソルバーの基本設定から実践的に活用する方法まで解説させて頂きました

 Solverによるシミュレーション

  Solverによるシミュレーション2

 Solverによるシミュレーション3~実践編・整数設定~

 Solverによるシミュレーション4~実践編・バイナリ設定~ 

 ソルバーをマクロで活用できるようになると、弱気ベース、強気ベースなど様々なパターンで算出した値を瞬時に比較できるので、より実践的なシミュレーションが行えるようになります!

 今回はソルバーのシナリオを2つ、記録マクロで保存してみてVBAコードを比較してみましょう!

そうするとコードの書き方がつかめてきます

その前に準備が1つあります

 事前にVBE(Alt+F11で開く画面)でSolverライブラリを参照可能にしておきましょう!

このライブラリが無いと、記録マクロが動きません!

では本題に入ります

今回は1~3まで以下の表を通じて解説していきます

 注)D4セルも同様の数式(X1xX2)が入力されています

注)D3セルのYはシナリオ1用、D4セルのYはシナリオ2用になります

1.シナリオ1記録(※X1,X2は4以下の制約にてYを最大化する)

➀以下のシナリオでマクロを記録

②VBEを開けて重複しているコードを削除し、コードを以下の画像のように3部構成にします

③表の値を1にセットし直してシナリオ1マクロを起動

もしもうまくいかなかったら、余計なコードまで消してしまったか、もしくは前述の”Solverライブラリ”にチェックが入っていない状態になっています


2.シナリオ2記録(X1,X2は2以上の制約にてYの値を4にする)

➀設定リセット

記録する際には、ソルバーの設定画面上に前回の設定が残っているのでリセット処理を行っておきましょう

②以下のシナリオでマクロを記録

③VBEを開けて重複しているコードを削除し、コードを4部構成にします

注)シナリオ1にも”SolverReset”を追加しておきましょう!

④表の値を1にセットし直してシナリオ2マクロを起動する

どうです?うまくソルバーは無事に稼働したでしょうか?

3.2つの記録マクロを比較してみる

大前提として2つのシナリオの違いをもう一度整理してみましょう!

➀目的セル、変数セル、及び制約条件を指定するセルの行位置が違う

②目標値の設定の仕方が違う;シナリオ1/最大値、シナリオ2/指定値

③制約条件の符号が違う:シナリオ1/<=、シナリオ2>=

どうでしょう?朧気ながらどのへんをVBAの変数で置き換えられそうかについてのイメージがついてきたと思います

 今回は記録マクロにとどまりましたが、次回は本格的にソルバー・マクロを作成していきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション6~マクロ編・操作~

 今回は、前の回にて作成した2つのマクロを書き換えて、本格的にソルバーを動かすマクロを作成します

前回作成した2つのマクロは、作成条件が次の3つの項目で違っていました

➀目的セル、変数セル、及び制約方法を指定するセルの行位置

目標値の設定の仕方;シナリオ1/「最大値」、シナリオ2/「指定値」

制約方法の指定:シナリオ1/「<=」、シナリオ2「>=」

記録したマクロのコードを比較して、上記の違いがどう反映されているかを見てみましょう!

シナリオ1
シナリオ2

上がシナリオ1で下がシナリオ2です

おおよそ、黄色の目印を付けたうち、どこが「変数」で置き換えられるか見当がついたと思います

変数で置き換えられる箇所をまとめると、次の通りになります

・SolverOK➡SetCell:「目的値のセル」

      ➡MaxMinVal:1は最大値、2は最小値、3は指定値

      ➡ValueOf:指定値がある場合に指定(無い場合は0)

・ByChange➡「変動する値:値範囲」

・ SolverAdd➡CellRef: 「制約条件の範囲」

      ➡Relation:制約方法の指定

       *下の図の順番で指定:etc 「<=」は1、「=」は2)


      ➡FormulaText:制約条件の指定           

今回は前回作成したシナリオ1マクロを以下の3つ、

1.SetCell(目的セル)、2.ByChange(変数セル)、3CellRef(制約条件セル)

それぞれを変数で置き換えてマクロを動かせるようにしましょう!

1.範囲を指定できるようにしておく

今回はC2セルで1を選んだら上の行を操作する(前回のシナリオ1)、2を選んだら下の行(前回のシナリオ2)を操作するようにします

次の2.ではシナリオ1の前に、IF構文を配置し、C2セルに応じて前述の1~3の範囲を動かせるようにします

If mySheet.Range(“C2”).Value = “1” Then ~ else ~

2.マクロに変数をセットしてマクロを実際に動かす

➀If構文で変数を可変にする

②ソルバーのコードに変数を設定する

各X1、X2を1に直しながら、マクロを試してみてください

<まとめ>

 今回は、前回操作した記録マクロの範囲(以下の3つの範囲)を事前に指定できるようにしました

1.SetCell(目的セル)、2.ByChange(変数セル)、3CellRef(制約条件セル)

 次回はいよいよ、ループ処理により、複数のシミュレーションを一気に走らせることができるようにします!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ソルバーによるシミュレーション7~マクロ編・ループ処理~

 今回はマクロ編の総仕上げとして、計算結果を示すダイアログボックスを表示させないまま、繰り返し処理の中でソルバーを動かせるようにします  

画像に alt 属性が指定されていません。ファイル名: 36.jpg

 前回は、前々回に作成した記録マクロを修正し、ソルバーの指定範囲(目的セル、変数セル、制約条件)を変数で指定できるようにしました(TargetRange、myRange)

前回作成マクロ

 繰返し処理とソルバーを組み合わせると、一度のマクロ操作で必要な値を一覧で取得できます。

 一覧で取得した値を比較することにより、 シミュレーションの精度を格段に向上させることができます

それはもう、AIを導入したかのうような感覚になりますよ!

ぜひ今回で繰り返し処理をしっかり習得しておきましょう!

今回、前回作成したマクロを引き続き使って、繰り返し処理をおこなっていくのですが、

実は今回、習得するポイントはあまり多くなく、1つだけです

!ソルバーのダイアログの表示をどう防ぐか?

ソルバーの計算が終わった後にダイアログが必ず表示されますよね?

この表示が出てしまうと繰り返し処理が止まってしまいます。

このダイアログの表示を止める方法については、後ほど解説致します。

 繰り返し処理自体のVBAコードの詳細な解説については、他の専門サイトや専門書に譲りたいと思います

目次

ソルバーの各指定範囲を変数で置換え

ソルバーのダイアログを非表示

実際に動かしてみる

<まとめ>

ソルバーの各指定範囲を変数で置換え

前回は2つの範囲をIF構文で切り替えて指定できるようにしました

今回は表の最終行(変数:Last)を取得した上で、カウントアップ用変数のiと連動してソルバーの対象範囲を変えていけるようにします

上の画像にあるmyRowはiと連動してカウントアップしていきます

以降は有料ページ/課金サービスへ