Excelソルバーで線形計画法を解く方法｜初心者向けステップバイステップ解説

Excelは、さまざまな数学的な操作を実行できる強力なツールです。線形計画法（Linear Programming）は統計学や応用数学の一分野であり、実務において非常に幅広い応用が可能です。線形計画法の問題を手作業で解くのは手間がかかりますが、Excelソルバーを使えば、そうした問題を簡単かつ迅速に解決できます。本記事では、Excelソルバーを使用して線形計画法を解くための手順を、ステップごとにわかりやすく解説します。

線形計画法とは

線形計画法は、統計学および応用数学における重要な手法の一つです。既存のデータ変数を用いて予測分析を行うことができ、資源の最適化に役立ちます。この手法を活用するには、いくつかの制約条件と目的関数が必要です。Excelソルバーを使えば、Excel上で線形計画法の問題を素早く解くことができます。

Excelソルバーで線形計画法を解く手順

ここでは、以下のビジネス上の問題を例として説明します。

ある製造業者が「A」と「B」という2種類の製品を扱っているとします。製品Aを1単位生産するには、原材料Pを25kg、Qを35kg、Rを10kg必要とします。同様に、製品BにはPを15kg、Qを20kg、Rを15kg必要です。製造業者は最低でもPを500kg、Qを850kg、Rを300kg確保する必要があります。製品Aの単価が35ドル、製品Bの単価が30ドルである場合、最低限の原材料要件を満たしながらコストを最小限に抑えるためには、それぞれの製品を何単位ずつ配合すべきでしょうか。また、その際のコストはいくらになるでしょうか。

それでは、以下の手順に沿って、Excelソルバーによる線形計画法の解き方を学びましょう。

ステップ1：Excelでソルバーを有効化する

ソルバーはMS Excelのアドインプログラムで、デフォルトでは無効になっています。使用する前に有効化する必要があるため、以下の手順に従って設定を行いましょう。

• まず、「ファイル」➤「オプション」をクリックします。
• 次に、「アドイン」タブを選択します。
• 画面下部の「管理」ドロップダウンから「Excelアドイン」を選択します。
• その後、「設定」ボタンを押します。

• すると、「アドイン」ダイアログボックスが表示されます。
• 「ソルバーアドイン」にチェックを入れます。
• 「OK」をクリックします。

• これで、「データ」タブの「分析」グループ内にソルバーが表示されるようになります。

ステップ2：制約条件を入力する

このステップでは、Excelワークシートに制約条件と目的関数を入力します。問題の設定に基づき、製品Aをx単位、製品Bをy単位配合すると仮定します。総コストは「$35x + $30y」となり、これが目的関数となります。私たちの目標はこのコストを最小化することです。同時に、以下の要件も満たす必要があります。

25x + 15y ≧ 500、35x + 20y ≧ 850、10x + 15y ≧ 300、x ≧ 0、y ≧ 0 が制約条件です。それでは、これらを入力していきます。

• まず、製品AとBの単位あたりのコストを入力します。
• 次に、各製品に対応する原材料の必要量を入力します。
• そして、最低必要量を入力します。

ステップ3：Excelの数式を作成する

• セルC5にxの値、セルD5にyの値を入力するものとします。
• まず、セルE6を選択します。
• 次に、以下の数式を入力します。

=($C$5*C6)+($D$5*D6)

• Enterキーを押します。現時点ではC5とD5のセルが空のため、結果は0または空白として返されます。

• 続いて、セルE8を選択し、以下の数式を入力します。

=($C$5*C8)+($D$5*D8)

• Enterキーを押して値を表示させます。
• オートフィル機能を使って、残りのセルにも数式をコピーします。
• C5とD5が空であるため、現時点では結果はすべて0になります。

ステップ4：Excelソルバーで線形計画法を解く

• 「データ」タブにある「ソルバー」を選択します。
• 「ソルバー パラメーター」ダイアログボックスが表示されます。
• 「目的セルの設定」ボックスにセルE6を指定します。
• 「最小値」のラジオボタンを選択します。
• 変数セルとして範囲C5:D5を指定します。
• 次に、「追加」ボタンを押して制約条件を登録します。

• 「制約条件の追加」ダイアログボックスが表示されます。
• 範囲C5:D5を選択し、ドロップダウンから「>=」（以上）記号を選択します。
• 値に「0」を入力します。
• その後、「追加」を押します。

• さらに、最低必要量に関する制約条件として範囲E8:E10を選択します。
• ドロップダウンから「>=」記号を選択します。
• 制約条件フィールドに範囲G8:G10を指定します。
• 「OK」をクリックします。

• これで、設定した制約条件が一覧に表示されます。
• 「解決」ボタンを押します。

• 計算結果を通知するダイアログボックスが表示されます。
• 「ソルバーの結果を保持する」のラジオボタンにチェックを入れます。
• 「OK」をクリックします。

• 最後に、指定したセルに正確な計算結果が表示されます。

最終的な結果

• xの値は77単位、yの値は6.15単位となります。
• 最小コストは912ドルです。
• 最適化された原材料P、Q、Rの量は、それぞれ54kg、850kg、300kgです。
• したがって、製造業者は製品Aを77単位、製品Bを6.15単位配合すべきということになります。

まとめ

以上の手順に従うことで、Excelソルバーを使った線形計画法の問題解決ができるようになります。ぜひ実際に試してみてください。他にも効率的な方法をご存じの方は、ぜひお知らせください。今後もこのような役立つ記事をお届けしていく予定です。ご質問やご意見、ご提案などがございましたら、下のコメント欄でお気軽にお寄せください。