表計算でゴールシークを使う方法

スプレッドシートのゴールシークとは何ですか？

目標値があり、その目標値に対応する入力値を決定する必要がある場合は、スプレッドシートのゴールシーク機能を使用します。この機能は、指定した目標に合うように変数を変更します。ゴールシーク機能は、財務モデリング、ビジネス分析、その他の複雑な問題の解 決に特に役立ちます。例えば、利益目標を達成するための売上予測、試験の合格点、選挙に必要な得票数などを知ることができます。

スプレッドシートでゴールシークを使うには？

スプレッドシートでゴールシークを使うのは簡単です。以下の3つのパラメータを指定するだけです：

• 操作する数式

• 目標値

• 目標を達成するために変更する入力値

注: ゴール・シークは、一度に 1 つの変数の入力値しか処理できません。

次の例でゴールシークを順を追って説明しよう。

例：ローン返済の計算

1,500ドルを1ヶ月分支払い、完済までに必要なローン期間を月単位で知りたいとします。

1.データを「計算式セル」、「対象セル」、「変更によるセル」で設定します。

この場合、数式セル(B5)ではPMT関数を使って月々の支払額を計算します。

=PMT(B3/12,B2,B1)

2. リボンの「Data」タブで「What-If Analysis」ボタンをクリックし、「Goal Seek」を選択する。

3. ゴールシークダイアログ・ボックスで、セルを選択し、目標値を入力します。

• Formula cell：B5 (月払いの計算式)

• Value to find：-1,500（希望する毎月の支払額）

注：この数値は支払額を表しているのでマイナスです。

• By changing cell：B2（月単位の入力条件）

4. ゴールシークが実行され、答えが見つかる。'By changing cell'の値が新しい値に置き換えられる。新しい値を受け入れるには「OK」をクリックし、元の値に戻すには「Cancel」をクリックする。

この例では、ゴールシークは、希望する毎月の支払額1,500ドルに達するには82ヶ月（次の整数に切り上げ）必要であるという答えを見つけました。

ゴールシークの精度

ゴールシークは、望ましい結果を見つけるための反復近似によって動作します。したがって、計算された解が必ずしも正確ではないことを理解することが不可欠です。スプレッドシートの反復設定を変更することで、より正確な解を求めることができます。

「File」タブで「Option」をクリックし、「Formula」を選択する。「Iteration」反復計算を有効にし、より正確な結果を得るために設定をコントロールする。

• より多くの可能性のある解をテストするには、"Maximum Iterations"を増やす。

• "Maximum Change"を減らして、反復計算の間に許容できる変化量を減らします。例えば、デフォルトの0.01を0.0000001に変更します。

ゴールシークのトラブルシューティング

ゴールシークが正確な結果を出さなかったり、収束しなかったりすることがあります。一般的な原因をいくつか挙げてみましょう：

• 循環参照：ゴールシークは循環依存関係を処理できないため、ワークシートに循環参照がないことを確認してください。

• 複雑な数式：ゴールシークの精度を向上させるには、複雑な数式を単純化するか、小さなステップに分割します。

• データ制約：入力データが現実的な制約の範囲内にあるかどうかをチェックします。非現実的な値や極端な値は、収束の問題につながる可能性があります。