スポンサーリンク
はじめに
Excelでは、計算式により様々な問題の答えを出すこともできる。
それに対し、求めたい答えが先にあって、それに適合する条件数値を求めたいという、いわゆる「逆算」の計算をしたいときに役立つのが「ゴールシーク」機能だ。
あまり頻繁に使う機能ではないと思うし、優先順位としてはさほど上げなくても良いので、ひとまずこういう機能があるというくらいは覚えておこう。
そしてゴールシーク機能には、後述するが色々と不便というか制限事項もあるので、それを改良するマクロも次回の記事で公開する。
それではゴールシーク機能について、Excel2007以降の、リボンを使ったUIを基準にして解説する(Excel2003までの、リボンでないUIの説明は省略)。
例題の前に：ゴールシーク機能の利用場面
今回の記事では単純な足し算の例題を示すが、こんなのなら、ちょっと算数ができる人だったらゴールシーク機能なんか使わず解決してしまえば良い。
実際には、もっと複雑な計算にゴールシーク機能を用いるべきだろう。
代表例(?)としては、お金のローンとか複利における利率計算などだと思う。
そういうときは多分、NPVだとかFVだとかいう訳の分からん関数を使うことになると思う。
そういうときに逆算をするというのは、よほど数学が得意な人でもない限り、ゴールシーク機能を使わないと埒が明かない。
例題
それでは例題として、下図のE2セルには1月～3月の数値を合計する式が入っていて、3月の値は空白にしている。
合計数値目標として70,000円を達成したいというのが先にあって、そのためには3月(D2セル)の数値を幾らにしたら良いかという例題だ。
まあこの程度の問題ならシンプルな解法が幾らでもあるけど、ゴールシーク機能を使って、この逆算をやってみることにする。
ゴールシークをやるに当たっては、コツとして
目標値を導き出したい計算式が入力されたセルを選択した状態で、ゴールシークの作業を開始する
ようにしてみよう。
今回の例題では、E2セルが目標値70,000円を導き出したい計算式の入ったセルなので、E2セルを選択した状態で作業開始するということだ。
その作業とは、
→「What-If分析」
→「ゴールシーク」
という手順だ。
そうするとゴールシークのダイアログが表示されるが、下図のように
②「目標値」→目標値となる数値を手入力(例では70,000)
③変化させるセル→逆算における条件数値を入れるセル(例ではD2セル)
という要領で入力していく。
そこまでやったら、総当たり式でExcelが逆算の計算を行い、しばらく経つと結果が表示される。
「OK」ボタンを押せば、導き出した答えが反映される。
ゴールシーク機能の注意点など
このゴールシーク機能は、どうにも使い所を選ぶ難しい機能だ。
まず下図②「目標値」の欄は、必ず数値を手入力しなければならなくて、「F2」などのセル参照で記入することはできない。
まずこの時点で、けっこう面倒だ。
また、先程の例ではD2セルにA課単独の目標数値を導き出せば良いだけだったが、実務では下図のようにB課、C課・・・と複数部署の目標数値を同時に求めなければならないのが当たり前だ。
そしてそれは、ゴールシークではできない。
これがゴールシーク機能の最大の弱点であり、ゴールシーク機能は複数セルに対応しておらず、あくまで単一セルに答えを導き出すことしかできない。
こんな感じだから、ゴールシーク機能についてよく知っている人でも、敬遠してあまり使いたがらない人が多数派のはずだ。
単一セルの答えしか出せないのでは、乱暴な言い方をすれば間に合わせ程度でしかない。
これをちょっと改善して、複数セルのゴールシークを一括して行うマクロを次回の記事で公開する。
スポンサーリンク
