ソルバーによるシミュレーション3~実践編・整数設定~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はソルバーの基本的な操作を行いました

今回はもう少し実践的な方法に取り組んでみましょう!。

 今回は、ソルバーで解決した値が実際のビジネスで使用できるように、整数にて「最適な解」を出力する方法を解説します!

 実は、実践的にソルバーを使うにあたっては「符号」の問題もクリアする必要があります

*********** ブログ管理人・ガッツの期間限定レッスン ***********

売りは技術でなく、経験です!

時間は30分から!あなたの都合のいい時間に、エクセルのお困りごとや悩みに対するヒントを提供します!!平日の昼間でも可です **************************************************************

この最重要ポイントについては、後述する事前準備・解説の②の中で行います

今回は下の図の課題に取り組みます

・購入単価が違う商品A~Cを組み合わせて購入します

・購入に当たっては、購入金額が予算を超えないようにします

 では本題に入る前にソルバーを実戦的に活用する為の必要知識として、プラスで下の①②の2点を解説します

➀SUMPRODUCT関数

この関数を使うと、本来は長くて複雑な数式の設定が必要なところを2つの引数を指定するだけで済ませることができます

下の画像の右側、購入金額の欄には、左表の商品A、B、Cそれぞれの【価格x数量】の合計を数式で表示する必要があります

この場合、本来は購入金額(価格1X数量1+価格2X数量2+・・・)を計算するのに、延々と数式を足す作業が必要となります

今回はSUMPRODUCT関数を使用して、図の左の価格欄と数量欄、それぞれの範囲だけを指定すれば済むようにします

②^2

これは2乗するという意味です

今回の課題では、下の図のG4セルに【差額:予算-購入金額】の「2乗」を入力します

何故、2乗をする必要があるかという点について以下、実際の例(シナリオ1と2)で解説を行って行きます

今回の課題では差額を「目的」として設定し、差額を最小化する値範囲を設定します

・シナリオ1

*予算金額:5,000,000➡購入金額:5,000,0000➡差額:0

・シナリオ2

*予算金額:5,000,000➡購入金額:5,000,0002➡差額:-2

この場合、シナリオ1が本来は正解です

ただ、差額の符号をマイナスでも取りうることができると、シナリオ2の方が差額が小さいので、シナリオ2の差額-2が正解になってしまいます

ですので、-1x-1=1、と-2x-2=4という風に2乗することにより、「目的値を絶対値」に変換して計算できるようにしておきます

では準備の2点を説明したところで本題に入ります

1.制約無しでソルバーを動かしてみる

まずは、前述した数式を2つセットしましょう

②の(予算ー購入金額)の2乗が目的の値になります。これを最小値にする商品AからCの購入数量の組み合わせを求めます

実際のビジネスでは、数量を少数点にした値では購入しませんよね?

実際には役に立たない結果が出てしまっています

2.制約条件に整数を指定する

整数の指定の仕方はとても簡単です

まず制約条件の追加を押してください

実は次の画面でINTという指定をするだけで整数指定ができます

では下の画像のように整数指定できているのを確認できたら解決ボタンを押しましょう!

結果はきちんと整数になっています

今回はこれで終了です。今回のポイントを復習すると

Ⅰ.SUMPRODUCT関数で数式の足し算を省力化する

Ⅱ.^2を使い差額の絶対値を目的の計算に使用できるようにする

Ⅲ.制約条件画面にて、INTの指定を通じて、ソルバーの結果の整数指定を行う

の3つになります

最後に、ソルバーを使う上での注意点を付け加えておきますが、

”ソルバーの値はあくまでシミュレーションであり、ビジネスとして絶対的に正しいわけではありません”

この点は、予め理解した上でソルバーを活用していきましょう!

ではまた次回、更に実践的な内容に取り組みます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です