エクセルのゴールシークは、「利益を 10 万円にするには何個売ればいいか」のように、式の答え（目標）から逆算して、元の数値を求める機能です。「データ」タブの「What-If 分析」→「ゴール シーク」にあります。
この記事では、利益の目標から必要な販売数を求める例と、平均点の目標から次のテストに必要な点数を求める例で、ゴールシークの使い方を実際の Excel の画面で説明します。うまくいかないときのエラーの意味も紹介します。
ゴールシークとは
ふつうの計算は「販売数 → 利益」の順に答えを出しますが、ゴールシークは逆に「利益がこの値になるには、販売数はいくつ？」を、数値を少しずつ変えながら探してくれます。式を自分で変形しなくても、逆算ができます。
| ゴールシークの欄 | 入れるもの | 今回の例 |
|---|---|---|
| 数式入力セル | 目標にしたい式のセル | 利益の B6 |
| 目標値 | その式をいくつにしたいか | 100000 |
| 変化させるセル | 答えを入れたい数値のセル（式ではなく値） | 販売数の B5 |
ゴールシークの使い方（利益の目標から販売数を求める）
販売価格 1,200 円・原価 700 円・固定費 150,000 円の商品で、利益は =(B2-B3)*B5-B4 で計算しています。今は 300 個で利益 0 円（ちょうど損益分岐点）です。利益を 100,000 円にするには何個売ればいいかを求めます。
- 利益のセル（B6）を選びます。
- 「データ」タブの「What-If 分析」をクリックし、「ゴール シーク」を選びます。
- 「数式入力セル」に B6 が入っていることを確かめます（選んでいたセルが自動で入ります）。
- 「目標値」に 100000 と入力します。
- 「変化させるセル」に B5 と入力するか、B5 をクリックします。
- OK を押します。
販売数が 500 個に変わり、利益がちょうど 100,000 円になりました（①）。「解答が見つかりました」と出たら OK を押すと、この値がセルに残ります。
平均点の目標から、次のテストの点数を求める
1〜3 回目のテストが 72・85・78 点のとき、4 回目で何点取れば平均 80 点になるかも、同じ手順で求められます。平均のセル（B6、=AVERAGE(B2:B5)）を数式入力セル、目標値を 80、4 回目のセル（B5）を変化させるセルにします。
4 回目に 85 点取れば、平均がちょうど 80 点になりました（①②）。
ゴールシークがうまくいかないとき
「変化させるセル」に数式のセル（ここでは利益の B6）を指定すると、「数値が入力されているセルを指定してください。」と出ました（①）。変化させるセルには、式ではなく数値を直接入力したセルを指定します。
| 症状 | 考えられる原因 | 対処 |
|---|---|---|
| 「数値が入力されているセルを指定してください」 | 変化させるセルが数式のセル | 数値を入力したセル（販売数など）を指定する。 |
| ゴールシークがどこにあるかわからない | 「データ」タブの中の「What-If 分析」にまとめられている | 「データ」→「What-If 分析」→「ゴール シーク」。 |
| 変える数が2つ以上ある | ゴールシークは変化させるセルが1つだけ | 複数のセルを変えたり条件を付けたりするならソルバーを使う。 |
よくある質問
ゴールシークとソルバーの違いは？
ゴールシークは「1つのセルを変えて、1つの式を目標値にする」機能です。ソルバーは、複数のセルを変えたり、「0 以上」「100 個まで」などの条件を付けたりして、最大・最小になる組み合わせを探せます。
損益分岐点（利益が 0 になる販売数）を求めるには？
目標値を 0 にしてゴールシークを使います。今回の例では、利益が 0 になる販売数は 300 個です。式で求めるなら、固定費 ÷（販売価格 − 原価）で 150,000 ÷ 500 ＝ 300 個です。
平均や合計を求める関数は？
AVERAGE関数や SUM 関数を使います。ゴールシークは、それらの式の答えから逆算するときに使います。
あわせて読みたい：計算の関連記事
条件を守りながら最大・最小の組み合わせを探すときは、エクセルのソルバーの使い方もあわせてご覧ください。
コメント