MOS受験にむけて番外編7What-If分析：ソルバー3）

今回からソルバーの実例をできるだけ多く挙げたいとおもいます。
前回、線形計画法の生産計画問題が解けることを確認しました。

今回もソルバーで解ける生産計画問題をできるだけ例示します。
問題はやはり培風館発行の木戸睦彦氏が著作された「現代数学レクチャ-ズ」（以下、参考資料1と略称）を参考にさせてもらいます。

「ある会社で、4種類の製品A1、A2、A3、A4を製造し、出荷している。
A1について、1個あたり部品の製造に100分、組立てに60分、包装に20分を要し、A2については1個あた、それぞれ120分、50分、20分、A3については1個あたりそれぞれ70分、120分、30分、A4については1個あたり、100分、80分、50分を要する。
ある期間中に、部品製造に使える時間が8800分、組立てには8220分、包装には3000分である。
A1、A2、A3、A4の各1個についての利益が3万円、4万円、5万円、6万円であるとき、この期間中に各何個を製造するのがもとっとも有利か？」
という問題ですが、これをソルバーが使えるように表すと下のような表になります。




時間というのは部品の製造時間を表します。

さらに数式を入れた状態を示すと



のようになります。
ここで当然のことながら、製品A1、A2,A3,A4とも製造と組み立てと包装とも同じなので、セル[F3：F6]とセル[I3:I6]には「=C3」...「=C6」と入力しています。
それ以外にはとくに難しいところはないとおもいます。

この表に基づいてソルバーを実行します。
[ソルバーノパラメーター]画面で、[目的セルの設定]には「$I$19」、[目標値]として[最大値]選択、[変数セルの変更]には「$C$3:$C$6」と入力、さらに制約条件を追加して、[解決]クリックすると下の表のような結果がえられます。
この結果は上記参考資料1の解答にぴったり一致しました。
製品A1,A2A3,A4を作る個数をそれぞれ0,30,40,24にするのが最適で、利益は464万円になるということです。





では、次の問題にいってみましょう

「3つの農場があり、その広さは30hA、40hA、50hAである。A、Bの2種類の農作物を作るが、1hAあたりのAの収穫は農場によって異なり、それぞれ20,30,30であり、同じくBは20,25,20である。全体としてA,Bの収穫の比は5：3になるようにしたいと考えている。
なるべく多くの収穫を得るには、どのように作付けするのがよいか（小数計算によれ）？」という問題です。

3つの農場をF1,F2,F3として、表を作るのはそんなに難しくないはずです。



数式を入れた状態を示すと、



のようになり、

この表に基づいてソルバーを実行します。
[ソルバーノパラメーター]画面で、[目的セルの設定]には「$G$21」、[目標値]として[最大値]選択、[変数セルの変更]には「$D$3:$D$5,$G$3:$G$5」と入力、さらに制約条件を追加して、[解決]クリックすると下の表のような結果がえられます。
なお、A:B=5:3は[$H$18=0.6*$H$17]としています。





この結果は上記参考資料1の解答にぴったり一致しませんでした。
参考資料の解答は
「30hAの農場F1にはBだけを、40hAの農場F2にはAを16.25hA（16.2791）、Bを23.75ｈA（23.7209）,50hAの農場F3にはAだけを作付けするのがよい。収穫はA1987.5（1988.372）、B1193.8（1193.023）である」と微妙に違う結果がでました。()内はソルバーで解いた結果

AとBの収穫量の合計は参考資料の解答は3181.3、ソルバーで解いた合計は3181.395でほぼ同じだが、厳密にはソルバーの解が題意に合っています。
また、B/Aは0.6にどちらが近いかをみると、参考資料では0.600654に対してソルバーの解は0.5999998となり、こちらもソルバーの解の方がまさっており、そのほかすべての制約条件を満足しているので、ややソルバーで求めた解の方がまさっているようです。


次回は輸送問題なども取り上げる予定です。

MOS受験にむけて番外編6(What-If分析：ソルバー2）

ソルバーの2回目です。
ソルバーについては、どんなケースで適用可能かが問題です。
何しろ得られた結果が制限事項に合致しているから正しいといえるとはおもいますが、やはり確認する方法がないだけに心配です。
したがって、答が自明の問題集などを参考にして解いていましたから、どんなケースで使えるかについては自信をもっていえませんでした。

今回は日経BP社の「Excel2000オフィシャルマニュアル」の問題をまずとりあげます。

[雑誌媒体向けの新製品の広告キャンペーンを考えます。
広告予算1200万円、広告主は最低でも800万人の読者の目に触れさせたいと考えています。そこで6種類の雑誌媒体を選びました。
次の制約条件を満足させながら最小の費用で達成することです。]





制約条件にあがっていませんが、当然掲載回数は整数になるので、その条件を加えてソルバーを実行します。

手順は省略します。





すべての制約条件を満足しています。

ここまで、既存のソルバーに関する問題を紹介してきましたが、それ以外では私自信もどのようなケースで使えるのか自信がありませんでした。

そこで図書館から線形計画法に関する本を借りてソルバーで解けるのか試すことにしました。
参考書は意外に少なく、1冊は培風館発行の木戸睦彦氏が著作された「現代数学レクチャ-ズ」（以下、参考資料1と略称）を参考にさせてもらいました。

まず、線形計画法の代表的な問題である生産計画問題から

「ある工場で、原料のQ1、Q2、Q3を用いて2種類の製品P1、P2を製造することができる。
P1を1Kg作るのにQ1、Q2、Q3をそれぞれ0.8Kg、0.2Kg、0.3Kg使用し、P2を1Kg作るのにQ1、Q2、Q3をそれぞれ0.6Kg、0.8Kg、0.4Kg使用する。
P1、P2を1Kg作ると、それぞれ1万円、2万円の利益が得られる。
なるべく多くの利益を上げたいが、Q1、Q2、Q3の使用には限度があり、それぞれ8.8Kg、6.4Kg、4.0Kgまでである。どのような生産計画を立てれば利益が最大になるか？」という問題です。

線形計画法ではP1をx1（Kg）、P2をx2（Kg)生産するとし

　　

上記の１次不等式を満足するときの利益ｚを最大にするものを求める問題になり、図形で解くかシンプレックス法などの方法によりとくことになり、結構面倒なもののようです。
そこで、ソルバーが使えるように下記のような表を作りました。作りかたに特別な工夫入らないと思います。



ここで、「単価」は製品1Kgをつくるに必要な量、「生産量」は製品の量、「使用料」は原料の使用量で、「単価」×「生産量」の計算式が入力されていますす。
「生産量」についてはx1、x2などの未知数を使うのはゴールシークの場合同様不可です。これは文字列なので計算するとエラーになります。
計算式を表示させると下の表に示すようになります。



生産量は原料1から原料3に対して同じなので、原料2の生産量と原料3は、原料1のセル[C3]とセル[C4]にリンクさせています。





この表に基づいてソルバーを実行します。
[ソルバーノパラメーター]画面で、[目的セルの設定]には「$I$16」、[目標値]として[最大値]選択、[変数セルの変更]には「$C$3:$C$4」と入力、さらに制約条件を追加して、[解決]クリックすると下の表のような結果がえられます。
この結果は上記参考資料1の解答にぴったり一致しました。
製品P1を4Kg,製品P2を7Kg生産したときの利益が最大(18万円）になるということです。



生産計画法をしらなくても、一発で解けたのですから、この結果はうれしかったですね。

この例では非負条件（生産量≧0）を制約条件に加えていませんが、加えることをいつも心がけたほうがいいかもしれません。
条件が足りなくて正解がえられないということをさけるために日ごろから習慣付けしておいた方がいいのかもしれません。


それから、原料2の生産量と原料3は、原料1のセル[C3]とセル[C4]にリンクさせていますが、このリンクを解いてしまって、セル範囲[$F$3:$F4]とセル範囲[$I$3:$I$4]を[ソルバーノパラメーター]画面の[変数セルの変更]欄に追加するだけでは、正解はえられません。
さらに、[ソルバーノパラメーター]画面の[制約条件の設定]欄にC3=F3,C3=I3とC4=F4、C4=I4の4条件を追加する必要があります。
そうすると正しい結果がえられます。




このほか、実用的に使える実例をできるだけ多く紹介したいとおもっています。

次回は線形計画法の「生産計画問題」を取り上げる予定です。