【経理会計Excel】ゴールシーク機能で目標値を逆算する方法

営業目標を作成する際、粗利益から販売数を逆算したい場合には通常自分で数式を入力し導き出しますが、Excel標準機能のゴールシークを使用すると面倒な計算をExcelが代行してくれます。

完成図

粗利益¥300,000を出すには何個販売すれば良いかExcelが計算してくれる。

ゴールシーク機能を使って粗利から販売数を算出

作成工程

①はじめにセルB3に粗利の計算式「=(B5-B6)*B7」を入力します。

数式の詳細:粗利益(B3)=(単価(B5)-原価(B6))×販売数(B7)

②続いてセルB4に「=B5*B7」を入力します。

ゴールシークで販売数が自動入力されるので、あらかじめ売上数式を入れておくと後に便利です。

ゴールシーク使い方1

③セルB3を選択し、リボンのデータタブの「What-If分析」をクリックし→「ゴールシーク」を選択します。

ゴールシーク使い方2

④ゴールシーク入力ボックスが表示されるので、「数式入力セル」に「B3」を入力します。

※画像では元々セルB3を選択していたので自動で入力されます。

ゴールシーク使い方3

⑤粗利の目標値、ここでは「300000」と入力。通貨単位の「,」は不要です。

※目標とする粗利の数値はお好きな数値を入力してください。

ゴールシーク使い方4

⑥変化させるセルに「$B$7」と入力し、OKを押下します。

※セルB7をクリックすると自動入力され手間が省けます。$マークが付きますがそのままでOK。

ゴールシーク使い方5

⑦販売数79個の解答(目標販売数)がセルB7に表示されました。

同時に売上セルB3にあらかじめ数式を入力していたので売上金額が計算されます。
OKを押下するとセルB7に「79」が確定入力されます。

ゴールシーク使い方6
まとめ
今回紹介したゴールシーク機能は面倒な逆算をExcelが総当たり計算を自動で行ってくれます。
目標設定をする際、簡単操作かつ正確なゴールシーク機能を是非活用しましょう。
もっと複雑な計算、例えば住宅ローンの返済額を逆算する場合などで真価を発揮します。
また、逆算したい項目が複数ある場合はアドイン機能のソルバーを追加すると多項目を計算できます。

Excelに関する最新ニュース・情報20件

2019年12月13日