• 会員限定
  • 2022/09/19 掲載

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

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

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

藤井 直弥、大山 啓介

藤井 直弥、大山 啓介

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

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


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

 ゴールシークは、希望の計算結果を先に決めてから、その結果を得るために必要な値を逆算する機能です。

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

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

●通常のExcelの計算
平均客単価[500円]× 客数[200人]= 売上[???円]
通常の場合、平均客単価、客数といった数式の要素はあらかじめ決まっている
●ゴールシークを用いたシミュレーション
平均客単価[500円]×客数[???人]=売上[12万円]
数式の結果だけ先に決まっていて、数式の要素を逆算するには、ゴールシークが便利
 ゴールシークは応用範囲の広い機能です。みなさんのアイデア次第でいろいろな計算に利用できます。例えば、上記の他にも「目標利益から必要な回転率を割り出す」(図1)、「総予算(利益)から調達できる人員を割り出す」(図2)などでも利用できます。ポイントは上記にある通り「希望の計算結果を得るために必要な値を逆算する」です。このことを念頭に、どのようなケースで利用できるか検討してみてください。また、既存の業務のなかでこの機能を活用できるものがないか、確認してみてください。

画像
図1:回転率を何%に上げると、営業利益1,000千円を達成できるだろうか
画像
図2:人件費の「売上に対する比率」を30%以内に収めつつ、バックオフィスの従業員数を最大化したい

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

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


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


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




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

【次ページ】目標利益から必要な回転率を割り出す

関連タグ

あなたの投稿

関連コンテンツ

PR

処理に失敗しました

トレンドタグ

おすすめユーザー

会員登録で動画、資料に使えるホワイトペーパー、オンラインセミナー年間500本など、会員限定記事が​閲覧できる!​

投稿したコメントを
削除しますか?

あなたの投稿コメント編集

機能制限のお知らせ

現在、コメントの違反報告があったため一部機能が利用できなくなっています。

そのため、この機能はご利用いただけません。
詳しくはこちらにお問い合わせください。

通報

このコメントについて、
問題の詳細をお知らせください。

ビジネス+ITルール違反についてはこちらをご覧ください。

通報

報告が完了しました

コメントを投稿することにより自身の基本情報
本メディアサイトに公開されます

必要な会員情報が不足しています。

必要な会員情報をすべてご登録いただくまでは、以下のサービスがご利用いただけません。

  • 記事閲覧数の制限なし

  • [お気に入り]ボタンでの記事取り置き

  • タグフォロー

  • おすすめコンテンツの表示

詳細情報を入力して
会員限定機能を使いこなしましょう!

詳細はこちら 詳細情報の入力へ進む
報告が完了しました

」さんのブロックを解除しますか?

ブロックを解除するとお互いにフォローすることができるようになります。

ブロック

さんはあなたをフォローしたりあなたのコメントにいいねできなくなります。また、さんからの通知は表示されなくなります。

さんをブロックしますか?

ブロック

ブロックが完了しました

ブロック解除

ブロック解除が完了しました

機能制限のお知らせ

現在、コメントの違反報告があったため一部機能が利用できなくなっています。

そのため、この機能はご利用いただけません。
詳しくはこちらにお問い合わせください。

ユーザーをフォローすることにより自身の基本情報
お相手に公開されます