ExcelのWhat-If分析「ゴールシーク」の使い方を徹底解説

Microsoft Excelには数多くの関数が搭載されており、それらは表計算ソフト最大の魅力の一つといえます。しかし、その関数の可能性をさらに広げる「隠れた便利機能」の中には、意外と知られていないものも少なくありません。その代表格が「What-If分析」です。

What-If分析は、大きく分けて3つの機能で構成されています。本記事で取り上げるのは、その中でも特に強力な「ゴールシーク」です。ゴールシークを使えば、数式の結果から逆算して、目的の出力値を得るために必要な入力値を自動的に求めることができます。この記事では、具体的な活用例とともに、ゴールシークの使い方をわかりやすく解説します。

ゴールシークの活用例：住宅ローンの許容金利を求める

ここでは、住宅ローンを例に考えてみましょう。10万ドル（約1,000万円相当）のローンを30年かけて返済する場合、金利が年間返済額にどのような影響を与えるのか気になるところです。

ExcelのPMT関数を使えば、金利0%の場合の年間返済額を簡単に計算できます。ワークシートはおおよそ次のようになります。

セルA2は年利、セルB2はローン期間（年数）、セルC2は借入額を表しています。セルD2に入力する数式は以下の通りです。

=PMT(A2,B2,C2)

この数式は、金利0%・期間30年・借入額10万ドルという条件での年間返済額を求めるものです。D2の値がマイナスで表示されるのは、Excelが返済を「マイナスのキャッシュフロー」として扱う仕様のためです。

残念ながら、金利0%で融資してくれる金融機関は現実には存在しません。そこで、年間6,000ドルまでなら返済可能だと仮定してみましょう。すると、「年間返済額を6,000ドル以内に抑えるには、最大どれくらいの金利まで許容できるのか？」という疑問が生まれます。

このような場面で多くの人がやりがちなのが、セルA2にいろいろな数字を打ち込みながら、D2の値が6,000ドル前後になるまで手作業で試行錯誤する方法です。しかし、What-If分析のゴールシークを使えば、この作業をすべてExcelに任せられます。つまり、D2の結果から逆算させ、年間支払額6,000ドルという条件を満たす金利を自動的に導き出すのです。

ゴールシークの実行手順

まず、リボンの「データ」タブをクリックし、「データツール」グループにある「What-If分析」ボタンを見つけます。このボタンをクリックし、メニューから「ゴールシーク」を選択してください。

小さなダイアログが開き、3つの項目の入力を求められます。

• 対象セル： 数式が入力されているセルを指定します。この例では D2 です。
• 目標値： 分析完了後にセルD2へ表示させたい値です。返済はマイナスのキャッシュフローとして扱われるため、ここでは -6000 を入力します。
• 変化させるセル： Excelに求めてほしい値が入力されるセルです。年間返済額が6,000ドルになる金利を知りたいので、A2 を指定します。

「OK」をクリックすると、Excelが各セルに一瞬たくさんの数値を表示しながら反復計算を行い、最終的な答えへと収束していきます。この例では、セルA2はおよそ 4.31% になります。

この結果から、「30年・10万ドルの住宅ローンで年間返済額を6,000ドル以内に抑えるには、金利4.31%以下でローンを組む必要がある」ことがわかります。数値や変数の組み合わせをいろいろ変えて試せば、有利な金利でローンを組むための選択肢を幅広く検討することも可能です。

まとめ

ExcelのWhat-If分析「ゴールシーク」は、スプレッドシートのさまざまな関数や数式を補完する強力なツールです。セル内の数式の結果から逆算することで、計算に関わる変数をより明確に把握でき、意思決定の精度も高まります。ローンの返済計画だけでなく、売上目標の達成条件や予算配分など、ビジネスシーンでも幅広く応用できるので、ぜひ活用してみてください。