Excelで回帰分析③

ツール

過去2回の統計分析シリーズでは、実データを用いて回帰分析を行いました。

Excelで回帰分析①
最も基本的な統計モデルのひとつに、線形回帰モデルがあります。今回から統計分析シリーズでは、実データを使って線形回帰モデルの構築を行います。なお、使用する計算ツールはExcel2013とし、分析ツール機能やVBAを使わずセル関数のみで...
Excelで回帰分析②
前回の統計分析シリーズでは、コンビニに関するデータを対象に線形回帰モデルを適用し、回帰分析を行いました。dataspirits.hatenablog.comその結果、回帰係数の有意性に問題はなかったものの、誤差分布が等分散性を満たし...

上記のエントリー内でも触れましたが、分析時の計算はpythonやRを使わず、すべてExcelの標準的な関数のみで行っています。

今回はそのファイルを公開し、計算方法や関数の説明をしていきたいと思います。

回帰分析を分析ツール等で行ったことがあるが、中でどのような計算が行われているかイマイチわからないという方は、ご参考にしていただければ幸いです。

分析ファイルのリンク

分析に使用したエクセルファイルは以下のリンク先にあります。

drive.google.com

適宜ダウンロード、保存していただいてご参照ください。

Microsoft Excel 2013を使用しています。

各シートの概要

分析ファイルは12個のシートにわかれています。

シート名 概要
データ 政府統計のデータと分析用の加工済みデータ
基礎分析 データのヒストグラムと散布図
基礎分析_対数 対数変換後データのヒストグラムと散布図
単回帰分析(セル関数) セル関数による単回帰分析と残差分析
単回帰分析(データ分析) データ分析ツールによる単回帰分析と残差分析
重回帰分析(セル関数) セル関数による重回帰分析と残差分析
重回帰分析(データ分析) データ分析ツールによる重回帰分析と残差分析
check セル関数とデータ分析ツールとの結果が一致しているかの確認
重回帰分析_対数(セル関数) 対数変換後データのセル関数による重回帰分析と残差分析
重回帰分析_対数(データ分析) 対数変換後データのデータ分析ツールによる重回帰分析と残差分析
予測 交差検証
予測_対数 対数変換後データの交差検証

データシートでは対数変換後データを作成するために自然対数への変換を行う関数=LN()を使用しています。

=LOG()という関数もあるのですが、こちらは常用対数への変換なので注意してください。

f:id:dataspirits:20200810233026p:plain

以降の解説は、「重回帰分析(セル関数)」シートをベースに進めていきます。

INDEX関数とLINEST関数

重回帰分析(セル関数)シートでメインとなるのは=LINEST()関数と=INDEX()関数です。

重回帰分析(セル関数)シートのI26:M28セルで使用しています。

f:id:dataspirits:20200810233326p:plain

=LINEST()関数は、第1引数に目的変数、第2引数に説明変数を取ります。

コンビニの店舗数を目的変数とした回帰分析を行いたいので、B列のデータを第1引数に、C列とD列のデータを第2引数にします。

このとき、後のために選択範囲はF4キーを1回押して絶対参照にしています。

第3引数で回帰モデルに切片をつけるかつけないかを選びます。切片ありのモデルなので1を入れます。

第4引数で出力結果に決定係数や残差平方和などを含むかどうかを選びます。今回は1を入れておきます。

これで=LINEST()関数に必要なデータの入力は完了です。

しかしこれだけでは出力結果を見ることはできません。

=LINEST()関数の出力は配列形式となっています。

つまり1つのセルで結果のすべてを表示することはできず、配列形式特有の処理をする必要があります。

そこで登場するのが=INDEX()関数です。

=INDEX()関数は配列の要素を指定して取り出す関数です。

第1引数を任意の配列とし、第2引数と第3引数でそれぞれ配列の行と列を指定します。

そのためには、=LINEST()関数がどのような形式で出力されているかを知らなければなりません。

今回のデータ形式(説明変数が2個)の場合、=LINEST()関数の出力形式は次のようになります。

f:id:dataspirits:20200725184755p:plain

1行目と2行目に切片及び各変数ごとの回帰係数と標準誤差1がそれぞれ出力されます。

3行目以降に、決定係数などのモデル全体に関する推定結果が出力されます。

そして、注意してほしいのは列の出力結果です。

一般的な線形回帰モデル式の表記やデータの入力方法のイメージだと、切片、説明変数1、説明変数2と並んでいそうですが、実際はその逆となっています。

さて、配列の形式がこのようになっていることを念頭に置いて、=INDEX()関数による要素取得を行います。

要素の指定はH26:H28セルとI25:M25セルで行います。

後にデータ分析ツールの計算結果と比較検算するために、出力結果の行列を入れ替えています。

また、出力結果が切片、説明変数1、説明変数2の順になるように、H26:H28セルは3,2,1という順にしています。

上記までの説明を踏まえて、I26セルには次のような式を入力しています。

f:id:dataspirits:20200810234852p:plain

予め行番号の行、列番号の列を絶対参照で固定し、I26セルをM28セルまでコピーします。

これで、=LINEST()関数の出力結果を得られました。

回帰分析の結果出力

ここから、回帰分析の分析結果を計算してみましょう。

今回は、係数、標準誤差、t値、p値、信頼区間、決定係数、自由度調整済み決定係数を解説します。

係数と標準誤差は=LINEST()関数の出力結果をそのまま使用します。

f:id:dataspirits:20200810235052p:plainf:id:dataspirits:20200810235130p:plain

t値は、回帰係数を標準誤差で割った値です。

f:id:dataspirits:20200810235156p:plain

p値は、自由度n-k-1のt分布の両側確率、つまり、t分布において推定値/標準誤差で求めたt値(のプラスマイナス)よりも外側(両側)の面積になります。

したがって、t分布の両側確率を求める=T.DIST.2T()にt値と自由度を引数にして次のように入力します。

f:id:dataspirits:20200810235410p:plain

下限95%は回帰係数の推定値の信頼区間の下限です。両側の95%なので、下限だけだと2.5%分です。t分布で左側2.5%の値に標準誤差を掛けた分、推定値に加えれば求まります。

関数はt分布の逆関数を返す=T.INV()を使用します。

f:id:dataspirits:20200810235549p:plain

上限95%も同様に、t分布で右側2.5%の値に標準誤差を掛けた分、推定値に加えれば求まります。

決定係数は回帰モデルがどの程度データに当てはまっているかを表す指標のひとつです。=LINEST()関数の3行1列に格納されていますが、残差平方和と目的変数の偏差平方和からも求めることができます。

Q列に目的変数の実現値、R列に推定された回帰モデルによる目的変数の予測値を入力しています。

S列に各データについての偏差(変数からその変数の平均を引いた値)、T列に各データについての残差(実現値から予測値を引いた値)を並べており、J17セルとJ16セルでそれぞれの平方和を計算しています。

J8セルで、1-残差平方和/偏差平方和を計算しており、これが決定係数と一致しています。

f:id:dataspirits:20200811000014p:plain

自由度調整済み決定係数は、説明変数が増えていくことへのペナルティが考慮された決定係数です。

I9セルで、残差平方和と偏差平方和を使って、データの数(47個)と説明変数の数(2個)で調整した上で求めています。

f:id:dataspirits:20200811000138p:plain

計算結果は、エクセルのデータ分析ツールによる出力と一致していることを確認済みです。

重回帰分析(データ分析)シートが、同じデータに対してデータ分析ツールの回帰分析を使用した結果で、checkシートにて各セルの値が一致していることを確認できます。

交差検証(クロスバリデーション)

セル関数で回帰分析ができるようになったため、これを利用してエクセルでも簡単にできるモデル評価をやってみましょう。

今回は交差検証の一つ、一個抜き交差検証(LOOCV)をご紹介します。

一個抜き交差検証とは何か、その手順は次の通りです。

まず、分析に用いるデータのうちどれかひとつ、例えば北海道のデータを除外して回帰分析をします。

次に、推定された回帰モデルによって北海道のデータ、ここでは人口と面積から、コンビニの数を予測します。

そして、予測したコンビニの数が、実際の北海道のコンビニの数とどれくらい離れているか(一致しているか)を評価します。

以上を、すべての都道府県に対して行い、最後に各都道府県の予測値の評価を平均するなどして指標化します。

これが、一個抜き交差検証の概要です。すべてのデータに対して、そのデータ自身を除いたデータによって推定されたモデルによる予測値を求め、評価し、平均するといったものです。

予測シートと予測_対数シートで、前回までに作成した2つのモデルの予測精度を評価しています。

一個抜きを実現するために、C、D、E列のデータを52行目以降に再度同じ順番で並べています。

そして、F、G、H列でINDEX()関数とLINEST()関数を使用し、データを一つ除いた回帰係数を推定しています。

I列で予測値を算出し、J列で予測誤差を算出しています。

K列で予測誤差の絶対値を算出し、L列でその平均をとっています。

この予測誤差の絶対値の平均が小さいほうがモデルとして予測精度が高いと言えます。

この値が、データをそのまま回帰分析した場合は約97.02、データを対数変換して回帰分析した場合は約95.43となりました。

だいたい同じ値となったので、どちらも予測精度としては優劣はなさそうですが、どちらかを選ぶとすれば対数変換のほうになる、と判断ができます。

f:id:dataspirits:20200811001050p:plain

エクセルで回帰分析シリーズは一旦ここまでとします。

データ分析に特化したソフトを使用すればこれらの分析はもっと簡潔にできるのですが、データをエクセルで保有している場合はエクセル上でさっと分析できると便利なこともあります。

目的や状況に応じて、適切に使い分けられるようにしたいですね。


  1. 最小二乗法による推定結果。他の出力も同様です。

コメント

運営者

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

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