【図解】5分でわかるゴールシークの使い方!関数嫌いでも簡単に計算可能

今回ご紹介するのは「ゴールシーク」。Microsoft Excel(以下、Excel)に搭載されている機能のひとつで、あまり知名度は高くないのですが知っておくと何かと便利な機能です。

【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能

 

「税込価格が500円のときの消費税額を知りたい」
「10万円の利益を出すための売上額を計算したい」
「10年間で300万円貯めるには毎月いくら貯金すればいいの」

などというときに使います。

こちらの記事では、ゴールシークの使い方や活用例を分かりやすく解説しています。計算が苦手という方はぜひご一読ください。

Excelの機能「ゴールシーク」は、算数・関数が苦手でも簡単に計算できる便利な機能

f:id:okazaki0810:20190920085107p:plain

ゴールシークとは、数式計算で得たい結果をあらかじめ指定し、その値から自動で逆算を行う機能です。「goal seeking」が語源で、直訳すると「ゴールから求める」という意味です。

たとえば、売上が100万円、利益率3%のときの利益額を計算するとします。この場合は、「100万円×0.03(3%)」を計算すればいいだけなので、簡単ですよね。答えは「3万円」です。

続いて、「利益率3%、利益額5万円のときの売上は?」と聞かれたらどうでしょうか。一瞬、戸惑いますよね。

この答えは「5万円÷0.03(3%)」で導くことができます。計算すると「166万6667円」の売上が必要ということになります。

ゴールシークでは、後者のような計算をExcelが行ってくれるのです。

 

Excel「ゴールシーク」の使い方!複雑な計算を結果から逆算しよう

では、ゴールシークの具体的な使い方をご紹介します。

ゴールシークを使うためにはまず、もとになる計算式が必要です。先ほど挙げた例の場合では、以下のような表を用意します。

f:id:okazaki0810:20190920085118j:plain

次に「データ」タブ→データツール内の「What-If分析」→「ゴールシーク」を選択します。

ゴールシークのショートカットは、「Altキー+T」→「G」です。このコマンドを入力することでも、ゴールシークのダイアログボックスを表示可能です。

そうするとゴールシークのダイアログボックスが表示されますので、「数式入力セル」「目標値」「変化させるセル」を指定します。

f:id:okazaki0810:20190920085130j:plain

「数式入力セル」:もととなる計算式が入っているセル(例ではB3セル)
「目標値」:求めたい計算結果(例では50,000)
「変化させるセル」:結果を求めるために変化させる値(例ではB1セル)

必要な項目を埋めて「OK」を押せば、自動で逆算を行ってくれます。機能の仕組みさえ分かれば使い方は簡単です。

f:id:okazaki0810:20190920085144j:plain

 

ゴールシークの活用例!実務で使えるシーンを紹介

それではゴールシークが活用できそうな場面をいくつかご紹介します。

 

ゴールシークで目標額から積み立て金額を算出しよう

「毎月○円、○年間積み立てたらいくら貯金できるか」というシミュレーションにもゴールシークは使えます。目標の貯金額を指定して、積立金額もしくは積立年数を逆算します。

f:id:okazaki0810:20190920085155j:plain

毎月1万円を積み立てて120万円貯めるには何年かかるんだろう?という場合、積立金額B1を1万円として、貯金額B3には上記の数式を入れます。

「数式入力セル」: B3
「目標値」: 「1200000」と入力
「変化させるセル」:年数を導きたいのでB2

すると、10年と表示されます。

f:id:okazaki0810:20190920085206j:plain

ゴールシークで損益分岐点を逆算しよう

先ほどの例にも似ていますが、損益分岐点を知りたい場合にもゴールシークは使えます。売上と固定費が決まっていて、「変動費率をどれくらいにしたら利益が出るのか」というときなどに活用できます。

f:id:okazaki0810:20190920085216j:plain

この例では、変動費率40%のときの利益が0円です。では10万円の利益を出したいとき、変動費率は何%にすればよいのか?という場合、B4に上記の数式を入れて

「数式入力セル」: B4
「目標値」:「100000」と入力
「変化させるセル」:変動比率を導きたいのでB3

すると、10万円利益を出したいときの変動費率は「38%」であると計算してくれます。

f:id:okazaki0810:20190920085227j:plain

 

ゴールシークで税込金額から税抜金額を計算しよう

税込の計算でも活躍します。「税込価格で1,000円にしたい」「税込500円の時の消費税額を知りたい」というときなどに利用します。

f:id:okazaki0810:20190920085238j:plain

上記にならって、税込価格を1,000円とするとき、本体価格をゴールシークを使って出してみましょう。ぜひ実際にやってみてください!

下記が正解です。

f:id:okazaki0810:20190920085248j:plain

 

ゴールシークで目標売上に必要な回転数を逆算しよう

今度はもう少し複雑な計算式を紹介します。飲食店において、月間200万円の目標利益に必要な回転数を求めます。消費税や積立の計算よりも少々複雑なので、暗算ではなかなか難しいかもしれません。

この例では、B7セルに利益を求める計算式を入力し、ゴールシークで平均回転数を算出します。なお、利益を求める計算式は以下です。

=(席数×平均回転数×客単価×営業日数)-
(客1人当たり原価×席数×平均回転数×客単価+固定費)

そして、ゴールシークを以下のセルや数値で設定すると、必要な回転数が求められます。

「数式入力セル」: B7
「目標値」:「2000000」と入力
「変化させるセル」:平均回転数を導きたいのでB2

 

ゴールシークの結果、利益200万円のために必要な平均回転数は「5回転」ということが分かりました。例では回転数を求めましたが、変化させるセルを「客単価」や「営業日数」などにすれば別の要素も求められます。

 

ゴールシークの注意点と「ゴールシークが収束しない」ときの対処法

ゴールシークを使うときの注意点として、「目標値」をセルで指定することはできないことと、「変化させるセル」に数式が入っていないことが挙げられます。

なので、目標値は手入力をし、変化させるセルは必ず値(数式ではなく)にしておきましょう。

また、時々ゴールシークの計算が延々と続くことがあります。それは求められる答えが複数あるケース(二次関数の計算とか)でよく起こります。収束しない場合には、「ファイル」タブ→「オプション」→「数式」で設定を変えましょう。

「最大反復回数」を多くして、「変化の最大値」を0.000000001というように小さくします。そうすると解決する場合があるので、ぜひ試してみてください。

f:id:okazaki0810:20190920085301p:plain

 

算数が苦手な人はどんどんゴールシークを活用しよう

ゴールシークは使い方さえ覚えてしまえば、あとは簡単。いろいろなシーンで活用できるので、「算数が苦手」「計算式を考えるのが嫌い」という方は、積極的に使ってみるといいですよ。

※一部、Excelのバージョンによって操作方法が異なる場合があります。

 

【関連記事】
Excel(エクセル)記事まとめ
覚えておけば残業知らず!Excel時短記事まとめ
見づらい資料からサヨナラ!Excel資料作成記事まとめ
覚えて仕事を効率化!Excel関数記事まとめ
PowerPoint(パワーポイント)記事まとめ
Word(ワード)記事まとめ

page top