Excelで線形計画法をグラフ化する方法|詳細ステップ解説

複数の変数を含む問題を線形計画法で解かなければならない場面は少なくありません。線形計画法にはいくつかの解き方がありますが、その中でも最も手軽なのが「グラフ化」による解法です。本記事では、Excelを使って線形計画法をグラフ化し、最適解を求めるための詳細な手順を解説します。

練習用ワークブックはこちらから無料でダウンロードできます。

線形計画法とは?

線形計画法(LP:Linear Programming)とは、複数の数学関数と制約条件をもとに状況を分析し、目的関数の最適点を見つけ出すための数学的手法です。ビジネスにおける投資の最適化や生産サイクルの改善、必要な製品の購買計画など、幅広い分野で活用されています。

線形計画法の基本構成要素

• 決定変数: 線形計画法によって目的関数の最適点を計算するために必要となる変数です。意思決定の状況、制約条件、目的関数はすべてこの変数によって表現されます。
• 制約条件: 目的関数を制限し、実行可能領域を決定する条件のことです。等式の場合もあれば、不等式の場合もあります。
• 目的関数: 目的そのものを表す関数です。適切な制約条件のもとでこの式を満たすことで、最適解が得られます。
• 実行可能領域: 適切な制約条件を適用した後の、目的関数にとって有効な領域です。最適解はこの領域内のどこかに存在します。
• 実行可能解: 実行可能領域の頂点(コーナーポイント)における目的関数の解のことです。
• 最適解: 目的関数にとって最も望ましい点のことです。計算された実行可能解の中から見つけることができます。

Excelで線形計画法をグラフ化する手順

ここでは、目的関数が F = 6X + 8Y として与えられ、以下の制約条件下でこの関数を最大化するケースを例に説明します。

2X + 4Y <= 60

4X + 2Y <= 48

それでは、以下の手順に従って、Excelで線形計画法をグラフ化し、最適点を求めてみましょう。

📌 ステップ1: 目的関数と制約条件の直線上の点を記録する

Excelで線形計画法をグラフ化するには、まず目的関数と制約条件の情報、および制約条件を表す直線上の点をワークシートに記録する必要があります。

• まず、目的関数と制約条件の係数や記号(<、= など)を正確に入力します。

• 次に、1つ目の制約条件(C1)について、直線を描くための2つの点を求めます。X=0 を代入すると Y=15 となり、同様に Y=0 を代入すると X=30 となります。

• 続いて、2つ目の制約条件(C2)についても同じ要領で2つの点を求めます。X=0 のとき Y=24、Y=0 のとき X=12 となります。

以上の作業により、目的関数・制約条件・制約直線を描くための2点が揃ったワークシートが完成します。最終的にワークシートは次のようになります。

📌 ステップ2: 実行可能領域を見つける

ステップ1が完了したら、次は実行可能領域を特定します。

• まず、セル範囲 B6:C8 を選択します。続いて、[挿入] タブ >> [グラフ] グループ >> [散布図またはバブルチャートの挿入] ツール >> [平滑線付き散布図] を選択します。

• すると、B6:C8 の値に基づいた平滑線付き散布図が作成されます。

• ただし、このままでは必要な形式になっていません。そこで、グラフ上で右クリックし、コンテキストメニューから [データの選択...] を選択します。

• [データソースの選択] ダイアログボックスが表示されるので、「系列1」を選択して [編集] ボタンをクリックします。

• [系列の編集] ウィンドウが表示されます。[系列名] ボックスに「C1」と入力し、[系列のX値] にセル範囲 B6:B7、[系列のY値] にセル範囲 C6:C7 を指定します。最後に [OK] をクリックします。

• [データソースの選択] ウィンドウに戻るので、今度は [追加] ボタンをクリックします。

• 再び [系列の編集] ウィンドウが表示されます。[系列名] に「C2」と入力し、[系列のX値] にセル範囲 B11:B12、[系列のY値] にセル範囲 C11:C12 を指定して、[OK] をクリックします。

• 再び [データソースの選択] ウィンドウに戻るので、[OK] をクリックします。

• これで、線形計画法のすべての制約条件を含む散布図が表示されます。グラフは次のようになります。

• 両方の制約条件が「以下(≦)」の不等式であるため、どちらの制約直線も原点方向に領域が広がります。したがって、実行可能領域は下図のようになります。

このように、ABCD が実行可能領域となり、A、B、C、D がそれぞれ領域の頂点(コーナーポイント)です。

📌 ステップ3: 最適解を求める

実行可能領域を特定したら、次に実行可能解を求めます。

• まず、各頂点の X 座標と Y 座標を求める必要があります。グラフと制約条件の値の表から、A、B、C の各点はそれぞれ (0,15)、(0,0)、(12,0) であることが簡単にわかります。

• 次に、点Dの座標を求めるために、セル D5:D6 を選択し、MMULT 関数と MINVERSE 関数を使用した以下の数式を入力して、Ctrl+Shift+Enter を押します。

=MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)

🔎 数式の解説:

• MINVERSE('Finding Points of Constraints'!C6:D7)

「Finding Points of Constraints」シートの C6:D7 の値の逆行列を返します。

結果: (-0.166666667, 0.333333333) と (0.333333333, -0.166666667)

• =MMULT(MINVERSE('Finding Points of Constraints'!C6:D7), 'Finding Points of Constraints'!F6:F7)

前述の結果の配列と「Finding Points of Constraints」シートの F6:F7 の配列の行列積を返します。

結果: {6, 12}

• これにより、2つの制約直線の交点である点Dの座標が求まります。

• これですべての頂点の座標が揃いました。次に、これらの点から実行可能解を求めます。C7 セルに以下の数式を入力し、Enter キーを押します。

=(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)

🔎 数式の解説:

• =(C5*'Finding Points of Constraints'!$C$5)

現在のシートの C5 セルの値と「Finding Points of Constraints」シートの C5 セルの値の積を計算します。

結果: 0

• ('Finding Points of Constraints'!$D$5*C6)

「Finding Points of Constraints」シートの D5 セルの値と現在のシートの C6 セルの値の積を計算します。

結果: 120

• =(C5*'Finding Points of Constraints'!$C$5)+('Finding Points of Constraints'!$D$5*C6)

上記の2つの結果を合計します。

結果: 120

• これで、頂点Aにおける目的関数の値が求まりました。続いて、セルの右下にカーソルを合わせるとフィルハンドルが表示されるので、それを右方向にドラッグして、他のすべての点にも同じ数式をコピーします。

• これで、すべての実行可能解が求まりました。

• 最後に、今回は F を最大化する必要があるため、F の最大値を見つけます。ご覧のとおり、F の最大値は D (6,12) の点で 132 となっています。したがって、最適点は D (6,12) です。

以上で、グラフによる線形計画法の解法は完了です。

まとめ

本記事では、Excelで線形計画法をグラフ化するためのすべての手順を詳しく解説しました。記事全体を丁寧に読み、練習用ワークブックを使ってしっかりと復習することをおすすめします。この記事が皆さんのお役に立てば幸いです。ご質問やご提案がありましたら、お気軽にコメント欄でお知らせください。
また、このような記事をもっと読みたい方は、ぜひ ExcelDemy をご覧ください。ありがとうございました!