ヒガサラblog

サラリーマン向けに、仕事を効率良く進めていくための方法についてご紹介しています。プログラミングから対人スキルまでを幅広く掲載中。

ヒガサラblog

【エクセル】数式の解を狙いの値に合わせこみ!VBAへの組み込みも!

f:id:yshgs_elec:20200610221806j:plain

 

仕事でエクセルを使っている際に、いくつかのセルを参照しながら数式を作成することはよくあると思います。

「数式の解は決まった値にしたい。その場合の参照しているセルの数値はどうすればいいんだろう?」

 

この記事ではこういった状況を一瞬で解決できる方法をご紹介します。

 

是非このスキルをマスターし、日々の業務を効率化していきましょう。 

 

それでは早速やっていきます!

 

数式合わせこみスキル【ゴールシーク】

エクセルには特別な設定をすることなく、

セルに組み込まれた数式の解を、指定した値に簡単に合わせこむ方法

が存在しています。

その方法はゴールシークと呼ばれています。

ゴールシークの意味を簡単に説明すると、

ゴール(目的の値)を

シーク(探索)するということです。

 

以降はこのゴールシークの使い方を詳細解説していきます。

 

ゴールシークの使い方

基本的には特別な設定は何も必要ありません。

以下の画像のように

【データ】タブ

⇒What-If分析

⇒ゴールシーク

を選択するだけです。

f:id:yshgs_elec:20200723205414j:plain

 

とはいえ、これだけではイメージがつかないでしょうから、次は実際の事例を用いながら説明していきます。

 

台形の面積に適用

次にゴールシークの使い方のイメージを持ってもらうために台形の面積を例に説明していきましょう。

まずは以下のように台形の面積を算出するために必要な情報、および台形の面積を求める式を入力しましょう。

B4セルには(上底+下底)×高さ÷2を示す

=(B1+B2)*B3/2

という式が入力されています。

f:id:yshgs_elec:20200317215634p:plain

 

この状態を作ったあと、先ほど説明した要領で、ゴールシークを選択します。

f:id:yshgs_elec:20200317215644p:plain

 

すると以下のようなウィンドウが現れます。

ここに適切な情報を入力していきます。

 

今回は台形の面積(B4セル)を300にするときの高さ(B3セル)を探索してみます。

この場合、数式入力セル、目標値、変化させるセルは以下のように入力します。

f:id:yshgs_elec:20200317215706p:plain

入力が終わったらOKを押しましょう!

 

すると以下のように面積(B4セル)が指定した300に変化し、さらに変化させるように指定した高さ(B3セル)がもともと入力されていた15から20に変化しています。

f:id:yshgs_elec:20200317215729p:plain

手計算してもこの台形の面積は300ですよね。

これがゴールシークの基本的な使い方です。

 

VBAへの組み込み方法

VBAで複雑な処理を行っていると、VBAの自動処理の中に、このゴールシークを組み込みたい状況はあると思います。

ここではVBA上でこのゴールシークを動かす方法をご紹介します。

 

※VBAの使い方については以下の記事をご確認ください。

www.higashisalary.com

 

 難しそうですが、実はたったの一行で終わります。

Sub GoalSeek()

Cells(4, 2).GoalSeek Goal:=300, ChangingCell:=Cells(3, 2)

End Sub

先ほど行った、台形の面積を300にする高さを変更する処理の記述はたったのこれだけです。

 

 

次はさらに応用してみましょう。

VBAで処理を行ってい途中で、目標である面積値が決まったとしましょう。

そしてその目標値が以下の画像のようにB5セルに入っている状態です。

f:id:yshgs_elec:20200723214811j:plain



 

先ほどの例では目標値は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)

というセルの場所に書き換えるだけです。

 

実際に実行すると以下のようになりました。

f:id:yshgs_elec:20200723214656j:plain

これでもきちんと動いていますね。

 

Cells(*,*)の記述が扱いにくい場合は以下のように記述することもできますので、セルの記述法についてはお好きなやり方をすればOKです。

Sub GoalSeek()

Range("B4").GoalSeek Goal:=Range("B5"), ChangingCell:=Range("B3")

End Sub

 

終わりに

というわけで今回は普段のエクセル業務に活用できるゴールシークスキルをご紹介しました。また、VBAの組み込みに関しても非常に簡単に行うことができます。

是非、少しづつエクセルスキルを向上させ、日々の業務を効率化していきましょう。

 

私のブログではエクセルはもちろん、様々なプログラミングスキルを紹介しています。

・日々の残業時間を減らしたい。

・業務をもっと効率的にこなしたい。

・もっと勉強する時間を作りたい。

という方は是非、その他の記事も見てみてくださいね。

それではまた!!

 

 

オススメ記事 

www.higashisalary.com

www.higashisalary.com