Excel。回帰分析して、適正値を見つけてみよう。
＜回帰分析:SLOPE関数&INTERCEPT関数&CORREL関数＞
Excelには、四則演算機能だけではなくて、データベース機能、そして、分析機能も搭載されています。今回は、分析機能の中から、回帰分析をつかって、適切値を見つけていきます。
データがあります。今回は、たった、30件のデータです。
「回帰分析」というコトバは馴染みがない人も多いので、なんか難しそう…と感じるかもしれませんが、関数で簡単に算出することができます。
最初に抑えておくのは、「y=ax+b」という回帰式というのがありまして、この公式を使うと回帰分析を行うことができます。
そして、aは『傾き』といい、bは『切片（せっぺん）』といいます。
このaの傾きとbの切片を求めることから行っていきます。
【傾きはSLOPE関数】
傾きを算出する時に使用する関数が「SLOPE関数」です。F1をクリックして、SLOPE関数ダイアログボックスを表示します。
F1の数式は、
=SLOPE(C2:C31,B2:B31)
算出結果は、-1.928938907
【切片はINTERCEPT関数】
続いて、F2に切片を算出していきます。切片を算出するには、INTERCEPT関数を使用します。
F2をクリックして、INTERCEPT関数ダイアログボックスを表示しましょう。
F2の数式は、
=INTERCEPT(C2:C31,B2:B31)
算出結果は、219.6720257
これを回帰式に当てはめてみましょう。
y=-1.928938907×x+219.6720257
ということがわかりました。
さて、回帰式を算出しただけでは、おもしろくありませんので、これを使って適切値を求めていきます。
仮定を立ててみますが、販売金額を100として、仕入金額が50で、売上個数が50の場合、利益を算出すると、利益＝（販売価格－仕入金額）×売上個数、で算出することができます。
利益は2500ですね。
もしこの仮定が正しければ、販売価格を200にしたら、利益は、7500となりますが、こんな単純なわけはありません。
販売価格が倍になったら、普通、販売しにくくなるはず。売上個数は、一般的には、変動するはずです。
そこで、回帰式をつかうことで『適正値』を見つけることができます。
売上個数は、次の計算式で適正個数を求めることができます。
売上個数=-1.928938907×販売金額+219.6720257
F6の数式を次のように設定します。
=INT(F1*F4+F2)
個数なので、INT関数を使って小数点をカットしています。
すると、次のような結果になりました。
現状では、販売価格を200にすると、売上個数は-167ということが判明しました。
ようするに、全く売れないということです。
販売価格を80にしてみましょう。
このようなことが、比較的簡単な関数を使って求めることができました。
ところで、ここまで算出しておいてなんですが、そもそも、販売金額と販売数に関係性はあったのでしょうか？
その関係性を知ることができるのが、相関関係です。
【相関関係はCORREL関数】
F9に、CORREL関数ダイアログボックスを表示します。
F9の数式は、
=CORREL(B2:B31,C2:C31)
算出の結果は、
-0.817378989
この数値が、「1」「-1」に近ければ、関係性が強いということを意味していますので、相関関係を強いことがわかりました。
