Excel。アドインしてソルバーを使ってみよう！
すごいぞ ソルバー
ソルバーとアドイン
先日ご紹介した、ゴールシーク。結構好評でして、仕事で使える！と仕事で使えるExcel講座などで言っていただいたのですが、
１つでなくて、複数のデータの場合は出来ますか？との質問が出まして…
そりゃ～確かに、セル1つだけじゃねぇ～。
ゴールシークを使って、根性を入れれば求められないこともないですしね。
実は、ソルバーという機能を使うと、このリクエストに答えることが出来るん訳なんです。
このソルバーの基本的な使い方は、非常に簡単で、ゴールシークじゃなくて、
こっちを標準装備にしてくれればいいじゃないの？と思っちゃったりするんですが、このソルバー。アドインしないと、使えないんですね。
それでは、まずは、アドインの方法をご紹介しましょう。
Excelのバージョンは2010です。
まず、前提として、開発タブが表示されているものとして紹介していきます。
開発タブが表示されていないとアドインすることが出来ませんので、注意が必要です。
アドインは、開発タブのアドインをクリックすると、アドインダイアログボックスが表示されてきます。
ソルバーアドインにチェックをつけて、OKボタンをクリックしましょう。
これで、下準備は完了です。
何が変わったのでしょうか？Excelに劇的な変化が起こっているようにみえませんね。
変わった場所を、ご紹介しましょう。
それは、データタブに移動しましょう。
データタブの中に分析というのが登場して、そこにソルバーが登場しましたね。
コレで確認できましたね。
さて、準備は出来ましたので、ソルバーを紹介していきましょう。
下記のような表があります。
売上を10万円に到達するには、
A～S定食をそれぞれ何個売ればいいのか？
というのをソルバーを使うとあっさり、簡単に求めることが出来るんですね。
答えを求めたいところは、C7:C9。
準備としては、D7:D9に価格×数量の数式が、D10には、
総合計を求める数式が設定されています。
もう一つ準備がありまして、条件を加えることが出来ます。
今回は、このような条件を付けたいと思います。
個数なので、整数にする。
S定食は50個以上売る。
達成金額は最低10万円
という条件で求めたいと思います。
本来ならば、S定食は50個以上売るという条件は無いほうがいいと思いますが、
今回は、データの作り方という事で、いれております。
それでは、データタブのソルバーをクリックしましょう。
ソルバーのパラメーターダイアログボックスが表示されます。
目的セルの設定ですが、
これは、目標とする合計金額のセルになりますのでD10を範囲選択しましょう。
目標値は、目標とする合計金額に最も近くなるようにしますので、今回は「最大値」を選びます。
変数セルの変更は、数量にありたいますから、C7:C9を範囲選択しましょう。
制約条件の対象は、先程、準備しました、条件を作っていきましょう。
追加ボタンをクリックしましょう。
最初は、「個数なので、整数にする。」ですので、
セル参照は、C7:C9を範囲選択して、条件にはintを選ぶと制約条件に整数が表示されます。
続けて「S定食は50個以上売る。」の条件を作っていきますので、追加ボタンをクリックしましょう。
セル参照は、S定食の数量のC9を選択して、
条件は>=で制約条件には50個以上売りたいので、50と入力します。
さらに、「達成金額は最低10万円」という条件も追加しますので、追加ボタンをクリックしましょう。
合計金額が最低10万円なので、セル参照はD10を選択します、
条件は最低ということですので<=を選択して、制約条件は、10万と直接入力してもいいのですが、B4に10万という数字が用意してありますので、このセルを使いたいと思います。
このようにセル参照も出来ます。条件は今回3つですので、ここでOKボタンをクリックしましょう。
先程のソルバーのパラメーターダイアログボックスに戻ってきます。
あとは、解決ボタンをクリックしてみましょう。
すると、ソルバーの結果ダイアログボックスが表示されます。
あとは、OKボタンをクリックすると完成ですが、ここで、レポートの解答をクリックすると、
別シートに詳細解答を表示してくれます。
結果は、
A定食13個。B定食16個。S定食68個となりました。
このソルバーを知るとより複雑な条件でも最適値を求めることが可能になりますので、
アドインのソルバー。覚えておいて損はないと思いますね。
なお、条件の変更や追加がある場合は、改めて、データタブのソルバーをクリックすれば、
ソルバーのパラメーターダイアログボックスが表示されます。
まぁ、折角なので、解決方法に、滑らかな非線形のGRG非線形と、線形には、LPシンプレックスと、滑らかでない非線形のエボリューショナリーがありますので、この3つの結果を比べてみましょう。
先程、算出したのが、GRG非線形
では、シンプレックスLPだと、どうなるでしょうか？
最後にエボリューショナリーだと、
ソルバーの結果で、条件が足らないので、そのままでは算出することが出来ませんと表示されました。このエボリューショナリーの場合は、すべての変数に上限と下限が必要になりますので、もっと詳細な条件を考えないといけませんね。
