配列変数に格納したデータを計算する方法はありますか？

質問

簡単な例ですが、例えばB列にあるデータの平均値を求めるときに以下のようにしています。
sub 平均計算()
　　Dim X(1 To 1000, 1 To 1) As Variant, i as Integer
　　For i = 10 To 1000
　　　　X(i, 1) = WorksheetFunction.Average _
　　　　　　　　　(Range(Cells(i - 9, 2), Cells(i, 2)))
　　Next
　　Range(Cells(1, 1), Cells(1000, 1)) = X
End Sub

　ここで処理速度改善のため、B列のデータを別の配列変数Yに格納してから平均値を求めるというようなことをしたいのですが、そんなことは可能でしょうか？イメージとしてはこんな感じです。

　　Y = Range(Cells(1, 2), Cells(1000, 2))
　　for i = 1 to 1000
　　　　X(i, 1) = WorksheetFunction.Average _
　　　　　　　　　(Range(Y(i - 9, 2), Y(i, 2)))
　　Next

　当然これはエラーになってしまいますが、このようなことを可能にする方法があれば、どなたか教えてください！よろしくお願いします。

回答No.7

KenKen_SP

こんにちは。

配列を使えば必ず高速化できるわけではないです。ご質問文の
例だと下記の方が早いと思いますよ。

しかし、コンマ何秒とか数秒の高速化が必要な状況なのですか？

私なら体感できないほどの高速化なら、ソースの可読性を優先
させますが。。

Sub TestProc()
　　With Range("A10:A1000")
　　　　.FormulaR1C1 = "=AVERAGE(R[-9]C[1]:RC[1])"
　　　　.Value = .Value
　　End With
End Sub

回答No.6

Gocho_

セル範囲は配列ではないVariant型に代入することが出来ます。
ちなみ処理速度は、セル参照型と変数参照型で約2倍の差が出ました。

私の環境でっていう条件ですが・・・・

Option Explicit

Sub Test()
Dim X(1 To 1000, 1 To 1) As Variant
Dim Y As Variant
Dim i As Integer
Dim j As Integer
Dim STime As Single

'処理開始時間の処理
STime = Timer

'連続したセル範囲の値を変数に代入
Y = Range("B1:B1000").Value

'処理時間が早すぎて比較しづらいため、同じ処理を100回繰り返す
For j = 0 To 100
For i = 10 To 1000
'変数に格納した値を参照(参考値 0.34375 秒)
X(i, 1) = WorksheetFunction.Average(Y(i, 1), Y(i - 1, 1), Y(i - 2, 1), Y(i - 3, 1), Y(i - 4, 1), Y(i - 5, 1), Y(i - 6, 1), Y(i - 7, 1), Y(i - 8, 1), Y(i - 9, 1))
'セルの値を参照(参考値 0.7192383 秒)
'X(i, 1) = WorksheetFunction.Average(Range(Cells(i - 9, 2), Cells(i, 2)))
Next i
Next j

Sheet1.Range(Cells(1, 1), Cells(1000, 1)) = X
Debug.Print Timer - STime

End Sub

<蛇足>
処理速度は速いですが、コーディングの観点から言えば、褒められません。
視認性が著しく低下し、何をしているのかがわかりにくくなります。

コードを作成者しか見ない場合は問題ないでしょうが、
そうでない場合はコメントでなぜこんな書き方をしているのか
わかりやすく理由を記述しておくとよいと思います

回答No.5

end-u

>hige_082さん
>前者であれば、配列や関数を使用するより、シンプルなものの方が・・・
ぉおー。なるほど、確かに速いですね。

Sub try5()
　　Dim v
　　Dim w
　　Dim x(10 To n, 1 To 1)
　　Dim i As Long
　　Dim t As Single

　　t = Timer
　　v = Range("A1").Resize(n).Value
　　For i = 1 To 10
　　　　w = w + v(i, 1)
　　Next
　　x(10, 1) = w / 10
　　For i = 11 To n
　　　　w = w + v(i, 1) - v(i - 10, 1)
　　　　x(i, 1) = w / 10
　　Next
　　Range("G10").Resize(n - 9).Value = x
　　Debug.Print "try5", Timer - t
End Sub

ありがとうございます。勉強になりましたm(_ _)m

#また修行のタビに出なくちゃ...

回答No.4

hige_082

処理速度改善？それとも配列の練習？
前者であれば、配列や関数を使用するより、シンプルなものの方が・・・

Sub test()
Dim i, ii As Integer
Dim x As Integer
ii = 0
x = 0
For i = 1 To 1000
x = x + Cells(i, 2)
ii = ii + 1
Next
MsgBox x / ii
End Sub

速いかなと思っただけです
忘れてください　お邪魔しました

お礼

確かにシンプルで速いですね。参考にさせてもらいます。皆さんに回答して頂く度にいろいろなやり方があることに気付かされます。ありがとうございます！

回答No.3

end-u

ぁー…すみません、ムダなLoopしてました。
'-------------------------------------------------
Sub try3改()
　　Dim v
　　Dim w(0 To 9)
　　Dim x(10 To n, 1 To 1)
　　Dim i As Long
　　Dim j As Long
　　Dim t As Single

　　t = Timer
　　v = Range("A1").Resize(n).Value
　　With WorksheetFunction
　　　　For i = 0 To 9
　　　　　　w(i) = v(i + 1, 1)
　　　　Next
　　　　x(10, 1) = .Average(w)
　　　　For i = 11 To n - 9 Step 10
　　　　　　For j = 0 To 9
　　　　　　　　w(j) = v(i + j, 1)
　　　　　　　　x(i + j, 1) = .Average(w)
　　　　　　Next
　　　　Next
　　End With
　　Range("F10").Resize(n - 9).Value = x
　　Debug.Print "try3改", Timer - t
End Sub
'-------------------------------------------------

お礼

多くの例を示して頂いてありがとうございます。いろいろとやり方があるんですね。これを参考にして検証させてもらいます。どうもありがとうございました！

回答No.2

end-u

別配列に取得した後に、もうひとつ別サイズの配列にLoopで格納し計算させる事はできますが......
いろいろ試してみるのもいいかもしれませんね。

Option Explicit
Const n = 60000 'テストデータ行数
'-------------------------------------------------
Sub pre() 'テストシート作成
　　With Sheets.Add.Range("A1").Resize(n)
　　　　.Formula = "=rand()*" & n
　　　　.Value = .Value
　　End With
End Sub
'-------------------------------------------------
Sub try1()
　　Dim x(10 To n, 1 To 1) As Variant
　　Dim i As Long
　　Dim t As Single

　　t = Timer
　　For i = 10 To n
　　　　x(i, 1) = WorksheetFunction.Average _
　　　　　　　　　(Range(Cells(i - 9, 1), Cells(i, 1)))
　　Next
　　Range("B10").Resize(n - 9).Value = x
　　Debug.Print "try1", Timer - t
End Sub
'-------------------------------------------------
Sub try2()
　　Dim r As Range
　　Dim x(0 To n - 10, 1 To 1)
　　Dim i As Long
　　Dim t As Single

　　t = Timer
　　Set r = Range("A1:A10")
　　With WorksheetFunction
　　　　For i = 0 To UBound(x)
　　　　　　x(i, 1) = .Average(r.Offset(i))
　　　　Next
　　End With
　　Range("C10").Resize(n - 9).Value = x
　　Set r = Nothing
　　Debug.Print "try2", Timer - t
End Sub
'-------------------------------------------------
Sub try3()
　　Dim v
　　Dim w(0 To 9)
　　Dim x(1 To n - 9, 1 To 1)
　　Dim i As Long
　　Dim j As Long
　　Dim t As Single

　　t = Timer
　　v = Range("A1").Resize(n).Value
　　With WorksheetFunction
　　　　For i = 1 To n - 9
　　　　　　For j = 0 To 9
　　　　　　　　w(j) = v(i + j, 1)
　　　　　　Next
　　　　　　x(i, 1) = .Average(w)
　　　　Next
　　End With
　　Range("D10").Resize(n - 9).Value = x
　　Debug.Print "try3", Timer - t
End Sub
'-------------------------------------------------
Sub try4()
　　Dim t As Single

　　t = Timer
　　With Range("E10").Resize(n - 9)
　　　　.Formula = "=average(A1:A10)"
　　　　.Value = .Value
　　End With
　　Debug.Print "try4", Timer - t
End Sub

回答No.1

n-jun

>Range(Y(i - 9, 2), Y(i, 2))
配列Ｙがマイナス（１－９＝－８）になるのは変ですし、Ｙには値が入りますので、
Range型にもならないのでは？

10行を1行ずつずらした場合、1～1000行なら回数は991回だと思うのですが・・・
Sub test()
Dim r As Range
Dim i As Integer
Dim v(1 To 991, 1 To 2) As Variant

Set r = Range("B1:B10")

For i = 1 To 991

v(i, 1) = WorksheetFunction.Average(r)
v(i, 2) = r.Address
Set r = r.Offset(1)

Next
Range("C1:D991").Value = v
End Sub
違ってたらスル～して下さい。

補足

おっしゃる通りWorksheetFunction.Average(Range(Y(i - 9, 2), Y(i, 2)))
という表記は実行するとエラーになります。
このようなことが出来ればいいな、ということで記載しました。

教えていただいた方法を参考にして検証してみます。