ゴールシークとは

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


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


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

ゴールシークの使い方

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

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



まず、「ツール」メニュー→「ゴールシーク」をクリックします。

Excel2016では「データ」タブから「What-If 分析」をクリックし、「ゴール シーク」をクリック。
ゴールシーク

[データ] タブの [データ ツール] グループで、[What-If 分析] をクリックし、[ゴール シーク] をクリック

表示された「ゴールシーク」ダイアログボックスの「数式入力セル」に、BMI値を求める数式が入力されているセルB6を設定し、「目標値」に目標とする計算結果を設定します。ここでは、目標とするBMI値「20」を設定します。そして、その目標値を得るための数値(逆算したい「体重」)が入力されているセルB4を「変化させるセル」に設定して「OK」ボタンをクリックします。


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



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



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

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



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

ゴールシークのしくみ

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

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


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

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


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


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


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

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


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

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


もし、値が見つからないまま反復計算が停止すると、「解答が見つかりませんでした」というメッセージが表示されます。
※上図は、BMIとは別のサンプルです


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

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

※記事内容は執筆時点のものです。最新の内容をご確認ください。
※OSやアプリ、ソフトのバージョンによっては画面表示、操作方法が異なる可能性があります。