Excelで始める最適化問題～ソルバー実践編～

みなさんこんにちは。
テック博士です。

前回は、最適化問題を解く上で非常に役に立つ
「Excel ソルバー」の導入方法についてお話しました。

そして、私からの問題も皆さんに考えてもらおうと、
先んじて出していましたね。
今日は、その問題について
ソルバーを用いて一緒に解いてみよう。

少し長い戦いにはなるけれども、
この記事だけでソルバーの使い方や注意事項がわかるから、
根気強く追っていってほしい。


ちなみに、まだソルバーをExcelに入れてない！
という人は、先にソルバーを入れてからにしよう。
前回の記事
「Excelで始める最適化問題～ソルバー導入編～」
にてソルバーが使えるようになるからね。


それでは、まずは問題をおさらいしておこう。



さて、この問題をソルバーで解いていく。
まず、Excelを開いて下準備をしよう。




まず、机と椅子について、必要な材料と利益、
作る個数についての表を作り、
となりには、手持ちの材料の数を記しておこう。

ここで、最初は作る個数が決まってないから、
どちらとも0にしておく。

ソルバーを使う上では表である必要はないけれども、
自分がわかりやすいようにしておく必要があるね。


次に、使う木材を図のように
”必要な材料”×”作る個数”
という形で式を書いてあげよう。

実際に図のような式を書くと、使う材料はそれぞれ”0”に
なるはずだけども、それで問題ない。
なぜなら作る個数は、今の段階では共に0個だからね。


そして最後に得る利益の計算式を次の図のように準備しよう。




もちろん、この式を書いても利益は0だ。
ここまでが、ソルバーを使う為の下準備となる。


ではここで、
前回お話した目的と条件について、この表を使って説明しておこう。



最適化問題では、一般的に
目的のことを”目的関数”、条件のことを”制約条件”
というんだ。

今回でいうと、目的関数は得る利益になるわけだ。
そして、目的関数を最大化することが
今回やりたいことだね。


次に、制約条件は、この表の手持ち材料と使う材料となる。
手持ち材料以上のものを、使うことはできないからだ。
式で書くと、




さあ、ようやくソルバーを使う準備ができたので、
ソルバーを開いてみよう。
開くには、「データ」タブの一番右にある
「ソルバー」を押せばいいんだった。

開くと次の画面が出てくるはずだ。


この画面で、「目的セル」の部分には、先ほどの目的関数のセル
つまり利益のセル”B8”を選択する。

そして、今回は、この目的セルを”最大化”したいので、
最大化のラジオボタンを選択。


次に「変化させるセル」だ。
これは、最適化する為にどのセルを調節すればいいか、を
示してあげればよい。

そして、これが最適化問題の”知りたいもの”にあたる部分になるんだ。
今回の場合、知りたいのは
「机と椅子をどれだけ作ればよいか(利益が最大になるか)」 なので、
”E2”と”E3”のセルを選択してあげよう。
これは範囲指定のように ”$E$2:$E$3”と書いても構わない。


そして最後に「制約条件」だ。
これはさっき説明した通りの条件を書けばいいので、
「追加」を押して、設定してあげよう。

その際、木材の制約条件と、鉄の制約条件の
2つを制約条件として書く必要がある。

例として、木材の制約条件は次の画像のように書いてくれればいい。


この画像では、
がきちんと表されているね。 鉄も同様に制約条件を追加しておこう。

すべてがおわると、次のようになっているはずだ。



さあ、いよいよ実行する時がきた。
実行ボタンをおして、最適化をしてみよう！


…
あれ。


机の作る個数は6.666…個で小数、
椅子の作る個数はマイナスになってしまった！
じつは、今回の最適化問題は制約条件が足りなかったんだ。


まず1つ目。
机と椅子の作る個数は負の値を取らない。
(-3個作る　みたいなことはない)

そして2つ目。
机と椅子の作る個数は整数である。
(6.6個作る　とかはなし)

そう、これが抜けているんだ。
これは表からわかるものではなく、その時の問題に合わせて
最適化する人(ソルバーを操作する人)が気付かなければならない。

こういったセンスが必要なのも、 データサイエンスの
奥深さのゆえんの一つなのかもしれないね。



さあ、では制約条件を付け加えよう！
まず、負の値に関する制約条件として、  
を加えよう。
次の図は机の作る個数に関する制約条件だ。



これにならって、椅子の制約条件も追加しよう。

次に、整数に関する制約条件だ。
これは、さっき「≧」としていた
中央のプルダウンを「区間」 にすることで、整数とできる。

同様に机の作る個数の条件の図を載せておくから、
椅子に関しても自分でやってみてほしい。



全てがおわると、ソルバーの設定画面はつぎのようになっている。



さあ、長くなったがこれで準備は万端だ！ いよいよ実行を押してみよう。

やったね！！

作る個数は、机が6個、椅子が2個で、
得る利益は33万円が最大らしい。

使う木材は98[Kg]で手持ち範囲内、
使う鉄は20[Kg]できっちり全部使い切っている。

これで最適化ができたわけだ！！


あとは、OKを押すとこの状態でソルバーを閉じてくれるから、
最適化完了となる。


さあ、これで最適化の第一歩を踏むための
ソルバーの使い方は一通り学ぶことができた。


どうだろう、長い長い記事ではあったけども、
この時間でソルバーを使うことができる状態まで来れたんだ。

あとはいろいろな最適化問題を実際にソルバーを触ることで
慣れていってほしい。
まだまだいろんな活用法があるからね。


それでは、今回はこの辺で。
お疲れさまでした！！