Microsoft Excel には、計算の実行や方程式の解決のプロセスを効率化し、生産性を向上させるさまざまな機能が備わっています。これらの機能の 1 つが、ゴール シーク機能に類似したソルバー ツールです。
ソルバーは、通常「What-if」分析に使用され、複数の制約の下で特定のセルの値を確認することができます。特定の値、最小しきい値、または数値の最大制限を決定するために使用できます。ソルバーはすべての問題に対応できるわけではありませんが、最適な決定を見つけることが重要な最適化シナリオにとって非常に貴重なリソースです。
このツールは、スプレッドシート内の決定変数と呼ばれる特定のセルの値を調整して、目的セルと呼ばれる別のセルの最大値または最小値を特定することによって動作します。ソルバーは、線形および非線形プログラミング、整数プログラミング、目標探索タスクなど、さまざまなプログラミング タイプに適用できます。
Solver の一般的な用途としては、交通費の最小化、最適な作業スケジュールの作成、広告活動に最適な予算の確立、投資収益の最大化などが挙げられます。
Excel でソルバーを有効にする
Solver を使い始めるには、このアドインを最初に有効にする必要があります。これは、ゴール シーク機能のようにデフォルトでは有効になっていないためです。幸い、プロセスは非常に簡単です。
- まず、画面上部の「ファイル」メニューを選択し、「オプション」をクリックします。
- 次に、「オプション」ウィンドウの左側にある「アドイン」をクリックします。
- 次に、下部にある「管理」ドロップダウン メニューから「Excel アドイン」を選択し、「移動」をクリックします。
- 次のダイアログボックスで、「ソルバーアドイン」の横にあるチェックボックスをオンにして有効にし、「OK」をクリックします。
- Excel の [データ] タブをクリックすると、ソルバーが利用可能になるはずです。
ソルバーの主要コンポーネント
ソルバーが問題の最適値を特定するには、次の 3 つの主要コンポーネントを確立する必要があります。
- 目的セル:このセルには、最小化、最大化、または特定の値に到達するかといった、問題の目標またはターゲットを表す数式が含まれます。
- 変数セル:これらのセルには、ソルバーが目的を達成するために調整する変数が保持されます。ソルバーでは最大 200 個の変数セルを指定できます。
- 制約:制約は、ソルバーが目的の結果を達成するために動作する必要があるパラメータです。制約は、必要な値を決定する際に満たす必要のある条件を定義します。
ソルバーの適用
Excel にソルバーを追加したら、ソルバーの利用を開始できます。この例では、ソルバーを活用して、パレットごとに必要なリソースやさまざまなパレット タイプの可用性などの既知のリソース値に基づいて、パレット製造ビジネスの利益を計算します。
- セル B3 から E3 には、会社が製造しなければならないさまざまなタイプのパレットがリストされています。すぐ下の行は、各タイプで製造されるパレットの数を表し、ゼロで初期化されています。次の行には、各パレット タイプに関連付けられた利益の詳細が表示されます。私たちの目標は、各種類で製造するパレットの数を決定することです。合計利益はセル F5 に表示されます。ここでの制約は、会社が実際に製造できるパレットの数を決定する利用可能なリソースです。
- まず、右上隅にある「ソルバー」をクリックすると、ソルバー ダイアログ ボックスが表示されます。目的のセルの名前またはセル参照を入力し、数式が含まれていることを確認します。このシナリオでは、セル F5 が目的関数として機能し、使用可能なリソースと生産されるパレットの両方を考慮して、すべてのパレット タイプを合わせた総利益を算出します。
- 「変数セルの変更」フィールドで、マウスをドラッグするか、セル名を直接入力して、範囲 B4:E4 を選択します。これらのセルはタイプごとのパレットの数を表し、現在は 0 に設定されています。ソルバーは実行中にこれらの値を調整します。
- 次に、「追加」ボタンをクリックして制約を導入します。ソルバーは、接着剤、プレス、松のチップ、オークのチップなどの材料の可用性に基づいて、会社が製造できるパレットの数を計算します。ソルバーを実行すると、現在ゼロになっている「使用済み」列の値が変化することがわかります。
- 「セル参照」に F8:F11 と入力し (「使用済み」列に対応)、「制約」フィールドには G8:G11 と入力します。関係が
<=デフォルトで に設定され、使用済み列の値が使用可能列の値以下である必要があることを示します。
- すべての変数と制約を入力したら、[制約の追加] ダイアログ ボックスでもう一度 [追加] をクリックして閉じます。また、[ソルバー パラメーター] ダイアログでは、[制約のない変数を非負にする] オプションが既定で有効になっているため、特定の制約が設定されていなくても、すべての変数が非負のままになります。
- [ソルバー パラメーター] ダイアログ ボックスの入力が完了したら、[解決] ボタンをクリックし、Excel が結果を表示するのを待ちます。
- 結果が生成されると、ソルバーの結果ダイアログ ボックスが表示され、セル B4 から E4 の新しい値が表示されます。ソルバーによってデータが変更されることに注意してください。元の値に戻したい場合は、[元の値を復元] オプションを選択できます。ソリューションを維持するか、元のデータに戻すかを決定したら、右側の [回答] がオンになっていることを確認し、[OK] をクリックしてダイアログ ボックスを終了します。
- 新しいソリューションを保持することを選択した場合、ソルバー ダイアログを閉じると、スプレッドシートに反映されます。会社の生産には、23 個の Tahoe パレット、15 個の Pacific パレット、39 個の Savannah パレットが含まれ、Aspen パレットは含まれません。これらは、パレット行の B4 から D4 に表示されます。さらに、総利益セルは 0 から 58,800 ドルに更新されます。
重要な考慮事項
- Excel のゴール シーク機能と同様に、ソルバーが正しく機能するには、必要な数式を事前に設定しておく必要があります。
- ソルバー パラメータ ダイアログの [オプション] ボタンを選択して、問題解決方法に影響を与えることができます。ここで、[すべての方法]、[GRG 非線形]、および [進化型] の値を指定できます。
- さらに、ソルバーでは、後で使用するためにモデルを保存して読み込むことができます。既存のモデルを読み込むときは、手元の問題に関連するセル範囲全体の参照を入力するようにしてください。
- ソルバーを実行すると元のデータが変更され、変更後はそのデータを回復できなくなる可能性があるため、ソルバーを使用するときはデータのコピーを使用して作業することをお勧めします。
コメントを残す