Excel(エクセル)には、結果から求めたい数値を逆算できる「ゴールシーク」という機能が存在します。
「数式の計算結果を〇〇にするためには、このセルはいくらにすれば良いのか」、といった計算を自動で行ってくれます。
この記事では、「ゴールシークの使い方と実例」「ゴールシークが上手くいかない場合」「ゴールシークのショートカットキー」を紹介します。
「ゴールシーク」とは？仕組みを紹介
「ゴールシーク」とは、どんな機能なのかを紹介します。
まずは、下の表を見て下さい。
E3セルの販売高「4,500」を「8,000」にするためには、単価「150」をいくつに変更すれば良いか、と問われた場合、どのように計算しますか？
多くの人は、電卓や他の空いたセルを使って、売上高「8,000」を販売数量「30」で割って、答えは単価「266.667」と計算するでしょう。
このように、頭の中で逆算用の数式を頭の中で組み立てるのって面倒くさいですよね。
こんな時に便利な機能が、「ゴールシーク」です。
英語で書くと「goal seek」なので、「ゴールシーク」の意味は、「目標値を探す」です。
「ゴールシーク」機能では、「変化させるセル」と「数式入力セル」と「目標値」を設定すると、下の図のように、自動で逆算してくれる機能です。
詳しい使い方は次の見出しで紹介するよ。
「ゴールシーク」で結果から逆算する方法
それでは実際に「ゴールシーク」を使って、結果から求めたい数値を逆算する方法を紹介します。
下の表は、「売上高」「利益」「利益率」をまとめた表です。
この「利益率」のC5セルには、「＝C4/C3*100」が挿入されています。つまり、「利益÷売上高×100」の数式です。
売上高は変えずに、利益率を「9.0％」にするためには、利益をいくらにすればよいか「ゴールシーク」で算出します。
手順１．「ゴールシーク」をクリック
まず、「データ」タブの「What-IF分析」の「ゴールシーク」をクリックします。
手順２．「数式入力セル」の「↑」をクリック
「ゴールシーク」ダイアログボックスが表示されます。
まず一番上の項目「数式入力セル」(※１)を選択するために、「↑」マークをクリックします。
数式入力セルとは
計算結果が表示されている、数式が入力されているセルのことを指します。上の表の例では、利益率が算出されているC5セルです。
手順３．「数式入力セル」を指定
結果(利益率）が表示されたC5セルをクリックします。選択し終えたら、「ゴールシーク」ダイアログボックスの「↓」マークをクリックします。
手順４．「目標値」を指定
続いて、「ゴールシーク」ダイアログボックスの「目標値」を入力します。「目標値」は、手順３で指定した「数式入力セル」をいくつに変えたいかを値で入力します。
利益率を「9.0%」にしたいので、「9.0」と入力します。
手順５．「変化させるセル」の「↑」をクリック
「ゴールシーク」ダイアログボックスの一番下の項目「変化させるセル」(※２)を選択するために、「↑」マークをクリックします。
変化させるセルとは
逆算して数値を変化させたいセルのことを指します。値が入力されているセルを指定する必要があります。数式セルは指定できません。上の表の例では、利益が入力されているC4セルです。
手順６．「変化させるセル」を指定
利益を変化させたいので、C4セルをクリックします。選択し終えたら、「ゴールシーク」ダイアログボックスの「↓」マークをクリックします。
手順７．「ゴールシーク」を実行
「ゴールシーク」の３つの項目を設定し終えたら、「OK」を押して「ゴールシーク」を実行します。
手順８．「利益」が逆算される（完了）
「ゴールシーク」ダイアログボックスに「セルC5の収束値を探索しています。解答が見つかりました。」と表示されれば逆算は成功です。
C4セルの利益は「110」⇒「135」と変化し、利益率は「9.0」%となりました。
このように「ゴールシーク」を使うと、求めたい結果から値を逆算することが出来ます。
「ゴールシーク」を使った逆算の実例を紹介
「ゴールシーク」に慣れていない人にとっては、どんなケースで利用できるかイメージが湧かない人も多いはず。
この見出しでは、ゴールシークを使った実例を３つ紹介します。
税抜金額を求める
消費税率は、軽減税率の8.0％や10％が存在します。さらに、23年10月からのインボイス制度開始に伴いさらに複雑な税率計算となります。
「ゴールシーク」は、消費税込の金額から本体価格（税抜価格）を逆算したい時に役立ちます。
税込金額は「税抜価格×（1＋税率）」で算出できるので、税込金額のC5セルには「=C3*(1+C4)」が入力されています。
今回は、「税込金額」を「500」にしたい場合、本体価格をいくらにすればよいか、「ゴールシーク」で求めます。
「データ」タブの「What-IF分析」の「ゴールシーク」をクリックし、以下のように設定します。
ゴールシークの設定
「数式入力セル」：C5セル(税込金額)
「目標値」：500
「変化させるセル」：C3セル(税抜金額)
「ゴールシーク」を実行します。
解答が見つかり、「本体価格」が「454.54545・・」と小数が無限に続く数値が解答として見つかりました。
「454.54545・・」「0.33333・・」の数値ように、小数点が無限に続く数値のことを「無限小数」と言います。
このように「ゴールシーク」の解答の結果が「無限小数」とならなることは良く発生するケースです。
損益分岐点売上高を求める
『損益分岐点売上高』とは、損益が赤字から黒字になる分岐点となる売上高のことを言います。
会社を経営する上で、赤字にならない最低の売上高を把握することが出来るので、経営指標として良く利用される数値です。
「損益分岐点売上高」をこのくらい下げるためには、「固定費」はいくらにすべきか、といったシミュレーションに「ゴールシーク」は役立ちます。
損益分岐点売上高の計算式は、以下の通りです。
１．損益分岐点売上高 ＝ 固定費 ÷｛（売上高 － 変動費）÷ 売上高｝
「売上高」「変動費」「固定費」「損益」「損益分岐点売上高」をエクセルにまとめると、以下の表となります。
「変動費」は売上高によって変動する費用、「固定費」は売上高が変動しても変動しない費用です。
上の表のケースでは、損益分岐点売上高が「1,000」です。つまり、この売上高を下回ると赤字になってしまうということです。
今後、無駄な固定費を削減し、損益分岐点売上高を「900」に下げたい、としましょう。
そんな時は、「ゴールシーク」を使って、以下の様に設定します。
ゴールシークの設定
「数式入力セル」：C9セル(損益分岐点売上高)
「目標値」：900
「変化させるセル」：C7セル(固定費)
「ゴールシーク」を実行します。
解答が見つかり、「固定費」が「180」にすると、損益分岐点売上高が「900」になることが分かりました。
損益分岐点について詳しく知りたい方は、以下の記事と合わせて参考にしてください。
ローンの借入可能額を求める
エクセルでPMT関数(ペイメント関数）という関数を使うと、ローンの毎月の返済額を計算することが出来ます。
「毎月の返済額」をこのくらいに抑えるためには、「ローン借入額」はいくらにすべきか、といったシミュレーションに「ゴールシーク」は役立ちます。
PMT関数は、「利率」「返済期間」「借入額」の３つの条件を指定することで、毎月の返済額が計算できる関数です。
＝PMT(利率, 期間, 現在価値, [将来価値], [支払期日])
上の表の場合、「利率：1.0％」「返済期間：30年」「借入額：20,000,000円」の借入条件の場合、「毎月の返済額」は「64,328円」であることがPMT関数の結果から分かります。
利率と返済期間は変えずに、「毎月の返済額」を「58,000円」を変更した場合、いくら借入出来るのか、「ゴールシーク」を使って逆算します。
ゴールシークの設定
「数式入力セル」：C6セル(毎月の返済額)
「目標値」：58000
「変化させるセル」：C3セル(借入額)
「ゴールシーク」を実行します。
解答が見つかり、「借入額」を約「18千万円」にすると、「毎月の返済額」を「58,000円」に抑えることが出来ると分かりました。
PMT関数の詳しい使い方は、以下の記事で詳しく紹介しています。興味のある方は参考にしてみてください。
「ゴールシーク」が上手くいかない場合
「ゴールシーク」が上手くいかないケースを紹介します。
それは、セルの指定などに関する「エラー」が表示される場合です。
エラーが表示される場合
「ゴールシーク」ダイアログボックスで「OK」を押して実行しようとした際、下のようなエラー画面が表示されることがあります。
「ゴールシーク」を使うためには、「数式入力セル」と「目標値」と「変化させるセル」の３つの項目を設定する必要があります。
これらの項目には、入力の制約が存在します。
入力の制約条件
「数式入力セル」は数式セルを指定する必要があります。値が入力されたセルは指定出来ません。
「目標値」は値を直接入力します。セルを指定することは出来ません。
「変化させるセル」は、値が入力されたセルを指定する必要があります。数式セルは指定出来ません。
「ゴールシーク」のショートカットキー
「ゴールシーク」を「データ」タブの「What-IF分析」から起動させなくても、「ショートカットキー」を使えば簡単に利用出来ます。
「ショートカットキー」は、キーボードにある複数のキーを押すことで、マウス操作なしでを操作できる機能です。
エクセル画面上で、以下の３つのキーを順番に押してください。
Alt⇒T⇒G
