average関数の範囲を、セル数で指定したいのですが

質問

初めて質問させていただきます。

現在、データ整理するためにexcel(2003)でマクロを組んでいます（初心者です）。その際、「あるセルから下方○○個のセルの平均値を計算させる」方法がわからずに困っております。具体的には、

A1：平均値算出に用いるデータ数
A2～A5000　生データ

上記のデータがある状態で、B2-B5000までに算出した平均値を書き込ませたいと思っています。A1の数値はファイル毎に異なります。

例えば、
A1が100の場合はB2=average(A2:A101), B3=average(A3:A102)...
A1が50の場合はB2=average(A2:A51), B3=average(A3:A52)...

というような処理を想定しています。処理数が少なければ手作業でやるのですが、いかんせん数が多くて辟易しております。

どなたか、お力をお貸し下さい。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

>B2-B5000までに算出した平均値を書き込ませたいと思っています。

それは、100行間の平均、50行間の平均でしたら、

>A1が100の場合はB2=average(A2:A101), B3=average(A3:A102)...
>A1が50の場合はB2=average(A2:A51), B3=average(A3:A52)...

つまり、A1 が100で、生データが5000行あるなら、5000-99=4,901 行まで
A1 が50なら、5000-49=4,951 行までの、それぞれのデータ範囲になるはずです。その範囲を越えたら、それ以降は正しい平均値ではないように思います。

なお、数式なら、
=AVERAGE(OFFSET(A1,1,,$A$1))
となるはずです。

'標準モジュール
'--------------------------------------------------------

Sub AverageOutPut()

If IsNumeric(Range("A1").Value) And Range("A1").Value > 0 Then
　 Columns(2).ClearContents 'B列のデータ削除
　 With Range(Range("A1").Offset(1), Range("A1").End(xlDown).Offset(-Range("A1").Value + 1)).Offset(, 1)
　　　 .FormulaLocal = "=AVERAGE(RC[-1]:R[" & CStr(Range("A1").Value - 1) & "]C[-1])"
　　　 .Value = .Value '定数化
　 End With
End If
End Sub
'--------------------------------------------------------

お礼

詳細な内容、ありがとうございます。一応自分で作ったやつでも動くんですが、私のは色々と文法を無視してるので美しさが全くありません。スキルのある方の作品を見せて頂くと、本当に参考になります。

＞その範囲を越えたら、それ以降は正しい平均値ではないように思います。

後ろの方は、実際に利用する部分からははずす予定です。お気にかけて頂いて恐縮です。ありがとうございました。

回答No.1

mshr1962

B2=AVERAGE(OFFSET(A2,0,0,IF(ROW()+$A$1>5000,5001-ROW(),$A$1),1))
でどうでしょうか？

お礼

早速のご回答、ありがとうございました。ご呈示頂いた方法で無事に組めました。おかげで仕事効率が５割り増しになった気がします（笑）。本当にありがとうございました。