Excel-data-analysis-advanced-data-analysis-optimization-with-excel-solver
Excelソルバーによる最適化
オブライエンとマラカスによると、*最適化分析*は、目標探索分析のより複雑な拡張です。 変数に特定のターゲット値を設定する代わりに、特定の制約の下で1つまたは複数のターゲット変数に最適な値を見つけることが目標です。 次に、ターゲット変数に最適な値が見つかるまで、指定された制約に従って、1つ以上の他の変数が繰り返し変更されます。
Excelでは、 Solver を使用して、目的セルと呼ばれる1つのセル内の数式の*最適値*（最大値または最小値、または特定の値）を見つけることができます。ワークシート上のセル。
これは、ソルバーが、目的セルと制約セルの数式の計算に使用される決定変数と呼ばれるセルのグループで動作することを意味します。 ソルバーは、決定変数セルの値を調整して制約セルの制限を満たし、目的のセルに必要な結果を生成します。
あなたはソルバーを使用して、次のような多様な問題の最適な解決策を見つけることができます-
- 収益性を最大化する医薬品製造ユニットの毎月の製品構成を決定します。
- 組織内の労働力のスケジューリング。
- 輸送の問題を解決します。
- 財務計画と予算編成。
ソルバーアドインのアクティブ化
ソルバーを使用して問題の解決策を見つける前に、次のようにExcelで*ソルバーアドイン*がアクティブになっていることを確認してください-
- リボンの[データ]タブをクリックします。 以下に示すように、 Solver コマンドが分析グループに表示されます。
ソルバーコマンドが見つからない場合は、次のようにアクティブにします-
- [ファイル]タブをクリックします。
- 左ペインの[オプション]をクリックします。 [Excelオプション]ダイアログボックスが表示されます。
- 左ペインの[アドイン]をクリックします。 *[管理]ボックスで[Excelアドイン]を選択し、[実行]をクリックします。
[アドイン]ダイアログボックスが表示されます。 [ソルバーアドイン]をオンにして、[OK]をクリックします。 これで、リボンの[データ]タブの[ソルバー]コマンドを見つけることができるはずです。
ソルバーが使用する解法
問題の種類に基づいて、Excelソルバーがサポートする次の3つの解法のいずれかを選択できます-
LPシンプレックス
線形問題に使用されます。* ソルバー*モデルは、次の条件下で線形です-
- ターゲットセルは、（変化するセル）＆ast;（定数）形式の項を加算して計算されます。
- 各制約は線形モデルの要件を満たします。 これは、（変化するセル）＆ast;（定数）形式の項を加算し、合計を定数と比較することにより、各制約が評価されることを意味します。
一般化縮小勾配（GRG）非線形
滑らかな非線形問題に使用されます。 ターゲットセル、制約のいずれか、または両方に（変化するセル）＆ast;（定数）形式ではない変化するセルへの参照が含まれている場合、非線形モデルになります。
進化的
滑らかな非線形問題に使用されます。 ターゲットセル、制約のいずれか、または両方に（変化するセル）＆ast;（定数）形式ではない変化するセルへの参照が含まれている場合、非線形モデルになります。
ソルバー評価について
ソルバーには、次のパラメータが必要です-
- 決定変数セル
- 制約セル
- 対象細胞
- 解法
ソルバー評価は以下に基づいています-
- 決定変数セルの値は、制約セルの値によって制限されます。
- 目的セルの値の計算には、決定変数セルの値が含まれます。
- ソルバーは、選択した解法を使用して、目的のセルに最適な値をもたらします。
問題の定義
特定の製品を製造および販売する会社の利益を分析するとします。 次の2四半期に最大20,000件の広告掲載に費やすことができる金額を見つけるように求められます。 各四半期の広告のレベルは以下に影響します-
- 販売単位の数。販売収益の量を間接的に決定します。
- 関連する費用、および
- 利益。
あなたは問題を次のように定義することに進むことができます-
- 単価を検索します。
- ユニットあたりの広告費用を見つけます。
- 単価を検索します。
次に、以下に示すように、必要な計算のセルを設定します。
あなたが見ることができるように、計算は検討されているQuarter1とQuarter2のために行われます-
- No. Quarter1で販売可能なユニットの数は400で、Quarter2では600です（セル-C7およびD7）。
- 広告予算の初期値は、四半期ごとに10000に設定されています（セル-C8およびD8）。
- No. 販売単位の割合は、単位あたりの広告費用に依存するため、四半期/前売の予算です。 ユニットあたりのコスト。 Min関数を使用して、noを確認することに注意してください。 ⇐ noで販売されたユニットの 利用可能なユニットの。 （セル-C9およびD9）。
- 収益は単価として計算されます＆ast;いや 販売単位（セル-C10およびD10）。
- 費用は、ユニットコストとして計算されます＆ast;いや 使用可能なユニット数＆plus;前売 その四半期のコスト（セル-C11およびD12）。
- 利益は収入-費用（セルC12およびD12）です。
- 合計利益は、四半期1の利益＆plus; Quarter2の利益（セル-D3）。
次に、以下のようにソルバーのパラメーターを設定できます-
あなたが観察できるように、ソルバーのパラメータは次のとおりです-
- 目的セルは、最大化する合計利益を含むD3です。
- 決定変数セルはC8とD8で、2つの四半期（Quarter1とQuarter2）の予算が含まれています。
- C14、C15、C16の3つの制約セルがあります。
- 合計予算を含むセルC14は、20000の制約を設定します（セルD14）。
- を含むセルC15。 Quarter1で販売されたユニットの⇐ noの制約を設定します。 Quarter1で使用可能なユニット（セルD15）。
- を含むセルC16 Quarter2で販売されたユニットの⇐ noの制約を設定します。 Quarter2で使用可能なユニット（セルD16）。
問題を解決する
次のステップは、ソルバーを使用して次のように解決策を見つけることです-
- ステップ1 *-リボンの[データ]> [分析]> [ソルバー]に移動します。 [ソルバーパラメータ]ダイアログボックスが表示されます。
- ステップ2 *-[目的の設定]ボックスで、セルD3を選択します。
- ステップ3 *-最大を選択します。
- ステップ4 *-[変数セルの変更]ボックスでC8：D8の範囲を選択します。
- ステップ5 *-次に、追加ボタンをクリックして、特定した3つの制約を追加します。
- ステップ6 *-[制約の追加]ダイアログボックスが表示されます。 以下に示すように総予算の制約を設定し、[追加]をクリックします。
- ステップ7 *-合計番号の制約を設定します。 以下に示すように、Quarter1で販売されたユニットのうち、追加をクリックします。
- ステップ8 *-合計番号の制約を設定します。 以下に示すように、Quarter2で販売されたユニットのうち、OKをクリックします。
[ソルバーパラメーター]ダイアログボックスが表示され、[制約の対象]ボックスに3つの制約が追加されます。
ステップ9 *-[*解法の選択]ボックスで、[シンプレックスLP]を選択します。
- ステップ10 *-[解決]ボタンをクリックします。 [ソルバーの結果]ダイアログボックスが表示されます。 [ソルバーソリューションを保持]を選択し、[OK]をクリックします。
結果はワークシートに表示されます。
あなたが観察できるように、与えられた制約の対象となる、最大総利益を生み出す最適なソリューションは、次のように発見されます-
- 総利益– 30000。
- Adv. Quarter1の予算– 8000。
- Adv. Quarter2 – 12000の予算。
ソルバートライアルソリューションのステップスルー
ソルバーのトライアルソリューションをステップスルーして、反復結果を確認できます。
- ステップ1 *-[ソルバーパラメーター]ダイアログボックスの[オプション]ボタンをクリックします。
- オプション*ダイアログボックスが表示されます。
- ステップ2 *-[反復結果の表示]ボックスを選択し、[OK]をクリックします。
- ステップ3 *-*ソルバーパラメータ*ダイアログボックスが表示されます。 *解決*をクリックします。
ステップ4 *-*試用ソリューションの表示*ダイアログボックスが表示され、メッセージが表示されます-*ソルバーが一時停止し、現在のソリューション値がワークシートに表示されます
ご覧のとおり、現在の反復値が作業セルに表示されます。 ソルバーが現在の結果を受け入れないようにするか、ソルバーが次のステップで解決策を見つけるのを続行できます。
- ステップ5 *-[続行]をクリックします。
[試行ソリューションの表示]ダイアログボックスがすべてのステップで表示され、最終的に最適なソリューションが見つかった後、[ソルバー結果]ダイアログボックスが表示されます。 ワークシートはすべてのステップで更新され、最終的に結果値が表示されます。
ソルバー選択の保存
あなたはソルバーで解決する問題のための次の保存オプションがあります-
- ワークブックを保存することにより、ソルバーパラメータダイアログボックスでの最後の選択をワークシートとともに保存できます。
- ワークブック内の各ワークシートには、独自のソルバー選択項目を含めることができ、ワークブックを保存すると、すべてのワークシートが保存されます。
- また、ワークシートに複数の問題を定義し、それぞれに独自のソルバーを選択することもできます。 このような場合、ソルバーパラメーターダイアログボックスのロード/保存を使用して、問題を個別にロードおよび保存できます。
- [ロード/保存]ボタンをクリックします。 [ロード/保存]ダイアログボックスが表示されます。
- 問題モデルを保存するには、問題モデルを配置する空のセルの垂直範囲の最初のセルの参照を入力します。 保存をクリックします。
- 問題モデル（ソルバーパラメーターセット）は、参照として指定したセルから始まります。
- 問題モデルをロードするには、問題モデルを含むセル範囲全体の参照を入力します。 次に、[ロード]ボタンをクリックします。
