VBA 配列に格納した値の平均のやり方についてお教え願います

質問

いつもお世話になります。
配列に格納した値の平均のやり方についてお教え願います。
現在、実験測定データの移動平均（下記avg(x,1)）を計算をしたいのですが、
配列に格納した値の平均が思うように計算できず困っております。

a =Range("A1:A20000")‘測定データを配列に格納
avg(x, 1) = Application.WorksheetFunction.average(a(x, 1), a(x + 5, 1))

という記述方法ではa(x, 1)とa(x + 5, 1)の2点の平均になってしまいます。
これをa(x, 1), a(x+1, 1),a(x+2, 1),a(x+3, 1) ,a(x+4, 1) ,a(x+5, 1)の5点の平均が計算できる記述方法はないでしょうか？

○実際の式は下のようなもので移動平均の平均区間yも変化します。
Dim avg(1 To 20000, 1 To 1)

－前式省略－

For y = 1 To 20000

－途中式省略－

a =Range("A1:A20000")‘測定データを配列に格納
For x = 1 To 20000
avg(x, 1) = Application.WorksheetFunction.average(a(x, 1), a(x + y, 1))
‘↑これでは2点の平均値になってしまいます。
Next

－途中式省略－

Next

－後式省略－

○Range(“B1”)＝AVERAGE(Ａ1:OFFSET(A1,y-1,0))
Range(“B2”)＝AVERAGE(Ａ2:OFFSET(A2,y-1,0))　
・・・
というふうにセルに一つずつaverage関数を入力すれば移動平均も計算できるのですが、繰り返し回数が多いため、計算に非常に時間がかかります。そのため上記配列を利用したもので計算したいと思います。

○ ネットでヒントを見つけたのですが↓
Set ran = Range(Range("A1"), Range("A1").End(xlDown))
Range("C4").Value = WorksheetFunction.average(ran)
こう記述すればある範囲内の平均を求められるようですが、
Set ran=　以降の部分をRange(＊＊＊)ではなく、
Set ran = (a(x, 1), a(x + y, 1))　←a(x, 1)～a(x + y, 1)まで、というイメージですが・・・
というふうに範囲を配列で表すやり方を教えて頂けるだけでも結構です。

excel2003です。
以上、よろしくお教え願います。

回答No.1

redfox63 · ベストアンサー

ワークシート関数で手抜きしようとしないで
簡単な平均算出関数を定義すれば済むことではありませんか？

Function myAvg( ar() as integer, nStart, nCount ) as double
　　dim n as integer, dSum as double
　　dSum = 0
　　for n = nStart to nStart + nCount -1
　　　　dSum = dSum + ar(n,1)
　　next
　　myArg = dSum / nCount
End Function

呼び出し側で
dim dRes() as Double
a = Range("A1:A20000").Value
ReDim dRes( 1 to Ubound(a,1), 1 to 1)
for n = 1 to 19900
　　dRes(n) = myAvg( a, n, 100 )
next
Range("B1:B20000").value = dRes
といった具合で ・・・

お礼

ご回答有難うございます。
すみません、平均の出し方はaverage関数しか思いつきませんでした。（私がその程度のレベルとの断りを書き忘れていました。）手抜きのつもりではなかったのですがもっと勉強させて頂きます。
なるほどです。全体的な流れが大変よく分かりました。正直、関数の定義など初めて見る部分が多いのですが、じっくり調べ試させて頂きます。どうもご丁寧に教えていただき有難うございました。

回答No.2

imogasi

エクセルのVBAのことか。標題にエクセルVBAとするように。
ワークシート関数ならエクセルセルの数値（多分セル範囲的に固まっていると思うが）Range("a1:A20")のように表現する。
質問では２セルの平均
Sub test01()
MsgBox WorksheetFunction.Average(Range("A1"), Range("A10"))
End Sub
のようなカタチになっている。
それを
Sub test01()
MsgBox WorksheetFunction.Average(Range("A1:A10"))
End Sub
のようなカタチに持ってゆかないとならない。
Sub test01()
MsgBox WorksheetFunction.Average(Range(Cells(1, "A"), Cells(10, "A")))
End Sub
のような表現が使われる。
ーー
それと配列にデータがある（入れる）なら
入れるときにデータを加算し、件数を変数に記録し、最後に
全合計/件数にするか
Sub test03()
d = 0
t = 0
For i = 1 To 10
If Cells(i, "A") <> "" Then
d = d + 1
t = t + Cells(i, "A")
av = t / d
End If
Next i
MsgBox av
End Sub
に当たるような、その都度今までの合計を件数で割って行くような
ロジックで平均はでますよ。
ーー
もともとエクセルでセルという配列みたいな仕組みがあるのに
配列に入れる必要性はビジネスでの利用では、少ないと思うが。

お礼

ご回答有難うございます。
すみません、標題今後気をつけます。平均の出し方は、平均の基本的な計算方法にて算出すればよいのですね。了解しました。試してみたいと思います。どうもご丁寧に有難うございました。