こんにちはえびかずきです。
今回はゴールシークの使い方について説明します！
こんな人におすすめ：
・ゴールシークの使い方がわからない。
・ソルバーより簡単に最適化をする方法を教えて欲しい。
基本操作方法
ここではまず、ゴールシークの基本的な操作方法について説明します。
ゴールシークは簡単に言うと、
セルに入力した数式の”答え”を指定して使われる変数の値を”逆算”する機能です。
ゴールシークの場所
まず、ゴールシークがどこにあるかですが、
データタブ→What-if分析→ゴールシークで起動します。
（もしくは、Alt+T→Gでの開くことが出来ます。）
※ゴールシークはアドインではなくExcelに標準装備されている機能ですので、アドインの有効化などの前準備は必要ありません。
ゴールシークの操作方法
ゴールシークを起動すると下図のような画面が現れます。
手順としては、
- ①数式を入力したセルを指定
- ②目標値を設定
- ③変化させるセルを指定
- ④OKを押して逆算開始
となります。下で簡単な例を使って説明してみましょう。
ここでは、\(y=x^2\)という関数の結果（\(y\)）を指定して変数（\(x\)）を逆算するケースを考えます。
まず下のようにセルへ数式を入力します。
そしてゴールシークを起動し、
- 数式を入力するセル：C3
- 目標値：５
- 変化させるセル：C5
と言う風に設定してやって、OKをクリックします。
すると下図のような結果画面が表示されます。
エクセルシートに戻って確認してみると、C5の変化させるセルの値が変わっており、”2.236065”と期待した\(\sqrt{5}\)の値に近い結果が出力されていることがわかります。
また、ゴールシークはソルバーと違って結果を出力した後でも「Ctrl+Z」で元の状態に戻すことが可能です。便利ですね。
VBAでの応用（ゴールシークボタンの作成）
ここでは、マクロの記録機能を使ってゴールシークを実施するボタンを作成してみます。
マクロの記録
①開発タブ→マクロの記録をクリックします。
②マクロ名を入力する。
すると、マクロの記録の設定画面が現れますので、マクロ名（今回は”ゴールシーク”としました）を入力し、マクロの保存先を”作業中のブック”として「OK」をクリックします。
※今回はショートカットキーの欄には何も入力する必要はありません。
③マクロを記録する。
上でOKを押した瞬間からマクロの記録が始まります。
ゴールシークを再度起動し、上述した手順と同じく下の通り設定を入力し、OKをクリックします。
- 数式を入力するセル：C3
- 目標値：５（仮値）
- 変化させるセル：C5
④マクロの記録を終了する。
左下ステータスバーに表示されている四角ボタン（■）を押して、マクロの記録を終了します。
⑤VBAコードを修正する。
Alt＋F11でVBAの開発画面を開くと、標準モジュールの中に新たに”ゴールシーク”というマクロが記述されています。
このコードを開いて、仮値で入力していた目標値”5″を下のようにセル指定に変更してやります。こうすることでプログラムの汎用性が増して、後で使いやすくなります。
変更前：GoalSeek Goal:=5
変更後：GoalSeek Goal:=Range(“C4”)
変更後のコードは以下の通りです。マクロの記録機能を使わなくても、このコードそのまま使用しても構いません。
Sub ゴールシーク()
' ゴールシーク Macro
Application.CutCopyMode = False
Range("C3").GoalSeek Goal:=Range("C4"), ChangingCell:=Range("C5")
End Subコードから分かるように、ゴールシークを使用するためのVBA文法は、
数式を入力するセル.Goalseek Goal:=目標値, ChangingCell:=変化させるセル
となります。
※マクロの記録で記述されている、
Application.CutCopyMode = False
というコードは、コピーやペーストをするセルが点線で囲まれた状態をリセットするもので、今回は実質必要ありません。
⑥ボタンを作成する。
開発→挿入→左上のボタンをクリックします。
すると下のようなマクロの登録という画面が開きますので、先ほど作成した”ゴールシーク”のマクロを選択してOKをクリックします。
そして、ボタンに名前を入力すれば完成です。
試しに、目標値に”7”を入れてボタンを押してみます。
すると、変化させるセルの値が、”2.64574”と期待した\(\sqrt{7}\)の値に近い結果が出力されていることがわかります。
このようにボタンを準備しておくことで、自由に変化させるセルを変えてゴールシークを実施できるようになりました。
使用環境
今回説明した内容の使用環境は下記の通りです。
excelのバージョンなどによって若干操作法に違いがあるかもしれませんので、ご注意を。
- OS：Windows10 pro
- Excel：Microsoft Excel 2016 (バージョン1908)
まとめ
今回はゴールシークの基本的な使い方と、VBAを使ってボタンを作成する応用について説明しました。
ゴールシークは説明した通り簡単に使用できる機能ですので、ぜひご活用ください！
