ソルバーを利用して線形計画問題を解く - Excel

ソルバーを利用して線形計画問題を解く手順を紹介します。

概要

こちらの記事では、C#のプログラムで線形計画問題をシンプレックス法で解きましたが、Excelでも同様の処理をすることができます。この記事ではExcelでソルバーアドインを利用して線形計画問題を解く方法を紹介します。

問題

こちらの記事と同じ問題とします。

下記の条件のとき、売上を最大にするためには「ぺんぎんクッキー」と「らくだサブレ」をいくつ作るのが良いでしょうか？

製品情報

商品	小麦粉	砂糖	販売価格
ぺんぎんクッキー	4	3	14,500
らくだサブレ	6	2	9,800

在庫情報

原料	在庫
小麦粉	25
砂糖	10

事前準備

Excelでソルバーアドインを利用できるようにします。アドインを利用できるようにする手順はこちらの記事を参照してください。

手順

左上のセルをB3セルとしたときに下記の表を作成します。

(ここはB3セル)	ぺんぎんクッキー	らくだサブレ
生産数	1	1
売上	=14500*C4	=9800*D4	=C6+D6
小麦粉	=4*C4	=6*D4	=C8+D8
砂糖	=3*C4	=2*D4	=C9+D9

画面に表示される数値では下記になります。

	ぺんぎんクッキー	らくだサブレ
生産数	1	1
売上	14500	9800	24300
小麦粉	4	6	10
砂糖	3	2	5

上記の表で、E8セルの値が25以下になる必要があり、E9セルの値は10以下になる必要があります。この時、売上の合計E6セルを最大にするための生産数、C4,D4セルの値を求めます。(初期値として両方の値を1で仮置きしています。)

Excelのメインウィンドウのツールリボンの[データ]タブをクリックします。下図の画面が表示されます。ツールリボンの一番右側の[分析]セクションの[ソルバー]ボタンをクリックします。


[ソルバーのパラメーター]ダイアログが表示されます。


ソルバーのパラメーターを設定します。[目的セルの設定]には目的のセルを指定します。今回の問題では売上の最大化を目的としていますので、売上の合計値である、E6セルセルを指定します。また目標は売上の最大なので、[目標値]のラジオボタンは[最大値]にチェックをします。
続いて、[変数セルの変更]ですが、こちらは生産する"ペンギンクッキー"と"らくだサブレ"の個数になります。C4セルとD4セルが変数のセルになります。
[制約条件の対象]は生産の際の材料の制約になります。小麦粉の使用量のE8セルの値が25以下になる必要があります。また、砂糖の使用量のE9セルの値は10以下になる必要があります。2つの制約式を追加します。
制約式の追加は、[制約条件の対象]セクションの右側の[追加]ボタンをクリックします。[制約条件の追加]ダイアログが表示されますので、参照するセルと制約条件の不等号と制約条件の値を設定します。


[解決方法の選択]には[シンプレックス LP]を選択します。
設定ができたら[ソルバーのパラメーター]ダイアログの[解決]ボタンをクリックします。


計算が実行され[ソルバーの結果]ダイアログが表示されます。ソルバーが解を見つけた旨のメッセージが表示されます。すべての制約条件と最適化条件を満たしている旨のメッセージも表示されます。


ExcelのシートのC4セルとD4セルの値が、計算された最適値に置き換わっています。この値をそのまま利用する場合は、先の[ソルバーの結果]ダイアログの[ソルバーの解の保持]のラジオボタンをチェックしてダイアログの[OK]ボタンをクリックします。下位の値だけ確認して元の数値に戻したい場合は[計算前の値に戻す]ラジオボタンをチェックしてダイアログの[OK]ボタンをクリックします。


結果を確認すると、「ぺんぎんクッキー」の生産数が"1"、「らくだサブレ」の生産数が"3.5"が解になりました。こちらの記事での計算結果とも一致しますので、正しく解が求められていることが確認できます。

ソルバーを利用して線形計画問題を解くことができました。