こんにちは、blueです。
前回はExcelでできる基本統計やグラフの作成について説明しました。
前回までの記事はこちら
今回はExcelを使った回帰分析についての説明をします。
この記事を読めば
- Excelの「分析ツール」アドインを用いた回帰分析ができる
- 回帰分析で得られた結果の見方がわかる
- 回帰分析を行う際の注意点がわかる
ようになります。
できる限りわかりやすく説明していますのでぜひ勉強していってください。
今回はこの書籍を利用して作成しています。Excelの機能を使うことで最低限の数式で統計を使いこなせるようにしてくださっているのでお勧めの一冊です。
統計に対して苦手意識を持っている方にはこちらの書籍がお勧めです。統計学とは？というところから丁寧に書いてくださっています。数式についても図を使いながら一つずつ説明してくださっています。
数式が大嫌い！という方にはこちらの書籍がお勧めです。この本だけでは統計解析ができるようにはなりませんが、文章や図でわかるようにすることをモットーにされているのでイメージで理解されたい方向けの書籍です。
回帰分析とは
「データを予測した式を作ること」を回帰分析といいます（Excelによるやさしい統計解析）。
その為回帰分析は「推測統計」の中の一つの分析手法になります。
簡単に言うとグラフに対して近似の直線or曲線を想定することででデータにない部分に対して予測をする方法となります。
なおExcelによる回帰分析は直線の方法になります。以降で単回帰分析、重回帰分析の方法について説明します。
回帰分析に関する用語の説明についてはこちらをご参考ください
単回帰分析
単回帰分析は「説明変数」が１つの場合の解析手法です。
式に表すとy=ax+bになります。
Excelでの単回帰分析の方法は以下になります。
1.「データ」-「データ分析」-「回帰分析」を選択-「OK」をクリック
（データタブにデータ分析の項目がない方はこちら）
2. 回帰分析ウィンドウで「入力Y範囲」を選択-「入力X範囲」を選択-「ラベル」を選択-「出力オプション」を該当するものにする-「OK」をクリック
すると以下のような表が自動で作成されます。
重回帰分析
重回帰分析は「説明変数」が１つ以上の場合の解析手法です。
式に表すとy=a1x1+a2x2+・・・・+bになります。
重回帰分析の方法は単回帰分析と同じです。
1.「データ」-「データ分析」-「回帰分析」を選択-「OK」をクリック
（データタブにデータ分析の項目がない方はこちら）
2. 回帰分析ウィンドウで「入力Y範囲」を選択-「入力X範囲」を選択-「ラベル」を選択-「出力オプション」を該当するものにする-「OK」をクリック
重回帰分析においては入力X範囲が複数列になることがポイントです
すると以下のような表が自動で作成されます。
回帰分析の概要の見方
次に得られた回帰分析の概要の見方について説明します。
見るべきポイントは３つです。それぞれについて説明します。
（統計学的な用語として知りたい方はこちら）
①重決定R2と補正R2
重決定R2は０～１の間を取ります。高いほどよく一般的に0.5～0.6以上を取るとYをよく表していると言えます。
重決定R2はデータ数が少ないと大きくなるので一般的には補正R2を使います。
②係数
係数の絶対値が高いほどYに与える影響が高いと言えます。ただこれはXの単位にも影響するので注意が必要です。
③t、P-値
tは係数を標準偏差で割った値です。tはぶれの程度を表しており、絶対値が1.96以上であればその変数の影響は大きく、回帰式からは外すことができないことになります。
一方P-値は小さいほどよいです。P-値が0.05以下であればその変数の影響は大きく、回帰式から外せないことを意味します。
以下では条件付き書式を使ってP-値の列が≦0.05となる行について色を付けるようにしています。
1.範囲を指定した後「ホーム」-「条件付き書式」-「新しいルール」をクリック
2. 新しい書式ルールウィンドウにて「数式を使用して、書籍設定するセルを決定」をクリック-「数式」に-$(列番号)(行番号)<=0.05を記載（参照列は固定するが行はすべてのセルに反映させたいので固定しないこと）-「書式」にて設定-「OK」をクリック
結果は以下のようになり、影響のある変数が一目でわかるようになります。
回帰分析を行うの際の注意点
このように回帰分析はExcelで簡単に行うことができますが注意点がいくつかあります。
外れ値を考慮すること
外れ値が存在すると変数間の相関係数、回帰分析の決定係数は大きく影響を受けます。
その為外れ値はできるだけ排除する必要があります。
外れ値を見つけるには以下の表やグラフを作成する方法があります。
- 1つの変数内の外れ値は「基本統計量」の作成
- 1つの変数内の外れ値は「ヒストグラム」「箱ひげ図」の作成
- 2つの変数間の外れ値は「散布図」の作成
それぞれの作成方法や削除方法については以下で詳しく説明していますので参考ください。
もし上記の方法で外れ値が見つかった場合は除外したうえで再度回帰分析を実行しましょう。
多重共線性を考慮する
重回帰分析の際は変数同士の相関が存在する（多重共線性）と傾きが決まらず、決定係数があがってしまうという問題があります。
多重共線性を確認するには変数間の相関係数を確認する方法があります（作成方法はこちら）。
以下では相関行列を作成し、ヒートマップで相関係数の値を可視化しています。
この例では相関係数が大きくなるものはありませんでしたが0.5や0.6を超えてくるものがあるような場合は一方を削除したうえで再度回帰分析を行うことをお勧めします。
今回のまとめ
今回はExcelを使って
- Excelの「分析ツール」アドインを用いた回帰分析の方法
- 回帰分析の結果の見方
- 回帰分析を行う際の注意点
にについて説明しました。
線形の回帰分析であればExcelでも簡単に行うことができます。
今回の記事を通して少しでもExcelでの回帰分析について慣れていただけたら幸いです。
今回はこの書籍を利用して作成しています。Excelの機能を使うことで最低限の数式で統計を使いこなせるようにしてくださっているのでお勧めの一冊です。
統計に対して苦手意識を持っている方にはこちらの書籍がお勧めです。統計学とは？というところから丁寧に書いてくださっています。数式についても図を使いながら一つずつ説明してくださっています。
数式が大嫌い！という方にはこちらの書籍がお勧めです。この本だけでは統計解析ができるようにはなりませんが、文章や図でわかるようにすることをモットーにされているのでイメージで理解されたい方向けの書籍です。
コメント