エクセルのゴールシークの意味や使い方！

エクセルのゴールシークとは

「ゴールシーク」は、ある数式の計算結果から、その結果を得るための数値を逆算する機能です。通常、逆算値を得るためには、逆算するための数式を作成する必要がありますが、ゴールシークを利用すると、入力されている数式を利用して逆算値を求めることができます。「逆算用の数式を作成する必要がない」という点は、ゴールシークの大きな魅力といえるでしょう。

たとえば、粗利率を計算する数式は「粗利率＝（売値-仕入値）／売値」です。この数式は、「売値と仕入値」から「粗利率」を計算するわけですが、「粗利率と仕入値」から「売値」を逆算するには、「売値＝仕入値／（１-粗利率）」という数式を導く必要があります。ところが、ゴールシークを使用すると、粗利率を計算する数式「粗利率＝（売値-仕入値）／売値」を利用して「売値」の逆算することができる、ということです。

もちろん、逆算用の数式を導くことができる場合は、その数式を使用した方が正確な結果を得ることができます。しかし、数式が複雑だったり、数式で専門的なワークシート関数が使用されていた場合など、逆算用の数式を導くことが難しい作業が場合、入力されている数式を利用して逆算できるゴールシークは、大変便利な機能といえるでしょう。
 

ゴールシークの使い方

ここでは、「シナリオ機能を活用してみよう！」で作成した体重管理シートを使用して、ゴールシークの操作方法を解説します。

このシートには、「身長」と「体重」から「標準体重」や「BMI値」を計算する数式が入力されています。そこで、セルB6に入力されているBMI値を算出する数式を利用して、「ある身長」のときに「特定のBMI値」を得るための「体重」を逆算してみましょう。
 

まず、「ツール」メニュー→「ゴールシーク」をクリックします。
Excel2016では「データ」タブから「What-If 分析」をクリックし、「ゴール シーク」をクリック。 表示された「ゴールシーク」ダイアログボックスの「数式入力セル」に、BMI値を求める数式が入力されているセルB6を設定し、「目標値」に目標とする計算結果を設定します。ここでは、目標とするBMI値「20」を設定します。そして、その目標値を得るための数値（逆算したい「体重」）が入力されているセルB4を「変化させるセル」に設定して「OK」ボタンをクリックします。

「解答が見つかりました」というメッセージが表示されます。このとき、逆算して求められた「体重」の値は、セルB4に入力されています。そして、表示されているメッセージには、設定した目標値の下に、逆算した値を使用して実際に計算した「現在値」が表示されています。
 

「OK」ボタンをクリックすると、逆算で求められた「体重」の値が入力されたまま、メッセージが閉じられます。「キャンセル」ボタンをクリックした場合は、逆算で求められた「体重」の値は入力されずに、ゴールシークを実行する前の値に戻って、メッセージが閉じられます。
   

逆算の結果が反映されたワークシートを元に戻す

なお、逆算値が入力されたあと、ゴールシークが実行される前の状態に戻したい場合、実行前のデータを覚えておいたり、メモなどに書き留めておいたりするのも良いのですが、「シナリオ機能を活用してみよう！」で紹介したシナリオを利用して、実行前のシナリオを登録しておけば、すぐに元に戻すことができます。
 

最後に、ゴールシークをしくみを紹介しながら、利用する上で知っておくべき重要なポイントを紹介します。
 

ゴールシークのしくみ

ゴールシークでは、次のような「反復計算」が実行されています。

まず、「変化させるセル」に指定したセルの値（逆算して求めたい値）を少しずつ変化させて対象の数式に代入し、その試算結果と目標値を比較します。この処理を、試算結果と目標値が一致するまで繰り返しています。  

逆算値が見つからないとき

したがって、いつまでたっても試算結果と目標値が一致しない場合、つまり、逆算値が見つからない場合、Excelは計算を続けることになります。そのような状態になって、止まらなくなってしまったら、「ゴールシーク」ダイアログボックスの「一時停止」ボタンをクリックして、計算を中断してください。

※上図は、BMIとは別のサンプルです

また、ゴールシークが実行されている途中で、「ステップ」ボタンをクリックすると、試算のたびに計算が中断されます。これなら、逆算で求められた数値や、その数値を使って計算された試算値（現在値）を確認しながら、ゴールシークを実行できます。

※上図は、BMIとは別のサンプルです

次の試算結果を表示する場合は、再度「ステップ」ボタンをクリックします。また、ステップモードを抜けて、最後まで計算させるには「継続」ボタンをクリックします。

※上図は、BMIとは別のサンプルです

 

ゴールシークの設定を変える

ゴールシークでは、無限ループのような状態になるのを防ぐために、反復計算の最大回数などが設定されています。その設定を確認してみましょう。「ツール」メニュー→「オプション」をクリックして下さい。

表示された「オプション」ダイアログボックスの「計算方法」タブをクリックして下さい。

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

もし、値が見つからないまま反復計算が停止すると、「解答が見つかりませんでした」というメッセージが表示されます。

※上図は、BMIとは別のサンプルです

「最大反復回数」の値を小さくしたり、「変化の最大値」の値を大きくしたりすることで、ゴールシークの反復回数を制限し、必要以上に反復計算が実行されるのを防ぐことができます。

逆に、「最大反復回数」の値を大きくしたり、「変化の最大値」の値を小さくしたりすることで、より多く反復計算を実行させて、より正確な逆算値を求めることができる場合もあります。