GoalSeekメソッドを使ってますか？
GoalSeekは、 エクセルの逆算機能「ゴールシーク」の機能を持つメソッドです。
エクセルでゴールシークを使うけど、VBAマクロではどのように記述すればいいのかわからない・・・という声が非常に多いです。
今回 は、VBAでGoalSeekメソッドを使う方法について徹底的に説明していきます。
【VBA】GoalSeekの使い方・マクロ記述方法
GoalSeekは、エクセル「ゴールシーク」をプログラムに置き換えたもの
VBAでGoalSeekというと敷居が高そうですが、そんなことはありません。
基本的にVBAマクロは、エクセルを手入力した動作をプログラムに置き換えたものです。
VBAでGoalSeekを使うには、エクセルで設定したゴールシークを、そのままVBAコードに置き換えればよいのです。
まずは、エクセル上でのゴールシークの動作を見てみましょう。
エクセル上でのゴールシークの設定・動作
今回は、下の表のように「販売価格」と「売上目標」が決まっているときに、商品が何個売れれば「売上目標」を達成できるのか、「販売個数」を求めたい場合にゴールシークを使ってみます。
今回は、売上目標は10,000円、販売価格は5,000円、そのときの販売個数を求めます。
ゴールシークを使うために、売上目標＝販売価格×販売個数として、「C3」に数式「=C4*C5」を入力します。
これで、「売上目標」を達成するための「販売個数」をゴールシークを使って逆算する準備ができました。
ゴールシーク機能を表示するために、「データ」→「What-If分析」→「ゴールシーク」を選択します。
表示された「ゴールシーク」メニューに、
- 数式入力セル：C３（売上目標）
- 目標値：100,000
- 変化させるセル：C５（販売個数）
「OK」を押してゴールシークを実行すると、以下のように販売個数「C５」に20と入力されます。
以上で、ゴールシークを使って売上目標に対する販売個数を逆算して求めることができました。
次に、これらの動作をVBAで置き換えて記述してみます。
VBAでゴールシーク(GoalSeek)を記述する
GoalSeekの構文は、以下のとおりです。
構文：数式入力セル. GoalSeek Goal:=目標値, changingcell:=変化させるセル
数式入力セルは「売上目標C３」、目標値は「売上目標の値（今回は10,000）」、変化させるセルは「販売個数C５」です。
VBAを記述する前に準備として、エクセルのゴールシーク機能を使うときと同様に、セル「C３」に、売上目標＝販売価格×販売個数として、数式「=C4*C5」を入力しておくことを忘れないようにしてください。
GoalSeekを使ったサンプルコード：
Sub ゴールシーク()
Range(“C3”).GoalSeek goal:=100000, changingcell:=Range(“C5”)
End Sub
実行結果：
エクセルのゴールシークと同様に、セルC５に20と入力されます。
目標値を変更したい場合は、コードの 「goal:=100000」の数字を変更すればよいのです。
【まとめ】GoalSeekの使い方・マクロ記述方法
エクセルのゴールシーク機能をVBAで使用するには、GoalSeekメソッドを使用します。
構文：数式入力セル. GoalSeek Goal:=目標値, changingcell:=変化させるセル
GoalSeekメソッドは複雑な表にこそ真価を発揮しますので、今回の記事を基本として使っていってくださいね。
コメント