Introduction
Ver. all 2013 2016 2019 365
エクセルでのゴールシークの使い方です。ゴールシークは、目標を達成するための数字を知りたい時に使用します。
ゴールシークの設定画面には、［数式入力セル］［目標値］［変化させるセル］と、普段見ない言葉が並びます。
そのため、どこにどのセルを指定して良いのか困ってしまう方もいるかもしれません。
設定したセルがおかしいと様々なメッセージが表示されて注意されてしまいます。
ここでは、あえて小さな表を使用してゴールシークの基本的な使い方を説明していきます。
ゴールを探すにゃ
ゴールシークとは
「ゴールシーク」とは英語にすると「Goal Seek」
- Goal・・・目標
- Seek・・・探す、求める
つまり、「目標を探し求める」という機能です。
具体的には、目標を定めて、その目標に達するための数値を探します。
本来は、複雑な計算式を使用した場所に使用する機会が多いかもしれませんが、ここでは説明を分かり易くするため小さな表で説明していきます。
下の表は、それぞれの商品の「単価」と売れた「数量」から、「売上」を算出しています。
この表の「E列」色を塗った部分には下のように数式が入っています。
まず、この表の１列目、「猫缶A」に注目しましょう。
「100円」の猫缶が「100個」売れて、「10000円」儲かっています。
これを「来月の売上は20000円にしたいなー」という場合はどうすれば良いでしょうか。
今は「売上」セル「E3」の計算式は、
100×100=10000
になっています。
値段の「単価」は変更せずに「数量」を増やすことで考えると・・・。「数量」を「100」から「200」売るように頑張れば解決ですね。
100×200=20000
このように簡単な場合は何となくで解決できるのですが、下の場合はどうでしょう。
１つ下の「猫缶B」の売上を「7500」 → 「10000」にしたい。さて、「数量」はいくつにすれば解決するんだろう・・・。
今の式は
150 × 50 = 7500
なので、「数量」を「x」にして、
150 × x = 10000
x = 10000 ÷ 150
x= 66.6666
という感じでしょうか。
面倒ですね。今回の例では単純な式で済みますが、もっと複雑な計算式が必要な場合はもっと大変。
この面倒を解決してくれるのが「ゴールシーク」機能です。
ゴールシークの使い方
では、先ほどの例をゴールシークで解決してみましょう。
まず、ゴールシークの画面を表示します。
［データ］タブ → ［What-IF 分析］をクリック。
一覧から、［ゴールシーク］を選びます。
下の設定画面が表示されます。
ゴールシークが苦手な方は、この場所にどの場所を指定すれば良いのか悩んでしまうのではないでしょうか。
- ［数式入力セル］・・・一番大切となる、結果を知りたいセルを指定します
- ［目標値］・・・上で指定したセルの目標はいくつにするか、自分で数字を入力します
- ［変化させるセル］・・・目標を達成させるために、数値を変更させたいセルを指定します
今回の例では、下のように設定します。上の説明と照らし合わせてじっくり見てください。
※セルはマウスで指定すると、自動で「$」記号が付き、絶対参照になります。
［OK］をクリックすると、指定した［変化させるセル］の場所に結果が表示されます。
今回は、66.666・・・ということで、67個売れば10000を達成できるようですね。
そして、最後に［OK］をクリックするか、［キャンセル］をクリックするかも大切です。
［OK］をクリックすると、結果が表に反映され、［キャンセル］した場合は結果が元に戻り表の内容は変更されません。
結果の数字を参考に見ているような時は、［キャンセル］した方が良いですね。
また、このゴールシークは複数の計算結果の場所でも使用できます。
例えば、下のように「E7」セルの数値を目標として、変更するセルを「D3」にしたような場合です。
「E7」セルは、上の計算結果の合計です。「数量」の場所を変化させた結果を知るには、計算が２度（掛け算とSUM関数）必要になりますね。
そんな時でも、下のように設定してあげれば、
ちゃんと結果が表示されます。
どうやら「猫缶A」を175売れば、目標の「70000」に到達するようですね。
収束しない時の原因
ゴールシークの設定画面で［OK］を押しても、結果が表示されず、エラーが出ることがあります。
そのほとんどは、指定するセルの決まりを守っていないことが原因です。
原因１ ［変化させるセル］を間違えている
その決まりをまとめると、下のようになります。
先ほどの設定画面と説明を見比べてみてください。
最初の［数式入力セル］は、「結果を知りたいセル」を指定するのですが、そのセルは必ず数式が入力されている必要があります。
次の［目標値］にの場所にセル参照は指定できません。必ず自分で数字を入力します。
最後の［変化させるセル］は、セル参照で指定しますが、そのセルは数式が入っていてはいけません。数値だけが入力されている必要があります。
これらのルールを１つでも守っていないと、ゴールシークは実行されず、注意画面が表示されます。
上から順番に、
「数式が入力されているセルを指定してください。」
「入力した数値は使用できません。整数型または小数点数を入力してください。」
「参照が正しくありません。」
という感じ。
この中で、注意したいのは、最後の［変化させるセル］です。
最初の［数式入力セル］は、「結果を知りたいセル」には通常数式が入力されているはずですので、自然と数式が入力されているセルを指定するはずです。
また、真ん中の［目標値］は目標となる値は設定する瞬間に想像すると思うので、セル参照を使わず自分で数値を入力しようとするでしょう。そもそも、マウスでセル番地をクリックすることができなくなっています。
しかし、最後の［変化させるセル］ですが、この場所は数式が入っているセルを指定したい可能性も十分ありえます。
数式が入っているセルを使いたくても、そうすると「参照が正しくありません」と注意が表示されてしまう・・・。
その場合の解決策ですが、単純に考えてしまいましょう。
他のセルにいったん、その数字を入力してから指定する
下のようなイメージです。
これで解決です。
ちょっと格好悪いですが、結果を知りたいだけであれば、十分だと思います。
原因２ 関係ないセルを指定している
もう一つの原因ですが、何となく関連性の無いセルを指定してしまっているかもしれません。
極端な例ですが、下の画像をご覧ください。
［変化させるセル］に、目標として指定する［数式入力セル］の数式と全く関連性の無いセルを指定しています。
すると、一応予測計算はスタートしてくれるのですが、下のように「解答が見つかりませんでした。」と表示されます。
この場合は、もう一度自分が必要としている予測データと必要なセルを冷静に見直す必要がありますね。
まとめ
ゴールシークが上手くいかない場合や、難しく感じる場合は、指定するセルを的確に選ぶことができないことが原因です。
冷静に表の内容を把握し、指定するセルのルールを守る必要があります。
途中に使用した２つの画像がポイントです。
この２つをまとめると下のようになります。
参考にしてください。
損益分岐点を知りたい場合とかで使うと便利なんだにゃ