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受験にむけて番外編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]をクリックします。
そうすると、別シート（「シナリオ情報」という名前）にアウトライン(おりたたんだり、展開することができます）が設定されて結果が表示されます。





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

MOS受験にむけて番外編1(What-If分析：データテーブル）

ブログの投稿は2015年7月以来ですから1年近くご無沙汰しました。
エクセルの「MOS受験にむけて」にいたっては2014年8月以来で2年近くご無沙汰していました。

それでも,6月15日のアクセス数は閲覧す数が105件、訪問者数が53件ありました。
本当なんでしょうか？1年近く投稿していないのに、これだけのアクセスをいただいたことが信じられないです。

今回エクセルの「MOS受験に向けて」を再開するというほどではなく、時々投稿する程度になるとおもいますが、きっかけはある人が「What-If」について聞いてくれたことがきっかけです。その人は女性ですが、非常に意欲的なひとで、知識欲も旺盛なので、いつも刺激を与えてくれます。

「What-If」とは「What-If分析」のことで、「データテーブル」、「「シナリオ」、「ゴールシーク」、［ソルバー］などの機能で、変数の値を変えて返済額や利益などの結果がどのようになるかを確認する機能です。
実はエクセル2002バージョンでMOS上級（のちのExpert)を受験した時の試験範囲だったので、一応やったことはありますが、ピボットテーブルなどと違って、それほど実社会では効果はないとおもっていたので、実際に使ったことは一度もありませんが、今回もう一度復習して、使えるかもしれないと思って、投稿することにしたのです。

・データテーブルについて
セルの値を変化させて計算結果を確認する玉目の機能です。データテーブルで処理できる変数は１つまたは２つまでです。
１つのセルに順番に値を代入して計算結果を得る「単入力テーブル」、２つのセルに順番に代入して計算結果を得る「複入力テーブル」があります。



上左の表は「単入力テーブル」の例で空欄の黄色塗りつぶし部分を求める問題です。

1つの方法はセル[C8]に「=PMT(B3/12,B4*12,B2)」と入力してコピーする方法があります。

もう一つの方法がデータテーブルの機能を使う方法です。
①セル［B5］に=PMT(B3/12,B4*12,B2)と入力
②セル[B8]に数式のあるB5を「=B5」と入力
③セル範囲[A8:B15]を選択して[データ]→[What-if分析]→[データテーブルブル]クリック、[データテーブル]画面で「列の代入セルに変数であるB3を絶対参照で入力して[OK]クリックすると上右の表のようにもとめることができます。
計算結果が示されるB9：B15には{=TABLE(,B3)}と入力されています。TABLEという特殊な関数がつかわれ、全体が配列数式になっています。（式の両側に中括弧がついている。

次は複数の数式を対象にした単入テーブルの例です。



セル［B6］には、数式「=PMT($B$4/12,$B$5*12,B3)」が入力、セル［C6］には「=PMT($B$4/12,$B$5*12,C3)」が入力されています。
ここで、このPMT関数だけを使っても黄色部分を求めることができますが、データテーブルを使ってもとめてみると
①セル［B9］、［C9］にはそれぞれ「=B6」、「=C6]と入力
②セル範囲［A9:C16］選択して、[データ]→[What-If分析]→[データテーブルブル]クリック、[データテーブル]画面で「列の代入セルに変数であるB4を絶対参照で入力して[OK]クリックすると上右の表のようにもとめることができます。

つぎに変数が2つある複入力テーブルの場合について説明します。



ここで肝心なのは数式が入ったセルとして、このような複入力セルでは、行と列の代入する値の一覧の交点にあたるセル［A8］に数式「=PMT(C41/12,C42*12,C40)」を入力します。
①その状態でセル範囲［A8:F16］を選択し、②[データ]→[What-if分析]→[データテーブルブル]クリック、[データテーブル]画面で「行の代入セル」にB4を「列の代入セルに変数であるB3を絶対参照で入力して[OK]クリックすると下の表のようにもとめることができます。



いずれの例も簡単に結果を求めることができますが、データテーブルを知らなくても、通常のPMT関数を使う方法で求めることができるので、有用性に疑問があります。
このデータテーブルは昔のたとえばExcel2000バージョンでもすでにありました。しかしながら私が受けたMOS2002バージョンのExpertでは出題されなかったので、使ったことがありませんでした。ただ、バージョン2010ではExpertの問題として出題されるようになっています。

次回はたぶんデータテーブルより有用と思われる「シナリオ」の機能について述べます。