仕事でエクセルを使っている際に、いくつかのセルを参照しながら数式を作成することはよくあると思います。
「数式の解は決まった値にしたい。その場合の参照しているセルの数値はどうすればいいんだろう？」
この記事ではこういった状況を一瞬で解決できる方法をご紹介します。
是非このスキルをマスターし、日々の業務を効率化していきましょう。
それでは早速やっていきます！
数式合わせこみスキル【ゴールシーク】
エクセルには特別な設定をすることなく、
セルに組み込まれた数式の解を、指定した値に簡単に合わせこむ方法
が存在しています。
その方法はゴールシークと呼ばれています。
ゴールシークの意味を簡単に説明すると、
ゴール（目的の値）を
シーク（探索）するということです。
以降はこのゴールシークの使い方を詳細解説していきます。
ゴールシークの使い方
基本的には特別な設定は何も必要ありません。
以下の画像のように
【データ】タブ
⇒What-If分析
⇒ゴールシーク
を選択するだけです。
とはいえ、これだけではイメージがつかないでしょうから、次は実際の事例を用いながら説明していきます。
台形の面積に適用
次にゴールシークの使い方のイメージを持ってもらうために台形の面積を例に説明していきましょう。
まずは以下のように台形の面積を算出するために必要な情報、および台形の面積を求める式を入力しましょう。
B4セルには（上底＋下底）×高さ÷２を示す
=(B1+B2)*B3/2
という式が入力されています。
この状態を作ったあと、先ほど説明した要領で、ゴールシークを選択します。
すると以下のようなウィンドウが現れます。
ここに適切な情報を入力していきます。
今回は台形の面積（B4セル）を３００にするときの高さ（B3セル）を探索してみます。
この場合、数式入力セル、目標値、変化させるセルは以下のように入力します。
入力が終わったらOKを押しましょう！
すると以下のように面積（B4セル）が指定した300に変化し、さらに変化させるように指定した高さ（B3セル）がもともと入力されていた15から20に変化しています。
手計算してもこの台形の面積は300ですよね。
これがゴールシークの基本的な使い方です。
VBAへの組み込み方法
VBAで複雑な処理を行っていると、VBAの自動処理の中に、このゴールシークを組み込みたい状況はあると思います。
ここではVBA上でこのゴールシークを動かす方法をご紹介します。
※VBAの使い方については以下の記事をご確認ください。
【初心者用】エクセルマクロ（VBA)の始め方を詳細解説 – ヒガサラblog
難しそうですが、実はたったの一行で終わります。
Sub GoalSeek()
Cells(4, 2).GoalSeek Goal:=300, ChangingCell:=Cells(3, 2)
End Sub
先ほど行った、台形の面積を300にする高さを変更する処理の記述はたったのこれだけです。
次はさらに応用してみましょう。
VBAで処理を行ってい途中で、目標である面積値が決まったとしましょう。
そしてその目標値が以下の画像のようにB5セルに入っている状態です。
先ほどの例では目標値は300と指定していましたが、ここもセルの数値にしてみましょう。
目標値が記入されているB5セルを指定した場合は以下のように記述すればOKです。
Sub GoalSeek()
Cells(4, 2).GoalSeek Goal:=Cells(5, 2), ChangingCell:=Cells(3, 2)
End Sub
最初はゴールを
Goal:=300
と具体的な数値を指定していましたが、
ここを
Goal:=Cells(5, 2)
というセルの場所に書き換えるだけです。
実際に実行すると以下のようになりました。
これでもきちんと動いていますね。
Cells(*,*)の記述が扱いにくい場合は以下のように記述することもできますので、セルの記述法についてはお好きなやり方をすればOKです。
Sub GoalSeek()
Range("B4").GoalSeek Goal:=Range("B5"), ChangingCell:=Range("B3")
End Sub
おわりに
というわけで今回は数式の解を狙った値になるように、参照セルを自動調整する方法をご紹介しました。
データ分析の際などにぜひご活用ください。
このように、私のブログではエクセルスキルはもちろん、様々なプログラミングスキルを紹介しています。
今は仕事中で時間がないかもしれませんが、ぜひ通勤時間中などに他の記事も読んでいただけると嬉しいです。
⇒興味をもった方は【ヒガサラ】で検索してみてください。
確実にスキルアップできるはずです。
最後に、この記事が役に立ったという方は、ぜひ応援よろしくお願いします。
↓ 応援ボタン
それではまた！
コメント