Excel。事務職のデータ分析29。近似曲線（回帰直線）は表示できるけど、どうしたらいい？
＜回帰分析：CORREL・SLOPE・INTERCEPT関数＞
売上データから様々な資料を作ることができるのも、
Excelが優れたアプリケーションだということを証明しているわけですが、
次の売上表からでも、様々な資料を作ることができます。
“見える化”ということから、
B1:C13を範囲選択して散布図を使ってグラフ化します。
すると、このような散布図ができます。
やはり、近似曲線を表示しないと役に立ちませんので、表示しましょう。
MOSの試験範囲ということもあって
この近似曲線を表示することはできるけど、
「
いったい、この線は、なんなのか？」
というケースもあるようなので、
今回は、この近似曲線について、スポットを当てていくことにします。
【相関係数はCORREL関数】
表の売上高と来店客数の関係表したのが散布図と、
近似曲線なので、まずは、相関係数を算出するところから、
スタートしてきましょう。
今回使用する関数で、xとかyという範囲で聞かれてきますので、
売上高がX（x軸）で、来店客数がY（y軸）で考えてきます。
相関関係を算出するには、CORREL関数で算出することができます。
また、PEARSON関数でも算出することができますが、
今回は、CORREL関数を使っていきます。
F1をクリックして、CORREL関数ダイアログボックスを表示します。
配列1には、B2:B13
配列2には、C2:C13
として、OKボタンをクリックします。
F1の数式は、
=CORREL(B2:B13,C2:C13)
となっています。
この相関関係は、どのような意味を持つ数値なのかというと、
この値が、”1”に近いほど関係が強く、”0”に近いほど、
関係が弱いということがわかります。
今回は、”1”に近いので、
売上高と来店客数の関係は強いということがわかりました。
【近似曲線に数式を表示する】
散布図に、近似曲線を表示するだけでは、
もったいないので、数式も表示させましょう。
近似曲線をダブルクリックして、
右側に近似曲線の書式設定作業ウィンドウが表示されます。
その中にある、「グラフに数式を表示する」をオンにすると、
グラフに数式が表示されます。
y = 0.4108x + 30.94
これは、y=ax+bという形の数式で表すことができます。
数式は表示できたけど、この数式は何の意味なのかがわからないと、
おもしろくないので、確認していきましょう。
【傾きと切片（せっぺん）】
近似曲線は統計学的には「回帰直線（曲線）」といいまして、
y=ax+bの数式は、「
回帰式」ともいいます。
この数式を使って、『回帰分析』を行うことができるのですが、
この数式のaにあたるのが、「
傾き」。
bにあたるのが「
切片（せっぺん）」といいます。
この「傾き」はSLOPE関数、
「切片」はINTERCEPT関数で簡単に求めることができます。
F3に、SLOPE関数ダイアログボックスを表示しましょう。
F3の数式は、
=SLOPE(C2:C13,B2:B13)
です。
F4に、INTERCEPT関数ダイアログボックスを表示して、切片を求めます
F4の数式は、
=INTERCEPT(C2:C13,B2:B13)
ですね。
結果はグラフに表示された数式と同じになっているのがわかります。
さて、傾きや切片の算出する関数や、回帰式は表示できましたが、
そもそも、この数式で何ができるのかというと、
予測することができるわけです。
例えば、売上高を1500にするには、
来店客数がどのぐらい必要なのか？ということを求めることができます。
F7に数式を作って算出てみましょう。
=F3*E7+F4
すると、売上高が1500ならば、
来店客数は647.0711と算出することができました。
相関関係が1に近いので、関係性が強いことから、
648人は来客してもらわないと難しいことがわかりました。
しかし、実のところ、傾きや切片を算出してきましたが、
FORECAST関数という関数があって、
この関数を使うと簡単に予測値を求めることができちゃいます。
F8をクリックして、FORECAST関数ダイアログボックスを表示しましょう。
F8の数式は、
=FORECAST(E8,C2:C13,B2:B13)
となっています。では、確認してみましょう。
F7の数式と同じ結果になりましたね。
関数を使うと簡単に算出できるものも多くありますが、
意味を知るためには、
一度バラした数式を作ってみると理解しやすいケースもあります。
このように、色々な数値から様々なことが見えてきたり、
使えたりしますので、アレコレ想像して資料を作っていくと、
日頃の資料にプラスして、面白いものが見えてくるかもしれませんね。
