Excelを用いて移動平均・自己相関係数の算出,コレログラムを作成する方法について解説します.サンプルデータとして,名古屋市の月平均気温の時系列データを使用しています.
考え方や解釈については以下のページで紹介しています.
移動平均
移動平均は,以下のようにAVERAGE関数を用いることで簡単に求めることができます.
3時点の平均を用いる3ヶ月移動平均を計算する例になります.
① コピー元となるセルに関数を入力
C列の3時点分の移動平均を求めるため,C3セルからC5セルの中間となるC4セルの横のD4セルに以下の数式を入力します.
=AVERAGE(C3:C5)
② 数式コピー
コピー元セルに関数を入力したら,入力したセルの右下にマウスカーソルを移動させ「✛」に変化したら下にドラッグもしくは,ダブルクリックを行います.
ドラッグした場合はドラッグを行ったセルまで,ダブルクリックした場合は隣の入力されているセルの行まで自動で数式がコピーされます.
数式コピーを行った場合,数式内で参照しているセルが更新されるため以上で移動平均を求めることができます.
自己回帰係数
自己相関係数は原系列のデータを1行ずつずらしてコピーをし,CORREL関数を用いることで求めることができます.
① 原系列をコピーする
原系列データの右側に1行ずつずらして,コピーを行います.行をずらす方向は,過去の時点になるようにずらします.例えば,例のように原系列データが上から下に向かって新しい時点の場合は,上方向に1行ずつずらしてコピーを行います.
② 相関係数を算出する
原系列とずらした値間の相関係数を計算します.最初に1時点分ずらした(ラグ k=1)データとの相関係数を求めます.
例ではD21セルに以下の数式を入力しています.原系列データの範囲は絶対参照で入力します.
=CORREL($C8:$C19,D8:D19)
横方向に数式コピーを行い,原系列とk=2,3,4,,,の自己相関係数を計算します.
コレログラム
コレログラムは上記のように自己相関係数を算出したら,棒グラフなどで簡単に作成することができます.ここでは敢えて散布図を用いて,下記のような少しかっこいいコレログラムを作成します.
① 散布図を作成する
ラグと自己相関係数の範囲を選択して,「挿入」→「散布図」を選択します.
② 誤差範囲を表示
作成した散布図を選択して,「グラフ要素」→「誤差範囲」→「その他のオプション」をクリックします.
画面右側に「誤差範囲の書式設定」という画面が表示されるので,以下のように設定を行いプロットからy軸原点まで伸びる棒を作成します.
棒部分のデザインは「バケツ」タブから調整することができます.
③ x軸方向の誤差範囲の削除
x軸方向の誤差範囲(棒)を削除します.「誤差範囲のオプション」から「系列1X誤差範囲」を選択して以下のように設定します.
④ デザインを整える
最後に全体的なデザインを書式設定から調整してください.以下は藤丸がよく用いるデザインのコレログラムです.
StaatAppを用いた分析
統計解析アプリStaatAppでは,時系列分析の様々な手法(移動平均やコレログラムの作成)を無料で行うことができます.手元のデータを読み込みクリック操作だけで分析できるため,Excelを用いて数式を作成するよりも手軽に行うことができます.