Excel。散布図に追加できる近似曲線の式と数値は何？
＜回帰分析：CORREL・SLOPE・INTERCEPT関数＞
「散布図」はデータの分布状況を確認することができるグラフなのですが、散布図に『近似曲線』を追加することができる機能があるのですが、この表示される近似曲線について確認していきましょう。次のデータを使って、散布図をつくっていきます。
C1:D11を範囲選択して、挿入タブの散布図またはバブルチャートの挿入にある、「散布図」を選択します。
すると、散布図が表示されました。今回はグラフを大きく表示したいので、グラフタイトルは削除しておきます。
この散布図に、近似曲線を追加設定します。
グラフのデザインタブのグラフの要素の追加にある「近似曲線」のその他の近似曲線オプションをクリックします。
近似曲線の書式設定作業ウィンドウが表示されます。今回は、線形近似にします。
近似曲線のオプションにある「グラフに数式を表示する」にチェックマークをいれます。
すると、散布図に、線形近似が表示され、グラフに数式が表示されました。
今回は、表示された数式がわかりやすいように、フォントサイズや色を調整しております。
表示された数式。y = 2.2822x + 49.304 はどう見たらいいのでしょうか？
この数式は、y=ax+b なので、いわゆる「回帰直線」ということですね。
回帰分析を行うことができる数式というわけです。
aは2.2822で、「傾き」を表しています。bの49.304が「切片」ですね。
例えば、フロアを200にしたら、売上高はどのぐらいになるのか？という予測をすることができるわけです。
=2.2822×200+49.304 で、505.744 という売上高の予測を算出することができるわけです。
散布図をつくるならば、この近似曲線と数式も合わせて表示して使いたいところですね。
なお、aの「傾き」とbの「切片」ですが、Excelには関数が用意されていますので、それぞれ簡単に算出することができます。
傾きを算出するのが、SLOPE関数です。
G1にSLOPE関数ダイアログボックスを表示して算出してみます。
既知のyには、D2:D11。
既知のxには、C2:C11。
あとは、OKボタンをクリックします。
G1の数式は、
=SLOPE(D2:D11,C2:C11)
G2に「切片」を算出してみますので、切片を算出するための関数は、INTERCEPT関数です。
INTERCEPT関数ダイアログボックスを表示して設定していきます。
既知のyには、D2:D11。
既知のxには、C2:C11。
あとは、OKボタンをクリックします。
G2の数式は、
=INTERCEPT(D2:D11,C2:C11)
それぞれの算出結果を確認してみましょう。
近似曲線に表示した数式の「傾き」と「切片」は合致していますので、「回帰式」を作る場合には、散布図をどうしても作る必要はありません。
ただ注意しないといけないのは、「相関関係」も算出しておく必要があります。
関連性のない数値どうしで算出しても意味がありません。
G4にCORREL関数をつかって相関関係を算出しておきましょう。
G4をクリックして、CORREL関数ダイアログボックスを表示します。
配列1には、C2:C11。
配列2には、D2:D11。
あとは、OKボタンをクリックします。
G4の数式は、
=CORREL(C2:C11,D2:D11)
算出結果を確認してみましょう。
0.942345と算出されました。
相関関係は、1に近づくと関係性が強いという意味なので、フロア面積と売上高には関係性が強いということがわかりました。
このように、散布図の近似曲線から、回帰分析までと、今までの資料にプラスアルファ出来るかもしれませんので、色々確認してみるといいかもしれませんね。
