【Googleスプレッドシート】移動平均の計算!過去N件の平均値表示

株価の推移や売上データを分析するとき、日々の細かい変動に惑わされず、全体的な傾向をつかみたいと感じたことはありませんか。移動平均は、過去N件のデータの平均を順に計算することで、グラフをなめらかにする便利な手法です。この記事では、Googleスプレッドシートで移動平均を計算する具体的な手順を、関数の組み合わせを中心に解説します。OFFSETやARRAYFORMULAを使った方法を学べば、動的な平均値を簡単に表示できるようになります。

移動平均の計算に使う関数の仕組み

移動平均とは、時系列データの連続するN個の値の平均を、1つずつずらしながら求める計算方法です。例えば、3日間の移動平均であれば、1日目〜3日目の平均、2日目〜4日目の平均というように計算します。Googleスプレッドシートでは、範囲を動的に変化させる関数をAVERAGE関数と組み合わせることで実現します。主に使用する関数は、OFFSET、INDEX、FILTER、ARRAYFORMULAなどです。これらの関数は、行番号や列番号を基準にして参照範囲を自動調整するため、ドラッグコピーや配列数式で一括処理に向いています。

OFFSET関数を使った移動平均の計算手順

ここでは、もっともシンプルで分かりやすいOFFSET関数を用いた方法を紹介します。データはA列に時系列順で入力されている前提です。Nの値は任意のセル(例:C1)に入力しておき、後から変更できるようにします。

手順1: データとN値を準備する

1. データをA列に入力する
   A1に見出し(例「売上」)、A2以降に数値を日付順に入力します。データは昇順に並んでいることを確認してください。
2. N値をC1セルに入力する
   移動平均の期間(例:3)を入力します。このセルを数式で参照することで、後から期間を変えても自動的に計算が更新されます。

手順2: 移動平均の数式を入力する

1. B2セルに数式を入力する
   次の数式をB2に入力します。
   =AVERAGE(OFFSET($A$2,ROW()-2,0,$C$1,1))
   この数式は、A2セルを基準にして、現在の行からN個上方向(すなわち過去)の範囲を参照し、その平均を計算します。ROW()は現在の行番号を返すため、2行目ならROW()-2=0(基準セル自身)、3行目なら1つ上のセルから始まる範囲になります。
2. 数式を下方向にコピーする
   B2セルを選択し、右下のフィルハンドルをダブルクリックするか、下方向にドラッグして必要な行までコピーします。すると、各行で過去N件の平均が自動計算されます。

手順3: エラーを回避する

先頭のN-1行は、過去に十分なデータがないため#REF!エラーが発生します。これを防ぐには、IFERROR関数でラップします。B2の数式を次のように変更してください。
=IFERROR(AVERAGE(OFFSET($A$2,ROW()-2,0,$C$1,1)),"")
これにより、データ不足の行は空白になります。

ARRAYFORMULAを使った一括計算の手順

大量のデータがある場合、数式をコピーする代わりにARRAYFORMULAで1つの数式を入力する方法も便利です。ただし、移動平均は各行で範囲が異なるため、工夫が必要です。

手順1: 配列数式を準備する

1. B2セルに次の数式を入力する
   =ARRAYFORMULA(IF(ROW(A2:A)-ROW(A2)+1<$C$1,"",AVERAGEIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)))
   ただし、この数式は正しく動作しません。正しくは、過去N件を範囲指定するためにOFFSETを配列内で使う必要があります。
2. より実用的な配列数式の例
   次の数式は、各セルでOFFSETを利用して過去N件の平均を算出しますが、ARRAYFORMULA内でOFFSETは使用できません。そのため、代わりにINDEXとROWを使って範囲を構築します。
   =ARRAYFORMULA(IF(ROW(A2:A)-ROW(A2)+1<$C$1,"",AVERAGE(INDIRECT("A"&ROW(A2:A)-$C$1+1&":A"&ROW(A2:A)))))
   この数式は、INDIRECT関数を使って文字列から範囲を生成し、AVERAGEで平均を求めます。ただし、INDIRECTはARRAYFORMULA内で行ごとに評価されるため、注意が必要です。実際には、この数式は期待通りに動作しません。

手順2: カスタム関数を使う代替案

ARRAYFORMULAで移動平均を計算するのは複雑なため、代わりにGoogle Apps Scriptでカスタム関数を作成する方法をおすすめします。しかし、本記事では関数のみの方法に絞ります。実用的には、先ほどのOFFSET+IFERRORの数式をコピーする方法が簡単です。

FILTER関数を使った移動平均の計算手順

FILTER関数は、条件に合うデータのみを抽出して平均を計算できます。移動平均の場合、現在の行から過去N行のデータを抽出する条件を指定します。

手順1: 条件行番号の列を作成する

1. C列に行番号を表示する
   C2セルに=ROW()と入力し、下方向にコピーします。これにより、各行の行番号が表示されます。
2. D2セルに移動平均の数式を入力する
   =AVERAGE(FILTER($A$2:$A,$C$2:$C<=ROW(),$C$2:$C>ROW()-$C$1+1))
   この数式は、A列のデータのうち、行番号が現在の行以下かつ現在の行からN-1以上大きい範囲をフィルターし、平均を計算します。つまり、過去N件が抽出されます。
3. 数式を下方向にコピーする
   D2セルを下方向にコピーすると、各行で移動平均が計算されます。データ不足の先頭行はエラーになるため、IFERRORで処理してください。

移動平均計算でよくあるトラブルと対処法

データ不足でエラーが表示される

最初のN-1行は過去のデータがないため、#NUM!や#REF!エラーが発生します。IFERROR関数で空白にするのが簡単な対処法です。または、データがN件に達した行からのみ計算を開始するように数式を調整します。

N値を変更しても計算が更新されない

N値をセル参照にしている場合、そのセルの値を変更すれば自動的に再計算されます。ただし、数式内でN値を直接数字で書いている場合は、手動で修正する必要があります。常にセル参照を使う習慣をつけましょう。

オフセットの方向を間違える

OFFSET関数の第3引数(行オフセット)と第4引数(高さ)の符号に注意してください。過去のデータを参照するには、行オフセットを負の値にするか、高さでNを指定して基準セルから上方向に向かって範囲を取る必要があります。間違えると未来のデータを参照してしまうので、十分確認しましょう。

各方法の比較表

方法	メリット	デメリット	使用シーン
OFFSET	シンプルで理解しやすい、N値の変更が容易	大量データで重い、ドラッグコピーが必要	小〜中規模データ、期間を頻繁に変えたい場合
FILTER	条件の自由度が高い、停止条件を細かく設定可能	補助列が必要、数式が長くなる	期間が不規則な移動平均、複数条件がある場合
ARRAYFORMULA	数式1つで済む、セルのコピーが不要	複雑でエラーが出やすい、INDIRECTの制限	データ量が多く、数式を1か所にまとめたい場合(ただし実用性は低い)
INDEX+ROW	OFFSETより高速、配列数式に組み込みやすい	やや複雑、範囲の構築に工夫が必要	パフォーマンスを重視する中規模データ

まとめ

この記事では、Googleスプレッドシートで移動平均を計算する主な方法として、OFFSET関数、FILTER関数、ARRAYFORMULAを使った手法を解説しました。もっとも実用的なのは、OFFSETとAVERAGEを組み合わせ、IFERRORでエラーを抑える方法です。この方法なら、N値を変更するだけで自動的に平均期間を調整できるため、分析の効率が格段に向上します。ぜひ実際のデータで試してみてください。さらに応用として、QUERY関数を使った移動平均の計算や、グラフに移動平均線を追加する方法も学んでみるとよいでしょう。