エクセルマクロの配列研究 1
ナンバーズの分析でマクロの計算は都度直接セルを指定するとマクロの速度が分単位に成って来ました。下の記事のマクロも結構時間が掛かります。
ナンバーズ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
If i = 10 Then Range("t2") = i
stoptime = Timer
MsgBox "件数は" & k1 & " 時間は" & Round(stoptime - starttime, 3)
End Sub
k0~ k9の変数は配列変数としてk(0)~ k(9)にすると下の様に簡単になる。?
For n=0 to 9
For jj = k(n) To 1 Step -1
Cells(2001 - jj, 8+n) = gok(n, k(n) - jj + 1)
Next jj
Next n