Excel でゴール シークを使用する方法 (実際の例を使用)

数式の結果はわかっていても、その結果を生成する入力値がわからない場合は、Microsoft Excel が役に立ちます。Excel でゴール シークを使用する方法を学び、実際のデータを変更せずに数式や大きな数値データのシミュレーションを実行するだけです。

個人の財政、貯蓄への投資、ビジネス プロジェクトのいずれであっても、十分な情報に基づいた意思決定が成功の鍵となります。情報に基づいた意思決定を行うには、数式と目的に基づいてデータベースのシミュレーションからシミュレートされたデータが必要です。Excel ゴール シークは、このようなデータ ベースのシミュレーションのためのツールです。

Excel を初めて使用する場合、または使用経験はあるが専門家ではない場合は、Excel でのゴール シークの基本と、Excel でのゴール シーク機能の使用方法について説明します。

Excel のゴール シークとは

ゴール シークは、Excel に組み込まれている仮説分析ツールです。式の最終値が入力値にどのように影響するかを示します。簡単に言えば、同じ数式から目的の結果を取得するための数式の入力値を示します。

金融投資家、財務管理者、事業主、および管理職は、この Excel What-If 分析ツールを頻繁に使用して、任意の数学的モデルから望ましい結果への入力を予測します。

Excel のゴール シークは、任意のサイズのデータ​​セットにリンクされた数式を分析し、入力値を通知する自動シミュレーション システムと呼ぶこともできます。数学モデルに入力値を入力すると、探している結果が得られます。

このツールはほぼすべてのシナリオで使用できますが、主なシナリオは次のとおりです。

• 割引とエージェント手数料を追加して、特定の収益を達成するために販売しなければならない製品の数
• 特定の月々の分割払いで特定の期間内にローンを完済できるように、どのくらいの金利で借りなければならないか
• 所定の期限内にプロジェクトを完了するために必要な作業時間

Excel のゴール シークはどこにありますか?

Excel ゴール シークは、Microsoft Excel の 3 つの What-If 分析ツールの 1 つです。そのため、このツールは What-If Analysis コマンド メニューにあります。Excel リボン メニューで [データ] タブをクリックし、リボンの右端に移動します。そこに、予測セクションが表示されます。What-If 分析ボタンを選択すると、ゴール シークが ドロップダウン リストの 2 番目になります。

Excel のゴール シークはどこにありますか

ツールの場所は、Mac アプリ用の Excel でも同じです。執筆時点では、この機能は次の Excel エディションで利用できます。

• Excel for Microsoft 365 (Windows および Mac)
• Excel 2021 (Windows および Mac)
• Excel 2010 以降の Windows および Mac でも利用可能

Excel でゴール シークを使用するための要件

Excel ゴール シークは、バックエンドですべての計算を実行します。数学モデルまたはデータ テーブルから次の 3 つのパラメーターを定義するだけです。

Excel ゴール シークのコンポーネントとパラメーター

• [セルの設定]ボックスにセル参照を入力します。この参照セルには数式が含まれている必要があります。Excel ゴール シークは、この式を使用して入力値を導き出します。
• [終了値]ボックスに、達成する必要がある結果または目標を入力する必要があります。
• By changing セルは、シミュレートしている数学的モデルの入力が必要なセル参照です。

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

Excel でゴール シークを使用して、数式から目的の出力の入力を予測できるいくつかのシナリオ例を以下に示します。手順は、Windows 11 デバイスと macOS デバイスの両方で似ています。

1. 試験の点数を予測するためのゴール シーク

たとえば、大学の最終学期に学習計画のペースを上げて、卒業時に平均 85% のスコアを達成する必要があるとします。ゴール シーク式を適用して、他の 7 学期のスコアを取得した場合に、最終学期に必要なスコアをシミュレートする方法は次のとおりです。

• Excel ワークシートに、SemesterおよびScoreという名前の列ヘッダーを作成します。
• Semester列の下にSemester IからSemester VIIIと入力します。
• セメスター VIIIを除くすべての 7 セメスター試験のスコアを入力します。
• 次の式を使用して学期平均スコアを作成します。

=平均(B2:B9)

• 独自の Excel ワークシートに従ってセル参照を調整します。
• ここで、ゴール シーク関数を呼び出します。
• Setセルは、平均を計算した数式セルである必要があります。
• To valueセルは、希望する学期の平均スコアである 85% である必要があります。
• セメスター VIII試験のスコアの空白のセル参照を[セルを変更する]ボックスに入力します。

ゴール シークを使用して試験の点数を予測する方法を学ぶ

• [OK]をクリックして、 85% の平均卒業スコアのために確保する必要があるセメスター VIIIスコアを取得します。

目標の卒業スコアに到達するには、最終学期に 98% のスコアを取得する必要があります。

 Microsoft Excel: シートを簡単に管理する方法

2. ゴール シークを使用して融資額を予測する

あなたの事業のために固定金利 (8%) で 40 年間資本を借りる必要があるとします。これで、ビジネスの収益から月額 1,800 ドルを支払うことができることがわかりました。ただし、上記の期間、上記の利率でいくら借りなければならないかわかりません。次の手順に従って、簡単に借入金額を計算できます。

• 次のコンポーネントの行を作成します: Annual Interest Rate、Years to Payoff、Loan Amount、およびMonthly Installments。
• ゴール シークを使用して計算する必要があるLoan Amountを除いて、上記のコンポーネントのそれぞれの値をそれぞれのセルに入力します。
• ここで、毎月の分割払いの値を、 Excel で次のPMT式に置き換えます。

=PMT(B1/12,B2*12,B3,0)

• [データ] > [What-If 分析] > [ゴール シーク]からゴール シーク関数を呼び出します。
• セット セルは、 PMT 数式セルを参照する必要があります。
• To value は$1,500 である必要があります。
• セルを変更すると、ローン金額の値になります。

ゴールシークを使用してローン金額を予測する方法を学ぶ

• [OK]をクリックして、反復計算によってローン金額をシミュレートします。

ゴール シーク関数によると、年利 8% で 40 年間借り入れ、毎月の分割払い $1,500 を支払う場合、ローン額は $215,731 である必要があります。

3. ゴール シークを使用して販売目標を見つける

あなたが営業およびマーケティングの責任者であるか、営業幹部として働いていて、特定の収益目標を達成するために販売目標を作成する必要がある場合、ゴール シークは理想的なツールです。たとえば、あなたのビジネスは現在 1,000 ユニットの製品を販売しています。

製品の希望小売価格は 50 ドルで、卸売業者に 10% の割引も提供しています。ただし、MSRP と割引率を変更せずに、製品販売による会社の収益目標を $75,000 に増やしたいと考えています。では、いくつの製品を販売する必要がありますか? 以下のゴールシークを使用して調べてみましょう。

• Units Sold、MSRP、Discountなどの既知のコンポーネントの行を作成します。
• 次の式を使用して収益を計算します。

=B2*(B3*(1-B4))

ゴールシークを使用して販売目標を見つけることを学ぶ

• ここで、ゴール シークを開き、次のパラメーターを設定します。
  • セルの設定: $B$5
  • 価値: $75,000
  • セルの変更: $B$2

独自のデータと Excel ワークシートに従ってセル参照を調整してください。OKを押すと、同じメーカー希望小売価格と割引率で 1,667 ユニットの製品の販売目標が得られます。

Excel でのゴール シーク: FAQ

How Do I Use Goal Seek in Excel for Multiple Cells?

Excel のゴール シークを使用すると、数式の入力をシミュレートして、所定の結果を得ることができます。残念ながら、変数や複数のセルを考慮に入れることはサポートされていません。

What Is the Shortcut for Goal Seek?

現在、Excel のゴール シーク機能の専用ショートカットはありません。Excel で Alt キーを使用し、キーボードのいくつかのキーを押して、マウスを使用せずにゴール シーク ツールを呼び出すことができます。手順は次のとおりです。

Press Alt on Excel > without clicking anywhere on the app press A > press W > press G to open Goal Seek

How Do I Make My Goal Seek More Accurate?

場合によっては、ゴール シークが入力値を 10 進数で導出することがあります。自動的に丸められた数値が必要な場合は、その精度を上げることができます。方法は次のとおりです。

• [ファイル]タブをクリックし、サイドバーの[オプション]を選択します。
• [Excel のオプション]ウィンドウ内で、[数式]オプションをクリックします。
• 反復計算を有効にする機能にチェックマークを付けます。

Excel ゴール シークを最適化する方法

• [最大反復回数]フィールドを 150 以上に増やして、 Excel がより実行可能なソリューションをテストできるようにします。
• また、最大変更値をデフォルトの 0.001 から 0.00001 に増やします。

Can I Do Goal Seek on Multiple Cells?

Excel で複数のセルに対してゴール シークを実行することはできません。

What Are the Advantages of Goal Seek?

Excel でのゴール シークの利点は次のとおりです。

• ゴール シークは、Excel の What-If 分析で使用できる最も単純なシミュレーションです。
• Excelデスクトップアプリですぐに使用できます
• ゴール シークはバックエンドで動作し、データベース全体を変更する必要はありません
• 財務モデリングに役立つ

What Are the Limitations of Goal Seek in Excel?

Excel でのゴール シークには、次の欠点があります。

• 循環参照があるとゴール シークが機能しない 
• ゴール シーク シミュレーションの実行時に変数を入力することはできません
• 複数のセルでは機能しません
• Excel ゴール シークは、Excel Web App または Excel for the Web アプリでは使用できません

Excel ゴール シーク: 最後の言葉

上記の Excel ゴール シークに関する記事を読むことで、Excel で What-If 分析を行う方法について、基本レベルから中レベルレベルの理解を深めることができます。Excel のゴール シークは、明確な数式、入力値、数式にリンクされたいくつかの変数、および達成すべき 1 つの目標がある数学タスクで特に役立ちます。

上記の Excel ゴール シークのステップバイステップ ガイドを使用した経験について、以下にコメントを残してください。他の Excel What-If 分析手法を知っている場合は、お知らせください。

次は、  Excel でテキストを比較する方法です。