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条件を追加する必要があります。
そうすると正しい結果がえられます。




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

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

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

今回からソルバーについてふれます。
MOSの受験では、FOM出版の問題集ではExpertの範囲に入ってますが、日経BP出版の問題集にはありません。
微妙ですが、高度な機能であり、是非目をとおされることをすすめます。

ひとつの変数を含む数式の解を求めるにはゴールシークが便利ですが、複数の変数があり、より複雑な数式の解と求める場合にソルバーを使います。
この機能はアドインとして提供されていますので、まずアドインを有効にします。

①[ファイル]タブ→[オプション]クリック
②開く[Excelのオプション]画面で左側の一覧から[アドイン]選択
③[管理]の▼から[Excelアドイン]を選択し、[設定]クリック
④[アドイン]画面が開くので、[ソルバーアドイン]にチェックを入れ[OK]クリック
[データ]タブの[分析]グループの[ソルバー]として追加されます





実際に簡単な問題をやってみましょう。



この表で
目的：予算総額3,000,000円を使ってできるだけおおくの商品を購入する
制約条件:購入総額が予算額以下
各商品は最低でも10以上仕入れる
各商品の注文数の差が５以下とする
各商品の注文数は整数

なお、セル[D3:D5]には「単価*仕入数]セル[D7]にはセル[C3:C5]を合計するSUM関数、セル[D10]にはセル[D3:D5]を合計するSUM関数、セル[D13]には「MAX(C3:C5)-MIN(C3：C5)」がそれぞれ入力されています。

まず、注文数は整数という条件ぬきでやってみます。

①[データ]タブの[ソルバー]クリック
②[ソルバーのパラメーター]ダイアログボックスが開くので
　「目的セルの設定」にはセル[D7]をクリックして「$D$7」と入力、[目標値]として[最大値]選択、
　[変数セルの変更]には「$C$3:$C$5」入力

注）[目的セルの設定]には、目的の値に到達させたい値が含まれるセルを指定セルで、数式が入力されている必要があります。この例では「D7]
　　[目標値]には、目的セルの値を、「最大値」、「最小値」、「指定値」の中から選択、この例では「最大値」、「指定値」を選択する場合は数字を入　　力する必要があります。
　　[変数セルの変更]には、目的セルの値が目標値になるために、値を変化させるセルまたはセル範囲を指定します。この例では「$C$3:$C$5」
　　
③つづいて、[追加]クリックすると制約条件の追加]ダイアログボックスが開くので、購入総額が予算総額以下の条件を入力し、[追加]クリック、
以下制限条件を入力し、すべて入力したら[OK]クリック
④すべての条件が入力されている状態で[解決]クリックすると、[ソルバーの結果]ダイアログボックスが開き、元の仕入れ計算表の内容がかわります。












購入金額がちょうど予算総額に等しくなっており、すべての条件を満足する結果が得られたことがわかります。


では、各商品数は整数という条件を追加してみましょう。

①[データ]タブの[ソルバー]クリック
②[ソルバーのパラメーター]ダイアログボックスが開くので、[追加]をクリック
③[制約条件の追加]ダイアログボックスが開くので、セル範囲[C3:C5]が整数、（整数は「int」と入力）と入力し[OK]クリック




④[ソルバーのパラメータ]画面で[解決]クリックすると、[ソルバーの結果]ダイアログボックスが開き、元の仕入れ計算表の内容がかわります。

結果、すべての仕入れ数が整数になった分、購入金額が減少しましたが、やはりすべての条件を満足する結果がえられました。

　



さらに今回はこの画面でレポート覧の「解答」をクリックします。
そうすると、別シートに「解答レポート」という名で解答が示されます




このような問題が実用的なのか疑問ですが、仕入数を最大にしたいのであれば、当然単価の安いものを多く購入することになりますから、ある程度予想できた解答ではありますね。

この機能は実際には全く使ったことがありませんが、線形計画法などでも応用できるのかもしれません。
次回に別の問題を例示したいとおもいます。

MOS受験にむけて番外編5(What-If分析：ゴールシーク2）

ゴールシークの２回目です。
前回、説明不十分なところがあったと思います。

PMT関数（利率,期間,現在価値,[将来価値],[支払日]）で、定期支払金を求める関数で、通常の借入では現在価値が借入金額になります。（[将来価値],[支払日]は省略します。）

すなわち、この関数は、たとえば年利率が1.2％、返済期間30年の条件で2000万円を借りる場合の月あたりの返済金を求めるものです。
したがって、返済金を変えたときに、返済期間や借入金がどうなるかを求める関数ではないのですが、ゴールシークを使えば求めることができるというわけです。
このPMT関数はMOS試験のExpertに出ます。どうようにFV(Future Value、満期後の受取金額））関数もExpertの範囲になっています。
書式はFV(利率,期間,定期支払額,現在価値（－で入力）,支払期日）で現在価値は頭金（－で入力）で省略すると「0」とみなされます。支払期日は預期日で、期末の場合「0」、期首の場合「1」になります。



このような表において、毎月5万円の積み立てを10年続けたときの受取金額は
セル[B2]に「=FV(B3/12,B4*12,B5)」という式で求めると




のように求めることができます。

このFV関数は積立額、年利率、積立期間から受取金額を求めるもので、受取金額から積立額や積立期間を求める関数ではありません。

そこで、一例として受取金額を「1000万」として年利率、積立期間を変えずに、積立額を求める場合、ゴールシーク機能を使うことになります。

①[データ]タブ→[What-if分析]の[ゴールシーク]クリック
②開く[ゴールシーク]画面で[数式入力セル]に「B2」、[目標値]として「10000000」、[変化させるセルとして「B5]を入力後、[OK]クリック
ゴールシーク画面で解答が見つかりました。としてセル[B5]に「82,921」と表示されます。





受取金額を同じ1000万にして年利率、積立金額を変えずに、積立金を変えた場合にもゴールシーク機能により、約「16.5」年と求めることができます。





このようにPMT関数、FV関数などの財務関数でゴールシーク機能の威力が発揮できそうです。

・PV（利率,期間,定期支払額,[将来価値],[支払期日]）：present value
一定利率に基づいて、ローンまたは投資の現在価値を計算します。すなわち、一連の支払の合計金額を現時点での価値に換算する関数。PV 関数は、定　期的な定額支払 (住宅ローンや他のローンなど) または投資目標であ　る将来価値とともに使用できます。
　じつは前回のPMT関数と関連する関数で積立金額、年利率、支払期間から求めた現在価値はPMT関数の現在価値に相当します。
　したがって、前回の「返済期間「30」年、年利率「1.2％」で毎月の返済額を「80000」円とすると、借入金はいくらになるかという問題」はゴール　　シーク機能を使わなくてもPV関数で求めることができました。

・NPER（利率,定期支払額,現在価値,[将来価値],[支払期日]）：number of period
投資や返済に必要な期間を求める関数で、この関数もPMT関数に関連する関数で、NPER関数で求める期間はPMT関数やPV関数の期間に相当します。

そのほかRATE関数、PPMT関数、IPMT関数などの関数でも使えそうです。関数の説明は省略しますが、関数パレットで関数を表示させ、左下の[この関数のヘルプ]をクリックすると、詳しい説明があります。

ゴールシークは数値の大きさを少しずつ変えて反復計算を行うことによって目標の値に最も近づく時点の値を算出するので、明らかに間違った結果を出すことがあります。
下記の表のセル[B1]には、「=B2^2」という式が入力されています。（^はべき乗を表します）


　
目標値を「4」としてゴールシークど求めると



誤差がでてしまいます。
そこで、精度を上げるrために[ファイル]→[オプション]、[Excelのオプション]画面で[数式]タブの[計算方法の設定]の[反復計算を行う]にチェックをいれ[最大反復回数](初期設定100）を大きくするか、、[変化の最大値](初期設定0.01）を小さくすることによりじゃっかん精度が上がりますが、正解にはならないようです。下記の例では[最大反復回数]を「10000」に、[変化の最大値]を「0.0001」にしています。





いろいろなケースでゴールシークを実行した場合に結果を残したい場合には「シナリオ」として登録しておけば便利だとおもいます。

次回は「ソルバー」についてふれます。

MOS受験にむけて番外編4(What-If分析：ゴールシーク1）

今回は、MOS受験にむけて番外編の4回目でWhat-If分析のゴールシークについてふれます。

ゴールシークとは計算結果（目標値）を先に指定して、その結果を導きだすために必要な入力値（変数）を逆算する機能です。
わたしは長い間このゴールシークという機能は使うことはないだろうと過小評価していました。
次の例で説明します。



上のの算出表から家賃の賃料単価をいくらにすれば、合計が1680,000になるかもとめる問題です。
ゴールシークの機能で求めてみると、
①[データ]タブ→[What-if分析]の[ゴールシーク]クリック
②開く[ゴールシーク]画面で[数式入力セル]に「D6」、[目標値]として「1680,000」、[変化させるセルとして「B3]を入力後、[OK]クリック
ゴールシーク画面で解答が見つかりました。としてセル[B3]に「101,200」と表示されます。








なお、セル[D3:D5]には「単価*数量」の計算式、セル[D6]にはSUM関数（合計を加算）が入力されていますが、たとえば「数式入力セル」として[D3]を指定すると、家賃の合計が1680000になってしまうので注意が必要です。

この説明をよまれて、「うん？ゴールシークなんてしらなくてもいいじゃん！」と思われたのではないでしょうか？
そのとおりです。このように四則演算（加減乗除）により求めるる場合には、ゴールシークを知らなくても簡単に逆算できます。
賃料合計1680000から駐車場と駐輪場の料金を引いて「15」で割れば簡単に求めることができます。
私自身ゴールシークを使う必要はないと過小評価していました。

しかしながら、下の表において、返済金を月８万円にしたら、返済期間（年）はどうなるでしょうか
という場合、どうするでしょうか、セル[B5]には、計算式「=PMT(B3/12,B4*12,B2)」(定期返済額を求めるpayment関数）が入っていますが、この計算式に未知数ｘなどを使ってもエラーになり、方程式を解くようににはいきません。




このような四則演算でない数式の場合に、ゴールシークの出番です。

①①[データ]タブ→[What-if分析]の[ゴールシーク]クリック
②開く[ゴールシーク]画面で[数式入力セル]に「B5」、[目標値]として「-80,000」、[変化させるセルとして「B4]を入力後、[OK]クリック
ゴールシーク画面で「解答が見つかりました」。としてセル[B4]に「23.985491」(年）という解答がえられます。



この解答が正しいかどうか「=PMT(B3/12,B4*12,B2)」（セル[B4]には「23.985491」）で計算してみると、
返済額が「-80000」とぴたりと一致しており、正しいことがわかります。

この問題で、返済期間「30」年、年利率「1.2％」で毎月の返済額を「80000」円とすると、借入金はいくらになるかという問題もできそうですね。

ゴールシーク画面で、「数式入力セル」と「目標値は」同じにして「変化させるセル」をセル[B2]に変えるだけです。



返済額「66182」に比べ、増えていることがわかります。
これもPMT関数で逆算すると「返済額」がぴたり「-80000」になります。

その他どのような関数を使用する場合にゴールシークが有益か、また注意すべき点もあります。
それは次回で・・・・