今回は、Excelのゴールシークを使って、目標値から必要な入力値を逆算する方法を紹介します。
ゴールシークとは
Excelでは、数式を使って結果を計算することが多くあります。通常は、入力値を変えると結果が変わります。一方で、仕事では「結果をこの値にしたい。そのために入力値はいくつにすればよいか」と考える場面があります。ゴールシークは、このような逆算に使える機能です。
たとえば、売上目標に合わせて単価を考える、予算内に収まる数量を探す、目標利益に近づく費用を調整する、といった作業で役立ちます。複雑な分析機能を使う前に、1つの入力値を動かして1つの結果を合わせるならゴールシークが扱いやすいです。
使う前に表を整える
ゴールシークを使うには、結果セルが数式で計算されている必要があります。手入力の数値だけでは逆算できません。入力値、計算式、目標にしたい結果を分けておくと操作しやすくなります。
- 変更したい入力値のセルを用意する
- 結果を表示する数式セルを用意する
- 目標値を決めておく
- 途中計算がある場合は式のつながりを確認する
表を作るときは、どのセルが入力で、どのセルが結果なのかを見出しで示します。入力セルに色を付けるなどの工夫をすると、後から見直すときに迷いにくくなります。
ゴールシークの基本手順
ゴールシークは「データ」タブから開きます。操作では、数式が入っている結果セル、目標値、変更する入力セルを指定します。
- 「データ」タブを開く
- 「What-If分析」から「ゴールシーク」を選ぶ
- 数式入力セルに結果セルを指定する
- 目標値に達成したい値を入力する
- 変化させるセルに入力値のセルを指定する
- 結果を確認して確定する
ここで大切なのは、結果セルと変更セルを取り違えないことです。結果セルには数式が入っている必要があり、変更セルには直接値を入力できる状態が必要です。変更セルに数式が入っていると、意図した調整ができない場合があります。
向いているケース
ゴールシークは、1つの変数を変えると結果が決まる表に向いています。たとえば、単価を変えて合計金額を合わせる、数量を変えて予算に近づける、割引率を変えて請求額を調整する、といったケースです。
反対に、複数の入力値を同時に動かす必要がある場合は、ゴールシークだけでは足りません。単価と数量を同時に調整したい、費用項目を複数変えたい、といった場合は、条件を分けて試すか、別の分析機能を検討します。
結果をそのまま使う前の確認
ゴールシークで得られた値は、計算上の値です。実務では、端数処理、最小単位、入力できる範囲などを確認する必要があります。たとえば、数量は整数でなければならない、単価は決められた刻みでしか設定できない、割引率には上限がある、といった条件があります。
ゴールシーク後は、計算結果が業務ルールに合っているかを確認します。必要に応じてROUND関数で丸めた場合の結果も見ます。丸めると目標値から少しずれることがあるため、実際に使う値で再計算して確認します。
試算表として残す工夫
ゴールシークは変更セルの値を書き換えます。そのため、試算前の値を残したい場合は、表をコピーしてから実行するか、変更前の値を別セルに記録します。比較したい案が複数ある場合は、案ごとに行を分けて残すと便利です。
たとえば、単価案、数量案、割引率案をそれぞれ行として並べ、結果セルを横に置きます。ゴールシークで求めた値を記録しておけば、あとから条件を見比べられます。試算結果だけでなく、前提条件も一緒に残すと、説明資料に使いやすくなります。
エラーが出たときに見る場所
ゴールシークで解が見つからない場合、いくつかの原因が考えられます。数式の参照が間違っている、変更セルが結果に影響していない、目標値が現実的でない、入力値の範囲が合っていない、といった点です。
- 変更セルを手で変えたときに結果が動くか確認する
- 結果セルが手入力ではなく数式か確認する
- 目標値の単位が表と合っているか確認する
- 途中計算にエラー値がないか確認する
まずは変更セルにいくつかの値を手入力し、結果がどの方向に変わるかを見ます。動きがわかると、目標値が届く範囲にあるか判断しやすくなります。
使いすぎを避ける
ゴールシークは便利ですが、表の前提が曖昧なまま使うと、計算値だけが独り歩きします。目標値に合わせることだけを目的にすると、入力値が現実的でない形になる場合があります。ゴールシークは答えを決める機能ではなく、判断材料を作る機能として使います。
試算結果を共有するときは、どのセルを変更して、どの結果を目標にしたのかを説明できるようにします。セルにコメントを付けたり、表の近くに前提を書いたりすると、読み手が確認しやすくなります。
説明用のメモを残す
ゴールシークで求めた値は、後から見ると「なぜその数値になったのか」がわかりにくいことがあります。試算表には、目標にしたセル、変更したセル、目標値、実行日や前提を短く残しておくと説明しやすくなります。
- 目標にした結果セルを記録する
- 変化させた入力セルを記録する
- 端数処理のルールを書く
- 採用した値と不採用にした値を分ける
会議資料に使う場合は、ゴールシークの結果だけでなく、入力値を少し変えた場合の結果も並べると判断しやすくなります。計算の根拠を残すことで、後日の見直しにも対応しやすくなります。
複数案を比べるときの工夫
ゴールシークで求めた値が1つだけだと、判断の幅が見えにくいことがあります。目標値を少し変えた案、端数処理を変えた案、上限や下限を考慮した案を別行に残すと比較しやすくなります。案ごとに前提を書き、採用候補には印を付けます。計算値が似ていても、実行しやすさや説明しやすさが違う場合があります。数式の結果だけで決めず、実際の入力単位や社内ルールに合うかを並べて確認します。
まとめ
Excelのゴールシークは、目標値から入力値を逆算したいときに役立つ機能です。結果セルには数式を入れ、変更する入力セルを明確にしておくことが基本です。得られた値は計算上の候補として扱い、端数処理や業務ルールに合うか確認します。試算前の値や前提条件を残しておくと、後から比較しやすくなります。1つの入力値を調整する場面では、ゴールシークを使うことで、手作業の試行を整理できます。