エクセルマクロの配列研究 1

2021/11/14 09:16

ナンバーズの分析でマクロの計算は都度直接セルを指定するとマクロの速度が分単位に成って来ました。下の記事のマクロも結構時間が掛かります。
ナンバーズ3 の各属性（5803回） - 趣味のエクセルで当てよう！ロト・ナンバーズ

マクロは配列を使うと劇的に早くなるそうです。

これはメモリーにデータを入れて、計算もメモリー上で行い最後にセルに出力するので早くなるそうです。（セルを選択すると時間が掛かる）

F列の5816件のデータを0～9に分けたら0.176秒　(core-i7-8550u ノートPCで測定）
（一番上の表　H列から　A列の数字表示　思考錯誤中のテストマクロで計算）

思考錯誤中のテストマクロ　

Sub データ()
Dim allmydataArr As Variant
allmydataArr = Range("a1").CurrentRegion.Value
Range("h1:q3000").Clear

Dim starttime As Single, stoptime As Single
starttime = Timer

Dim i As Long, j As Long, jj As Long,ms As Long, gok(9, 2000) As Long
Dim k0 As Long, k1 As Long, k2 As Long, k3 As Long, k4 As Long
Dim k5 As Long, k6 As Long,k7 As Long, k8 As Long

For i = LBound(allmydataArr, 1) To UBound(allmydataArr, 1)

ms = allmydataArr(i, 6)

Select Case ms

Case 0: k0 = k0 + 1: gok(0, k0) = allmydataArr(i, 1)
Case 1: k1 = k1 + 1: gok(1, k1) = allmydataArr(i, 1)
Case 2: k2 = k2 + 1: gok(2, k2) = allmydataArr(i, 1)
Case 3: k3 = k3 + 1: gok(3, k3) = allmydataArr(i, 1)
Case 4: k4 = k4 + 1: gok(4, k4) = allmydataArr(i, 1)
Case 5: k5 = k5 + 1: gok(5, k5) = allmydataArr(i, 1)
Case 6: k6 = k6 + 1: gok(6, k6) = allmydataArr(i, 1)
Case 7: k7 = k7 + 1: gok(7, k7) = allmydataArr(i, 1)
Case 8: k8 = k8 + 1: gok(8, k8) = allmydataArr(i, 1)
Case 9: k9 = k9 + 1: gok(9, k9) = allmydataArr(i, 1)

End Select

Next i

For jj = k0 To 1 Step -1
Cells(2001 - jj, 8) = gok(0, k0 - jj + 1)
Next jj

For jj = k1 To 1 Step -1
Cells(2001 - jj, 9) = gok(1, k1 - jj + 1)
Next jj

For jj = k2 To 1 Step -1
Cells(2001 - jj, 10) = gok(2, k2 - jj + 1)
Next jj

For jj = k3 To 1 Step -1
Cells(2001 - jj, 11) = gok(3, k3 - jj + 1)
Next jj

For jj = k4 To 1 Step -1
Cells(2001 - jj, 12) = gok(4, k4 - jj + 1)
Next jj

For jj = k5 To 1 Step -1
Cells(2001 - jj, 13) = gok(5, k5 - jj + 1)
Next jj

For jj = k6 To 1 Step -1
Cells(2001 - jj, 14) = gok(6, k6 - jj + 1)
Next jj

For jj = k7 To 1 Step -1
Cells(2001 - jj, 15) = gok(7, k7 - jj + 1)
Next jj

For jj = k8 To 1 Step -1
Cells(2001 - jj, 16) = gok(8, k8 - jj + 1)
Next jj

For jj = k9 To 1 Step -1
Cells(2001 - jj, 17) = gok(9, k9 - jj + 1)
Next jj