Excelで予算を立てていると、今の計算式のままで、結果を0にするためにここの数字をいくつにすればいいだろう？と思うことがあります。
これもExcelでできます。
要は逆算できればいいのですよね。
というゴールシーク機能の紹介です。
実例
さっきこんなのをツイートしました。
Excelの予算策定の友、ゴールシーク機能で逆算です！！ pic.twitter.com/P7tlhHjLLO
— 佐藤嘉浩(Excelの魔法使い・スピルのひと) (@yosatonet) 2019年3月24日
予算の残額、不足を0にするために、4等の当選本数を何本にすればいいか、逆算の計算式を作れないことはないですけど、その計算式を考えるのは面倒だし時間がかかります。
今回紹介するゴールシーク機能は、逆算みたいに見えるのですが、実はもっと人間的というか、たくさん数字を入れ替えていってその中で答えを見つけるという人間がやったら大変時間がかかるものをExcelが代行してくれます。
ゴールシーク
ゴールシークは、計算式、目標値、入れ替えるセルの3つが必要です。
単純な逆算を例に解説します。
これはなにかの申し込みで、最大人数があって、そこに何人申し込んで、残席いくつという数字を求めています。
最大人数は100と手入力、申し込み人数は96と手入力、残席数は最大人数から申し込み人数を引く計算式が入力されて6と求まっています。
今回は残席が0になるためには申込者が何人になればいいかをゴールシークで求めてみます。
計算式は残席数に入っています。
その目標値は0です。
変化させて行くセルは申し込み人数です。
そこまで決まったら、データタブの中のデータツールの中のWhat-If分析のゴールシークを選択します。
数式入力セルが計算式、目標値が目標値、変化させるセルが変化させていくセルで、OKします。
結果が求まります。
ソルバー
もう一つ逆算ができる機能として、ソルバーという機能がExcelにあります。
これはさらに詳しく逆算でき、複数の変化するセルを設定したり、計算結果に制限をかけたり、目標値を考えられるものの中で最大値のようにあいまいな設定ができたりします。
与えられた条件の中で最高の効果を発揮するには、どことどこをどんな値にすればいいかという、もう人間業ではできないようなことも、ひとつひとつ丁寧に値を入れ替えていって試してくれます。丁寧と言っても実行すれば１，２秒で答えを出してくれます。
この機能は、ファイルタブの中のオプションの中でソルバーアドインを設定することで使用ができるようになります。
はじめから設定されていないのが不思議なくらいの機能なのですが、このアドインを設定するとExcelが不安定になることも時々あります。
コメント