開閉ボタン
ユーザーメニュー
ユーザーメニューコンテンツ
ログイン

  • 会員限定
  • 2018/09/13

Excel ゴールシークの使い方を基礎から図解、「結果から逆算」で何がわかるのか

連載:今日から使えるExcel最強活用術

Excelのゴールシークとは、希望の計算結果を先に決めてから、その結果を得るために必要な値を逆算する機能です。通常、逆算値を出すには、逆算するための数式を作る必要がありますが、ゴールシークを使えば不要になります。ただ、この説明だけではなかなかイメージが付かないと思いますので、ゴールシークの具体的な利用シーンと利用画面をもとに丁寧に解説していきます。

藤井 直弥、大山 啓介

藤井 直弥、大山 啓介

藤井 直弥(ふじい・なおや)
Excel研修講師。業務改善コンサルタント。研修やセミナーの受講生は延べ一万人を超える。研修の主たるテーマは「多くの人が日々の直面している日常の業務を、いかに効率よく、そしてミスなく遂行するか」といった業務改善策の提案。そのための実践ノウハウや、Excelを用いたビジネス分析の手法などを一人でも多くの人に伝えることを使命として日々奮闘している。Excelの業務効率化を極めることが人生の目標の1つ。

大山 啓介(おおやま・けいすけ)
編集者・著述家。Excelの解説書籍を中心に、WordやPowerPointなどのOffice製品の解説書籍の執筆・編集などを行う。Excelとは20年以上の付き合いで、あらゆる機能の使い方を熟知している。日夜、Excelに苦手意識のある人に大して「どのように解説したら、よりわかりやすいか」を探求しており、多くの経験と実績を積み重ねている。自称Excel博士。趣味は京都探訪とロードバイク。


結果から逆算する「ゴールシーク」とは?

photo
『Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術』
画像をクリックするとアマゾンに移動します
 ゴールシークは、希望の計算結果を先に決めてから、その結果を得るために必要な値を逆算する機能です。

 少し分かりにくいので、飲食店の売上を例に考えてみます。平均客単価が500円で、客数が200人のとき、1日の売上は10万円(=500円×200人)になります。

 では、平均客単価を500円に設定した場合に、売上目標12万円を達成するために必要な客数は何人になるでしょうか。この数を求める際に便利なのがゴールシークなのです。


●通常のExcelの計算
平均客単価[500円]×客数[200人]=売上[???円]
※通常の場合、平均客単価、客数といった数式の要素はあらかじめ決まっている。

●ゴールシークを用いたシミュレーション
平均客単価[500円]×客数[???人]=売上[12万円]
※数式の結果だけ先に決まっていて、数式の要素を逆算するにはゴールシークが便利。

 ゴールシークは応用範囲の広い機能です。みなさんのアイデア次第でいろいろな計算に利用できます。たとえば、上記の他にも「目標利益から必要な回転率を割り出す」、「総予算(利益)から調達できる人員を割り出す」などでも利用できます。ポイントは上記にある通り「希望の計算結果を得るために必要な値を逆算する」です。

 このことを念頭に、どのようなケースで利用できるか検討してみてください。また、既存の業務の中でこの機能を活用できるものがないか確認してみてください。

ゴールシーク用の表を作成する

 ゴールシークを利用するには、最初に「逆算用の数式を入力した表」を作成します。今回の場合は「平均客単価」と「客数」、そして2つを掛け合わせた「売上」の3つが記載されているシンプルな表でかまいません。客数には、仮の数字として「500」と入力しておきます。

画像
ゴールシーク用の表
「=E5*E6」の数式が入力されている
売上12万円に必要な「客数」を逆算したい

 ゴールシーク用の表を準備したら、[データ]タブの[What-If分析][ゴール シーク]をクリックして[ゴールシーク]ダイアログを表示します。このダイアログには3つの入力欄があります。各欄に何の値を入力するのか、ここでしっかりと把握しておいてください。

画像
[ゴールシーク]ダイアログの設定項目
逆算の基準となる数式が入力されているセルを指定する。今回の場合は「売上」の数式が入力されているセル
「数式入力セル」の計算結果がいくつになればよいかを指定する
どのセルの数値を変化させるのかを指定する。ここには逆算したい数値が入力されているセル番地を入力する。今回の場合は「客数」が入力されているセル

●[目標値]項目にセル参照は指定できるのか
[ゴールシーク]ダイアログの2つめの入力項目である[目標値]にはセル参照は指定できません。一見入力できそうですが、ベタ打ちの数値しか指定できないので注意してください。

画像
①[データ]タブの[What-If分析]→[ゴールシーク]をクリックする。

画像
②[ゴールシーク]ダイアログが表示される。3つの入力欄にセル参照と数値を入力し、[OK]ボタンをクリックする。

画像
③適切な値が見つかると、ダイアログに「解答が見つかりました」と表示される。[OK]ボタンをクリックすると、「客数」のセル($E$6)の数値が変更される④。(表にゴールシークの結果を反映させたくない場合は、結果のダイアログで[キャンセル]ボタンをクリックします。)

●ゴールシーク使用時の注意点
画像
セルF6には数式「=F7+F8」が入力されているため、ゴールシークの[変化させるセル]には指定できない
 ゴールシークは非常に便利な機能ですが、2つの大きな制約があります。1つは[目標値]にセル参照が指定できないこと、そしてもう1つが[変化させるセル]に数式が入力されているセルは指定できないことです。今回の例の場合、右表のように「客数」を数式で求めているとエラーが表示されます。

 このような場合は、表全体を新しいシートにコピーして、対象のセル(右の例では「客数」)をベタ打ちで入力してからゴールシークを行います。


目標利益から必要な回転率を割り出す

 前項ではゴールシークの基本を解説しました。解説した事例があまりにもシンプルだったため、「これなら割り算で計算したほうが早いのでは?」と思った人も多いと思います。実際その通りで、前項で求めた「客数」は12万円÷500円の割り算をするだけで簡単に計算できます。わざわざゴールシークを利用する必要はないかもしれません。

画像
回転率を何%に上げると、営業利益 1,000千円を達成できるだろうか
 前項で紹介した計算式は、あくまでもゴールシークの基本を解説するための簡易な事例とお考えください。ゴールシークが真に役立つのは複雑な計算が必要な場合です。実際、現実のビジネスシーンで数値を検証する際には、もっと複雑に関係し合い、影響を与え合う要素が複数存在すると思います。

 ここでは、ある飲食店の営業計画表を例に考えてみます。右表で、月次の営業利益1,000千円(百万円)を達成するには、回転率を何%に上げる必要があるでしょうか。

【次ページ】「回転率」はいろいろな数値と関連し合う

Office、文書管理・検索 ジャンルのトピックス

Office、文書管理・検索 ジャンルのIT導入支援情報

PR

ビジネス+IT 会員登録で、会員限定コンテンツやメルマガを購読可能、スペシャルセミナーにもご招待!