今回は、Excelのゴールシークを使って、目標とする計算結果から必要な入力値を逆算する方法を紹介します。
ゴールシークとは
ゴールシークは、数式の結果を指定した値にするために、参照元となる一つのセルをExcelに探させる機能です。「単価が決まっているとき、目標売上に必要な数量はいくつか」「返済額を一定範囲にするには借入額をいくらにするか」といった逆算に使えます。
通常は入力値から結果を計算しますが、ゴールシークでは結果を先に決めます。目標値と数式の関係を保ったまま入力値を試算できるため、何度も手作業で数値を入れ替える手間を減らせます。
ただし、変更できるセルは一つです。複数の条件を同時に動かしたい場合や、上限・下限などの制約を設定したい場合は、別の分析機能を検討します。
試算表を準備する
ゴールシークを使うには、目標とするセルに数式が入力され、その数式が変更対象セルを参照している必要があります。数値だけが直接入力されたセルでは逆算できません。
たとえば、数量、単価、売上の三項目を用意し、売上セルへ「数量×単価」の数式を設定します。この場合、売上を目標値へ合わせるために数量を変えることができます。
- 目標セルには数式を設定する
- 変更セルは目標セルの計算に関係させる
- 単位をそろえてから試算する
- 入力値と数式セルを見分けられるようにする
- 元の値を控えておく
数式が途中で文字列やエラーを返すと、適切な値を探せないことがあります。まず通常の入力値で数式が計算できることを確認します。
ゴールシークを実行する手順
データタブからWhat-If分析に関するメニューを開き、ゴールシークを選びます。数式入力セル、目標値、変化させるセルを指定して実行します。
- 目標とする数式セルを確認する
- データタブからゴールシークを開く
- 数式入力セルに結果のセルを指定する
- 目標値へ到達させたい数値を入力する
- 変化させるセルに入力値のセルを指定する
- 計算結果を確認して確定または取り消す
実行すると、Excelが変更セルの値を調整し、目標へ近づけます。結果を採用する前に、算出された値が実務上使える内容か確認します。
数値の単位に注意する
割合を指定する場合、セル内部の値と表示形式を区別します。表示が10%でも内部では0.1として扱われるため、目標値の入力を間違えると想定外の結果になります。
金額、個数、時間なども単位を明示します。単価が円で数量が千個単位になっている表では、数式上の換算が必要です。ゴールシークの前に、各セルの単位を見出しへ書いておくと確認しやすくなります。
算出結果をそのまま使わない
ゴールシークは、数式上で目標に近づく値を返します。しかし、数量が小数になる、予算の上限を超える、最低発注数を満たさないなど、業務条件に合わない結果になることがあります。
数量なら整数へ丸めた後に結果を再計算し、目標を満たすか確認します。切り捨てると目標へ届かない場合は、切り上げが必要です。金額では端数処理のルールを数式へ反映し、表示だけを丸めた状態にしないようにします。
計算上の解と実際に採用できる値は分けて判断することが重要です。
結果が見つからないときの確認事項
ゴールシークを実行しても、目標値に到達できないことがあります。数式と変更セルにつながりがない、計算結果が目標へ近づかない構造になっている、途中でエラーが発生するなどが主な原因です。
最初に、変更セルへ別の値を手入力し、目標セルが変化するか確認します。値が変わらなければ、参照関係や数式が誤っている可能性があります。
- 目標セルが数式になっているか
- 数式が変更セルを参照しているか
- シートの計算方法が適切か
- 途中の数式にエラーがないか
- 現実的に到達できる目標値か
数式に上限値を設けるMIN関数や条件分岐があると、変更セルを動かしても一定以上の結果にならないことがあります。目標値が数式の範囲内にあるか確認します。
複数条件がある試算との使い分け
ゴールシークで変更できるセルは一つなので、単価と数量を同時に調整する試算には向きません。また、「数量は整数」「予算は上限以下」といった制約を直接指定する機能でもありません。
一つの入力値を逆算するならゴールシーク、複数の候補を並べて比較するならデータテーブル、複数の変数と制約を扱うならソルバーというように目的で分けます。
ゴールシークを何度も繰り返して複数条件を手作業で合わせると、前提が分かりにくくなります。試算の複雑さが増えたら、分析方法を切り替えます。
試算結果を記録する方法
ゴールシークを確定すると、変更セルには算出された値が入ります。元の値を残したい場合は、実行前にファイルを複製するか、別の試算欄を用意します。
採用した値だけでなく、目標値、固定した条件、実行日、端数処理の考え方も記録します。後から数値を見た人が、どの条件で逆算したのかを理解できる状態にします。
たとえば次の項目を試算表の近くへ残します。
- 設定した目標値
- 変更対象としたセル
- 固定した単価や期間
- 算出値に行った丸め処理
- 最終的に採用した数値
計算結果だけでなく前提条件を残すことで、条件変更時の再計算や確認がしやすくなります。
実務で使いやすい試算表の作り方
入力セルと計算セルの色や書式を分けると、どの値を変更できるか分かりやすくなります。数式セルを誤って上書きしないよう、シート保護を使う方法もあります。
試算専用のシートを用意し、元データや確定値と分離することも有効です。試算結果を本番表へ直接反映するのではなく、確認と承認を経て転記する流れにすると、未確定の値が混ざることを防げます。
目標値の候補が複数ある場合は、結果を別表へ記録します。ゴールシークを実行するたびに前の結果が置き換わるため、比較したい値は手動で値貼り付けして残します。
非線形な数式での注意
変更セルと結果の関係が単純でない数式では、初期値によって見つかる答えが変わる場合があります。同じ目標を満たす値が複数あるケースや、途中で結果の増減方向が変わるケースです。
その場合は、変更セルへ現実的な初期値を入れてから実行し、算出値が想定する範囲にあるか確認します。別の初期値でも試し、結果が安定しているかを見る方法もあります。
ゴールシークが値を返したことだけを根拠にせず、グラフや複数パターンの計算で関係を確認します。
まとめ
Excelのゴールシークは、数式の結果を目標値へ合わせるために、一つの入力セルを逆算する機能です。目標セルに数式を設定し、変更セルとの参照関係と単位を確認してから実行します。
算出値には、端数、上限、整数条件などの業務ルールが自動では反映されない場合があります。結果を再計算し、採用可能か判断することが必要です。目標、前提、算出値、丸め処理を記録することで、説明しやすい試算表として活用できます。