- Excelの機能「ゴールシーク」は、算数・関数が苦手でも簡単に計算できる便利な機能
- Excel「ゴールシーク」の使い方！複雑な計算を結果から逆算しよう
- ゴールシークの活用例！実務で使えるシーンを紹介
- ゴールシークの注意点と「ゴールシークが収束しない」ときの対処法
- 算数が苦手な人はどんどんゴールシークを活用しよう
「税込価格が500円のときの消費税額を知りたい」
「10万円の利益を出すための売上額を計算したい」
「10年間で300万円貯めるには毎月いくら貯金すればいいの」
などというときに使います。
こちらの記事では、ゴールシークの使い方や活用例を分かりやすく解説しています。計算が苦手という方はぜひご一読ください。
Excelの機能「ゴールシーク」は、算数・関数が苦手でも簡単に計算できる便利な機能
ゴールシークとは、数式計算で得たい結果をあらかじめ指定し、その値から自動で逆算を行う機能です。「goal seeking」が語源で、直訳すると「ゴールから求める」という意味です。
たとえば、売上が100万円、利益率3%のときの利益額を計算するとします。この場合は、「100万円×0.03（3%）」を計算すればいいだけなので、簡単ですよね。答えは「3万円」です。
続いて、「利益率3％、利益額5万円のときの売上は？」と聞かれたらどうでしょうか。一瞬、戸惑いますよね。
この答えは「5万円÷0.03（3%）」で導くことができます。計算すると「166万6667円」の売上が必要ということになります。
ゴールシークでは、後者のような計算をExcelが行ってくれるのです。
Excel「ゴールシーク」の使い方！複雑な計算を結果から逆算しよう
では、ゴールシークの具体的な使い方をご紹介します。
ゴールシークを使うためにはまず、もとになる計算式が必要です。先ほど挙げた例の場合では、以下のような表を用意します。
次に「データ」タブ→データツール内の「What-If分析」→「ゴールシーク」を選択します。
ゴールシークのショートカットは、「Altキー＋T」→「G」です。このコマンドを入力することでも、ゴールシークのダイアログボックスを表示可能です。
そうするとゴールシークのダイアログボックスが表示されますので、「数式入力セル」「目標値」「変化させるセル」を指定します。
「数式入力セル」：もととなる計算式が入っているセル（例ではB3セル）
「目標値」：求めたい計算結果（例では50,000）
「変化させるセル」：結果を求めるために変化させる値（例ではB1セル）
必要な項目を埋めて「OK」を押せば、自動で逆算を行ってくれます。機能の仕組みさえ分かれば使い方は簡単です。
ゴールシークの活用例！実務で使えるシーンを紹介
それではゴールシークが活用できそうな場面をいくつかご紹介します。
ゴールシークで目標額から積み立て金額を算出しよう
「毎月○円、○年間積み立てたらいくら貯金できるか」というシミュレーションにもゴールシークは使えます。目標の貯金額を指定して、積立金額もしくは積立年数を逆算します。
毎月1万円を積み立てて120万円貯めるには何年かかるんだろう？という場合、積立金額B1を1万円として、貯金額B3には上記の数式を入れます。
「数式入力セル」： B3
「目標値」： 「1200000」と入力
「変化させるセル」：年数を導きたいのでB2
すると、10年と表示されます。
ゴールシークで損益分岐点を逆算しよう
先ほどの例にも似ていますが、損益分岐点を知りたい場合にもゴールシークは使えます。売上と固定費が決まっていて、「変動費率をどれくらいにしたら利益が出るのか」というときなどに活用できます。
この例では、変動費率40％のときの利益が0円です。では10万円の利益を出したいとき、変動費率は何％にすればよいのか？という場合、B4に上記の数式を入れて
「数式入力セル」： B4
「目標値」：「100000」と入力
「変化させるセル」：変動比率を導きたいのでB3
すると、10万円利益を出したいときの変動費率は「38％」であると計算してくれます。
ゴールシークで税込金額から税抜金額を計算しよう
税込の計算でも活躍します。「税込価格で1,000円にしたい」「税込500円の時の消費税額を知りたい」というときなどに利用します。
上記にならって、税込価格を1,000円とするとき、本体価格をゴールシークを使って出してみましょう。ぜひ実際にやってみてください！
下記が正解です。
ゴールシークで目標売上に必要な回転数を逆算しよう
今度はもう少し複雑な計算式を紹介します。飲食店において、月間200万円の目標利益に必要な回転数を求めます。消費税や積立の計算よりも少々複雑なので、暗算ではなかなか難しいかもしれません。
この例では、B7セルに利益を求める計算式を入力し、ゴールシークで平均回転数を算出します。なお、利益を求める計算式は以下です。
＝（席数×平均回転数×客単価×営業日数）－
（客1人当たり原価×席数×平均回転数×客単価＋固定費）
そして、ゴールシークを以下のセルや数値で設定すると、必要な回転数が求められます。
「数式入力セル」： B7
「目標値」：「2000000」と入力
「変化させるセル」：平均回転数を導きたいのでB2
ゴールシークの結果、利益200万円のために必要な平均回転数は「5回転」ということが分かりました。例では回転数を求めましたが、変化させるセルを「客単価」や「営業日数」などにすれば別の要素も求められます。
ゴールシークの注意点と「ゴールシークが収束しない」ときの対処法
ゴールシークを使うときの注意点として、「目標値」をセルで指定することはできないことと、「変化させるセル」に数式が入っていないことが挙げられます。
なので、目標値は手入力をし、変化させるセルは必ず値（数式ではなく）にしておきましょう。
また、時々ゴールシークの計算が延々と続くことがあります。それは求められる答えが複数あるケース（二次関数の計算とか）でよく起こります。収束しない場合には、「ファイル」タブ→「オプション」→「数式」で設定を変えましょう。
「最大反復回数」を多くして、「変化の最大値」を0.000000001というように小さくします。そうすると解決する場合があるので、ぜひ試してみてください。
算数が苦手な人はどんどんゴールシークを活用しよう
ゴールシークは使い方さえ覚えてしまえば、あとは簡単。いろいろなシーンで活用できるので、「算数が苦手」「計算式を考えるのが嫌い」という方は、積極的に使ってみるといいですよ。
※一部、Excelのバージョンによって操作方法が異なる場合があります。
【関連記事】
Excel（エクセル）記事まとめ
覚えておけば残業知らず！Excel時短記事まとめ
見づらい資料からサヨナラ！Excel資料作成記事まとめ
覚えて仕事を効率化！Excel関数記事まとめ
PowerPoint（パワーポイント）記事まとめ
Word（ワード）記事まとめ
あなたの本当の年収がわかる!?
わずか3分であなたの適正年収を診断します
