MOS受験にむけて番外編6(What-If分析：ソルバー1）

今回からソルバーについてふれます。
MOSの受験では、FOM出版の問題集ではExpertの範囲に入ってますが、日経BP出版の問題集にはありません。
微妙ですが、高度な機能であり、是非目をとおされることをすすめます。

ひとつの変数を含む数式の解を求めるにはゴールシークが便利ですが、複数の変数があり、より複雑な数式の解と求める場合にソルバーを使います。
この機能はアドインとして提供されていますので、まずアドインを有効にします。

①[ファイル]タブ→[オプション]クリック
②開く[Excelのオプション]画面で左側の一覧から[アドイン]選択
③[管理]の▼から[Excelアドイン]を選択し、[設定]クリック
④[アドイン]画面が開くので、[ソルバーアドイン]にチェックを入れ[OK]クリック
[データ]タブの[分析]グループの[ソルバー]として追加されます





実際に簡単な問題をやってみましょう。



この表で
目的：予算総額3,000,000円を使ってできるだけおおくの商品を購入する
制約条件:購入総額が予算額以下
各商品は最低でも10以上仕入れる
各商品の注文数の差が５以下とする
各商品の注文数は整数

なお、セル[D3:D5]には「単価*仕入数]セル[D7]にはセル[C3:C5]を合計するSUM関数、セル[D10]にはセル[D3:D5]を合計するSUM関数、セル[D13]には「MAX(C3:C5)-MIN(C3：C5)」がそれぞれ入力されています。

まず、注文数は整数という条件ぬきでやってみます。

①[データ]タブの[ソルバー]クリック
②[ソルバーのパラメーター]ダイアログボックスが開くので
　「目的セルの設定」にはセル[D7]をクリックして「$D$7」と入力、[目標値]として[最大値]選択、
　[変数セルの変更]には「$C$3:$C$5」入力

注）[目的セルの設定]には、目的の値に到達させたい値が含まれるセルを指定セルで、数式が入力されている必要があります。この例では「D7]
　　[目標値]には、目的セルの値を、「最大値」、「最小値」、「指定値」の中から選択、この例では「最大値」、「指定値」を選択する場合は数字を入　　力する必要があります。
　　[変数セルの変更]には、目的セルの値が目標値になるために、値を変化させるセルまたはセル範囲を指定します。この例では「$C$3:$C$5」
　　
③つづいて、[追加]クリックすると制約条件の追加]ダイアログボックスが開くので、購入総額が予算総額以下の条件を入力し、[追加]クリック、
以下制限条件を入力し、すべて入力したら[OK]クリック
④すべての条件が入力されている状態で[解決]クリックすると、[ソルバーの結果]ダイアログボックスが開き、元の仕入れ計算表の内容がかわります。












購入金額がちょうど予算総額に等しくなっており、すべての条件を満足する結果が得られたことがわかります。


では、各商品数は整数という条件を追加してみましょう。

①[データ]タブの[ソルバー]クリック
②[ソルバーのパラメーター]ダイアログボックスが開くので、[追加]をクリック
③[制約条件の追加]ダイアログボックスが開くので、セル範囲[C3:C5]が整数、（整数は「int」と入力）と入力し[OK]クリック




④[ソルバーのパラメータ]画面で[解決]クリックすると、[ソルバーの結果]ダイアログボックスが開き、元の仕入れ計算表の内容がかわります。

結果、すべての仕入れ数が整数になった分、購入金額が減少しましたが、やはりすべての条件を満足する結果がえられました。

　



さらに今回はこの画面でレポート覧の「解答」をクリックします。
そうすると、別シートに「解答レポート」という名で解答が示されます




このような問題が実用的なのか疑問ですが、仕入数を最大にしたいのであれば、当然単価の安いものを多く購入することになりますから、ある程度予想できた解答ではありますね。

この機能は実際には全く使ったことがありませんが、線形計画法などでも応用できるのかもしれません。
次回に別の問題を例示したいとおもいます。