エクセルゴールシークの使い方【基本操作&VBAでの応用】

エクセルゴールシークの使い方【基本操作&VBAでの応用】

基本操作方法

ここではまず、ゴールシークの基本的な操作方法について説明します。

ゴールシークは簡単に言うと、

セルに入力した数式の”答え”を指定して使われる変数の値を”逆算”する機能です。

ゴールシークの場所

まず、ゴールシークがどこにあるかですが、

データタブ→What-if分析→ゴールシークで起動します。

(もしくは、Alt+T→Gでの開くことが出来ます。)

※ゴールシークはアドインではなくExcelに標準装備されている機能ですので、アドインの有効化などの前準備は必要ありません。

ゴールシークの操作方法

ゴールシークを起動すると下図のような画面が現れます。

手順としては、

  • ①数式を入力したセルを指定
  • ②目標値を設定
  • ③変化させるセルを指定
  • ④OKを押して逆算開始

となります。下で簡単な例を使って説明してみましょう。

ここでは、\(y=x^2\)という関数の結果(\(y\))を指定して変数(\(x\))を逆算するケースを考えます。

まず下のようにセルへ数式を入力します。

そしてゴールシークを起動し、

  • 数式を入力するセル:C3
  • 目標値:5
  • 変化させるセル:C5

と言う風に設定してやって、OKをクリックします。

すると下図のような結果画面が表示されます。

エクセルシートに戻って確認してみると、C5の変化させるセルの値が変わっており、”2.236065”と期待した\(\sqrt{5}\)の値に近い結果が出力されていることがわかります。

また、ゴールシークはソルバーと違って結果を出力した後でも「Ctrl+Z」で元の状態に戻すことが可能です。便利ですね。

VBAでの応用(ゴールシークボタンの作成)

ここでは、マクロの記録機能を使ってゴールシークを実施するボタンを作成してみます。

マクロの記録

①開発タブ→マクロの記録をクリックします。

②マクロ名を入力する。

すると、マクロの記録の設定画面が現れますので、マクロ名(今回は”ゴールシーク”としました)を入力し、マクロの保存先を”作業中のブック”として「OK」をクリックします。

※今回はショートカットキーの欄には何も入力する必要はありません。

③マクロを記録する。

上でOKを押した瞬間からマクロの記録が始まります。

ゴールシークを再度起動し、上述した手順と同じく下の通り設定を入力し、OKをクリックします。

  • 数式を入力するセル:C3
  • 目標値:5(仮値)
  • 変化させるセル: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を使ってボタンを作成する応用について説明しました。

ゴールシークは説明した通り簡単に使用できる機能ですので、ぜひご活用ください!

エクセルカテゴリの最新記事