Excel。事務職のデータ分析その14。ソルバーを使って最適値を求めよう
＜ソルバー＞
下記の表があります。
適正値を求めることで、最適値をみつけやすくなりますが、
人力で行うとなるとなかなか大変なので、
ここは、Excelのアドイン機能のひとつ。
【ソルバー】
を使って求めてみたいと思います。
通常のExcelには、ソルバー機能はありません。
この機能は、アドインというプラスすると使えるツールなので、
ソルバーをExcelに追加していきます。
ファイルタブのオプションをクリックすると、
Excelのオプションダイアログボックスが表示されますので、
「アドイン」を選択して、ソルバーアドインをクリックして、
設定ボタンをクリックします。
アドインダイアログボックスが表示されますので、
「ソルバーアドイン」を選択して、OKボタンをクリックします。
データタブに「ソルバー」が追加されたことが確認されました。
それでは、早速このソルバーを使用して、最適化していきます。
今回は、販売金額をいくらに設定したら、
利益が一番よくなるのかを、ソルバーを使って算出してみます。
初期設定の確認をしておきます。
F1の60は、販売金額を60円で仮に設定した場合ということです。
F2の仕入金額は、50円で仕入れをしております。
F3の売上個数ですが、ここには、数式が設定されていて、
=INT(F8*F1+F9)
回帰分析で算出してあるF8とF9を使って算出しております。
その結果の96は、今の販売金額だと96個売れる見込みがあるということです。
F4の粗利は、
=F1-F2
という数式が設定されています。販売金額から仕入金額を減算したものです。
F5の利益には、
=F3*F4
売上個数に粗利を掛ければ、利益が算出できます。
それを踏まえて、
ソルバーをクリックします。
ソルバーのパラメーターダイアログボックスが表示されます。
目的セルの設定には、$F$5。F5は利益でしたね。
一番いい利益を算出したいわけですね。
目標値は、最大値でOKですね。
変数セルの変更には、
今回販売金額をいくらに設定したらいいのか？ということですから、$F$1
を設定します。
では、解決ボタンをクリックしましょう。
ソルバーの結果ダイアログボックスが表示されて、
メッセージに『すべての制約条件を満たしています。』と表示されていますので、
OKボタンをクリックします。
販売金額が60から89.31072に変わりましたね。
すなわち、89円にすると利益が最高値になるということが算出できたわけですが、
今回のような販売金額では89.31072という小数点表示ではなくて、
整数で算出したほうがいいので、
先ほどのソルバーを次のように条件を加えてみましょう。
ソルバーのパラメーターダイアログボックスを表示させたら、
「制約条件の対象」に条件を加えますので、追加ボタンをクリックします。
制約条件の追加ダイアログボックスが表示されますので、
セル参照には、$F$1
真ん中のブロックは、intに変更すると、制約条件が整数にかわりますので、
OKボタンをクリックしましょう。
ソルバーのパラメーターダイアログボックスに戻りました。
制約条件の対象に先程の設定が登録されているのを確認したら、
解決ボタンをクリックしましょう。
F1は整数の89と算出されていますね。
ちなみにF1の販売金額を90にしてみると、
利益がダウンしてしまうことがわかりますよね。
このように、ソルバーを使うことで、より効率的に、
適正な数値を見つけることが出来ますので、
回帰分析と合わせて使ってみるといいですね。
