【Excel】ゴールシークの使い方を簡単解説！目標値をサクッと計算【知っ得】

先日は、ビジネス能力検定サーティファイのExcel®表計算処理技能認定試験1・2級の問題集の模擬問題1を解いてみました。

問題だけでも、実技練習問題1・2・3、模擬問題1・2・3・4と計7種類もあり、これは勉強になると思いました。この記事の終わりで問題集へのリンクを貼ってありますので、ご購入希望の方は是非そちらのリンクからご購入のほどよろしくお願いいたします。

さて、今回の問題で引っかかったのは、セルの書式設定のユーザー定義、DSUM関数、ゴールシークの3つでした。

ゴールシークは、もうその名前からしてわけがわからず、以前問題を解いたときに出たな程度の認識でスルーしていたのですが、改めて問題で出てくると、これが使えないと話にならないと感じました。

結果としては、数式入力セルと変化させるセルを片っ端から試して正解を導いたのですが、それではスマートと言えません。

そこで本日は、ゴールシークの使い方について徹底的に調べていきたいと思います。

ゴールシークとは？

Excelの「ゴールシーク」は、特定の目標値を達成するために必要な入力値を自動的に計算する機能です。

例えば、目標売上や利益を設定し、そのために必要な価格や数量を導き出すことができます。この機能を使うことで、データ分析やシミュレーションを効率的に行うことが可能になります。

初心者でも簡単に使えるので、具体的な使い方を見ていきましょう。

ゴールシークの基本的な使い方

1. データの準備: まず、ゴールシークを使用するためのデータを入力します。例えば、売上やコストなどの数値を含む表を作成します。
2. ゴールシークを起動: Excelのメニューから「データ」タブを選び、「予測」グループ内の「What-If 分析」にある「ゴールシーク」をクリックします。
3. 目標値の設定: 開いたウィンドウで「数式入力セル」に、目標値を求めたい数式のセルを指定します。次に「目標値」に達成したい目標値を入力し、「変化させるセル」にはその数値を調整するためのセルを指定します。
4. 結果の確認: 設定が完了したら「OK」をクリックすると、Excelが自動的に計算を行い、指定した目標値を達成するための最適な値が表示されます。

データの準備

次の表を、ゴールシークを用いて、目標値を導くための計算式を作っていきたいと思います。

ゴールシークを起動

「データ」タブを選び、「予測」グループ内の「What-If 分析」にある「ゴールシーク」をクリックすると、ゴールシークが起動します。

目標値の設定

まずこの表に指定された問題文を紐解いていきましょう。

問題 5-1 「予算配分」の「合計」（H8）を30,000,000円にするには、「配分率」（C4）を何％にすればよいか、ゴールシーク機能を使用して解を求めなさい。なお、「予算配分」（’C8からG8）、「修繕費見積もり」の「合計」（H7）、「予算配分」の「合計」（H8）には、既に計算式が設定されている。

という問題です。

これを紐解いていきますと、まず、目標値が「30,000,000」であることがわかります。

開いたウィンドウで「数式入力セル」に、目標値を求めたい数式のセルを指定します。次に「目標値」に達成したい目標値を入力し、「変化させるセル」にはその数値を調整するためのセルを指定します。

とありますが、「数式入力セル」と「変化させるセル」のどっちがどっちかわかりづらいですよね。だいたい、ゴールシークの考え方がわからないという方も多いのではないでしょうか。

そこで、ゴールシークの考え方について良い図がありましたので、それを参考に図を作ってみました。

「数式入力セル」と「変化させるセル」を区別するための覚え方

ゴールシークの「数式入力セル」と「変化させるセル」を区別するための覚え方を以下に紹介します。

1. 数式入力セル:
   • 覚え方: “結果”のセル
   • 説明: このセルには数式が入っており、求めたい目標値（結果）が表示されます。たとえば、売上や利益を計算する数式が入ったセルです。
2. 変化させるセル:
   • 覚え方: “変更”するセル
   • 説明: このセルは、数式の結果を目標値に合わせるために変更されるセルです。たとえば、売上を目指すために変える価格や数量のセルです。

具体例で考える

• 数式入力セル: 売上を計算する数式（例: =価格 * 数量）が入っているセル。ここが結果として「目指す値」を出します。
• 変化させるセル: 価格や数量など、売上を達成するために調整するセル。これが実際に変更されます。

このように、「数式入力セル」は目指す結果を出す場所、「変化させるセル」はその結果に影響を与える変更対象として覚えておくと理解しやすいでしょう。

この考え方を元に、目標価などの値を設定していきます。目標値は「30,000,000」となります。予算配分の合計を目標値とするので、H8が“結果”のセルとなります。そして、その目標値に合わせるために“変更”するセル、つまり、C4となります。

結果の確認

配分率が69％のときに、予算配分合計金額が目標値の30,000,000円になるということが導き出されます。

ゴールシークの活用例

たとえば、売上目標を達成するために必要な販売価格を求める場合、売上金額を計算する数式を設定しておきます。

その数式が「=販売価格×販売数量」となっている場合、販売数量を固定し、販売価格を調整することで、目標売上金額に必要な価格を簡単に算出できます。

最後に

以上、ゴールシークの使い方について調べてみたわけですが、いかがだったでしょうか。

なにかお役に立てたならば光栄です。

おまけ

サーティファイの Excel®表計算処理技能認定試験2級を受験するにあたり、これさえ買っておけば必ず受かるといっても過言ではない一冊の本をオススメ致します。

それがこちら。

私も購入させていただき、大変お世話になっております。