ゴールシークの使い方は？エクセルで逆算する方法をご紹介！

「ゴールシーク」は最適化分析ツールと呼ばれるコマンドの1つと言われております。ゴールシーク機能はエクセルの関数で計算する結果が目標値になるように代入する値の最適値を求めます。エクセルでは関数を使って計算結果を求めるのが一般的ですが、ゴールシークでは先に計算結果を決定してその計算結果を得るために必要な数値を求めることができます。

ゴールシークを利用することで数式が目標値を返すまで、その参照元セルの値を変化させて答えをシュミレーションできるのです。ゴールシークを使用できるようになれば複雑な計算など様々なシーンで有効活用することができます。

「ゴールシーク」の数値を逆算する機能をご紹介します。通常エクセルを開いて数式を求める場合、関数を利用してエクセルの計算結果を求めますが、「ゴールシーク」機能を利用するとその計算結果から求める数値を逆算することができます。

逆算する数式を作成しなくても数値を求められるのがゴールシークの最大の魅力と言えるでしょう。もう少し簡単に説明すると、10になんの数字を足すと100になるか、こちらは計算で簡単に求まりますが、そうではなく、一回一回足す数字を繰り返し変更していきます。

まず50を足してみて、答えが足りなかったので次は70を足してみる、それでも足りないければ90を足してみるというように繰り返し計算する機能です。

ゴールシークを利用するメリットは、たとえば、粗利益を求めたい場合、数式は「粗利益＝（売値−仕入値）÷売値」になります。この数式の場合、「売値と仕入値」から粗利益を求めるわけですが、「粗利益と仕入値」から売値を逆算するとした場合には、「売値＝仕入値÷（1-粗利益率）」という数式になります。

ところが、ゴールシークを利用すると最初の「粗利益＝（売値−仕入値）÷売値」を利用して売値の逆算を求めることができるのです。売値を逆算する数式を求める必要がないので数式が簡略化されます。これがゴールシークを利用するメリットになります。

求める数値を逆算するための数式を導くことができる場合、その数式を利用した方が正確な数値を得ることができるかもしれません。しかし、専門的なワークシート関数が利用されていた場合や、複雑な数式を使用しないと数値を求められない場合、入力されている数式を利用して逆算できるゴールシークは、逆算式を作成する必要がないのです。

逆算式を作成する必要がないということはその分時間を節約することができます。ゴールシーク機能を利用することで複雑な数式であればあるほどその分時間を節約できるのです。

ゴールシークはエクセル2017年以降の使い方とエクセル2016以前の使い方をご紹介しますが、この二つには若干の違いがあります。機能の違いはほぼありませんが、エクセルを開いた後の操作方法に違いがあります。冒頭から言葉では説明しておりますが、実際にエクセルを開いてからどのように計算結果を逆算して目標値を求めるのか説明させていただきます。

エクセル2017以降のゴールシークの使い方を説明します。まずは、エクセルを開いて「ツール」を選択します。その中の「ゴールシーク」をクリックします。「ゴールシーク」ダイヤログボックスの「数式入力セル」に求める値の数式を設定します。次に「目標値」の目標とする計算の結果を設定します。

そして、逆算して求めたい値を「変化させるセル」に設定してOKボタンをクリックします。「解答が見つかりました」とメッセージが表示されたら逆算した数式が選択したセルに表示されます。

エクセル2016以前のゴールシークの使い方を説明します。エクセル2016では「データ」タブをクリックまでは同じです。次に2017以降は「ツール」を選択でしたが、2016以前は「What-If 分析」をクリックします。その中の「ゴールシーク」をクリックすればダイヤログボックスが表示されます。使い方は前項のエクセル2017年以降と同じです。

つまり違いはエクセルを開いたあと「ツール」を選択するか、「What-If 分析」を選択するかの違いです。表示される項目は変わりませんので呼び方が変わったということです。

ゴールシーク機能で目標達成に必要なアクションに落とし込むことができます。例えば、2021年の年末までの月数を求めて、目標とする売上をその月数で割れば毎月のアクションとして落とし込むことができます。簡単な数値であれば暗算でも計算できますが、エクセルのDATEDIF関数を使うと簡単に計算結果を求めることができるので次項で説明します。

月々の目標値が導き出されるのでその目標値を達成するためのアクションに落とし込むことができるのです。ゴールシーク機能はこのように様々な活用方法があります。

前項でお話したDATEDIF関数を使う場合を紹介します。例えば2021年の年末までの月数を求め、目標とする売上をその月数で割れば毎月の目標値を計算することができます。指定期間の日数、月数、年数を求める時にDATEDIF関数を使います。

この場合=DATEDIF(“2019/8/6″,”2020/12/1″,”M”)と入力します。この数式をもとに、2021年12月に1000万円達成するために必要な毎月の売上は=(目標売上-現在の売上）/月数ですから、約59万円と求めることができます。

ゴールシークでは、反復計算が実行されております。まず「変化させるセル」に設定した逆算して求めたい数値を少しずつ変化させて行き、対象となる数式に代入してその計算結果と目標値を比較して行きます。

このような処理をして、計算結果と目標値が一致するまで繰り返し行うのです。しかし、複雑な計算をする場合、計算結果を無限にループしてしまうことがあります。

その無限ループのような状態になるのを防ぐために、反復計算の最大回数などが設定されています。エクセルの設定を確認してみましょう。次項では実際にエクセルを開いた状態からどのような流れで設定するのかを紹介します。

まずは「ツール」を選択してください。次にメニューの「オプション」をクリックして下さい。ダイヤログボックスの「計算方法」をクリックしてください。

反復回数が設定されている最大反復回数を超えたとき、または計算結果の変動が変化の最大値より小さい変動の場合、反復計算は停止されます。

最大回数の値、変動の最大値の値を大きくしたり小さくしたりすることで反復回数を制限することができます。必要以上に反復計算が行われることを防ぐことができるのです。また、最大反復数の値を大きく、変化の最大値の値を小さくした場合、より正確な逆算結果を求めることができる場合もあるのです。

ゴールシーク機能の注意点として、使い所が難しい点があります。まず目標値を必ず手入力しなけらばならないこと、エクエルの「F2」などのセル参照入力をすることができないことです。ゴールシークの機能は複数セルに対応できないため、あくまでも単一セルに答えを導きだすことしかできないのです。

単一セルの答えしか導き出せないのであれば、言い方は悪いですが、問い合わせ程度の役割にしかならないのです。これがゴールシーク機能の最大の弱点です。以上を理由にゴールシーク機能についてよく知っている人間でもあまり使いたがらない人がいることが事実です。

エクセルのゴールシークについて知名度はあまり高くはないですが、使いこなすことができれがエクセルを充実させることができる機能です。例えば、「税込価格が1000円の時の消費税額を知りたい」「100万円の利益を出すための売上額がを計算したい」「10年間で貯金を1000万円貯めるには毎月いくら貯金すればいいのかなどというときに活用できます。

実際には、もっと複雑な計算にゴールシーク機能を使います。例えばお金のローンや複利における利率計算などには最適です。ゴールシークは使い方さえ覚えてしまえば、あとは簡単で、様々なシーンで活用することができるのです。

特に計算式を考えるのが嫌いな方、計算が苦手な方にとっては有効活用できるツールです。この機会にゴールシークの使い方を覚えてエクセルを充実させましょう。