Excel VBAでコレログラムを作成するマクロを作ってみた1
2019年11月実施の統計検定2級試験の問題が公式WEBサイトに公開されていたので、ダウンロードして解いてみたのですが、またしてもコレログラムの問題で間違ってしまいました。
2015年11月に初登場してこれで4回目の出題になりますが、4回とも一度で正解できたことがありません。
コレログラムについては、手持ちの本でもGoogleで検索しても、詳しくてしかもとっつきやすい解説に巡り合えなかったので、主に公式問題集の解説を読んでなんとなくわかった気になっていました。
しかし実は全くわかっていなかったのだという現実を、そろそろ直視しないといけないようです。トホホ。
しかたがないので、一からきちんと学ぶとしましょう。
例によって、作成手順をExcel VBAでマクロにしながら学んでいきたいと思います。が、まずはコレログラムに関する用語の定義を確認しておきましょう。
- 時系列データ
ある現象の時間的な変化を、連続的に(または一定間隔をおいて不連続に)観測して得られた値の系列(一連の値)のこと。
- 自己相関係数
元データと時間をずらしたデータとの相関のことを「自己相関」と言います。
-
コレログラム
ラグと自己相関を表したグラフを「コレログラム」と言います。コレログラムを見ると、データが周期性をもつかどうかを調べることができます。
(出典は2.に同じ)
これだけ読むと簡単な感じがするんです。相関係数はばっちりマスターしたし、2変数の片方が元データをずらしただけのもんでしょ、って。
ところが、いざ問題に向き合うと正解がわからない。
そもそも自己相関をあやふやにしか理解していないものだから、コレログラムから元の時系列データの変動の具合をイメージできないし、その逆もまたしかり、なんです。
で、まじめに入門書を読んでみて驚いたのは、自己相関係数の計算は、いわゆる普通の相関係数*1とはちょっと違うということ。マジか。
最初に頼ったのは、おなじみ赤本です。
57ページに、
一般に,遅れ の自己相関係数は
で定義される
とあります。
積率相関係数の計算式とよく似てますが、ちょっと違いますね。
では、実際にExcel上で計算しながら、積率相関係数との違いについても確認してみましょう。
- 平均値 を計算する
用意したのは2016年6月~2019年11月の我が家の電気利用料金です。
画像は見切れてますが。 - 偏差 を計算する
- 遅れ の偏差 を計算する
関数について少し説明すると、INDIRECT関数は文字列で指定したアドレスのセルの値を返します。INDIRECT("B"&ROW()) なら、入力セルと同じ行のB列の値を返しますが、+COLUMN()-3 としているので、入力セルの列番号から3引いた分(つまりラグの数)だけ行をずらしたセルの値を得ることができます。
- 分母の偏差平方和 と分散=偏差平方和 を計算する
ここは積率相関係数と違ってますね。積率相関係数なら、 となるところです。
- 分子の偏差積和 と自己共分散=偏差積和 を計算する
ここは計算式どおりなら =SUMPRODUCT($C$2:$C$41,E2:E41) とすべきところですが、SUMPRODUCT関数は欠損値を0扱いしてくれるので手抜きしました。
自己共分散のほうは、 COUNTA関数で に対応しています。
偏差積和をで割るところも積率相関係数の計算とは異なりますね。
というところで、あれっ?となりました。
もう一冊、
も読んだのですが、こちらの76ページには
個の時系列データが観測されている場合、ラグの自己相関係数を
と定義します。
とあるではありませんか!