Excel VBA ゴールシーク機能を縦に一括で連続実行できるマクロ 個人用マクロブックに組み込めるよ

Excel カスタマイズ

スポンサーリンク

前回の記事では、Excelで逆算を行う機能である「ゴールシーク」について紹介した。

Excel 逆算を行う「ゴールシーク」機能 色々と制限事項もある

2018年11月21日

今回は、そこで紹介したゴールシークのデメリットの解消にチャレンジしたマクロを作成してみた。

おさらいだが、ゴールシーク機能のデメリットは大まかに次の通りだ。

  • ゴールシークのダイアログ「目標値」の欄には、必ず数値を手入力しなければならなくて、「F2」などのセル参照で記入することはできない。
  • ゴールシーク機能は複数セルに対応しておらず、あくまで単一セルに答えを導き出すことしかできない。

詳細は省略するので、前回の記事を参照してね。

で、今回紹介するのがこのデメリットを解消するマクロってことだけど、複数セルに対するゴールシークを一括で実行することができるものだ。

このマクロは、個人用マクロブックに組み込んで使うと良い。

個人用マクロブックとかについては、ここを展開。

オリジナルのマクロをリボンに追加する方法はこちら

個人用マクロブックについては、こちらこちらで述べています。

 

動作イメージ

ではまず動作イメージから。下図では、

①E2:E4セルが計算式を入れてる箇所。
②F2:F4セルが目標値を入れてる箇所。
③D2:D4セルが空白になってて数値を入れる箇所。

で、③を入力して①の値を②に一致させるってわけね。

通常のゴールシークでは、目標値をこうやって別セルにまとめておくって手法は取れないし、複数同時にはできない。

今回のマクロ、操作方法の大まかな流れは、

(1)数式を入れてあるセルを選ぶ
(2)目標値の入力されているセルと同じ列にあるセルをどこか1つ選ぶ
(3)変化させていくセルと同じ列にあるセルをどこか1つ選ぶ

というようになるけど、順を追っていく。

まずマクロを起動すると、数式入力セルを選んでくれとメッセージが出るので、数式を入れてある①E2:E4セルを正しく選ぶ。
ここで選択するセルには、全て数式が入っていないとエラーを返す。

次に、「目標値の入力されている最初のセルを選べ」とかいうメッセージが出る。
ちょっと難しい言い回しをしてるが、実はこれ、列さえ合ってればどこのセルをクリックしても良い
この例の場合、目標値ってのは②F2:F4セルに入れてあるが、F列のうちどこかをクリックしさえすれば良い。

何でかって、最初に選んだ①E2:E4セルは2行目~4行目を選んでるけど、それに対応して同じ2行目~4行目を選ぶ仕様のマクロだからだ。
この辺、厳密に選択させようかとも思ったけど、それはやめた。

だから、もしF列のうちどこか1個のセルを選べば、該当の目標値セル範囲はF2:F4セルと自動判定するというわけだ。

最後に「変化させていく目的のセル範囲の最初のセルを選べ」とメッセージが出る。
これも先程と同様の話。

変化させていくセル範囲は正確には③D2:D4セルだけど、この場合はD列のうちどこか1つのセルをクリックすれば良い。

ここまでやれば最後には、条件を満たす値がゴールシーク機能により、③D2:D4セルに入る。

3回もダイアログ(正確にはInputBoxというやつ)を出すというのが不細工なんだけど、これ専用の新しいダイアログをオリジナルで作るとかいう手間を掛けるのは性に合わない。

何とか可能な限り手を抜いて作ったつもりのマクロだ。
この記事の解説はグダグダ長すぎるのは自覚しているが。

ソースコード

それでは最後にソースコード。
細かいテクはそれなりに使ってるけど、それの解説は割愛。

やはり目玉はゴールシークを行うGoalSeekで、これの構成は
(数式の入力されたセル範囲).GoalSeek goal:=(目標値のセル範囲), ChangingCell:=(変化させていくセル範囲)
というものだ。

流石にこんなの、私も滅多に使いはしないからイチイチ覚えたりはしてないけどね。

エラーチェックなんかは、そんなに丁寧にやってはいないので、綻びは簡単に見つかるでしょう。
ま、元のゴールシークって機能自体が割りと間に合わせ的なものだと思ってるし、このマクロの利用機会もそう多くはないだろうし、あまり深い粗探しはしないでやってほしいもんです。

スポンサーリンク