Excelのゴールシーク機能の使い方【目標値から必要な入力値を逆算する方法】

さまざまな値を試してみたい場合、特に数式の結果は分かっているものの、その結果を得るために必要な入力値が不明なケースでは、「ゴールシーク」機能が非常に役立ちます。例えば、銀行から融資を受ける際に、希望する月々の返済額を達成するためにはどの程度の金利が必要なのかを知りたい、といった場面が挙げられます。Microsoft Excelのゴールシーク機能を使えば、このような逆算を簡単に行うことができます。

Excelでゴールシークを使う手順

以下の手順に従って、Excelのゴールシーク機能を使用しましょう。

1. Excelを起動します。
2. 「借入金額」「利率」「期間(月)」「月々の返済額」を含むローン表を作成します。
3. PMT関数を使って月々の返済額を計算します。
4. 続いて、ゴールシークを使用します。
5. 「データ」タブの「予測」グループにある「What-If分析」ボタンをクリックし、メニューから「ゴールシーク」を選択します。
6. ゴールシークのダイアログボックスが開きます。
7. 「目的セル」ボックスに、解決したい数式が含まれるセルの参照を入力します。
8. 「目標値」ボックスに、求めたい数式の結果を入力します。
9. 「変化させるセル」ボックスに、調整したいセルの参照を入力します。
10. 「OK」をクリックします。
11. ゴールシークの状態ダイアログボックスが開き、目標値と現在値の結果が表示されます。

それでは、具体的な操作方法を詳しく見ていきましょう。

Excelを起動します。

「借入金額」「利率」「期間(月)」「月々の返済額」を含むローン表を作成します。

PMT関数を使って月々の返済額を計算します。例えば上の画像では、数式 =PMT(B2/12, B3, B1) を使用しています。ただし、この時点ではB2(利率)に値が入力されていないため、Excelは利率を0%とみなし、($500.00) という値を返します。

次に、ゴールシークを使用します。

「データ」タブの「予測」グループにある「What-If分析」ボタンをクリックし、メニューから「ゴールシーク」を選択します。

ゴールシークのダイアログボックスが開きます。

「目的セル」ボックスには、解決したい数式が含まれるセルの参照を入力します。ここでは、月々の返済額が入力されている「B4」を指定します。

「目標値」ボックスには、求めたい数式の結果である「-800」を入力します。「800」にマイナス符号を付けているのは、この値が支払い(出費)を表すためです。

「変化させるセル」ボックスには、調整したいセルの参照を入力します。ここでは、まだ値が空欄になっている「B2」を指定します。

「OK」をクリックします。

ゴールシークの状態ダイアログボックスが開き、目標値と現在値の結果が表示されます。内容を確認したら「OK」をクリックして閉じます。

その結果、B2セルに自動的に7%の利率が入力され、B4セルの月々の返済額が($800.00)に更新されたことが確認できます。これにより、月々800ドルの返済を実現するためには約7%の金利が必要であることが分かりました。

このチュートリアルが、Excelのゴールシーク機能の理解に役立てば幸いです。ご不明な点がある場合は、ぜひコメントでお知らせください。

ゴールシークとデータテーブルの主な違いは何ですか?

ゴールシークもデータテーブルもWhat-If分析の機能ですが、両者には明確な違いがあります。ゴールシークは、求めたい特定の値を実現するために必要な入力値を見つけるために使用されるのに対し、データテーブルは、複数の入力値に対応する出力結果を一覧として同時に確認するために使用されます。

シナリオマネージャーとゴールシーク機能の違いは何ですか?

シナリオマネージャーも、ExcelのWhat-If分析機能の一部です。シナリオマネージャーでは、異なる値のセット(シナリオ)を複数作成し、それらを切り替えながら比較することができます。一方、ゴールシークは、目標とする結果を得るために必要な単一の入力値を逆算するために使用されます。用途に応じて使い分けることで、より効果的なデータ分析が可能になります。