リンクユニット

Office 2019 Excel 2019

損益分岐点をExcelのゴールシークで簡単に求めてみよう

投稿日:2020年7月22日 更新日:

損益分岐点とは、売上と費用が等しくなるポイントのことです。

Excelには[ゴールシーク]という機能があり、セルをクリックするだけで、解を求めることができます。

赤字にならないためには、何個売れたらいいのか?単価は適切かしら?など悩んだら、Excelのゴールシークを思い出してください。

ゴールシークは、最低販売数を求めたり、販売数が決まっているなら、商品の価格はいくらにすればよいかなどを算出できます。

ゴールシークで損益分岐点を求める

売上と費用と利益を求める表


サンプルとして以下のような表を作成しています。

Excelの売上、費用、利益を求める表

[数式]タブの[ワークシート分析]グループにある[数式の表示]をクリックして、入力されている数式を確認します。

[売上]を求めるセル[D2]には、単価×販売数、[費用]を求めるセル[D5]には、原材料費と家賃と人件費の合計、[利益]を求めるセル[D9]には、売上から費用を引く数式が入力されています。

ゴールシークを使用する場合、数式を入力して設定しておく必要があります。

再度[数式の表示]をクリックして、数式は非表示にします(元に戻します)。

[数式の表示]をクリックして数式を表示した表

ゴールシークの設定と実行

[データ]タブの[データツール]グループにある[What-If 分析]をクリックしてメニューの中から[ゴールシーク]をクリックします。

ゴールシーク

必要な値に対し、適切な入力を探します。

[データ]タブの[What-If 分析]の[ゴールシーク]

[ゴールシーク]ダイアログボックスが表示されます。各テキストボックスには、以下の内容を指定します。

  • 数式入力セル]には、目標値を求める数式が入力されているセルを指定します。
  • 目標値]には、目標値を直接入力します。
  • 変化させるセル]には、値を変化させるセルを指定します。

ここでは、[数式入力セル]には、セル[D9]を指定します。[数式入力セル]のテキストボックス内にカーソルを置いて、セル[D9]をクリックします。

絶対参照の$マークは、自動で表示されます。

[目標値]のテキストボックスには、[0]を直接入力します。

[変化させるセル]には、セル[D4]を指定します。[変化させるセル]のテキストボックスにカーソルを置いて、セル[D4]をクリックします。

[ゴールシーク]ダイアログボックスで設定

[OK]ボタンをクリックします。

結果、以下のようになります。セル[D4]の値が[120]、セル[D9]は[0]と表示されます。

赤字にならないためには、最低120個の販売数が必要という解が求められたわけです。

[ゴールシーク]でもとめられた解答

販売数を固定して単価を変化させる

販売数を固定して、単価を変化させるには、以下のように設定します。

[変化させるセル]にセル[D3]を指定します。

[ゴールシーク]ダイアログボックスで設定

結果、以下のようになります。

単価は、2400円にする必要があるということですね。

ゴールシークで求められた解答

目標額を設定して販売数を求めるには

損益分岐点ではなく、目標額を設定して販売数を求める場合の方法です。

目標額を100,000円にしたいのであれば、[ゴールシーク]ダイアログボックスの[目標値]に[100000]と入力します。

[数式入力セル]はセル[D9]、変化させるセルはセル[D4]を指定します。

[ゴールシーク]ダイアログボックスで設定

以下のように求められます。

100,000円の利益をだすためには、170個の販売数が必要になるということですね。

ゴールシークで求められた解答

反復計算の設定

数式によっては、ゴールシークは解答が見つからずに何度も計算を繰り返すことがあります。

反復計算の設定をしておくと、計算を中断させたりすることができます。

設定するには、[ファイル]タブをクリックして[オプション]をクリックします。

[Excelのオプション]ダイアログボックスの[数式]タブにある[反復計算を行う]から設定します。

[Excelのオプション]ダイアログボックスの[数式]タブにある[反復計算を行う]設定

参考ゴールシークについては、以下の記事でも解説しています。

ゴールシーク(逆算シミュレーション)

ゴールシークを使って目標を達成するための値を求める

Excelのゴールシークという機能を使うと、目標値を設定して特定のセルの値を変化させて、目標達成に必要な値を求めることができます。 ここでは ...

続きを見る

また、[ゴールシーク]はバージョンによってボタンの位置が変わっています。

以下の記事を参考にしてください。

ゴールシークのボタンの位置

[What-If 分析]のメニューにある[シナリオの登録と管理]と[データテーブル]については、以下の記事で解説しています。

シナリオの登録と管理でシミュレーション

Excelには、[What-If 分析]ツールというのがあり、複数の異なる値を入力して結果をシミュレーションすることができます。 [データ] ...

続きを見る

データテーブルを使って複数の計算結果を同時に表示する

Excelの[データ]タブにある[What-If 分析]の中にある[データテーブル]を使うと、複数の計算結果を同時に一覧形式で表示することが ...

続きを見る

Excelには、ソルバーという分析ツールもあります。

ソルバーを使用して最適値を求める

Excelには、ソルバーという機能があります。 ソルバーとは、数式の計算結果を目標値として設定して、その結果を得るために複数の制約条件を指定 ...

続きを見る

Profile

執筆者/はま

もっと理解したいあなたへ

おすすめ記事と広告

-Office 2019 Excel 2019

Copyright © 2001-2020 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2020 AllRights Reserved Powered by AFFINGER4.