スプレッドシートで移動平均を計算する方法
基本的には簡単で，始点の処理でちょっと工夫．
作り方 (と，サンプルデータ)
説明が抽象的になりすぎるのを避けるために，具体的なサンプルデータに基づいて説明しますね．今回使うのは，このブログ (lookbackmargin.blog) の月別の更新数 (= 書いた記事数) のデータ．A 列が年月，B 列がその月の更新数になってるね．移動平均の計算結果は C 列に書くことにしましょう．
2つの定数を準備
移動平均を計算するには平均する窓の幅を決める必要がありますね．今回はその値をセル D2 に書きました．「窓の幅」と呼びましたが，「区間数」とか「項数」とか呼ぶ流儀もあるようですね．
そしてもう1つの定数，データ先頭行をセル E2 に設定します．多くの場合，データ先頭行は 2 (1行目は見出し) だと思うので，面倒な人は以降の数式中の $E$2 を 2 にハードコードしても問題ないです．ここでは数式の意味の見通しを良くするために，あえて一般化した形で書いておきます．
計算式！やや，ややこしい
さあ，移動平均を計算する数式をセル C2 に書きましょう！一見複雑に見えますが，1つずつ見ていきましょう…
=IF(ROW()-$E$2+1>=$D$2, AVERAGE(OFFSET(B2, -1*$D$2+1, 0, $D$2, 1)), #N/A)
IF 式の判定と FALSE の場合
条件式はこんな形をしています
ROW()-$E$2+1>=$D$2
意味は簡単で，データの先頭行からその行までに集計に必要な個数以上のデータがあるかを判定して，あるなら TRUE を返し，ない (データの先頭付近だと，平均するデータの個数が足りない) ときは FALSE を返す．
FALSE のとき，IF 式は #n/a を返してる．#n/a はちょっと見た目が悪いけど「該当の数値はありません」を意味する重要なデータなので，値がないときは素直に #n/a を使いましょう．
TRUE の場合
IF 式の条件が TRUE のとき，移動平均が計算されます！計算式はこんな感じ．
AVERAGE(OFFSET(B2, -1*$D$2+1, 0, $D$2, 1))
AVERAGE 関数は，複数のセルを入力すると，そのセルに入力された値の平均を計算する．今回は，平均を計算したい「複数の行」を OFFSET 関数を使って持ってきて，それを AVERAGE 関数に渡してる．簡単だね．
OFFSET 関数は5つの引数を取り，それぞれ順番に セル参照 ，オフセット行 ，オフセット列 ，[高さ] ，[幅] ．重要なのは オフセット行 で，-1*$D$2+1[高さ] が $D$2 (= 集計区間の幅) の分だけセルを取ってきてくれるわけだ．
各行にコピー
面倒くさいけど，地道にコピーしましょう…
Google Sheets には ARRAYFORMULA 関数という素晴らしい機能があるんだけど，今回使った計算には対応してない．ARRAYFORMULA が対応できる形式で計算式を書き直すことは可能だと思うけど，面倒くさいので今回はこれくらいで…
余談: 標準機能の移動平均は何を計算している？
実はこんな操作をしなくても，移動平均線をグラフに描くだけ (具体的な数値を知る必要がない) なら Google Sheets の標準機能でできる．データ系列にトレンドラインを追加して，「種類」に「移動平均」を選択すればいい．でも Google Sheets が追加する移動平均線はよく分からなくて，データ末尾を超えて平均線が追加されるんだよね… これどんな計算で出てきてるわけ？
なぜ移動平均を計算したくなった？
サンプルデータにある通り，ブログの月次の更新数を集計してみた．下図がそのグラフなんだけど，薄い線で書いた「更新数」の生データは変動が激しすぎて傾向を読み取りづらい．これを上手に要約して傾向を読み取りやするために，移動平均を計算したくなったのでした．短期的な変動が除去されて，いくらか平滑化された移動平均のほうが，やっぱり大局的な傾向を読み取りやすいよね．
2019年末の振り返り記事でも作ったように，今回も横軸を時間，縦軸を記事数に取って記事の総数が分かるグラフを作ってみた．現在の記事数は (この記事自身と，1つ前の記事を除いて) 1,404件！つまり，この記事が1,406番目の記事だね．
上2つの画像では各データに対してグラフを2種類書いてるけど，左が「真面目なグラフ」で，右が「学会や論文には適さないけど，さっぱりして見やすいグラフ」のつもり．僕が参考にする伝わるデザインのサイトで，この2通りのグラフの作り方が解説されてる．僕はグラフを正方形に描くのが好きなんだけど，伝わるデザインではグラフが正方形だったからなんだよね．
1件のコメント