Excel。分析ツールのソルバーで回帰直線を算出してみよう
＜分析ツールのソルバー＞
ソルバーという機能が、Excelのアドインに用意されています。このソルバーを使うと、例えば目標を達成するための値を逆算して算出することができます。
次のデータをつかって、ソルバーで傾きと切片を算出して、回帰直線を求めていきます。
最初にソルバーがアドインされていないと使えないので、アドインから確認していきましょう。
データタブの分析にソルバーが表示されていなければ、アドインされていませんので、ファイルタブのオプションをクリックして、Excelのオプションダイアログボックスを表示します。
アドインのソルバーアドインを選択して設定ボタンをクリックします。
アドインダイアログボックスが表示されますので、ソルバーアドインにチェックマークをいれOKボタンをクリックします。
データタブの分析にソルバーが表示されました。
ソルバーで最小二乗法を使用して、近似直線を算出していきます。
近似直線をy=ax+bとして、ソルバーで傾きのaと切片のbを算出していきます。
最終的に、最小二乗法で残差平方和を算出する必要がありますので、傾きと切片を共に「1」として、E列の売上予測を算出していきます。
E2には、=$H$1*C2+$H$2
と設定します。
この式は、y=ax+b と同意です。
オートフィルで数式をコピーしておきます。
店舗ごとの売上予測が算出できましたので、最小二乗法の合計＝残差平方和を算出して、その値をソルバーで使用していきます。
ただ、単純に、売上予測を合算するわけではないので、ここはSUM関数ではなくて、SUMXMY2関数を使って算出していきます。
E12の数式は、
=SUMXMY2(D2:D11,E2:E11)
E12の残差平方和をクリックして、データタブのソルバーをクリックします。
ソルバーのパラメータダイアログボックスが表示されます。
目的セルの設定には、$E$12
最小二乗法を使用するので、目標値は「最小値」にします。
変数セルの変更には、傾きと切片なので、$H$1:$H$2
各係数がマイナスになることを想定して、「制約のない変数を非負数にする」のチェックマークを外します。
設定が終わったら、解決ボタンをクリックします。
ソルバーの結果ダイアログボックスが表示されますので、OKボタンをクリックします。
これで、傾きと切片を算出することができたので、現状において新店舗を作る場合、この傾きと切片をつかうことで、売上を予測することが可能になるわけです。
例えば、フロアー面積が150の新店舗をOPENしたい場合、
=H1*150+H2
とすれば、359.13と算出されるので、359の売上が見込めると予測できるといったわけです。
経験則もいいですが、数字から算出できるものも資料として検討材料に加えるのもいいかもしれませんね。
