スタートページ> Web教材一覧> オペレーションズリサーチ
Excel、ゴールシーキング
Excelのゴールシーキングは、「データ」→「What-If分析」→「ゴールシーク」を実行して行います。
以下の例のExcelデータは、reidai.xlsxにあります。ダウンロードして開いてください。
ゴールシーキングとは何か? を説明する前に「例1」をやってみましょう。
下左図のように入力すれば、下右図のように、D4(赤)は50になります。D3(青)を200にすれば、D4は100に変化します(当然ですね)。
ここで、「yを60にするにはxをいくつにすればよいか?」という問題を考えます。D3を「=D4*2」とすればよいでしょうが、変化する項目が多いと、書き直すのが面倒ですし、逆関数が作れないこともあります。そこでゴールシークを用います。
「データ」→「What-If分析」→「ゴールシーク」とすると、ゴールシーク画面が表示されるので、次のように入力します。
数式入力セル:目標値を入れるセル、D4(赤)
目標値:yの値、60
変化させるセル:xの入っているセル、D3
「OK」をクリックすると、下左図が表示され、Excel画面は下右図のように、xの値D3(青)が120になります。
「OK」ではExcelの表示はここまま、「キャンセル」では最初の画面になります。
このように、ゴールシーキングとは、数式入力セルの値が目的値になるように、指定したセルの値を計算する機能です。私はそのアルゴリズムを知りませんが、入力した計算式を保存しておき、入力データをいろいろ変えてみて、目的値になるまで繰り返し計算しているのだと思います。
ゴールしキングのように、モデルを作成しておき、「もし~だったら、どうなるか」を机上実験することをWhat-If分析といいます。最適化のアルゴリズムが発見できないとき、What-If分析分析により、実験的に満足解を得ることができますし、試行錯誤を繰り返す間で、「何がどのようにどの程度影響するか」を理解するすることができます。
次のモデルを対象にします(参照:「損益分岐点」)。
問題2-1もし、利益(D17)を現在の100から140に増加させるためには、売上数量(D9)をいくつにしなければならないか?
ゴールシーク画面で次の設定 |
問題2-2もし、仕入単価(D12)が6→8になっても、利益(D17)100を維持するには、売上数量(D9)をいくつにしなければならないか?
仕入単価(D12)を8に変更 |
下の表は「ある設備の取得価額(D25)は1000万円で、対象5年間の毎年の利益(D27:H27)は、200、300、~、200万円である。金利(D24)を0.1(10%)とすれば、5年後は現在価値で134.5万円(H31)の利益になるので、投資すべきである。」ことを示したものです(参照:「現在価値法(DCF法)」)。
ここで、5年後の利益が0となる金利(ROI)を求めましょう。将来の利益が下がるのだから、将来利益の現在価値が小さくなる、すなわち、金利は0.1より大になるはずです。
ゴールシーク画面で次の設定
数式入力セル:5年後の回収(H31)
目標値:0
変化させるセル:金利(D24)