|
今回は、前回の続きと【ソルバー】というのに挑戦しましょうネ!! ｑ(^◎＾＊)ｐ
●前回の宿題
それでは、前回の宿題【ゴールシーク】の宿題をしてみましょう!!
問題はこうでしたね!!
【宿題】
データテーブルでイチロー「４割打者」への道
ありましたよね。これをゴールシークで求めてみてください。
【こたえ】
では、Let's ｔｒｙ しましょう!!
① ゴールシークの出し方はもうわかるよね!!
② 【数式入力セル】には
セル Ｂ6 をクリックします
セルには次の式が入力されています =D6/C6
【目標値】は４割ですので、0.4 を入力します
変化させるセルは残り２０試合の安打ですので
セル D5 （現在は26となっています）
セル D6 には 現在安打＋残り試合の安打 ですよ!!
↑の図のようにしましょう!!
そうすると
という結果になりました。
つまり残りの打数７０本で３１本以上打てば、４割を超えることが
できるということなんだね!!
みなさ～ん、できましたか!! やれば、以外と簡単だね ｑ(^◎＾＊)ｐ
●ソルバー
★ アドインの追加をしよう!!
【アドイン】という言葉、ご存知ですか？
これは、エクセル９７の機能を拡張するためのプログラムです。
ソルバーは、アドインを使って追加セットアップするため
アドインの登録が出来ていないひとは、エクセル９７のセットアップで
コンポーネントのアドインを追加してくださいね!!
方法は、アドインをチェック、それからオプションの変更で
必要なアドインチェック、【ＯＫ】ボタンを押します。
注意!!必要なアドインを追加したからといって、
すぐには利用できませんよ!!
★ アドインの登録をしよう!!
アドインの登録は、↑の追加をしておくと
メニューの【ツール】→【アドイン】
ここからチェックして選択、【ＯＫ】ボタンを押せば
やっとここで使用できるようになるんだね!! ヽ(´▽｀)ノ
ここで、ソルバーをチェックして、使えるようにしよう!!
ツールの中に【ソルバー】があれば、ＯＫ!! だね ｑ(^◎＾＊)ｐ
●ヘルプの参照!!
トピックの検索で【キーワード】で「ソルバー」と入力してみましょう!!
ソルバーだけでもかなり量が表示されていますよね!!
これをみたあなた、これじゃ、ひいちゃいますな～
初心者の方ならなおさらですよね ヽ(∇⌒ヽ)
これを、全部検証するとなると、小生もひいちゃいますぅ （笑）
でも、エクセル97にはサンプルワークシートなるものが
ついているのがわかったのじゃわい ヽ(´▽｀)ノ
\Microsoft Office\Office\Examples\Solver\Solvsamp.xls
これを、大いに利用して、ソルバーなるものを習得していこうじゃ
あ～りませんか ｑ(^◎＾＊)ｐ
★ソルバーって？
ソルバー、って先ほどから言ってるんだけど、
なんの意味かよくわからないよね、
ここでおおまかな説明をしておきますネ
ソルバーとは、
ゴールバーが、変数が１つに対して
ソルバーは、決められた条件の範囲内で、
複数のセルの値を変化させて、
条件にあった最適値を求めてくれるツール。 |
●実践してみよう!!
それでは、上のサンプルを使って、実践してみよう!!ｑ(^◎＾＊)ｐ
今回は【概要】シートからはじめましょう。
ここでは、典型的なマーケティング モデルの例としてあげられいますね。
内容を見てみますと、
【広告費】の費用と【季節指標】により【売上数】（予想売上数）が決定されています。
Q１の【売上数】の式を見ますと、
=35*B3*(B11+3000)^0.5
という設定になっていることがわかります。
広告費の年間の費用が決まっていることが条件になっています。
四半期の季節ごとにいくら広告費をかければ【純益】として儲かるか、
利益をあげられるか、これをソルバーを使って調べようということです。
注意!!
=35*B3*(B11+3000)^0.5
何故、こういう式になるかとここで考えていけません!!
こういのは、いままでの過去のデータから出た、経験式と思えばいいのですよ!!
●【目標値を最適にする設定値を求める】
６８行目、これを、試してみましょう!!
重要
ソルバーを使って解ける問題の 1 つは、
いくつかの設定値を変化させて特定の目標値の最大値を求める
ということです。
変化させるセルと目標値を表すセルは数式によって
関連付けられている必要があります。 |
69～71行目にも書いてありますが、
設定値と目的値とはかならず関連された式がなければ、
答えが求められないということなんです。
それでは、実際にトライしましょう!!
メニューからソルバーを選びましょう!!
ソルバーをクリックしましょう!!
次に↓図のように【ソルバー：パラメータ設定】のダイアログボックスが表示されます。
【問題】は第１半期の純益を最大にするには、広告費をいくらにすれいいか
ということですので
･目的セルにセルB15を選択します。
･変化させるセルにセルB11を設定します。
関連式を簡単に説明すると、
売上数=35*B3*(B11+3000)^0.5
純益=粗利-総経費
粗利は売上数と売上高、総原価費とが関連式で成立しています
総経費は販促費＋広告費＋社内経費という関連式が成り立っています
結果はこういう具合にでてきました。
それぞれの値が変化して結果がセルに反映されています。
セルの値をその答えを代入するなら、そのまま【ＯＫ】ボタンを
元の値に戻すなら、元の値に戻すをチェックして【ＯＫ】してください。
広告費が変化してので、それに
ともなって関連したすべての数値が変化しましたね、
うまくいきましたか？
ハイ、では次、行ってみよう!!ｑ(^◎＾＊)ｐ
●ソルバー オプションのリセット（89行目）
[ｿﾙﾊﾞｰ ﾊﾟﾗﾒｰﾀ設定] ダイアログ ボックスのオプション設定を元に戻して新しい問題を設定するには、
[ﾘｾｯﾄ] を選択します。
一度ソルバーを実行すると、前の状態を覚えているようです。
新しくする場合は、リセットをかけることにより初期状態にもどります。
●複数の設定値を変化させて目標値の最適解を求める（９４行目）
先ほどの例題はゴールシークと同じですよね!!一つの変数による変化から
こたえを見つけました。
次は複数の変数で考えてみましょう!!
広告費の四半期全部に対して純益が最大になるようなこたえを求めます。
今度は、目的セルを純益の合計欄のF15に
そして変化させるセルをB11～E:11を選択します。
↓の図のとおりですね!!
そうすると、こたえは次のとおりになります
サンプルのこたえは合計欄の広告費の合計が89,706ですが
実際に行ったこたえは89,710となります（ただの、ミスプりだと思いますが）
まぁ、そんなことはいいのですが、
とりあえず、広告費が89,710円で純益が79,706の純益最大利益が得ること
ができるというわけですね。
ここで非線形とか線形の言葉がでていますが
売上数=35*B3*(B11+3000)^0.5という式が
グラフ考えると指数の式ですので、非線形の形のグラフになります。
たとえば１次式となるような式ですと線形になるわけですよね。
そういう具合に理解しておいてください。
●制約条件の追加
↑のこたえを見ると、すべて広告費を上げれば、準益があがることは
わかりますよね、でも、ここでは、来年の予算を考えて、あくまでも
予想ですので、出費を抑えて、つまり広告費を抑えて利益をあげることを
提案しています。
そこで、まず１年間の広告費を年間４万円で、純益を最大にするには、
てなことを考えました。
つまり、これが制約条件になるわけですね!!
①広告費を四半期すべて１００００円にもどしておきましょう。
②[ｿﾙﾊﾞｰ ﾊﾟﾗﾒｰﾀ設定] ダイアログ ボックスの制約条件の【追加】
をクリックします。
セル参照はF11を選択し 40000円以下ということで
<= 40000 とします。
これで【ＯＫ】ボタンを押せばはじめのダイアログにもどります。
【実行】ボタンをクリックしてください。
結果は全て10000円のときの利益は69962円
そして、ソルバーによって、季節指標にあわせた
最適なこたえを求めた最大純益は7１447円、
限られた予算でも、季節指標の変化に合わせて
広告費を投入すれば、利益があがるということですねぇ
●制約条件の変更
ここの説明では、広告費の年間40000を50000円にしたら
どういった純益があがるかを示しています。
その方法は、
①制約条件のリストボックスの条件を選択状態にしする
（選択状態にしないと 【変更できる制約条件はありません】というメッセージが
出て、次のダイアログボックスがでてこないのだな!!）
②変更ボタンをクリックする。
③【変更】ボタンを押す
40000を50000に変更 【ＯＫ】ボタンをクリック
④[ｿﾙﾊﾞｰ ﾊﾟﾗﾒｰﾀ設定] ダイアログ ボックスで【実行】をクリック
⑤結果は↓に出ました。
●分析結果
|
初期 |
年間制約 |
年間制約 |
制約無 |
| 年間広告費 |
40000 |
40000 |
50000 |
89710 |
| 純益 |
69962 |
71447 |
74817 |
79706 |
純益/初期純益
の割合 |
|
102% |
107% |
114% |
広告費/初期広
告費の割合 |
|
100% |
125% |
224% |
分析結果から、広告費を２５％あげることにより５％の純益がでるが
広告費を２倍以上あげても１２％しか純益をあげることができない
ということがわかるよね!!
つまり、これから計画で収益をあげる判断の目安として
２５％の広告費の投資はかなり有効であるよね、
が、倍以上の広告費を投入してその収益を得るという方法は、
あまり効果的でないと判断できるんじゃないかな？
あなたが実業家や経営者ならどうしますかねぇ？
●問題モデルの保存
今回の最後です。
[ｿﾙﾊﾞｰ ﾊﾟﾗﾒｰﾀ設定] ダイアログ ボックスのオプションをクリックすると
次のダイアログボックス
モデルの保存の際には、保存に必要なセルがいります。
必要なセル数＝制約条件の数＋３（行数）
ワークシートの空きセルに合わせて保存場所を選択してから
【モデルの保存】をクリックしましょう!!
そうすると↑のように自動的にセル範囲を
選択してくれますので、その場所でＯＫなら、
【ＯＫ】ボタンをクリックしよう!!
モデルの読込は、その逆をすればＯＫです ｑ(^◎＾＊)ｐ
今回は、結構長くて辛かったかな!!でも、ここまできた人は
かなりソルバーっていうのがわかってくれたと思うんだけど？
難しかったかな？
サンプルは他にも生産管理、物流、人事、財務、投資、エンジニアリング
があります。興味のある方は、是非、それもトライしてみてくださいね。
今回は長かったので宿題はなしだよ!! (∩_∩)ゞ
|
中級編第６彈