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

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

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

今回は、ソルバーで解決した値が実際に使用できるように整数にする方法を解説します!

本題に入る前にソルバーを実戦的に活用する為の準備として2点解説します。

➀SUMPRODUCT関数

この関数を使うと、下の画像で購入金額(価格1X数量1+価格2X数量2+・・・)を計算するのに、延々と数式を足す作業が必要となるところを、引数として2つの範囲を指定するだけで済ませることができます

②^2

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

差額が+もしくはーの両方を取りうる時、且つ、差額を最小化したい時に使います。上の画像の場合には、単に引き算(予算ー購入金額)にして、最小値を求めようとするとーが延々と大きくなり、ソルバーの計算が収束しなくなります。

実際の例で解説します

例えば、仮に予算金額が5,000,000で設定されている時に購入金額が5,000,0001とした場合には単なる差額だと-1となります。更に購入金額を5,000,0002とすると単なる差額はー2となります。当然、-2の方がー1より小さいので、最小値を求めたい場合には、差額が絶対値として大きい方が計算結果として適しているという望ましくない結果になってしまいます

ですので、-1x-1=1、と-2x-2=4という風に2乗することにより絶対値にして計算できるようにしておくのです

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

今回の題材は予算にあった購入数量を求めるものです。詳細は以下になります

・価格の違う商品が3つあり

・設定した予算に近くなるように3つの商品の購入数を決める

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

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

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

実際にソルバーを動かすと購入数量が整数でないので、実際には役に立たない結果が出てしまっています

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

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

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

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

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

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

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

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

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

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

の3つになります

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

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

この点はご注意頂きたいと思います

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

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


にほんブログ村

コメントを残す

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