Excel。散布図に表示された一次方程式は何？回帰分析？で予測できるんだって
＜SLOPE関数・INTERCEPT関数・RSQ関数＞
二つのデータの間をわかりやすくするために、「散布図」をつくってみたので、ついでに近似直線を描いてみました。
近似直線（近似曲線）を追加するには、散布図を挿入後、グラフのデザインタブの「グラフ要素を追加」にある「近似曲線」から、今回は「線形」をクリックしました。
ただし、近似直線を追加しただけでは、もったいないので、とある計算式を表示していきます。
近似直線をクリックして、書式タブの「選択対象の書式設定」をクリックします。
近似曲線の書式設定作業ウィンドウが表示されます。
グラフに数式を表示するとグラフにR-2乗値を表示するにチェックマークを設定します。
チェックを入れると、散布図に数式が表示されます。
なお、フォントサイズが小さいので大きくしております。
このy = 8.6481x + 104.18は、予測計算式であり、R² = 0.8722は重決定係数と呼ばれています。
ちょっと難しい式のようにみえますが、予測を立てることができる計算式が表示されるわけです。
y = 8.6481x + 104.18 を説明すると、
yは、「目的変数」で、予測したい数値を指します。
8.6481は、「回帰係数」で、傾きを表します。横軸が1増加する時の縦軸の増加量です。
xは、「説明変数」で、横軸の数値を指します。
104.18は、「切片（せっぺん）」といって、横軸がゼロのときの縦軸の値です。
よく、回帰分析（単回帰分析）で登場する y=ax+b という一次方程式だったわけです。
では、もう一つの式。
R² = 0.8722
これは、「寄与率」とか、「重決定係数」といって、y = 8.6481x + 104.18の「目的変数」が「説明変数」によってどの程度説明できるかを示す式です。
y = 8.6481x + 104.18を87,2%説明できるという意味です。87.2%で予測値が使えるわけですね。
次の表は、今回の散布図を作成するのに使ったデータですが、予測値を算出するための数式を散布図で作成し、近似曲線を描かなければ求めることができないのでしょうか？
実は、関数であっさり算出することができます。
回帰係数は、SLOPE関数
切片は、INTERCEPT関数
寄与率は、RSQ関数
この3つの関数を使います。
回帰係数から算出してみます。
F3に次の数式を設定します。
=SLOPE(C2:C11,B2:B11)
F4に切片を算出してみますので、
=INTERCEPT(C2:C11,B2:B11)
最後に、寄与率をF5に算出します。
数式は、=RSQ(C2:C11,B2:B11)
算出された結果を確認してみましょう。
算出されたそれぞれの値は、散布図をつかって表示させた計算式と変わりませんね。
なので、関数で簡単に算出することができます。
最後に、
y = 8.6481x + 104.18
という計算式をつかうことで、予測できることができます。
たとえば、最高気温が32℃だとしたら、約380は販売できると予想することができます。
このように、ちょっとした関数を使うだけで、今までの資料にプラスすることが出来ますので、少しずつ加えてみるといいかもしれませんね。
