区間可変で移動平均を求める−OFFSET関数

AVERAGE関数を使って移動平均が求められることをご紹介しました。

この方法を知った方からは、
「平均を求める区間を可変にして移動平均を求めるにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。

単純にAVERAGE関数を使うだけでは、表示したい移動平均分の列を用意しておく必要があります。

例えば、
　5日移動平均・25日移動平均・200日移動平均を表示したければ、
　5日移動平均用数式・25日移動平均用数式・200日移動平均用数式
をそれぞれ入力しておく必要があります。

これを、いずれかのセルに例えば
　「5」と入力すれば5日移動平均が、
　「25」と入力したときには25日移動平均が
　「200」と入力したときには200日移動平均が
表示されるようにしたいというのが、
「区間を可変にして移動平均を求めるには」
というご質問の主旨です。

基準のセルから指定された行数や列数シフトした位置にあるセルの参照 (オフセット参照) を返すOFFSET関数を使うと、区間を可変にした移動平均が求められるようになります。

区間を固定で3日移動平均を求める場合、F4セルに
「=AVERAGE(E2:E4)」
という数式を入力しましたが上記の操作を行うとF4セルには
「=AVERAGE(OFFSET(E4,1-$F$1,0):E4)」
という数式が入力されることになります。

AVERAGE関数の引数で、
「E2」
だった部分が
「OFFSET(E4,1-$F$1,0)」
になったわけです。

「OFFSET(E4,1-$F$1,0)」は、
E4セルを基準にして、
行数を「1-$F$1」だけシフトしたセル参照を指定していることになります。

ですからF1セルに「3」と入力されていれば
「OFFSET(E4,1-$F$1,0)」は、
「OFFSET(E4,1-3,0)」
「OFFSET(E4,-2,0)」
ということになり
E2セルを参照しているわけですから
「=AVERAGE(OFFSET(E2,1-$F$1,0):E2)」は
「=AVERAGE(E2:E4)」と
同じことになります。

▼サンプルファイル（003239.xls 141KByte）ダウンロード

サンプルファイルでは上記のようにF1セルに数値を入力すると、F2セルから下に移動平均が表示されるように設定してあります。

参考のために、G列にはOFFSET関数だけの「=OFFSET(E2,1-$F$1,0)」という数式を入力し、H:J列には区間固定の3日移動平均・5日移動平均・25日移動平均が計算されるようにしています。

F1セルの値を変更して、F:G列にどのような値が表示されるか、H:J列の値と比較しながらご確認ください。

なお、
F1セルの値によって、いくつかのセルに#REF!エラーが表示されたり、不適切な値が計算されるセルが存在したりします。この回避方法については近日中に別途ご紹介します。