Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 区間可変で移動平均を求める−OFFSET関数

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

対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007

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

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

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

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

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

[スポンサードリンク]

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


▼操作手順:区間可変で移動平均を求める
※E2セルから下に終値が入力されているときに、F1セルに区間を入力するとF2セルから下に移動平均が表示されるようにする例

F2セルに
「=AVERAGE(OFFSET(E2,1-$F$1,0):E2)」
という数式を入力
 ↓
F2セルを下にオートフィル

区間を固定で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!エラーが表示されたり、不適切な値が計算されるセルが存在したりします。この回避方法については近日中に別途ご紹介します。

[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 区間可変で移動平均を求める−OFFSET関数

TrackBack:1

TrackBack URL
区間可変の移動平均でエラーが表示されないように from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007 OFFSET関数を使って、区間を可変にした移動平...

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 区間可変で移動平均を求める−OFFSET関数

「検索・行列」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.