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

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

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

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

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

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

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

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

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

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

➀SUMPRODUCT関数

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

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

この場合、本来は購入金額（価格１X数量１＋価格２X数量２＋・・・）を計算するのに、延々と数式を足す作業が必要となります

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

②＾２

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

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

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

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

・シナリオ１

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

・シナリオ２

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

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

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

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

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

１．制約無しでソルバーを動かしてみる

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

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

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

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

２．制約条件に整数を指定する

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

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

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

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

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

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

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

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

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

の３つになります

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

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

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

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

にほんブログ村