患者中心の保健医療を支える
処方情報分析のリーディングカンパニー

 03-3239-6840

Excelアドイン「ソルバー」を使って売上予測をしてみよう!

time 2017/11/06

※解説記事内の画像はExcel 2010のものです。

こんにちは、二回目の投稿となります甘党です。

最近はセ○ン○レブンのマカダミアクッキーをよく食べています。甘すぎずサクサクしておいしいので皆さんも良かったら食べてみてはいかがでしょうか?

さて、今回はExcelにあるソルバーというツールを使ってみたいと思います。
ソルバーは与えられた関数を制約条件のもと、複数の値を変化させながら最適な解を算出するツールとなっています。
あまりなじみのないツールですので、今回は大まかな使い方を知って頂けたらと思います。

ソルバーをアドインしてみよう

ではさっそく、ソルバーを使う準備をしていきましょう。
ソルバーはデフォルトでは表示されていませんので、まずは設定を変更して表示させましょう。

「ファイル」メニューより「オプション」を選択してください。
ソルバー30
その次に「アドイン」の画面下にある「設定」を押してください。
ソルバー1
すると現在有効になっているアドインとそうでないアドインが表示されますので「ソルバー アドイン」をチェックして有効にしましょう。
ソルバー2
画面上に表示されてあるリボンの「データ」の右側に「ソルバー」が表示されていれば準備OKです。
ソルバー3

では、実際に簡単な例を交えながらソルバーを使ってみましょう。

実際にソルバーを使ってみよう

まずは、今回使う表を見てみましょう。
ソルバー20

1月と2月の数量が確定していると仮定して、現状の売上額としてセルG12に36,200円と入っています。

この表の各セルには関数を入れており、数量を入力するだけで売上目標が自動的に計算されるようになっております。
ソルバー21
今回は、このような条件を設定しました。

  • 1月と2月の数量は確定済み
  • A薬の薬価は40円、B薬の薬価は60円
  • 3月にA薬・B薬それぞれ200個以上売る
  • 売上は数量×薬価で計算

上記の条件のもと、売上80,000円を達成するためには3月・4月の数量をどう目標設定すれば良いか、ソルバーを使って計算してみます。

まずは、「データ」リボンの「ソルバー」ボタンを押して、「ソルバーのパラメーター」ダイアログを開きます。
ソルバー22
この中に今回設定した条件を入れていきましょう。

① 目的セルの設定
結果を出したいセルである$G$12を入れます。
変数セルに関わる関数を入れる点に注意してください。今回の例では、セルG12にはSUM(G8:G9)が入力されています。

② 目標値
指定値をクリックし、今回の目標値である80000と入れます。

③ 変数セルの変更
ソルバーを使って変動させたいセルの範囲を指定します。今回の表ですと$C$4:$F$5の範囲になります。

④ 制約条件の対象
右側の[追加]ボタンを押して制約条件を追加していきます。
ソルバー6
左端のセル参照には、制約条件を設定したいセルを選択します。
中央にあるドロップリストから比較演算子を設定できます。
右端の制約条件から設定したい値などの制約条件を設定できます。

今回は1月と2月の数量が確定しておりますので、
1月のA薬数量260個 →“$C$4 = 260”と入力して[OK]または[追加]ボタンで登録しましょう。
同様に、
$C$5 = 140
$D$4 = 180
$D$5 = 170
と条件を追加していきます。

続いて、3月にはA薬・B薬それぞれ200個以上売ることを予定しているので、
$E$4:$E$5 >= 200
と条件を追加します。

また、今回は数量を変化させていくので、変数セルの変更範囲を整数型に指定します。
$C$4:$F$5 = 整数
整数型の指定は上の場合と少し違い、セル参照を指定した後、中央にあるドロップリストから "int" を選んで [OK] ボタンを押してください。
ソルバー23
ソルバー24
条件の設定が完了しましたら、 [解決] ボタンを押して結果を表示させます。
ソルバー12
計算結果を反映させたくない場合は「計算前の値に戻す」にチェックしてください。
再度計算を行いたい場合は「ソルバー パラメーターのダイアログに戻る」にチェックをつけてください。
※一度ソルバーの解の保持をしてしまうと計算前の状態に戻せないため、注意して実行してください(バックアップを取っておく・名前を付けて保存する等)。
「ソルバーの解の保持」にチェックし、OKボタンを押すと完了です。
ソルバー25
目標値80,000円など条件を満たした、A薬・B薬の3月・4月の数量が導き出されました。

最後に

今回はソルバーの説明をしてきましたが、いかがでしたか?

4月の数量や合計の数量など他にも様々な制約条件を付けて計算してみたら精度の高い予測が出来るかもしれませんね。反対に、大まかな制約条件だけで行っても予測にある程度の目星がつけられるので、大幅なズレをなくせるのではないかと思います。

ぜひ、色々と試してみてソルバーをマスターしてください。

タグ

このブログについて

このブログ(JMIRI Office部)は、製薬企業のマーケティング部門の方やアナリティクス/レポーティングを担当している方に向け、Excel / PowerPointなどのOfficeソフトの使い方、分析の考え方、グラフの使い方、美しい資料の作り方、効率的な作業の進め方など、仕事に役立つ様々な情報を少しゆるめに発信していきます。

Author:医療情報総合研究所(JMIRI)について

医療情報総合研究所 JMIRIは、まだビックデータという言葉が無いころに日本で初めて処方情報データベースの運用・分析を開始した、処方情報分析のリーディングカンパニー。これまでに累計数十万ページ規模の分析レポート/グラフを製薬企業に向けて作成/提供してきました。

[詳細]


お問い合わせ

arrowup

mail