Excel。アドインのデータ分析ツールで「回帰分析」をつかってみよう。【regression analysis】

Excel。アドインのデータ分析ツールで「回帰分析」をつかってみよう。

＜データ分析ツール＞

ちょっとしたデータから、色々な角度の情報を、手早く算出してくれる「データ分析ツール」。

アドインしないとつかえませんが、追加すると様々な情報を教えてくれます。

例えば、「回帰分析」。

なんか難しいそうだし、関数も色々つかわないといけないイメージがしますが、データ分析ツールをつかえば、手早く算出してくれます。

次のデータをつかって、「回帰分析」を表示します。

データタブの「データ分析」をクリックします。

データ分析ダイアログボックスが表示されます

回帰分析を選択したら、OKボタンをクリックします。

回帰分析ダイアログボックスが表示されます。

入力Y範囲には、「売上高」の$C$1:$C$7　を設定します。

入力X範囲には、「来店客数」の$B$1:$B$7　を設定します。

今回は、入力範囲にラベルも含めて範囲選択しているので、ラベルにチェックマークをいれます。

出力先は、新規ワークシートとしました。

ついでに、残差も表示できるので、残差にもチェックマークをいれたら、OKボタンをクリックします。

これで、新規シートに、回帰分析の詳細かつ残差も合わせて、表示することができました。

分析データツールには、回帰分析だけではなく、様々な資料をあっという間に、算出してくれます。

Excel。データ分析の回帰分析をつかえば残差まで手早く算出できます。【regression analysis】

Excel。データ分析の回帰分析をつかえば残差まで手早く算出できます。

＜データ分析＞

データ間の相関をはじめ、アドイン機能にある、データ分析の「回帰分析」をつかうことで、回帰式や残差までを手早く算出することができます。

用意したデータです。

B列の来店客数とC列の売上高のデータをつかって、データ分析「回帰分析」を行います。

データタブの「データ分析」をクリックします。

データ分析ダイアログボックスが表示されますので、「回帰分析」を選択して、OKボタンをクリックします。

回帰分析ダイアログボックスが表示されますので、各種設定をしていきます。

入力Y範囲には、$B$1:$B$7

入力X範囲には、$C$1:$C$7

それぞれ、範囲選択します。

ラベルにチェックマークをいれて、オンにします。

出力先は、「新規ワークシート」を選びました。

出力されるデータ量があるので、新規ワークシートにしました。

残差も合わせて算出したいので、残差のチェックマークをオンしたら、OKボタンをクリックします。

これだけの処理で、

「回帰統計」

「分散分析表」

そして、「残差出力」も算出してくれます。

分析ツールには、色々用意されていますので、つかってみると、作業効率が改善するものが見つかるかもしれませんね。

Excel。分析ツールで相関分析を手早く算出してみよう。【correlation】

Excel。分析ツールで相関分析を手早く算出してみよう。

＜分析ツール＞

アドインで、追加したデータタブにある「データ分析」をつかうことで、「相関分析」を手早く算出することができます。

次のデータをつかっていきます。

B列の来店客数とC列の売上高。

そして、D列の広告費の相関関係が強いのか弱いのかを手早く算出するのに「データ分析」をつかうわけです。

「データ分析」をクリックすると、データ分析ダイアログボックスが表示されます。

「相関」を選んだら、OKボタンをクリックします。

相関ダイアログボックスが表示されます。

入力範囲には、B1:D7を選択します。見出し行も含めて範囲選択します。

先頭行をラベルとして使用にチェックマークをいれます。

出力先は、A9とします。

あとは、OKボタンをクリックします。

これで、相関の強さがわかりますね。

なお、算出結果の意味ですが、

0.9以上なら「非常に強い相関」

0.7以上0.9より小さいなら「やや強い相関」

0.5以上0.7より小さいなら「やや弱い相関」

0.5より小さいなら「非常に弱い相関」

といわれています。

Excel。分析ツールやソルバーをアドインすると使えるようになります。【add-in】

Excel。分析ツールやソルバーをアドインすると使えるようになります。

＜アドイン＞

分析ツールやソルバーといいた便利なツールがありますが、通常のExcelには表示されいません。

これらは、「アドイン」という追加処理をすることで、使えるようになります。

ファイルタブのオプションをクリックします。

Excelのオプションダイアログボックスが表示されます

アドインをクリックします。

管理が「Excelアドイン」になっているのかを確認したら、設定ボタンをクリックします。

「アドイン」ダイアログボックスが表示されます。

追加したいアドインを選択します。

今回は分析ツールにチェックマークを入れます。

そのあと、OKボタンをクリックします。

データタブに、「データ分析」が追加されたことが確認できます。

これで、アドインの「データ分析」をつかうことができます。

Excel。標準正規分布グラフの左右の棄却域を塗りつぶすにはどうすればいいの？【Abandon domain】

Excel。標準正規分布グラフの左右の棄却域を塗りつぶすにはどうすればいいの？

＜正規分布＞

正規分布グラフの左右の棄却域と呼ばれているところを、塗りつぶすのですが、Excelのグラフではどのようにしたらいいのでしょうか？

次のようなグラフを作りたいわけです。

 

標準正規分布のグラフですが、左右端が塗りつぶされていますよね。

この塗りつぶされているところを、「棄却域」と呼ばれています。

今回は説明のために、左右それぞれ別々の色で塗りつぶしをしています。

このグラフを作るのには、細かいデータの表を準備する必要があります。

 

横軸は、-4～4までを0.1ずつで用意しています。

正規分布の、B5には、

=NORM.DIST($A5,B$1,B$2,FALSE)

という数式を設定してあります。

NORM.DIST関数の説明は、棄却域を塗りつぶすのが今回の目的なので、割愛します。

左棄却域（左側）ですが、-4～-2を正規分布のデータをセル参照で、数値をコピーしております。この値が塗りつぶすための数値になります。

同じように、右棄却域（右側）は、2～4を正規分布のデータをセル参照で、数値をコピーしております。この値が塗りつぶすための数値になります。

普通、正規分布グラフを作成するには、散布図をつかって描くのですが、散布図では塗りつぶすことができません。

そこで、面グラフをつかって描く必要があります。

なぜ、0.1刻みの表を作ったのかというと、面グラフは、線を滑らかにすることができないので、データ量を増やすことで、放物線を綺麗に描くことができるというわけです。

では、グラフをつくっていきましょう。

A4:D85を範囲選択して、挿入タブの「すべてのグラフを表示」をクリックします。

 

グラフの挿入ダイアログボックスが表示されます。

 

おすすめグラフタブに「折れ線」が表示されていれば、そのままOKボタンをクリックします。一度、「折れ線」にしてから修正するほうが、簡単です。

もし、折れ線の放物線がなければ、すべてのグラフタブの折れ線から、放物線になっているものを選択します。

 

このグラフを面グラフに変更していきます。

グラフのデザインタブの「グラフの種類の変更」をクリックします。

 

再度、グラフの種類の変更ダイアログボックスが表示されますので、すべてのグラフタブの「組み合わせ」をクリックします。

 

すべて、グラフの種類を面に変更したら、OKボタンをクリックします。

グラフはこのようになりました。

ところで、なんで最初から「面グラフ」にしないのかというと、次のグラフのように、塗り分けの境界線が斜めになってしまうからなのです。

 

これを直すのがとても、面倒なので、折れ線で一度確定してから、面グラフに変更しています。

凡例を削除して、グラフタイトルを標準正規分布に変更します。

青色の部分をクリックして、塗りつぶしを「なし」。

図形の枠線を設定します。

 

最後に、横軸の表示を修正していきます。

横（項目）軸をクリックして、書式タブの選択対象の書式設定をクリックします。

 

軸の書式設定作業ウィンドウが表示されます。

 

軸のオプションのラベルにある「間隔の単位」を5に変更します。

 

これで、完成です。

正規分布グラフは、線グラフのイメージがあるので、なかなか面グラフをつかうという発想が浮かびにくいですが、線グラフでは塗りつぶすことができないので、塗りつぶしたい場合は、塗りつぶしができるグラフから考えてみると、いいかもしれませんね。

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 line】

Excel。分析ツールのソルバーで回帰直線を算出してみよう

＜分析ツールのソルバー＞

ソルバーという機能が、Excelのアドインに用意されています。

このソルバーを使うと、例えば目標を達成するための値を逆算して算出することができます。

次のデータをつかって、ソルバーで傾きと切片を算出して、回帰直線を求めていきます。

最初にソルバーがアドインされていないと使えないので、アドインから確認していきましょう。

データタブの分析にソルバーが表示されていなければ、アドインされていませんので、ファイルタブのオプションをクリックして、Excelのオプションダイアログボックスを表示します。

アドインのソルバーアドインを選択して設定ボタンをクリックします。

アドインダイアログボックスが表示されますので、ソルバーアドインにチェックマークをいれOKボタンをクリックします。

データタブの分析にソルバーが表示されました。

ソルバーで最小二乗法を使用して、近似直線を算出していきます。

近似直線をy=ax+bとして、ソルバーで傾きのaと切片のbを算出していきます。

最終的に、最小二乗法で残差平方和を算出する必要がありますので、傾きと切片を共に「1」として、E列の売上予測を算出していきます。

E2には、=$H$1*C2+$H$2
と設定します。

この式は、y=ax+b　と同意です。

オートフィルで数式をコピーしておきます。

店舗ごとの売上予測が算出できましたので、最小二乗法の合計＝残差平方和を算出して、その値をソルバーで使用していきます。

ただ、単純に、売上予測を合算するわけではないので、ここはSUM関数ではなくて、SUMXMY2関数を使って算出していきます。

E12の数式は、
=SUMXMY2(D2:D11,E2:E11)

E12の残差平方和をクリックして、データタブのソルバーをクリックします。

ソルバーのパラメータダイアログボックスが表示されます。

目的セルの設定には、$E$12
最小二乗法を使用するので、目標値は「最小値」にします。

変数セルの変更には、傾きと切片なので、$H$1:$H$2
各係数がマイナスになることを想定して、「制約のない変数を非負数にする」のチェックマークを外します。

設定が終わったら、解決ボタンをクリックします。

ソルバーの結果ダイアログボックスが表示されますので、OKボタンをクリックします。

これで、傾きと切片を算出することができたので、現状において新店舗を作る場合、この傾きと切片をつかうことで、売上を予測することが可能になるわけです。

例えば、フロアー面積が150の新店舗をOPENしたい場合、
=H1*150+H2
とすれば、359.13と算出されるので、359の売上が見込めると予測できるといったわけです。

経験則もいいですが、数字から算出できるものも資料として検討材料に加えるのもいいかもしれませんね。