Excelでゴールシークを使用する方法

ゴールシークは、ExcelのWhat-if Analysisツールの1つであり、数式の正しい入力値を見つけて目的の出力を取得するのに役立ちます。数式の1つの値が別の値にどのように影響するかを示します。つまり、達成したい目標値がある場合は、ゴールシークを使用して、それを取得するための適切な入力値を見つけることができます。

たとえば、ある科目で合計75点を獲得し、その科目でSグレードを取得するには少なくとも90点が必要だとします。ありがたいことに、平均スコアを上げるのに役立つ可能性のある最後のテストが1つあります。ゴールシークを使用して、Sグレードを取得するためにその最終テストで必要なスコアを把握できます。

ゴールシークは試行錯誤の方法を使用して、正しい結果が得られるまで推測を入力することにより、問題をさかのぼります。ゴールシークは、手動で計算するのに長い時間がかかったであろう数秒で、数式の最適な入力値を見つけることができます。この記事では、Excelでゴールシークツールを使用する方法をいくつかの例とともに示します。

ゴールシーク機能のコンポーネント

ゴールシーク関数は、次の3つのパラメーターで構成されます。

セルを設定 –これは数式が入力されているセルです。目的の出力が必要なセルを指定します。
貴ぶ –これは、ゴールシーク操作の結果として必要な目標/望ましい値です。
セルを変更する –これは、目的の出力を得るために値を調整する必要があるセルを指定します。

Excelでゴールシークを使用する方法：例1

簡単な例でそれがどのように機能するかを示すために、果物屋を経営していると想像してください。下のスクリーンショットでは、セルB11（下）に屋台用の果物を購入するのにかかる費用が表示され、セルB12にそれらの果物の販売による総収益が表示されます。そしてセルB13はあなたの利益のパーセンテージ（20％）を示しています。

利益を「30％」に増やしたいが、投資を増やすことができない場合は、利益を得るには収益を増やす必要があります。しかし、いくらですか？ゴールシークはあなたがそれを見つけるのを助けます。

セルB13で次の数式を使用して、利益率を計算します。

=（B12-B11）/ B12

ここで、利益率が30％になるように利益率を計算します。これは、Excelのゴールシークで行うことができます。

最初に行うことは、値を調整するセルを選択することです。ゴールシークを使用するたびに、数式または関数を含むセルを選択する必要があります。この例では、パーセンテージを計算する数式が含まれているセルB13を選択します。

次に、[データ]タブに移動し、[予測]グループの[What if Analysis]ボタンをクリックして、[ゴールシーク]を選択します。

[ゴールシーク]ダイアログボックスが表示され、次の3つのフィールドが表示されます。

セルを設定 –数式（B13）を含むセル参照を入力します。これは、目的の出力を持つセルです。

貴ぶ –達成しようとしている目的の結果（30％）を入力します。

セルを変更する –目的の結果を得るために、変更する入力値（B12）のセル参照を挿入します。セルをクリックするか、セル参照を手動で入力するだけです。セルを選択すると、Excelは列の文字と行番号の前に「$」記号を追加して、絶対セルにします。

終了したら、[OK]をクリックしてテストします。

次に、[ゴールシークステータス]ダイアログボックスがポップアップ表示され、以下に示すような解決策が見つかったかどうかが通知されます。解決策が見つかった場合、「セルの変更（B12）」の入力値は新しい値に調整されます。これが私たちが望んでいることです。したがって、この例では、分析により、30％の利益目標を達成するには、1635.5ドル（B12）の収益を達成する必要があると判断されました。

[OK]をクリックすると、Excelがセルの値を変更するか、[キャンセル]をクリックしてソリューションを破棄し、元の値を復元します。

セルB12の入力値（1635.5）は、目標（30％）を達成するためにゴールシークを使用して見つけたものです。

ゴールシークを使用するときに覚えておくべきこと

セルの設定には、「セルの変更による」セルに依存する数式が常に含まれている必要があります。
ゴールシークは、一度に1つのセル入力値に対してのみ使用できます。
「セルを変更する」には、数式ではなく値が含まれている必要があります。
ゴールシークが正しい解決策を見つけられない場合、それはそれが生み出すことができる最も近い値を示し、「ゴールシークは解決策を見つけられなかったかもしれない」というメッセージをあなたに伝えます。

Excelのゴールシークが機能しない場合の対処方法

ただし、解決策があると確信している場合は、この問題を解決するために試すことができることがいくつかあります。

ゴールシークのパラメータと値を確認する

確認する必要があるのは2つあります。まず、「セルの設定」パラメータが数式セルを参照しているかどうかを確認します。次に、数式セル（セルの設定）が、直接または間接的に、変化するセルに依存していることを確認します。

反復設定の調整

Excelの設定では、Excelが適切なソリューションを見つけるために実行できる試行回数と、その精度を変更できます。

反復計算の設定を変更するには、タブリストから[ファイル]をクリックします。次に、下部にある[オプション]をクリックします。

[Excelのオプション]ウィンドウで、左側のペインの[数式]をクリックします。

[計算オプション]セクションで、次の設定を変更します。

最大反復回数–Excelが計算する可能性のあるソリューションの数を示します。数値が大きいほど、実行できる反復回数が多くなります。たとえば、「最大反復回数」を150に設定すると、Excelは150の可能なソリューションをテストします。
最大変化–結果の精度を示します。数値が小さいほど精度が高くなります。たとえば、入力セルの値が「0」に等しいが、ゴールシークが「0.001」で計算を停止した場合、これを「0.0001」に変更すると問題が解決するはずです。

Excelでより多くの可能なソリューションをテストする場合は「最大反復」値を増やし、より正確な結果が必要な場合は「最大変更」値を減らします。

以下のスクリーンショットは、デフォルト値を示しています。

循環参照なし

数式がそれ自体のセルを参照する場合、それは循環参照と呼ばれます。 Excelゴールシークを正しく機能させる場合は、数式で循環参照を使用しないでください。

Excelのゴールシークの例2

誰かから「25000ドル」のお金を借りているとしましょう。彼らはあなたに20ヶ月間月額7％の利率でお金を貸し出します。これは月額「$ 1327」の返済になります。しかし、7％の利子で20か月間、月額「$ 1,000」を支払うだけの余裕があります。ゴールシークは、「$ 1000」の月々の支払い（EMI）を生み出すローン金額を見つけるのに役立ちます。

PMT計算を計算するために必要な3つの入力変数を入力します。つまり、利率7％、期間20か月、元本$ 25,000です。