Excel。回帰直線の決定係数を算出するのがRSQ関数です。【RSQ】

Excel。回帰直線の決定係数を算出するのがRSQ関数です。

＜関数辞典：RSQ関数＞

RSQ関数

読み方： アールエスキュー　　

分類： 統計 

RSQ(既知のy,既知のx)

回帰直線の決定係数を算出します

Excel。散布図に表示された一次方程式は何？回帰分析？で予測できるんだって【Linear equation】

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は販売できると予想することができます。

このように、ちょっとした関数を使うだけで、今までの資料にプラスすることが出来ますので、少しずつ加えてみるといいかもしれませんね。

Excel。グラフを使わずに、近似予測を関数で算出してみる【Approximate curve】

Excel。グラフを使わずに、近似予測を関数で算出してみる

＜近似曲線:SLOPE・INTERCEPT・FORECAST・RSQ関数＞

Excelでは、グラフで、近似曲線を描くことが簡単にできます。

例えば、近似直線を描き、近似曲線の書式設定ダイアログボックスに、

「グラフに数式を表示する」にチェックマークをオンすることで、グラフに、数式を表示することができ、その数式を使うことで、近似予測を算出することができますが、グラフを描くことなく、直接データから、近似予測を算出してみましょう。

【線形近似予測】

y=ax+bでお馴染みの、回帰直線。このaは傾きでbは切片と呼ばれていますが、このaとbを関数で算出してみましょう。

傾きは、SLOPE関数で算出することができます。

F2をクリックして、SLOPE関数ダイアログボックスを表示しましょう。

既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。

数式は、
=SLOPE(B2:B13,C2:C13)
と設定してあります。

切片は、INTERCEPT関数で算出することができますので、F3をクリックして、INTERCEPT関数ダイアログボックスを表示しましょう。

既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。

数式は、
=INTERCEPT(B2:B13,C2:C13)
と設定してあります。

では、結果を確認してみましょう。

グラフに表示した数式と同じ結果になりましたね。

このように関数を使うことで簡単に算出することができます。

y=ax+bを使うことで、例えば、700人の来店客数があれば、y=700×F2＋F3で、予測値は約1521となるのですが、この傾きや切片を算出しなくても、FORECAST関数を使うことで、予測値を算出することができます。

F5をクリックして、FORECAST関数ダイアログボックスを表示しましょう。

Xには700
既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。

数式は、
=FORECAST(700,B2:B13,C2:C13)
と設定してあります。

では、結果を確認してみましょう。

予測値は、1521.219と算出することができました。

ところで、そもそも、売上高と来店客数との間の相関関係はどうなっているのでしょうか？

関係性がないのであれば、算出しても、予測値を大きく外れてしまうことになります。

そこで、決定係数も、RSQ関数で簡単に算出することができます。

F6をクリックして、RSQ関数ダイアログボックスを表示しましょう。

既知のyには、B2:B13
既知のxには、C2:C13
と入力してOKボタンをクリックしてみましょう。

数式は、
=RSQ(B2:B13,C2:C13)
と設定してあります。

それでは、結果を確認してみましょう。

結果は、0.851092と『1』に近いので、関係性は高いようですね。

このように、関数を使うことで、予測値を簡単に導くことができますので、ちょっと会議資料とかに加えてみるといいかもしれませんね。

Excel。事務職のデータ分析31。近似曲線で「R-2乗値」ってあるけどどういう意味？【R-2】

Excel。事務職のデータ分析31。近似曲線で「R-2乗値」ってあるけどどういう意味？

＜R-2乗値＞

会議用の資料でグラフをつくって、近似曲線を描いて、
近似曲線の詳細設定にある、
「グラフに数式を表示する」にチェックを付けるだけで、
【回帰式】である、y=ax+bという式を簡単に表示できますが、

詳細設定の「グラフに数式を表示する」の下にある、
「グラフのR-2乗値を表示する」というのがあります。

今回は、この「グラフのR-2乗値を表示する」についてみていくことにします。

「グラフのR-2乗値を表示する」にチェックをつけてみると、

R2=0.8511というのがグラフに表示されました。
なるほど、R-2乗っていうのは、R二乗ってことなんですね。

このR-2乗値というのは、
回帰式のy=ax+bのxとyのデータの相関係数を2乗した値で、
回帰直線が当てはまっているかどうかを表す指標として
『決定係数』というのがあります。

この『決定係数』が【1】に近づくほど、
回帰直線が実際のデータに当てはまっているということがいえるわけですね。

要するに、表示された値が【1】に近ければ、
それだけ実際のデータに当てはまっているということを補う式が
グラフに表示されたわけですね。

改めて、グラフに回帰式を表示させてみます。

0.8511ということなので、
この回帰式は実際のデータに当てはまっているので、
予想した時には、
ある程度精度が高い数値を算出されてくるというのがわかります。

では、0.8511はどのようにして算出されてきたのでしょうか？
このグラフのもとになったデータを見てみましょう。

【決定係数を算出する】

回帰式の傾きは、
SLOPE（スロープ）関数で算出することができました。

切片（せっぺん）は、
INTERCEPT（インターセプト）関数を使って算出することができました。

同じように、決定係数を算出するにも、関数が用意されています。
それは、RSQ関数（アール・スクエア）を使うことで簡単に、
算出することができます。

F6に決定係数を算出していきます。

RSQ関数ダイアログボックスを表示します。
既知のyには、C2:C13
既知のxには、B2:B13
OKボタンをクリックします。

0.851092と算出されましたね。

R2=0.8511と繰り上がっていますが、同じ値になっています。

グラフの詳細設定で簡単に、回帰式やR-2乗値を表示することもできますが、
数式や関数を使って実際に値を、比較的に簡単に算出することができます。

日ごろ使っているデータから、プラスした資料を作ることができますので、
色々つくってみるといいかもしれませんね。