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の問題として出題されるようになっています。

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