本記事の目的
エクセルアドイン「Solver(ソルバー)」の使用例を紹介する。
商品の販売による目標利益から、実際に必要な販売個数を算出する。
要点
エクセルアドイン「ソルバー」は目標値から、実際に必要な数値を逆算することが可能な便利な機能です。
今回は以下の表に対してこの「ソルバー」の機能を使用し、目標利益の達成のために必要な商品の販売個数を求めていきます。
エクセルは初期状態ではソルバー機能が表示されていないため、設定する必要があります。
設定がまだの方は以下の記事を参考に、ソルバーを有効にしてください。
それでは解説していきます。
ソルバーを用いた目標利益の算出
利益の計算式の入力
まず最初に表中の”利益”、”利益合計”の欄を埋めていきましょう。
入力する内容は以下の通りです。
商品B、商品Cの利益の欄にも同様に式を入力してください。商品Aの利益が入力されたセルの右下を、クリックしながら下にドラッグすることで、商品B、Cの利益のセルに式を入れられます。
次に利益合計の右隣のセルには次のように入力してください。
これで実販売数のセルに数値が入力されることで、各商品の利益と利益合計が算出されるようになりました。
ではソルバーを操作していきましょう。
目標セルの設定
まずはエクセル上部のデータをクリックしてソルバーを表示しましょう。
上部の右端にソルバーがありますので、これをクリックします。
ソルバーをクリックすると以下のような画面が表示されるはずです。
”目標セルの設定”の欄の右にある矢印をクリックし、利益合計のセルを選択・入力します。
目標値の設定
次に求める目標値を設定していきます。
目標値の欄には最大値、最小値、指定値の三項目がありますが、今回は特定の値を求めたいため、指定値を選択します。
指定値を選択後、隣の入力欄に、今回の目標利益である”15,000″を入力しましょう。
変数セルの変更の設定
次に変数セルを設定します。この変数セルとは、実際に計算で値が変動するセルのことを指します。
今回の場合、利益を算出するために”実販売数”のセルの値が変動するため、商品A～Cの実販売数のセルを選択していきます。
実際の入力内容は以下の通りです。
制約条件の対象の設定
では次に、制約条件の対象の設定を行っていきましょう。
ここでは、特定のセルの値の条件を設定することになります。
今回の例では、商品Aは15個以上販売、商品Bは20個以上販売、商品Cは12個以上販売という条件があるため、これを指定していきます。
まずは”制約条件の対象”の右側にある”追加”をクリックしましょう。
すると次のような”制約条件の追加”という項目が表示されます。これの”セル参照”、”制約条件”に値を入力していきましょう。
まずは”セルの参照”の欄に商品Aの実販売数のセルを入力します。次に制約条件ですが、今回は最低販売数が15ですので、”制約条件”のセルに直接”15″を入力してください。
入力が完了しましたら、”追加”ボタンをクリックしてください。これによって条件が追加されます。
このとき、”制約条件”のセルに最低販売数のセルを直接選択するとエラーが発生しますので、注意してください。
”制約条件の変更”には数式のみを入れることが可能ですので、数値が入ったセルは選択できません。
実際に数値が入ったセルを選択し、”追加”をクリックすると以下のようなエラーが発生します。
では、商品B、商品Cについても同様に制約条件を追加していきましょう。
各商品について条件を付け終わりましたら、以下のような表示となります。
上記のような表示となりましたら、”ソルバーのパラメーター”の右下にある”解決”をクリックしましょう。
ソルバーの結果
“解決”をクリックしましたら、以下のような表示が出ます。そのまま”OK”をクリックしましょう。
OKをクリックすることで以下のように結果が表示されます。
各商品の必要な実販売数の一例が表示されましたね。
しかし、表示された数字は少数であり、整数の値が欲しい場合には不適です。
では制約条件に更に条件を追加し、結果を整数にしていきましょう。
ソルバーの結果の整数化
先ほどと同様に、”制約条件の対象”の右側にある”追加”をクリックしましょう。
そして表示される”制約条件の追加”の左側(セルの参照)は前回と同様に商品A、B、Cの実販売数のセルを選択します。
真ん中の欄は先ほどとは異なり、真ん中のプルダウン(↓)をクリックし”int”を選択します。
“int”を選択すると自動で右側(制約条件)に”整数”と入力されるはずです。
この状態になりましたら、”追加”をクリックしてください。これを商品B、商品Cの実販売数のセルにも適用します。
完了しましたら以下のような表示となります。
再度ソルバーを実行することで、表示される項目が整数になるはずです。
もし解が中々見つからない場合、”制約のない変数を非負数にする”のチェックボックスを外す、”解決方法の選択”を別の方法にすることで解決されると思いますので、試してみてください。
まとめ
本記事ではエクセルアドインの「ソルバー」の活用方法について紹介してきました。ソルバーは初めて使用する場合は戸惑うことが多いかもしれませんが、使いこなせれば非常に有用なツールですので、ぜひ使えるようになっていきましょう。
