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

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

スタディ スキルアップ
【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
  • 【Excel】ゴールシークの使い方|利益率3%で5万円利益を出すための売上は? 答えから逆算できる便利な機能
今回ご紹介するのは「ゴールシーク」。Microsoft Excel(以下、Excel)に搭載されている機能のひとつで、あまり知名度は高くないのですが知っておくと何かと便利な機能です。

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

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

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

ゴールシークとは?





ゴールシークとは、数式計算で得たい結果をあらかじめ指定し、その値から自動で逆算を行う機能です。

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

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

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

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

ゴールシークの使い方|活用方法をご紹介



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

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



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

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



「数式入力セル」:もととなる計算式が入っているセル(例では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円とするとき、本体価格をゴールシークを使って出してみましょう。ぜひ実際にやってみてください!

下記が正解です。



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


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

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

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

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



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


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

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

Excel(エクセル)記事まとめ

覚えておけば残業知らず!Excel時短記事まとめ

見づらい資料からサヨナラ!Excel資料作成記事まとめ

覚えて仕事を効率化!Excel関数記事まとめ

PowerPoint(パワーポイント)記事まとめ

Word(ワード)記事まとめ
《編集部》

関連記事

    page top