ソルバー利用例のサンプル

ソルバーの利用例

「ソルバー」はエクセルの強力な機能で、複数の制約条件の下で目標値を最適化する問題を解くことができます。以下に「ソルバー」を使った作業のいくつかの例を挙げます。

1. ポートフォリオ最適化: 投資家はソルバーを用いて、リスクとリターンのバランスを最適化するための資産配分を決定することができます。
2. 生産スケジューリング: 企業はソルバーを用いて、リソースの制約下で生産スケジュールを最適化することができます。これには、機械の使用時間、労働時間、原材料の利用可能性などの制約が含まれることがあります。
3. 配送ルート最適化: 物流会社はソルバーを用いて、コストと時間を最小化しながら配送ルートを最適化することができます。
4. プロジェクト管理: プロジェクトマネージャは、予算や時間、労働力などの制約下でプロジェクトスケジュールを最適化するためにソルバーを使用することができます。
5. エネルギー管理: エネルギー会社はソルバーを用いて、供給と需要のバランスをとりながらエネルギー配分を最適化することができます。

これらはいくつかの例ですが、「ソルバー」は様々な業界や分野で多目的最適化問題を解くために利用されます。

例：ポートフォリオ最適化

以下の表では、投資家が3つの投資先（投資先A、投資先B、投資先C）について考えています。それぞれの投資先に対する期待リターンとリスク（標準偏差）が定義されており、投資家はリスクを最小化しつつ、期待リターンを一定以上（ここでは例として5%以上）にしたいと考えています。

まず、それぞれの投資先の詳細情報を示す表を作成します。なお、総投資額は145万円とする予定です。

投資先A、B、Cへの投資額（D列）はソルバーが変動させるセル（変数セル）となります。それぞれのセルには初期値（例えば、均等に分配した場合なら、投資可能額の1/3）を入力しておきます。

次に、期待リターンとリスクを計算するセルを作成します。期待リターンは、各投資先の期待リターン（%）と投資額の積の総和で計算できます。リスクは、各投資先のリスク（標準偏差）と投資額の積の総和で表します。

期待リターン：各投資先の期待リターンと投資額の積の合計を投資額の合計で割ります。この計算をエクセルの式で書くと以下のようになります（ここでは投資額がD列、期待リターンがB列、投資先が3つと仮定）。

=(B2*D2 + B3*D3 + B4*D4) / (D2 + D3 + D4)

リスク：各投資先のリスク（標準偏差）と投資額の積の合計を投資額の合計で割ります。この計算をエクセルの式で書くと以下のようになります（ここではリスクがC列）。

=(C2*D2 + C3*D3 + C4*D4) / (D2 + D3 + D4)

さらに、投資額の合計を計算するセル(総投資額)も作成します。この値が投資可能額と等しいようにソルバーに制約条件を設定します。

そしてE列などに投資割合(各投資額/総投資額)を算出しておきます。

これらの準備が整ったら、「データ」タブから「ソルバー」を選択し、以下のようにパラメータを設定します(必要に応じてアドインの設定をする)。

• 目的セルの設定: リスクを示すセルを選択します。この目標はリスクを最小化することです。
• 目標値: ここでは「最小」を選択します。これはリスクを最小化することが目標だからです。
• 変数セル: 投資額（D列）を示すセルを選択します。
• 制約条件の対象: 「総投資額・投資額の合計」が投資可能額（例えば145万円）と等しい、という制約を追加します。また、期待リターンが一定以上（例えば5%以上）である、という制約も追加します。さらに、1つの投資先に投資額が集中することのないように、各投資額が0から投資可能額の一定割合（例えば10～55%・0.1～0.55）以内に収まるような制約を追加します。投資額には整数しか使用できない制約も追加可能です(int・整数)。

以上の設定を行った後、「解決」ボタンをクリックすると、ソルバーはリスクを最小化しつつ期待リターンと投資額の制約を満たす最適な投資額の配分を求めます。この解決結果を用いて投資ポートフォリオを構築することで、投資家はリターンを一定のレベルに保ちつつリスクを最小化する最適な投資戦略を得ることができます。

例：生産スケジューリング

エクセルのソルバーを使用して、特定の制約条件下で生産スケジュールを最適化する具体例を考えてみましょう。ここでは、2つの製品AとBを製造する工場を考えます。各製品の製造には異なる機械時間と労働時間が必要で、製品ごとの利益も異なります。制約として、1日の機械の利用可能時間と労働者の労働可能時間があります。

以下のような表を想像してみてください。

ここで、機械時間と労働時間の制約条件下で、製造する製品AとBの数量をどのように決定すれば全体の利益を最大化できるかを求める問題を考えます。

まず、「製造数量」には初期値として0を入力しておきます。

次に、全体の利益を計算するセルを作成します。それは各製品の「1製品あたりの利益」に「製造数量」を掛けたものの総和です。例えば「全体の利益」を計算するためのセルをE2とし、以下の式を設定します。

=E2 = B4*B5 + C4*C5

そして、「使用する機械時間」および「使用する労働時間」を計算するセルも作成します。それぞれは、「製造数量」に「1製品あたりの機械時間」および「1製品あたりの労働時間」を掛けたものの総和です。例えばそれぞれE3セルとE4セルで計算するとします。以下の式をそれぞれ設定します。

=E3 = B2*B5 + C2*C5
=E4 = B3*B5 + C3*C5

次に、ソルバーを開きます。「データ」タブの「解析」グループにある「ソルバー」をクリックします。

「目的セルの設定」には全体の利益を計算するセル（ここではE2）を指定し、「最大化」を選択します。

「制約条件の対象」には、まず「使用する機械時間」が1日の利用可能時間以下であることを指定します。つまり、「$E$3 <= $B$1」とします。次に、「使用する労働時間」が1日の労働可能時間以下であることを指定します。つまり、「$E$4 <= $B$2」とします。

「変数セル」には製品Aと製品Bの「製造数量」を指定します。つまり、「$B$5:$C$5」とします。また、「非負」を選択し、製品の製造数量が負にならないようにします。

以上の設定を行った後、「解」をクリックして最適な製造数量を求めます。

以上がソルバーを使用した生産スケジューリングの最適化の一例です。様々なリソースの制約条件下での最適化問題に対してソルバーは有用なツールとなります。