Excelソルバーによる最適化

Solver は、what-if分析の最適化に使用できるMicrosoftExcelアドインプログラムです。

オブライエンとマラカスによると、 optimization analysisは、ゴールシーク分析のより複雑な拡張です。変数に特定のターゲット値を設定する代わりに、特定の制約の下で1つ以上のターゲット変数に最適な値を見つけることが目標です。次に、ターゲット変数に最適な値が見つかるまで、指定された制約に従って、1つ以上の他の変数が繰り返し変更されます。

Excelでは、次を使用できます Solver を見つけるために optimal value ワークシート上の他の数式セルの値に対する、特定の制約または制限の対象となる、目的のセルと呼ばれる1つのセル内の数式の（最大または最小、または特定の値）。

これは、ソルバーが、目的セルと制約セルの数式の計算に使用される決定変数と呼ばれるセルのグループと連携することを意味します。ソルバーは、決定変数セルの値を調整して、制約セルの制限を満たし、目的のセルに必要な結果を生成します。

ソルバーを使用して、次のようなさまざまな問題の最適なソリューションを見つけることができます。

収益性を最大化する医薬品製造ユニットの毎月の製品構成を決定します。
組織内の従業員のスケジューリング。
輸送問題の解決。
財務計画と予算。

ソルバーアドインのアクティブ化

ソルバーの問題の解決策を見つける前に、次のことを確認してください。 Solver Add-in 次のようにExcelでアクティブ化されます-

リボンの[データ]タブをクリックします。ザ・Solver 以下に示すように、コマンドが分析グループに表示されます。

ソルバーコマンドが見つからない場合は、次のようにアクティブにします-

[ファイル]タブをクリックします。
左側のペインで[オプション]をクリックします。[Excelのオプション]ダイアログボックスが表示されます。
左側のペインで[アドイン]をクリックします。
[管理]ボックスで[Excelアドイン]を選択し、[移動]をクリックします。

[アドイン]ダイアログボックスが表示されます。小切手Solver Add-in[OK]をクリックします。これで、リボンの[データ]タブにある[ソルバー]コマンドを見つけることができるはずです。

ソルバーで使用される解法

問題の種類に基づいて、Excelソルバーがサポートする次の3つの解決方法のいずれかを選択できます-

LPシンプレックス

線形問題に使用されます。ASolver モデルは次の条件下で線形です-

ターゲットセルは、（変化するセル）*（定数）形式の項を合計することによって計算されます。
各制約は線形モデルの要件を満たしています。これは、（変化するセル）*（定数）形式の項を合計し、その合計を定数と比較することによって、各制約が評価されることを意味します。

一般化された減少勾配（GRG）非線形

滑らかな非線形問題に使用されます。ターゲットセル、制約のいずれか、またはその両方に、（変更セル）*（定数）形式ではない変更セルへの参照が含まれている場合は、非線形モデルがあります。

進化論

ソルバー評価を理解する

ソルバーには次のパラメーターが必要です-

決定変数セル
制約セル
目的のセル
解決方法

ソルバーの評価は以下に基づいています-

決定変数セルの値は、制約セルの値によって制限されます。
目的セルの値の計算には、決定変数セルの値が含まれます。
ソルバーは、選択した解決方法を使用して、目的のセルに最適な値をもたらします。

問題の定義

特定の製品を製造および販売している会社の利益を分析しているとします。最大20,000を条件として、次の2四半期に広告に費やすことができる金額を見つけるように求められます。各四半期の広告のレベルは、以下に影響します-

販売されたユニットの数。間接的に販売収益額を決定します。
関連する費用、および
利益。

問題を次のように定義することができます-

ユニットコストを見つけます。
ユニットあたりの広告費用を見つけます。
単価を検索します。

次に、必要な計算のセルを以下のように設定します。

ご覧のとおり、Quarter1とQuarter2の計算は次のように行われます。

Quarter1で販売可能なユニット数は400で、Quarter2では600です（セル– C7およびD7）。
広告予算の初期値は、四半期ごとに10000に設定されています（セル– C8およびD8）。
販売されたユニットの数は、ユニットあたりの広告費用に依存するため、四半期の予算/ Adv。ユニットあたりのコスト。最小関数を使用して、番号がないことを確認していることに注意してください。<=いいえで販売されたユニットの利用可能なユニットの。（セル– C9およびD9）。
収益は、単価*販売されたユニット数（セル– C10およびD10）として計算されます。
費用は、ユニットコスト*利用可能なユニット数+アドバンスとして計算されます。その四半期のコスト（セル– C11およびD12）。
利益は収益–費用（セルC12およびD12）です。
総利益は、Quarter1の利益+ Quarter2の利益（セル– D3）です。

次に、ソルバーのパラメーターを以下のように設定できます。

ご覧のとおり、ソルバーのパラメーターは次のとおりです。

目的のセルは、最大化したい総利益を含むD3です。
決定変数セルはC8とD8で、Quarter1とQuarter2の2つの四半期の予算が含まれています。
C14、C15、C16の3つの制約セルがあります。
- 総予算を含むセルC14は、20000の制約を設定するためのものです（セルD14）。
- を含むセルC15。Quarter1で販売されるユニットの数は、<= noの制約を設定することです。Quarter1で使用可能なユニットの数（セルD15）。
- を含むセルC16。Quarter2で販売されるユニットの数は、<= noの制約を設定することです。Quarter2で使用可能なユニットの数（セルD16）。

問題の解決

次のステップは、ソルバーを使用して次のように解を見つけることです。

Step 1−リボンの[データ]> [分析]> [ソルバー]に移動します。[ソルバーパラメータ]ダイアログボックスが表示されます。

Step 2 − [目標の設定]ボックスで、セルD3を選択します。

Step 3 − [最大]を選択します。

Step 4 −範囲C8：D8を選択します By Changing Variable Cells ボックス。

Step 5 −次に、[追加]ボタンをクリックして、特定した3つの制約を追加します。

Step 6− [制約の追加]ダイアログボックスが表示されます。以下に示すように合計予算の制約を設定し、[追加]をクリックします。

Step 7−合計数の制約を設定します。以下に示すように、Quarter1で販売されたユニットの数を入力し、[追加]をクリックします。

Step 8−合計数の制約を設定します。下記のQuarter2で販売されたユニットの数を入力し、[OK]をクリックします。

[ソルバーパラメータ]ダイアログボックスが表示され、[制約の対象]ボックスに3つの制約が追加されます。

Step 9 −で Select a Solving Method ボックスで、シンプレックスLPを選択します。

Step 10− [解決]ボタンをクリックします。[ソルバー結果]ダイアログボックスが表示されます。選択するKeep Solver Solution [OK]をクリックします。

結果はワークシートに表示されます。

ご覧のとおり、与えられた制約に従って最大の総利益を生み出す最適解は次のようになります。

総利益–30000。
アドバンス Quarter1 –8000の予算。
アドバンス四半期2〜12000の予算。

ソルバートライアルソリューションのステップスルー

反復結果を確認しながら、ソルバーのトライアルソリューションをステップスルーできます。

Step 1 − [ソルバーパラメータ]ダイアログボックスの[オプション]ボタンをクリックします。

ザ・ Options ダイアログボックスが表示されます。

Step 2 − [反復結果の表示]ボックスを選択し、[OK]をクリックします。

Step 3 − Solver Parametersダイアログボックスが表示されます。クリックSolve。

Step 4 − Show Trial Solution ダイアログボックスが表示され、メッセージが表示されます- Solver paused, current solution values displayed on worksheet。

ご覧のとおり、現在の反復値が作業セルに表示されます。ソルバーが現在の結果を受け入れるのを停止するか、ソルバーが次のステップで解決策を見つけないようにすることができます。

Step 5 − [続行]をクリックします。

ザ・ Show Trial Solutionすべてのステップでダイアログボックスが表示され、最後に最適解が見つかった後、ソルバー結果ダイアログボックスが表示されます。ワークシートはすべてのステップで更新され、最終的に結果値が表示されます。

ソルバー選択の保存

ソルバーで解決する問題には、次の保存オプションがあります-

ワークブックを保存することにより、ワークシートを使用して[ソルバーパラメーター]ダイアログボックスの最後の選択を保存できます。
ブック内の各ワークシートには独自のソルバー選択を含めることができ、ブックを保存するとそれらすべてが保存されます。
ワークシートに複数の問題を定義し、それぞれに独自のソルバーを選択することもできます。このような場合、[ソルバーパラメータ]ダイアログボックスの[ロード/保存]を使用して、問題を個別にロードおよび保存できます。
- クリック Load/Saveボタン。[ロード/保存]ダイアログボックスが表示されます。
- 問題のあるモデルを保存するには、問題のあるモデルを配置する空のセルの垂直範囲の最初のセルの参照を入力します。[保存]をクリックします。

問題のモデル（ソルバーパラメーターセット）は、参照として指定したセルから始まります。

問題のあるモデルを読み込むには、問題のあるモデルを含むセルの全範囲の参照を入力します。次に、[ロード]ボタンをクリックします。