エクセルについて

質問

offset関数の事で質問させて下さい。

例えば、A列にある数値から移動平均を求めるためにoffset関数を使い日数をいろいろ変えていますが、３日移動平均、5日移動平均というように日にちを変えていくと3日移動平均の数値はそのままで5日目以降の数値が変わっていきます。

多分、期間を長くしていく場合に前の数値がゼロとなり表示されなくなれば良いと思うのですが、今使用している以下の数式にどう追加で数式を足していけば良いのでしょうか?

=AVERAGE(OFFSET(A5,1-C$1,0):A5)

よろしくお願いします。

回答No.1

keithin · ベストアンサー

Ｂ１に
=IF($C$1="","",IF(ROW(B1)>=$C$1,AVERAGE(OFFSET(A1,0,0,-$C$1,1)),""))
として下向けにコピーして埋めてみます。

お礼

どうもありがとうございます。　上手く出来ました。　助かりました。

回答No.3

HRI-Sankou

すみません
合計のみ計算していました。

Ｃ２セルに　=SUMIF(A$2:A$21,E2,B$2:B$21)/COUNTIF(A$2:A$21,E2)

を、入力しなおして下さい。
以下にコピーは、同じです。
Ｅ２セルは、必要数のみ下にコピーです。
Ｄ２セルの数字が、過去の日数指定になります。
なお、Ｃ２セルが、エラー表示になるのが否なら

=IF(SUM(B$2:B$21)>0,SUMIF(A$2:A$21,E2,B$2:B$21)/COUNTIF(A$2:A$21,E2),"")

にしてみてください。

ごめんなさい。

回答No.2

HRI-Sankou

Ａ　　　Ｂ　　　　Ｃ　　　Ｄ　　　Ｅ
１　過去日　移動距離　平均値　何日前 引数
２　　下Ａ 　100　　　下Ｃ 5　　下Ｅ
３　　-6 　　90 　　250 　　4 >=-4
４　　-5 　　80 　　180 　　3 >=-3
５　　-4 　　70 　　120 　　2 >=-2
６　　-3 　　60 　　70 　　1 >=-1
７　　-2 　　50 　　30 　　0 >=-0
８　　-1 　　40
９　　 0 　　30

式Ａ　=IF(B2>=1,IF(B3=0,0,IF(A3<0,A3-1,-1)),"")
式Ｃ　=SUMIF(A$2:A$21,E2,B$2:B$21)
式Ｅ　=">=-"&D2

勝手解釈ですが、
過去の指定日数分の平均を計算するとして書いてみました。

Ａ列に何日前か？式Ａにより入力されます
Ｃ列にＤで指定した日数前からの平均値が式Ｃにより入力されます
Ｅ列に式Ｃに必要な引数がＤにより入力されます
２行目の式Ａ，式Ｃ，式Ｅを２１行目までコピーすると
計算できると思います。

お礼

丁寧に説明して頂きありがとうございます。　参考させて頂き試してみます。