Excel。回帰直線の傾きを算出するのがSLOPE関数です。【SLOPE】

Excel。回帰直線の傾きを算出するのがSLOPE関数です。

＜関数辞典：SLOPE関数＞

SLOPE関数

読み方： スロープ　　

分類： 統計 

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

回帰直線の傾きを算出する

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。

＜Excel関数辞典：VOL.73＞

今回は、SLN関数～SORTBY関数までをご紹介しております。

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

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

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。手早く切片と傾きを算出して売上予測を求めるにはどうしたらいい【Intercept and slope】

Excel。手早く切片と傾きを算出して売上予測を求めるにはどうしたらいい

＜SLOPE+INTERCEPT関数＞

たった2つのデータからだけでも、ちょっとした関数を知っているだけで、色々と見えてきます。

例えば予測を立てる時につかう、y=ax+bでお馴染みの「回帰分析」。

このy=ax+bに当てはめてあげると、予測した数値を手早く導き出すことができます。

なお、aのことを、「傾き」。

bのことを「切片」といいます。

また、回帰分析は、TREND関数をつかえば、一発で予測値を算出することもできますが、TREND関数をつかってしまうと、「傾き」と「切片」がわからないまま算出するので、今回は、傾きと切片を算出して、予測値を算出してみます。

次のデータを用意しました。

B列には、来店客数。C列には、売上高の各店舗のデータがあります。

この2つのデータを使って、来店客数が300人の時に、売上高の予測値を算出していきます。

最初に、F1の相関係数を算出しておきます。

相関係数は、CORREL関数を使えば、一発で算出できます。

F1の数式は、

=CORREL(B2:B9,C2:C9)

相関係数は、この2つのデータに何らかの関係性があるのか、そして、その関係性は強いのか弱いのかがわかります。

この値が、「1」に近ければ、関係性は強いと判断できます。

今回は、「1」に近いので、関係性は強いようです。

逆に「0」に近い場合は、関係性が弱いことを指します。

SLOPE関数をつかうことで、傾きの「a」を算出できます。

F3の数式は、

=SLOPE(C2:C9,B2:B9)

傾きの「a」は、8.847と算出されました。

INTERCEPT関数をつかえば、切片の「b」を算出できます。

F4の数式は、

=INTERCEPT(C2:C9,B2:B9)

切片の「b」は、186.33と算出されました。

この2つの数値を回帰分析の式に当てはめます。

F7の数式は、

=F3*300+F4

結果、yである売上高の予測値は、「2840.58」と算出されました。

このように、ちょっとした数式と関数を使うだけで、手早く、予測値を算出することができます。

日頃使っているデータをつかって、色々な角度から資料を作ってみると、見えなかったものが見えてくるかもしれませんね。

Excel。データがあれば直感に頼らず単回帰式の関数で算出することができます。【Regression equation】

Excel。データがあれば直感に頼らず単回帰式の関数で算出することができます。

＜INTERCEPT・SLOPE・FORECAST.LINEAR関数＞

明日は雨だから…など、売上予測を直感で頼るのではなく、チョットしたデータがあれば、関数をつかうだけで、算出することができます。

また、関数をつかった計算式なので、散布図をつかった、近似曲線に数式を表示しなくても、大丈夫です。

では、次のデータを用意しました。

予測するための計算式。

単回帰分析の式である、「y=ax+b」の傾きと切片を算出するところからおこなっていきましょう。

最初は「a」の傾きを算出するには、SLOPE関数をつかいます。

グラフを移動させて、F2にSLOPE関数の数式を設定して、傾きを算出します。

F2の数式は、

=SLOPE(C2:C11,B2:B11)

縦軸のYが売上高で、横軸のXが来店数なので、その範囲を設定してあげるだけで、aの傾きを算出することができました。

算出結果は、152.2238979。

グラフで表示した単回帰式の傾きと一緒であることが確認できます。

続いて、「b」の切片を算出してきます。

切片を算出するために使用する関数は、INTERCEPT関数をつかいます。

F3にINTERCEPT関数の数式を設定します。

F3のINTERCEPT関数の数式は、

=INTERCEPT(C2:C11,B2:B11)

算出結果は、-24.19721578。

散布図の近似曲線で表示した式の切片と、先程の傾きと同様に合致していることがわかります。

このように、わざわざ、グラフを用意する必要はありません。

これで、傾きと切片が算出できましたので、来店数を「20」だとした場合の、単回帰式にあてはめることで、売上予測を算出することができます。

F6の数式は、単回帰式にあてはめて、

=F2*F5+F3

と設定しています。

算出結果は、3020.2。

という予測値を算出することができました。

このように、比較的、簡単な数式をつかうことで、予測値を算出することができました。

しかし、この予測値は、SLOPE関数で算出した傾きやINTERCEPT関数で切片を算出しなくても、FORECAST.LINEAR関数で、一発算出することができます。

F8に、FORECAST.LINEAR関数をつかった数式を設定していきます。

=FORECAST.LINEAR(F5,C2:C11,B2:B11)

単回帰式のxであるF5をつかって、あとは、Y軸とX軸を設定します。

その算出結果が、

3020.28

先程、SLOPE関数やINTERCEPT関数をつかって、算出した結果と同じ値が算出されていることが確認できました。

このように、FORECAST.LINEAR関数だけでも、知っているだけで、データがあれば、直感に頼らずに、予測値を算出することができます。

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。散布図に追加できる近似曲線の式と数値は何？【Scatter plot】

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に近づくと関係性が強いという意味なので、フロア面積と売上高には関係性が強いということがわかりました。

このように、散布図の近似曲線から、回帰分析までと、今までの資料にプラスアルファ出来るかもしれませんので、色々確認してみるといいかもしれませんね。

Excel。回帰分析して、適正値を見つけてみよう。【regression analysis】

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」に近ければ、関係性が強いということを意味していますので、相関関係を強いことがわかりました。

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。事務職のデータ分析29。近似曲線（回帰直線）は表示できるけど、どうしたらいい？【Regression line】

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の数式と同じ結果になりましたね。

関数を使うと簡単に算出できるものも多くありますが、
意味を知るためには、
一度バラした数式を作ってみると理解しやすいケースもあります。

このように、色々な数値から様々なことが見えてきたり、
使えたりしますので、アレコレ想像して資料を作っていくと、
日頃の資料にプラスして、面白いものが見えてくるかもしれませんね。