例えば月間数値変動レポートで、
「過去7日間の平均を常に見たい。でもまだ7日分のデータが溜まっていない時は全平均として動いて欲しい」という時に使用する数式。
このような時はOFFSET関数を使用して平均の計算対象となる範囲が状況に応じて変化するような数式を作る。
C7セルの数式
=AVERAGE(OFFSET($D7,0,COUNTIF($D7:$O7,">="&0)-1,1,MIN(COUNTIF($D7:$O7,">="&0),$C$3)*-1))
C7セルの数式の意味
=AVERAGE(OFFSET([1日目のセル],0,[溜まっているデータ数]-1,1,MIN([溜まっているデータ数],[平均算出日数])*-1))
指定した平均算出日数分以上データが溜まっていればこれを用いた直近の数日の平均が算出される。
それ未満のデータ量しかない場合は溜まっているデータだけで平均の計算を行う。
OFFSET関数により最新のデータが入っているセルを計算対象範囲の始点とし、そこから平均算出日数分（あるいはデータ量分）左側に計算対象範囲を拡大する。
過去n日の日数変更も楽々
個人的にOFFSET関数を自在に使いこなすことがロジカルにExcel関数を使いこなす第一歩だと考えています。一見面倒に見えますが、分かってしまえばとても単純で柔軟、そして強力な関数なので、是非マスターすることをオススメいたします。