ゴールシークを使って数式を逆算する

今回は、ゴールシークという機能を使って数式を逆算する方法を紹介する。電卓で代用できる場合もあるが、ゴールシークを使うと、より手軽に解答を求めることが可能だ。Excelを便利に活用する方法として覚えておくとよいだろう。

ゴールシークの使い方

ゴールシークは、数式を逆算して「適切な数値」を求めてくれる機能。と言われても、機能の概要を想像しにくい人が多いだろう。そこで、簡単な例を用いてゴールシークの使い方を紹介していく。

以下は、「割引率」から「割引後の価格」を計算する表だ。この計算は数式により処理されている。たとえば、1番上の行(3行目)の場合、E3セルに入力されている「＝C3*(1-D3)」という数式によって、20%引き後の割引価格を算出している。

• 割引価格を求める数式

では、「割引後の価格」を14,800円にするには「割引率」を何パーセントに設定すればよいだろうか？　このような場合に活用できるのがゴールシークだ。

さっそく、具体的な操作手順を解説していこう。まずは、例のように数式を入力した表を作成。続いて「データ」タブを選択し、「What-If分析」→「ゴールシーク」を選択する。

• ゴールシークの起動

すると、以下のような設定画面が表示される。まずは「数式が入力されているセル」を指定する。今回の例では、E3セルを指定すればよい。

• 数式を入力したセルの指定

続いて「目標値」を指定。今回の例では「割引後の価格」を14,800円にしたいので14800と入力する。

• 目標値の指定

最後に「変化させるセル」を指定する。今回の例では「割引率」を変化させて調整するので、D3セルを指定すればよい。以上でゴールシークの設定は完了だ。最後に「OK」ボタンをクリックしよう。

• 値を変化させるセルの指定

すると、数式の逆算が行われ、その結果がセル内に表示される。今回の例の場合、24.10%の「割引率」を設定すれば「割引後の価格」が14,800円になることが分かる。

• 逆算された数値

その後、「OK」ボタンをクリックすると、求められた数値をD3セルに残した状態でゴールシークが終了する(D3セルの値が24.10%に変更される)。「キャンセル」ボタンをクリックした場合は、今回のゴールシークの結果が破棄され、元の数値に戻る(D3セルは20.00%のまま変更されない)。

このように「目標値」と「変化させるセル」を指定するだけで、最適な数値を逆算してくれるのがゴールシークだ。電卓を使って「割引率」を求めることも不可能ではないが、ゴールシークを使った方が素早く解答を導き出せる場合も多いので、この機会に使い方を覚えておくとよいだろう。

関数を含むゴールシークの使い方

もちろん、関数を含む計算にゴールシークを活用することも可能だ。以下は、2,500万円を金利1.50%、30年間で返済する場合の「毎月の返済額」を求めた表である。

• 「毎月の返済額」を求める関数

「毎月の返済額」の算出には関数PMTを利用している(詳しくは前回の連載を参照)。このように複雑な計算を行っている場合は、電卓を使って逆算を行うのは難しいだろう。しかし、ゴールシークを使えば簡単に逆算を行うことが可能だ。

たとえば「毎月の返済額」に7万円しか払えない場合、「いくらまで借りられるのか？」をこの表から求めることができる。この場合は、「目標値」に70000、「変化させるセル」にC4セル(借入額)を指定してゴールシークを実行すればよい。

• ゴールシークの設定

すると、以下のように結果が表示され、「約2,028万円のローンが組める」と分かる。

• 「借入額」を逆算した結果

同様に「変化させるセル」にC6セル(期間)を指定してゴールシークを実行すると、以下のような結果が表示される。つまり、「期間」を約39.4年に伸ばせば、毎月の7万円の返済で2,500万円を借りられることが分かる。

• ゴールシークの設定

• 「期間」を逆算した結果

ただし、ゴールシークを使っても、適切な数値が見つけられない場合があることに注意しなければならない。この場合は、結果の画面に「解答が見つかりませんでした。」と表示される。たとえば、先ほどの例で「目標値」(毎月の返済金額)に10000を指定してゴールシークを実行すると、以下のような結果が表示される。

• 解答が見つからない場合の表示例

目標値を10000に指定しているにも関わらず、「毎月の返済額」は31,250円と表示されてしまった。これは、毎月1万円の支払いでは利子すら返済できないことが原因。金利1.5%で2,500万円という条件の場合、最初の1カ月の利子だけでも31,250円が必要になる。つまり、毎月1万円の返済でローンを組むことは不可能という訳だ。

このように、設定した数値によっては、適切な解答を得られない場合もある。ゴールシークを使うときの注意点として、念のため覚えく必要があるだろう。