【Excel】ゴールシークで目標値から逆算する方法

Excelで特定の目標値を達成するために、どの数値を変更すれば良いか悩んでいませんか?

例えば、売上目標から必要な広告費を逆算したい場合など、複雑な計算を何度も繰り返すのは大変です。

この記事では、Excelの「ゴールシーク」機能を使って、目標値から逆算する方法を解説します。

数式の結果を目標値に合わせるための数値を自動で見つける手順を理解すれば、業務効率が格段に向上します。

ゴールシーク機能の概要と活用シーン

Excelの「ゴールシーク」機能は、数式の結果を特定の目標値にしたい場合に、その数式に使用されている数値を自動的に見つけ出すための便利なツールです。

例えば、商品の売上目標額を達成するために必要な単価を計算したい、あるいはローン返済額を一定に抑えるために借入期間を調整したい、といった状況で役立ちます。

この機能を使うことで、手作業での試行錯誤や複雑な数式を組み立てる手間を省き、効率的に目標達成のための数値を導き出すことができます。

ゴールシークの基本的な使い方:逆算の手順

ゴールシーク機能は、「データ」タブの「What-If分析」の中にあります。基本的な使い方を、具体的な手順に沿って説明します。

1. ゴールシークダイアログボックスを開く
   Excelのメニューから「データ」タブを選択します。次に、「予測」グループにある「What-If分析」をクリックし、表示されるメニューから「ゴールシーク」を選択します。
2. 「ゴールシーク」ダイアログボックスの設定
   表示された「ゴールシーク」ダイアログボックスで、以下の3つの項目を設定します。

1. 「数式入力セル」の設定

このセルには、目標値にしたい結果が表示されているセルを指定します。このセルに含まれる数式が、ゴールシークによって変更されます。

例えば、売上目標額が表示されているセル(例: B5)を指定します。

2. 「目標値」の設定

この項目には、数式セルに表示させたい最終的な数値を直接入力します。例えば、売上目標額が100,000円であれば、「100000」と入力します。

「,」(カンマ)や「円」などの記号は入力せず、数値のみを入力してください。

3. 「変数セル」の設定

このセルには、目標値を達成するために変更したい元の数値を入力します。このセルに含まれる数値が、ゴールシークによって自動的に調整されます。

例えば、単価が表示されているセル(例: B2)を指定し、この単価を変更することで売上目標を達成させたい場合、B2セルを指定します。

1. 「OK」をクリックして実行
   すべての設定が完了したら、「OK」ボタンをクリックします。ゴールシークが計算を実行し、数式セルが目標値に近づくように変数セルを自動的に変更します。
2. 結果の確認
   計算が成功すると、「ゴールシークは解を見つけました」というメッセージが表示されます。変数セルに、目標値を達成するための数値が入力されていることを確認してください。
3. 結果の確定またはキャンセル
   「OK」をクリックすると、変更された値が確定します。「キャンセル」をクリックすると、元の値に戻ります。

具体的な活用例:広告費の逆算

ここでは、商品の売上目標を達成するために必要な広告費を逆算する具体的な例を見ていきましょう。

準備:数式と初期値の設定

まず、Excelシートに以下のような表を作成します。

A1セル:広告費
B1セル:広告費の初期値(例: 50,000)

A2セル:商品単価
B2セル:商品単価(例: 1,000)

A3セル:販売数
B3セル:販売数(例: 100)

A4セル:売上
B4セル:売上(数式: =B2*B3)

A5セル:広告費率
B5セル:広告費率(数式: =B1/B4)

この例では、売上目標を150,000円と設定したいとします。

ゴールシークの実行手順

1. 「数式入力セル」の指定
   B4セル(売上)を選択します。
2. 「目標値」の入力
   「150000」と入力します。
3. 「変数セル」の指定
   B1セル(広告費)を選択します。
4. 「OK」をクリック
   ゴールシークを実行します。

結果として、B1セル(広告費)に、売上目標150,000円を達成するために必要な金額が表示されます。この例では、広告費が75,000円と表示されるはずです。

ゴールシークの制限事項と注意点

ゴールシークは非常に便利な機能ですが、いくつかの制限事項や注意点があります。これらを理解しておくことで、より効果的に活用できます。

1. 1つの変数しか変更できない

ゴールシークは、一度に1つのセルしか変更できません。複数の数値を同時に調整して目標値を達成したい場合は、ソルバー機能(アドイン)を利用する必要があります。

ソルバーは、より複雑な条件設定や複数の変数を扱える高度なツールです。必要に応じて導入を検討してください。

2. 解が見つからない場合がある

ゴールシークは、すべてのケースで解を見つけられるとは限りません。数式が複雑すぎる場合や、目標値が現実的に達成不可能な範囲にある場合、解が見つからないことがあります。

その場合、「ゴールシークは解を見つけられませんでした。」というメッセージが表示されます。その際は、数式や初期値、目標値を見直してみてください。

3. 変動するセルは数式セルである必要がある

「変数セル」に指定できるのは、数式が入力されているセルのみです。直接数値を入力したセルや、数式の結果が表示されているセルは指定できません。

つまり、ゴールシークは「数式の結果を目標値にするために、その数式に使われている数値をどう変えるか」を計算する機能です。

4. 循環参照に注意

ゴールシークを実行する数式に循環参照が含まれていると、正しく計算されない可能性があります。循環参照は、数式が自分自身を参照する状態を指します。

循環参照が発生している場合は、Excelのオプション設定で「反復計算を行う」を有効にするか、数式の見直しが必要です。

5. Excelのバージョンによる違い

ゴールシーク機能自体は、Excelの古いバージョンから搭載されています。そのため、Excel 2019やMicrosoft 365など、ほとんどのバージョンで同様に使用できます。

ただし、アドインであるソルバー機能は、追加のインストールが必要な場合があります。

ゴールシークとソルバーの使い分け

ゴールシークとソルバーは、どちらも数値を逆算する機能ですが、その能力には違いがあります。どちらを使うべきか迷った場合は、以下の比較を参考にしてください。

ゴールシークとソルバーの機能比較

項目	ゴールシーク	ソルバー
目的	数式の結果を特定の目標値に合わせる	制約条件を満たしながら、目的セルの値を最大化・最小化する
変更できるセル数	1つのみ	複数
制約条件	なし	あり(例:セル範囲が〇〇以上、〇〇以下など)
複雑な問題への対応	限定的	可能
必要性	Excel標準機能	アドイン(追加インストールが必要な場合あり)

単純に「この数式の結果を〇〇にしたい」という場合はゴールシークが適しています。

一方、「この条件を満たしながら、この数値を最大化・最小化したい」といったより複雑な最適化問題にはソルバーが適しています。

ソルバーを使用するには、まずExcelの「ファイル」→「オプション」→「アドイン」から「分析ツール」を選択して有効にする必要があります。

まとめ

この記事では、Excelの「ゴールシーク」機能を使って、目標値から逆算する方法を解説しました。

数式の結果を目標値に合わせるための数値を自動で見つける手順を理解すれば、売上目標達成に必要な単価の算出など、様々な業務で活用できます。

より複雑な条件設定が必要な場合は、ソルバー機能の利用も検討してみてください。

ゴールシークとソルバーを使いこなすことで、Excelでのデータ分析や計画立案の効率が大きく向上します。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】