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」になります。

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

MOS受験にむけて番外編3(What-If分析：シナリオ2）

MOS受験にむけて番外編の３回目で、シナリオの続きです。
番外編としたのは、通常いままでアップしてきた内容は主にMOSの一般用の内容でしたが、今回のWhat-If分析はMOSのEXPERT用だからです。
EXPERT用といっても特別難しいものではないと思いますので、できましたら、今回のシナリオと次回以降の「ゴールシーク」、「ソルバー」もやさしい実例を示しますので、エクセル上でやってみてください。

シナリオ2：詳しい手順は前回のブログを参照してください。
北ヨーロッパ旅行をする場合のシナリオ作成してみます。




この表で「エコノミー」、「ミドル」、「エグゼクティブ」の３種類のシナリオを下の表のようなデータを入力、作成してみます。



セル[A2]がシナリオ名になります。

①[データ]タブ→[What-If分析]→[シナリオの登録と管理]を開く
②[シナリオの登録と管理]画面で[追加]クリック
③[シナリオの編集]画面でシナリオ名「エコノミー」を入力、[変化させるセル]として「A2,B3:E3,B9」と入力して（最初セル範囲[A2]をクリック、Ctrlキーをおしながら、セル範囲[B3:E3]と[B9]をクリックすればよい）、[OK]クリック
④[シナリオの値]画面に値を入力し、[追加]をクリック。
⑤[シナリオの値]画面に「ミドル」のデータを入力、[追加]クリック
⑥[シナリオの値]画面で「エグセグティブ」のデータを入力、[OK]クリックすれば完了です。


これらのシナリオをもとに、たとえば「エコノミー」を選択して表示させると






さらに「ミドル」、「エグゼクティブ」と切り替えると表示を切り替えることができます。



前回と合わせて少ない例示だけでは、十分な効果が認識することができないかもしれません。
ただ、実際には「変数」の数は32まで可能であり、また、シナリオの数は要約レポートの数は上から251と制限があるようですが、単に表示させるだけならパソコンの能力次第ということで、意外に変数の数もシナリオの数も多く使うことができるので、効果的な使用方法があるかもしれません。


次回は「ゴールシーク」について触れます。

MOS受験にむけて番外編2(What-If分析：シナリオ1）

MOS受験にむけて番外編の２回目です。
前回はデータテーブルでしたが、今一つ有用性に疑問符が付きました。
今回はシナリオという機能を取り上げます。

・シナリオ
変数が１つまたは2程度の単純なシミュレーションでは、データテーブルが有効です。
しかし、現実世界における意思決定においては、不確定な要素がさらに多くなります。
こうした複雑な状況のシミュレーションにシナリオという機能が有効になります。
すなわち、変数の値を組み合わせて登録したものであり、計算結果を比較する場合に使います。

具体的に説明します。



この表でネット通販の上昇率と電話通販の上昇率を変えたときの３つのシナリオを作成してみます。
ネット通販と電話通販の今期の各セルには「前期（１+上昇率）」の計算式が、各前年比には「後期計/前期計」の計算式が入力されています。
３つのシナリオは
シナリオ名：予測１
ネッツ通販上昇率　東京：2.5％、大阪：1.8、福岡：0.7％、電話通販東京：-0.5％、大阪-0.4％、福岡：-0.3％
シナリオ名：予測2
ネッツ通販上昇率　東京：4.5％、大阪：3.8％、福岡：2.6％、電話通販東京：-0.3％、大阪-0.2％、福岡：-0.1％
シナリオ名：予測3
ネッツ通販上昇率　東京：6.5％、大阪：4.7%、福岡：3.8％、電話通販東京：-1.9％、大阪-1.4％、福岡：-1.2％
という条件で作成します。
　
①[データ]タブ→[What-If分析]→[シナリオの登録と管理]を開く
②[シナリオの登録と管理]画面で[追加]クリック
③[シナリオの編集]画面でシナリオ名「予測1」を入力、[変化させるセル]として「D5：D7,G5：G7」と入力して（最初セル範囲[D5:D7]をクリック、Ctrlキーをおしながら、セル範囲[G5:G7]をクリックすればよい）、[OK]クリック
④[シナリオの値]画面に値を入力し、[追加]をクリック。
⑤[シナリオの値]画面に「予測2」のデータを入力、[追加]クリック
⑥[シナリオの値]画面で「予測3」のデータを入力、[OK]クリックすれば完了である。






出来上がったシナリオをどう利用するのか？
すくなくとも3種類のシミュレーションができます。
[データ]タブ→[What-If分析]クリックして開く[シナリオの登録と管理]画面で「予測1」を選択して、[表示]をクリックすると、「予測1」のシナリオが表示されます。同様に「予測2」、「予測3」のシナリオを瞬時に切り替え表示させることができます。







また、シナリオを実行する前のデータと登録した複数のシナリオの実行結果をまとめて新しいシートに表示させる「レポート」という機能もあります。
[シナリオの登録と管理]画面で[情報]をクリックすると、[シナリオの情報]画面が開くので、「シナリオの情報」を選択、[結果を出力させるセル]は、数式が入力されたセルが自動的に表示されるので、そのまま[OK]をクリックします。
そうすると、別シート（「シナリオ情報」という名前）にアウトライン(おりたたんだり、展開することができます）が設定されて結果が表示されます。





この説明の中でシナリオを表示させたときに「シナリオ名」が表示されません。
これはまずかったですね。
変化させるセルに、名前を表示させたいセルを「変化させるセル」に追加するだけでよかったのですが、すべて書き直す必要があったので、ごめんなさい。
シナリオの説明はこれだけでも十分だと思いますが、実例をもう少し追加したいので、次回もシナリオの説明をいたします。