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

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