エクセルでできる重回帰分析

analysis-by-excel_eyecatch-1

実務の中でデータを扱う場合、「とりあえずエクセルを使おう」という方が圧倒的多数だと思います。エクセルには「分析ツール」というアドインが用意されており、これを使うと様々な統計的な分析も行うことができます。今回は、そのようなアドインを使わずに関数だけを用いて重回帰分析を行う手順と、その結果の読み方を紹介します。(ちなみにExcel for Macでは現時点で利用できません)

重回帰分析を行うデータを準備する

基本的な形式を理解する

まずは、分析に用いるデータを所定の形式で準備します。難しいことではないので、一度覚えれば簡単にできるようになります。データは、目的変数(被説明変数)となるデータを右端に、説明変数をそれよりも左に並べた形で準備します。(目的変数、説明変数などの用語については重回帰分析を理解するために知っておきたい7つの統計用語をご参照ください)

ここからは、家賃を題材にしたサンプルデータを使いながら説明します。目的変数には100件の物件の「家賃」、説明変数には「最寄り駅からの徒歩距離」「専有面積」「築年数」など14の変数を準備しています。

具体的なデータの準備

まずは説明変数を入力します。A1に「徒歩距離」とラベル名、その下のA2からA101にデータを入力します。これをB列「専有面積」からN列「ペット相談可」まで繰り返します。最後に、O1に「家賃」とO2からO101までに家賃の金額を入れます。

なお、今回のデータでは様々な物件情報を分析対象とするためにダミー変数を活用しています。簡単に言うと、数字ではない要素を0と1だけを使ったデータに変換することで、分析対象として扱えるようにしているものです。詳細はダミー変数に関しては重回帰分析の可能性を広げる「ダミー変数の扱い方」に詳しく記載しています。

エクセルの関数を用いて分析を行う

ここからは、LINESTという関数を用いた分析の進め方です。

まずは、先ほど入力したデータの下、A103からO107まで、縦5行×横15列の範囲を選択します。その上で、数式バーに以下のとおりにLINEST関数を入力し、Control+Shift+Enterを押します(mac であればcommand+shift+enter)。

=LINEST(O2:O101,A2:N101,,TRUE)

なお、ここで入力した内容はそれぞれ以下の意味があります。

  • 【既知のy】: 目的変数の範囲。
  • 【既知のx】: 説明変数の範囲。複数の説明変数を用いる場合はその全てを選択する。
  • 【定数】: TRUEと入れた場合は定数を0となるよう計算される。今回は定数を計算するので入力を省略した。
  • 【補正】: TRUEと入力すると追加情報が計算される。この後の分析で参照したいので、ここはTRUEと入れる。

LINEST関数の入力が正しく行われていれば、縦5行×横15列の範囲に以下のような分析結果が表示されるはずです。

analysis-by-excel

分析結果を読みやすくする加工をする

この状態ではまだ解釈しづらいため、次の2つの操作をすると分析結果が格段に読みやすくなります。

説明変数のラベルをつける

まずは説明変数を入力します。A1に「徒歩距離」とラベル名、その下のA2からA101にデータを入力します。これをB列「専有面積」からN列「ペット相談可」まで繰り返します。最後に、O1に「家賃」とO2からO101までに家賃の金額を入れます。

まずは出力された結果の上に、該当する説明変数の名前のラベルをつけます。ここで注意しなければならないのが、なぜかExcelでは説明変数がデータとは逆の順序で出力されるので、N102に「徒歩」M102に「専有面積」、最後にA102に「ペット相談可」と入れます。

t値を計算する

次に、結果を読み解きやすくするためにt値を計算します。計算自体は至って簡単で、それぞれの列ごとに、出力された結果の一番上を上から二番目の数字で割れば計算できます。(数学的な解釈は今回は省略します)

重回帰分析の結果を読む3つのポイント

ここで初めて分析結果の解釈に進みますが、その際に見るべき数字は大きく3つあります。

寄与率

A105のセルに表示されている数字です。この数字によって分析全体の精度を判断します。

これは0~1の値を取り、基本的には1に近いほど精度が良いことを意味します。ただし、説明変数の数やデータのサンプル数や分布などの様々なことに影響を受けるものであり、一概にどの程度があれば大丈夫だとは言えません。あくまでも参考までにご紹介すると、弊社データアナリストの実感値としては0.6を越えていると一定の精度があると判断できることが多いです。今回は0.94となっており、精度は問題なさそうだと言えます。

t値

次に、それぞれの物件条件が家賃にどの程度関係があるかを確認します。そのために見るのが先ほど計算したt値です。t値は絶対値が大きいほど影響が大きいことを意味し、絶対値が2.0を越えていることが統計的に「関係がある」と判断するひとつの目安となります。今回は、「徒歩」「面積」「階数」「オートロック」の4つが該当しています。

係数

最後に、それぞれの物件条件がどれくらい家賃の金額に影響するかを確認します。そのために係数を見る。これを見ることで、説明変数が1単位変動することの影響がどの程度大きいのか(もしくは小さいのか)を具体的に確認することができます。例えば「徒歩」の係数は-1102.4であるため、徒歩1分駅から遠くなると家賃は¥1,102円安くなることを表しています。

これらの3点を踏まえると、今回の分析からは次のような示唆を読み解くことができる。

  • 家賃の動きに影響があると統計的に判断できるのは「徒歩」「面積」「階数」「オートロック」の4つの物件情報である
  • 徒歩は1分駅から遠くなると家賃は1,102円安くなる
  • 専有面積は1㎡広くなると家賃は2,764円高くなる
  • 階数は1階高くなると家賃は1,442円高くなる
  • オートロックが「あり」になると家賃は9,864円高くなる

今回の家賃の分析結果を活用するのであれば、物件の家賃が相場観からするとどの程度が妥当なのかを判断する指標とすることができます。

具体的には、「貸す」立場では最適な家賃設定に活かし、「借りる」立場であれば割安な物件を探すことに使える可能性があります。

分析結果を扱う上での注意点

分析モデルは、説明変数の組み合わせを変えれば違った結果となります。その際、「影響がある」と判断される説明変数が変わることもあります。実際、上記のサンプルデータを弊社の分析ツールadelieを用いて分析した結果、用いる説明変数の数を少なくし、かつ「築年数」も「影響がある」(1年古くなると¥652安くなる)という分析結果も得られています。このどちらが正しいのか?を数字だけから判断することはできません。あくまでも分析結果をスタート地点に、最も現実に即しているモデルはどれか、自ら仮説を考え始めることが必要です。

この記事を書いた人