Excelで回帰分析①

統計分析

最も基本的な統計モデルのひとつに、線形回帰モデルがあります。

今回から統計分析シリーズでは、実データを使って線形回帰モデルの構築を行います。

なお、使用する計算ツールはExcel2013とし、分析ツール機能やVBAを使わずセル関数のみでモデル構築をやってみます。

記事を複数回に分けて回帰分析の流れを一通り説明した後、最後に計算に用いたファイルや数式の説明をしたいと思います。

今回は、実務的な視点をイメージして回帰分析の流れを抑えつつ、分析の際に注意していただきたいことなどを交えて進めたいと思います。

回帰分析とは

回帰分析における線形回帰モデルは次の式で表されます。


y=\beta_0+\beta_{1}x_{1}+\cdots+\beta_{n}x_{n}+\epsilon\\

n個の説明変数x_1,\dots,x_nによって目的変数yを予測する、という式です。

\beta_0,\dots,\beta_nは回帰係数で、今回はこれらの値を推定することがモデル構築となります。

\epsilonは正規分布に従う誤差項です。

分析対象データ

分析対象データは、「政府統計の総合窓口e-Stat」からコンビニエンスストアの店舗数に関するデータ1を選びました。

www.e-stat.go.jp

現時点(2020年7月)で最新の、2014年度の各都道府県における「コンビニエンスストア数」を目的変数とし、同時点の「総人口」と「総面積」を説明変数とします。

人口が多い都道府県のほうがコンビニ数は多いでしょうし、面積が大きい都道府県のほうがコンビニが多いだろう、というイメージです。

一方で、ビジネスの現場ではモデル構築の対象が「時系列データ」であることが多いと思います。

しかし、時系列分析には特有の分析方法や注意点があるため、比較的容易なクロスセクションデータ2で説明を進めたいと思います。

なお、元データのままだと目的変数が人口10万人当たりの数になっているため、「コンビニエンスストア数(人口10万人当たり)」×「総人口」として「コンビニエンスストア数」に直した上で分析をします。

このようなデータになります。サンプル数は47です。

単位

年度
コンビニエンスストア数

2014
総人口

万人

2014
総面積

100k㎡

2014
北海道 2192.4 540 834.24
青森県 402.6 132 96.45
岩手県 395.52 128 152.75
宮城県 775.89 233 72.82
秋田県 309.92 104 116.38
\vdots \vdots \vdots \vdots
熊本県 494.04 179 74.09
大分県 314.73 117 63.41
宮崎県 294.15 111 77.35
鹿児島県 449.23 167 91.88
沖縄県 383.4 142 22.81

基礎分析

さて、データがそろったわけですが、何も考えずにデータをモデルに放り込むのはよくありません。

今回は使用するモデルが決まっていますが、データの傾向や特性によって使うべきモデル・手法は当然異なってきます。

複雑な分析をする前に、扱いたいデータがどのようなものなのかをよく観察することが重要になります。

まずは散布図を描いて、目的変数と説明変数との関係を見てみましょう。

Excel2013で、コンビニエンスストア数と総人口の範囲を選択しながら、ツールバーの挿入→散布図を選択すると以下のグラフが作成できます3

f:id:dataspirits:20200703075632p:plain

一番右上に抜き出ているのが東京都です。

イメージした通り、各都道府県のコンビニ店舗数と人口に正の相関がありそうです。

相関係数も見てみましょう。空いているセルに、=CORREL()と打ちこみ、引数の配列1にコンビニエンスストア数、配列2に総人口の列をそれぞれ選択します。

相関係数は0.972849という正の強い相関がみられました。

この場合、線形回帰モデルに当てはめると人口の係数は正になりそうだ、と推測できます4

次に、コンビニエンスストア数と総面積の範囲を選択しながら、同じようにツールバーの挿入→散布図でグラフを作成します。

f:id:dataspirits:20200703083623p:plain

ひとつだけ上部に位置しているのが北海道です。右下は東京都です。

先程のように明らかな関係性は見られませんが、北海道を除けばやや負の相関があるように見えます

相関係数は0.195487となりました。ほぼ相関はないようですが、弱い正の関係があるかもしれません。

また、後に重回帰分析を行うために人口と面積の関係を見ておきます。

説明変数間に相関がある線形回帰モデルでは、パラメータ推定時に多重共線性(マルチコ)と呼ばれる現象が起きるため、係数が正しく推定されない場合があります。

グラフはこちら

f:id:dataspirits:20200703101837p:plain

コンビニエンスストアの数と人口にかなり強い相関があったため、人口と面積も似たようなグラフになりました。

相関係数は0.066845です。まったく相関はないようです。重回帰分析を行う場合、両方を説明変数に加えること自体は問題なさそうです5

最後に、各データのヒストグラムも確認します。

Excel2016から、簡単にヒストグラムを作成できる機能が追加されたようですが、Excel2013にはないため、関数から作成します。

といってもやることは単純で、データの最小値から最大値を均等に区間分割し、各区間にデータが何個入るかカウントするだけです。

ヒストグラムは次のようになります。

f:id:dataspirits:20200703103508p:plainf:id:dataspirits:20200703103518p:plainf:id:dataspirits:20200703103526p:plain

いずれのデータも小さい値にデータが集中しており、値が大きくなるほど個数が少なくなります。

この際、明らかに異常値と思われるデータが含まれている場合はそのデータを除いたり、何らかの方法で補間したりします。

今回の場合、北海道の面積がとても大きいため、モデル構築に大きく影響を及ぼしてしまう可能性があります。

以降は北海道を分析対象から除かずにそのまま進めますが、除いて分析することにも価値はあると思います。

なお、線形回帰モデルの場合は、目的変数や説明変数が正規分布に従っている必要はありません

正規分布に従う必要があるのは、誤差項の分布のみです(誤差の正規性)。

誤差項の分布は、パラメータ推定が終わった後でしか確認ができないので、後程確認することにします。

単回帰分析

基礎分析が終わったので、実際にモデルを構築してみましょう。

後の説明のために、いきなり説明変数2つの重回帰分析を行わず、

まずは2変数間で相関が大きかったコンビニエンスストア数と総人口で単回帰分析を行います。

次の式の、切片\beta_0と傾き\beta_1を推定します。


y=\beta_0+\beta_{1}x_{1}+\epsilon\\

ExcelではLINEST関数INDEX関数を使用します。詳細は別のエントリーにて。

結果はこちらになります。

f:id:dataspirits:20200703131127p:plain

切片は-21.8、傾きは2.84となりました。

傾きのt値は十分大きく、有意水準99%としても有意となります。切片のt値は小さく、あまり信頼できません。

自由度調整済み決定係数は補正R2の0.945です。散布図で見た通り、コンビニエンスストア数は概ね総人口で説明できるようです。

暫定的に、線形回帰モデルはこのようになりました。


y=-21.8+2.84x_{1}+\epsilon\\

では、誤差項の正規性を確認しましょう。

上の式のxに各都道府県の総人口を代入し、コンビニエンスストア数の予測値\hat{y}を求めます。

次に、実際のコンビニエンススストア数と予測値との残差y-\hat{y}を求めます。

これが今推定した線形回帰モデルの誤差\epsilonになります。ヒストグラムを描いてみましょう。

f:id:dataspirits:20200703140211p:plain

やや両裾に伸びているように見えますが、概ね左右対称でデータが中央に分布しているので、正規性は満たしていると判断しました6

重回帰分析

次に、説明変数を「総人口」と「総面積」とした重回帰分析を行います。

その前に、先程求めた残差y-\hat{y}と総人口の散布図を見てみましょう。

f:id:dataspirits:20200703141709p:plain

相変わらず北海道が外れているのが少し気になりますが、なんとなく正の相関がありそうに見えます。

ここで、コンビニエンスストア数と総面積の関係を見たときには、弱い正の相関があるように見えたことを思い出してください。

残差をとることによって、目的変数との相関が大きい説明変数の影響が元データから取り除かれ、もともとのデータでは見えなかった関係性が見えることがあります。

別の言い方をすると、あるモデルの残差との相関が大きいデータを見つけることができれば、モデルの当てはまり7をより高められることがあります。

あえて単回帰モデルから構築したのは、これを説明するためでした。もちろん重回帰モデルからより説明変数の多い重回帰モデルを構築する場合にもこの考え方が使えます。

今回の場合は、コンビニエンスストア数から総人口の影響を取り除くと、総面積との新たな関係性が見えるようになりました。

総面積を説明変数に追加して重回帰モデルを構築すると、係数は正になりそう、とイメージできます。

さて、モデルの構築を行いましょう。今回の線形重回帰モデルは次の式になります。


y=\beta_0+\beta_{1}x_{1}+\beta_{2}x_{2}+\epsilon\\

回帰結果は次の通りです。

f:id:dataspirits:20200703150558p:plain

推定された切片は-86.2、総人口の係数は2.81、総面積の係数は0.89となりました。t値はすべて有意水準99%で有意となっています。

自由度調整済み決定係数は0.962となり、単回帰モデルよりも当てはまりは向上しました。

モデル式を記載します。


y=-86.2+2.81x_{1}+0.89x_{2}+\epsilon\\

誤差項の正規性を見てみましょう。先程と同様、実際のコンビニエンススストア数と予測値との残差y-\hat{y}を求めます。

f:id:dataspirits:20200703151406p:plain

引き続き正規性に問題はなさそうです。

では、このモデルに問題はないのかというと実はそうではありません。

正規性の次に、誤差項の等分散性を確認する必要があります。

これは、各説明変数と残差との関係を散布図で表すことにより確認できます。

f:id:dataspirits:20200703152250p:plainf:id:dataspirits:20200703152300p:plain

見ていただくと、総人口が大きいほど残差のばらつきが大きく、総面積が大きいほど残差のばらつきが小さい、ということがわかります。

線形回帰モデルは等分散性(誤差のばらつきが一定)を前提としています。

つまりこのモデルは、係数が有意で決定係数が大きいにもかかわらず、そもそもモデルの前提が満たされていなかったということになります。

しかし、だからと言って今回の分析が全くの無駄であったというわけではありません。

そもそも誤差の正規性や等分散性はグラフの目視で確認しただけですし、データ間にある一定の関係性があったことは明らかです。

ただし、モデルの前提が満たされていない以上、決定係数、t値、P値などの値が本などに乗っている目安の値をクリアしていたからと言って、このモデルには何の問題もない、という結論を導き出すことのないよう注意することが必要です。

次回は、今回のモデルの問題点を解消するために、データを自然対数変換したうえで分析を進めたいと思います。


  1. 政府統計の総合窓口(e-Stat)トップページ/統計データを探す/ファイル/組織から探す/総務省/社会・人口統計体系
  2. 本稿のコンビニエンスストア数のように、一時点の断面を切り取ったデータ
  3. グラフのタイトルや単位は作成後に補足しました。
  4. 相関係数と回帰係数は別物です。
  5. ちなみに、北海道を外れ値として残りの46都道府県で相関係数を見た場合-0.27463とやや弱い相関があるという結果になりますが、今回はこのまま分析を進めました。
  6. 本来ならこの時点で誤差の等分散性も確認すべきですが、後の重回帰分析で確認するため、本稿では省略します。
  7. ここでは単にサンプルへの当てはまりのこと。未知データに対する予測力も重要な要素となります。

コメント

運営者

データ分析やモデル等の仕事をしてる人々。週に1回程度のペースで金融や統計に関する記事を更新しています。
C++/Python/R/SAS/HTML/VBA/PHP/SQL

naruseをフォローする
タイトルとURLをコピーしました