静粛に、只今統計勉強中

仕事でデータ分析をすることになったバリバリ文系アラフィフのおっさんが、独学で統計の勉強を始めました。

Excel VBAでコレログラムを作成するマクロを作ってみた１

統計学 統計検定 Excel

2019年11月実施の統計検定2級試験の問題が公式ＷＥＢサイトに公開されていたので、ダウンロードして解いてみたのですが、またしてもコレログラムの問題で間違ってしまいました。

2015年11月に初登場してこれで4回目の出題になりますが、4回とも一度で正解できたことがありません。

コレログラムについては、手持ちの本でもGoogleで検索しても、詳しくてしかもとっつきやすい解説に巡り合えなかったので、主に公式問題集の解説を読んでなんとなくわかった気になっていました。

しかし実は全くわかっていなかったのだという現実を、そろそろ直視しないといけないようです。トホホ。

しかたがないので、一からきちんと学ぶとしましょう。

 

例によって、作成手順をExcel VBAでマクロにしながら学んでいきたいと思います。が、まずはコレログラムに関する用語の定義を確認しておきましょう。

  1. 時系列データ

    ある現象の時間的な変化を、連続的に（または一定間隔をおいて不連続に）観測して得られた値の系列（一連の値）のこと。

     

  2. 自己相関係数

    元データと時間をずらしたデータとの相関のことを「自己相関」と言います。

     

  3. コレログラム

    ラグと自己相関を表したグラフを「コレログラム」と言います。コレログラムを見ると、データが周期性をもつかどうかを調べることができます。
    （出典は2.に同じ）

     

これだけ読むと簡単な感じがするんです。相関係数はばっちりマスターしたし、2変数の片方が元データをずらしただけのもんでしょ、って。

ところが、いざ問題に向き合うと正解がわからない。

そもそも自己相関をあやふやにしか理解していないものだから、コレログラムから元の時系列データの変動の具合をイメージできないし、その逆もまたしかり、なんです。

で、まじめに入門書を読んでみて驚いたのは、自己相関係数の計算は、いわゆる普通の相関係数*1とはちょっと違うということ。マジか。

 

最初に頼ったのは、おなじみ赤本です。 

統計学入門 (基礎統計学Ⅰ)

統計学入門 (基礎統計学Ⅰ)

 

５７ページに、

一般に，遅れ h の自己相関係数は

r_h=\frac{\displaystyle \sum_{i=1}^{n-h}(x_i-\bar x)(x_{i+h}-\bar x)/(n-h)}{\displaystyle \sum_{i=1}^n(x_i-\bar x)^2/n}

で定義される

とあります。

積率相関係数の計算式とよく似てますが、ちょっと違いますね。

では、実際にExcel上で計算しながら、積率相関係数との違いについても確認してみましょう。

 

  1. 平均値 \bar x を計算する

    f:id:cyclo-commuter:20200116143139p:plain

    用意したのは２０１６年６月～２０１９年１１月の我が家の電気利用料金です。
    画像は見切れてますが。

  2. 偏差 x_i-\bar x を計算する

    f:id:cyclo-commuter:20200116143306p:plain

  3. 遅れ h の偏差 x_{i+h}-\bar x を計算する

    f:id:cyclo-commuter:20200116145236p:plain

    関数について少し説明すると、INDIRECT関数は文字列で指定したアドレスのセルの値を返します。INDIRECT("B"&ROW()) なら、入力セルと同じ行のＢ列の値を返しますが、+COLUMN()-3 としているので、入力セルの列番号から３引いた分（つまりラグの数）だけ行をずらしたセルの値を得ることができます。

  4. 分母の偏差平方和 \displaystyle \sum_{i=1}^n(x_i-\bar x)^2 と分散＝偏差平方和÷n を計算する

    f:id:cyclo-commuter:20200116152216p:plain

    ここは積率相関係数と違ってますね。積率相関係数なら、\sqrt{V(X)\times V(Y)}=\sqrt{\displaystyle \sum_{i=1}^n(x_i-\bar x)/n \times \sum_{i=1}^n(y_i-\bar y)/n} となるところです。

  5. 分子の偏差積和 \displaystyle \sum_{i=1}^{n-h}(x_i-\bar x)(x_{i+h}-\bar x) と自己共分散＝偏差積和÷(n-h) を計算する

    f:id:cyclo-commuter:20200116154610p:plain

    ここは計算式どおりなら =SUMPRODUCT($C$2:$C$41,E2:E41) とすべきところですが、SUMPRODUCT関数は欠損値を０扱いしてくれるので手抜きしました。
    自己共分散のほうは、

    f:id:cyclo-commuter:20200116155353p:plain

    COUNTA関数で n-h に対応しています。
    偏差積和をn-hで割るところも積率相関係数の計算とは異なりますね。

 

というところで、あれっ？となりました。

もう一冊、 

現場ですぐ使える時系列データ分析 ~データサイエンティストのための基礎知識~

現場ですぐ使える時系列データ分析 ~データサイエンティストのための基礎知識~

 

も読んだのですが、こちらの７６ページには

n個の時系列データ \{r_1,r_2,r_3,\cdots,r_n\}が観測されている場合、ラグhの自己相関係数を
\frac{\displaystyle \sum_{t=h+1}^{n}(r_i-\bar r)(r_{t-h}-\bar r)}{\displaystyle \sum_{t=1}^n(r_i-\bar r)^2}

と定義します。

とあるではありませんか！ 

*1:ピアソンの積率相関係数のことです。