Excel2019ソルバーの使用方法

Excelのデータテーブルコマンドとゴールシークコマンドは、数式の入力と結果の間の直接的な関係を決定する必要がある単純な問題には問題なく機能しますが、より複雑な問題を処理する場合はソルバーアドインを使用する必要があります。たとえば、Excelモデルで複数の入力値を変更する必要があり、これらの値や出力値に制約を課す必要がある場合は、ソルバーを使用して最適なソリューションを見つけます。

ソルバーアドインは、反復法を適用して、入力、目的のソリューション、および課す制約を考慮して「最良の」ソリューションを見つけることによって機能します。プログラムは、反復ごとに、（線形または非線形の方程式と不等式の使用に基づく）試行錯誤の方法を適用して、最適な解に近づこうとします。

ソルバーアドインを使用するときは、多くの問題、特により複雑な問題には多くの解決策があることに注意してください。ソルバーは、開始値、変更可能な変数、および定義した制約を考慮して最適なソリューションを返しますが、多くの場合、このソリューションだけが可能なソリューションではなく、実際には最適なソリューションではない可能性があります。最適なソリューションを確実に見つけるために、ソルバーを複数回実行して、問題を解決するたびに初期値を調整することをお勧めします。

Excelワークシートでソルバーアドインの問題を設定するときは、次の項目を定義します。

• 目的セル：値を最大化、最小化、または特定の値に到達させるワークシート内のターゲットセル。このセルには数式が含まれている必要があることに注意してください。
• 可変セル：答えが見つかるまで値を調整するワークシート内の変化するセル。
• 制約セル：可変セルの値の変化や目的セルのターゲットセルに課す制限を含むセル。

これらのパラメーターを使用して問題を定義し、ソルバーアドインで問題を解決した後、プログラムはワークシートの値を変更して最適なソリューションを返します。この時点で、ワークシートの変更を保持するか、元の値をワークシートに復元するかを選択できます。ソリューションをシナリオとして保存して、後で元の値を復元する前に表示することもできます。

シナリオマネージャーでソルバーアドインを使用すると、解決する問題を設定したり、後日表示できるように解決策を保存したりできます。シナリオマネージャー用に定義した変更セルは、このコマンドを選択するとソルバーによって自動的に取得されて使用されます。その逆も同様です。また、問題に対するソルバーのソリューションをシナリオとして保存し（[ソルバー]ダイアログボックスの[シナリオの保存]ボタンをクリックして）、シナリオマネージャーで表示することもできます。

Excel2019での問題の設定と定義

ソルバーが作業する問題を設定する最初のステップは、目的セル、変数セル、および制約セルを定義するワークシートモデルを作成することです。

ソルバーはアドインユーティリティであることに注意してください。つまり、使用する前に、リボンの[データ]タブの最後にある[分析]グループの[ソルバー]ボタンの表示で示されるように、ソルバーアドインプログラムがまだロードされていることを確認する必要があります。このボタンがない場合は、[Excelオプション]ダイアログボックスの[アドイン]タブ（Alt + FTAA）を開き、[管理]ドロップダウンにExcelアドインが表示されていることを確認してから、[移動]ボタンをクリックしてソルバーを読み込むことができます。すぐ左にあるリストボックス。次に、[アドイン]ダイアログボックスの[ソルバーアドイン]チェックボックスをオンにしてチェックマークを付けてから、[OK]をクリックしてダイアログボックスを閉じ、アドインを再読み込みします。

アドインをロードしてワークシートモデルを作成した後、ソルバーアドインの問題を定義して解決するには、次の手順に従います。

リボンの[データ]タブの最後にある[分析]グループの[ソルバー]コマンドボタンをクリックします。

Excelは、[ソルバーパラメーター]ダイアログボックスを開きます。

[ソルバーパラメーター]ダイアログボックスで、モデルに適用するパラメーターを指定します。

ワークシートでターゲットセルをクリックするか、[目的の設定]テキストボックスにそのセル参照または範囲名を入力します。

次に、To設定を選択する必要があります。ターゲットセルの値をできるだけ大きくする場合は、[最大]オプションボタンをクリックします。ターゲットセルの値をできるだけ小さくしたい場合は、[最小]オプションボタンをクリックします。ターゲットセルの値を特定の値に到達させる場合は、[値]オプションボタンをクリックし、関連するテキストボックスに値を入力します。

ダイアログボックスの[宛先]セクションで、適切なオプションボタンオプションをクリックします。[値]オプションボタンを選択した場合は、関連するテキストボックスに一致する値を入力します。

次に、可変セルを指定します。つまり、ソルバーがEqualTo目標に到達するために変更できるセルを指定します。

[変数セルを変更する]テキストボックスをクリックし、ワークシートで変更するセルを選択するか、テキストボックスにセル参照または範囲名を入力します。

ワークシートで隣接していないセルを選択するには、Ctrlキーを押しながら選択範囲の各セルをクリックする必要があることに注意してください。選択したターゲットセルに基づいて変化するセルをExcelに選択させるには、このテキストボックスの右側にある[推測]ボタンをクリックします。

ソルバーにモデルを調整させる前に、値を調整するときにその制限を決定するターゲットセルまたは変更するセルの制約を追加できます。

（オプション）[ソルバーパラメーター]ダイアログボックスの[制約の対象]リストボックスの右側にある[追加]ボタンをクリックします。

このアクションにより、[制約の追加]ダイアログボックスが開きます。制約を定義するときは、値を制約するセルを選択するか、ワークシートでセルを選択するか、[セル参照]テキストボックスにそのセル参照を入力します。その後、関係を選択します（=、<=、> =、またはint型整数またはのためにビンバイナリ用）の右にあるドロップダウンリストボックスからと（あなたが選択した場合を除きint型やビンに適切な値またはセル参照を入力します）制約テキストボックス。

ソルバーで使用される他のセルに制約を追加し続けるには、[追加]ボタンをクリックして制約を追加し、[制約の追加]ダイアログボックスのテキストボックスをクリアします。次に、手順5を繰り返して、新しい制約を追加します。ターゲットセルの制約の定義とモデルの値の変更が終了したら、[OK]をクリックして[制約の追加]ダイアログボックスを閉じ、[ソルバーパラメーター]ダイアログボックスに戻ります（[制約の対象]リストボックスに制約が一覧表示されます）。

（オプション）変数セルに制約が適用されない場合に負の値を許可する場合は、[制約のない変数を非負にする]チェックボックスをオフにします。
デフォルトでは、ソルバーアドインは、パラメーターを設定するモデルを解く際にGRG（Generalized Reduced Gradient）非線形法を採用しています。これは、滑らかな非線形問題を解くための非常に効率的な方法として知られています。LPシンプレックス法（シンプレックスアルゴリズムに従った線形計画法の場合）または進化的エンジンを使用して滑らかでない問題を解決するには、手順7に従う必要があります。

（オプション）[解決方法の選択]ドロップダウンリストから[LPシンプレックス]または[進化的]を選択して、スムーズでない問題を解決するこれらの方法のいずれかを使用します。

[ソルバーパラメーター]ダイアログボックスで定義したとおりにソルバーに問題を解決させるには、[ソルバー]ボタンをクリックします。

Excelのソルバーで問題を解決する

[解決]ボタンをクリックすると、[ソルバーパラメーター]ダイアログボックスが消え、ステータスバーにソルバーが問題を設定していることが示され、中間（または試行）の番号が表示されて問題の解決の進捗状況が通知されます。それらが試されるときの解決策。Excelが最後の反復を計算する前にいつでも解決プロセスを中断するには、Escキーを押します。次に、Excelは[見せしめ裁判の解決策を表示]ダイアログボックスを表示し、解決策のプロセスが一時停止されたことを通知します。ソリューションプロセスを続行するには、[続行]ボタンをクリックします。解決プロセスを中止するには、[停止]ボタンをクリックします。

Excelが解決プロセスを終了すると、[ソルバーの結果]ダイアログボックスが表示されます。このダイアログボックスは、ターゲットセル、変更セル、および問題に対して定義された制約を指定して、ソルバーが解決策を見つけることができたかどうかを通知します。ソルバーがワークシートモデルに加えた変更を保持するには、[ソルバーソリューションを保持]オプションボタンを選択したままにし、[OK]をクリックして[ソルバー結果]ダイアログボックスを閉じます。元の値をワークシートに戻すには、代わりに[元の値に戻す]オプションボタンをクリックします。元の値を復元する前に変更をシナリオとして保存するには、[シナリオの保存]ボタンをクリックし、現在のシナリオに名前を割り当ててから、[元の値の復元]オプションと[OK]ボタンをクリックします。

ソルバーが問題の解決策を見つけたことを示す[ソルバー結果]ダイアログボックス。

ゴールシークコマンドを使用する場合とは異なり、[ソルバー結果]ダイアログボックスの[ソルバーソリューションを保持]オプションボタンをクリックした後、クイックアクセスツールバーの[元に戻す]コマンドボタンを使用して、元の値をワークシートに復元することはできません。ワークシートの「前」ビューと「後」ビューを切り替えられるようにする場合は、[シナリオの保存]ボタンで変更を保存してから、[元の値に戻す]オプションボタンを選択する必要があります。このようにして、元のワークシートの「前」ビューを保持し、シナリオマネージャーを使用してソルバーによって作成された「後」ビューを表示できます。

Excelのソルバーオプションの変更

ほとんどの問題については、ソルバーで使用されるデフォルトのオプションで十分です。ただし、状況によっては、ソリューションプロセスを開始する前に、ソルバーオプションの一部を変更したい場合があります。ソリューションオプションを変更するには、[ソルバーパラメーター]ダイアログボックスの[オプション]ボタンをクリックします。次に、Excelは、必要なすべての変更を行うことができる[すべてのメソッド]タブが選択された状態で[オプション]ダイアログボックスを開きます。

[オプション]ダイアログボックスでソリューションオプションを変更します。

Excel2019ソルバーオプション設定

オプションを変更したら、[OK]をクリックして[ソルバーパラメーター]ダイアログボックスに戻ります。ここから、[解決]ボタンをクリックして、変更したばかりの新しいソリューション設定でソリューションプロセスを開始できます。

デフォルトのGRG（Generalized Reduced Gradient）NonlinearまたはEvolutionaryメソッドを使用する場合、[オプション]ダイアログボックスの[GRG Nonlinear]および[Evolutionary]タブのオプションを使用して、追加のソルバー設定を設定できます。これらのオプションには、これらの特定の方法のいずれかの収束、母集団サイズ、およびランダムシード設定の変更が含まれます。

Excel2019でのモデルの問題の保存と読み込み

クイックアクセスツールバーの[保存]ボタン（Ctrl + S）をクリックすると、最近使用した目的セル、可変セル、制約セル、およびソルバーオプションがExcelワークシートの一部として保存されます。保存する同じワークシートに他の問題を定義する場合は、[ソルバーオプション]ダイアログボックスの[モデルの保存]ボタンをクリックし、問題のパラメーターを設定するアクティブなワークシートの範囲のセル参照または名前を指定する必要があります。挿入されました。

[ロード/保存]ボタンをクリックすると、Excelは[モデル領域の選択]テキストボックスを含む[モデルのロード/保存]ダイアログボックスを開きます。このテキストボックスには、アクティブセルから始めて、問題のすべてのパラメータを保持するのに十分な大きさの範囲のセル参照が含まれています。問題のパラメータをこの範囲に保存するには、[OK]をクリックします。この範囲に既存のデータを含むセルが含まれている場合は、[OK]をクリックする前に、このテキストボックスのセル参照を変更して、Excelが既存のデータを置き換えないようにする必要があります。

[OK]をクリックすると、Excelは問題のパラメーターを指定された範囲にコピーします。これらの値は、次にブックを保存するときにワークシートの一部として保存されます。問題を解決するときにこれらの問題パラメータを再利用するには、[ソルバーオプション]ダイアログボックスを開き、[ロード/保存]ボタンをクリックして[モデルのロード/保存]ダイアログボックスを開き、[ロード]ボタンをクリックして、保存された範囲を選択するだけです。問題のパラメータ。[モデルの読み込み]ダイアログボックスで[OK]をクリックすると、Excelはこのセル範囲のパラメーターを[ソルバーパラメーター]ダイアログボックスの適切なテキストボックスに読み込みます。次に、[OK]をクリックして[ソルバーオプション]ダイアログボックスを閉じ、[ソルバー]コマンドボタンをクリックしてこれらのパラメーターを使用して問題を解決できます。

前の問題で定義されたすべてのパラメーターをクリアし、ソルバーオプションをデフォルトに戻したい場合は、いつでも[すべてリセット]ボタンを使用できることに注意してください。

Excel2019でソルバーレポートを作成する

ソルバーを使用して、3つの異なるタイプのレポートを作成できます。

• 回答レポート：問題の解決に使用された制約とともに、ターゲットセルと変更セルを元の値と最終値とともに一覧表示します。
• 感度レポート：ターゲットセルと制約を計算する数式の変更に対する最適解の感度を示します。レポートには、変化するセルとその最終値、および各セルの勾配の減少が表示されます。（減少した勾配は、変化するセルの単位増加あたりの目標を測定します。）制約を定義した場合、感度レポートには、それらの最終値と各制約のラグランジュ乗数が一覧表示されます。（ラグランジュ乗数は、制約式の右辺に表示される単位増加あたりの目標を測定します。）
• 制限レポート：ターゲットセルと変化するセルを、それらの値、下限と上限、およびターゲットの結果とともに表示します。下限は、他のすべてのセルの値を固定し、制約を満たしながら、変化するセルが持つことができる最小値を表します。上限は、これを行う最大値を表します。

Excelは、ソルバーの問題に対して生成した各レポートをワークブックの個別のワークシートに配置します。これらのレポートの1つ（またはすべて）を生成するには、[ソルバー結果]ダイアログボックスの[レポート]リストボックスからレポートタイプ（[回答]、[感度]、または[制限]）を選択します。複数のレポートを選択するには、レポートの名前をクリックするだけです。

[OK]をクリックして[ソルバーの結果]ダイアログボックスを閉じると（[ソルバーソリューションを保持する]オプションと[元の値を復元する]オプションのいずれかを選択した後）、Excelは選択したレポートを新しいワークシートに生成し、ブックの先頭に追加します。 。（レポートシートのタブは、回答レポート1、感度レポート1、および制限レポート1のように、レポートタイプによって名前が付けられます。）