Excelのゴールシークをマクロで動的に利用する

Excelのよく使われる機能でゴールシークというものがあるのですが、ある計算結果が指定する結果通りに逆算してくれる機能です。結構広く使われています。

このゴールシークは値を変化させるセルを１つしか指定できないのですが、その時の最大値のセルを指定したいなどその時その時変える場合はやはりマクロで動かす必要があります。

[ad#top-1]

まずはゴールシークの基本

マクロの前にゴールシークの基本を抑えます。

こんな風にExcelの表があったとして、C8セルに上の5データの合計の数式を入れてあったとします。この合計値を現在の25ではなく20にするためにはその上のデータをどのようにすれば良いか？というための機能です。

 

ゴールシークを使うためにはデータリボンの「What-If分析」－「ゴールシーク」を選択します。

 

「数式入力セル」に計算結果のセルを、「目標値」にどの結果にしたいかの数値、「変化させるセル」に変化させるデータのセルを指定します。

 

そうすると、目標値になるよう値を変化させてくれます。

 

今回は合計という単純な計算式だったのでゴールシークを使わずとも答えは変わりますが、まあ使い方の基本ということで。

で、なんでマクロで使う必要があるかというと、個人的に変化させるセルが1か所しか指定できない点です。例えば目標値に1ずつ変化させていく過程で最も大きい数値を変化させるという要求だってあると思います。

そんな時はマクロを使います。（もしかして他に良い方法あるかも。でも僕は複雑になるとマクロで作ってしまいますね。）

 

マクロ(VBA)でゴールシークを使う場合

マクロでゴールシークを使うには以下のコードとなります。上の例と同じ動きをします。

Range("B7").GoalSeek Goal:=25, ChangingCell:=Range("B4")
 

 

マクロで制御するときはRangeよりもCellsを使った方が良いでしょう。

Cells(7, 2).GoalSeek Goal:=25, ChangingCell:=Cells(4, 2)
 

 

まずはこれが基本形です。

 

目標値に1ずつ変化させていく過程で最も大きい数値を変化させる

目標値に1ずつ変化させていく過程で最も大きい数値を変化させるためのソースコードは以下の通りです。

先ほどの例で合計値がもともと25だったのですが、これを目標値20にもっていく過程でその時の最大値を変化させるという動きになっています。

Sub ゴールシークマクロ()
  For i = 24 To 20 Step -1
    '最大値を探す
    最大値行 = 3
    For r = 4 To 7
      If Cells(r, 3) > Cells(最大値行, 3) Then
        最大値行 = r
      End If
    Next r

    Cells(8, 3).GoalSeek Goal:=i, ChangingCell:=Cells(最大値行, 3)
  Next i
End Sub
 

 

生産管理系のシステムで全体の生産キャパが決まっていて、どの顧客向けにアロケーションしていくか、というような時に使うと便利です。

 

[ad#ad-1]

 - Excel, VBA