ナンバーズ3 各間隔配列計算

2023/05/04 08:15

合計などの出現間隔をエクセルマクロで出して見た
表は下から最新データから配列計算（6202回分）

（元データ）

思考錯誤のエクセルマクロ

Sub 合計奇遇大小ミニデータ()
Dim starttime As Single, stoptime As Single
starttime = Timer

Dim i As Long, j As Long, jj As Long, gk As Long, gok(100, 2000) As Long, gokk(100, 2000) As Long
Dim t As Long, s As Long, r As Long, kaigo As Long
Dim k(100) As Long, tt(100) As Long, g(8) As Long
Sheets("原本").Range("gr10:lh5504").ClearContents '出力表示部のクリア
kaigo = Cells(Rows.Count, 4).End(xlUp).Row

Range("gh10") = "=a3 "
Range("gi10") = "=CL3 " 'データを参照する
Range("gj10") = "=CN3 "
Range("gk10") = "=DG3 "
Range("gl10") = "=DA3 "
Range("gm10") = "=DB3 "

Range("gh10:gm10").Copy Range(Cells(11, 190), Cells(7 + kaigo, 195)) 'データを回号分コピー

Dim allmydataArr As Variant
allmydataArr = Range("gh10").CurrentRegion.Value

Call saikeisanoff

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

gk = allmydataArr(i, 2) '合計

k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))

gk = allmydataArr(i, 3) + 27 '1～8奇遇

k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))

gk = allmydataArr(i, 4) + 35 '1～8大小
k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))

gk = allmydataArr(i, 5) + 44 '0～18ミニプラス

k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))

gk = allmydataArr(i, 6) + 63 '0～9ミニスペース

k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))

Next i

For s = 0 To 72 '回号から間隔計算
For j = 1 To k(s)
If j = 1 Then
gokk(s, j) = gok(s, j)
Else
gokk(s, j) = gok(s, j) - gok(s, j - 1)
End If
Next j
Next s

For r = 0 To 27 '合計各回号出力　最新回から
For jj = k(r) To 1 Step -1
Cells(5501 - jj, 200 + r) = gokk(r, k(r) - jj + 1)
Next jj
Next r

For t = 0 To 27
Cells(5501, t + 200) = k(t) '出現回数
Cells(5502, t + 200) = UBound(allmydataArr, 1) - tt(t) '現状はまり回数
Cells(5504, t + 200) = tt(t) '最終出現回号
Next t

For r = 28 To 35 '奇遇各回号出力
For jj = k(r) To 1 Step -1
Cells(5501 - jj, 213 + r) = gokk(r, k(r) - jj + 1)
Next jj
Next r

For t = 28 To 35
Cells(5501, t + 213) = k(t) '出現回数
Cells(5502, t + 213) = UBound(allmydataArr, 1) - tt(t)
Cells(5504, t + 213) = tt(t)
Next t

For r = 36 To 43 '大小各回号出力
For jj = k(r) To 1 Step -1
Cells(5501 - jj, 225 + r) = gokk(r, k(r) - jj + 1)
Next jj
Next r

For t = 36 To 43
Cells(5501, t + 225) = k(t) '出現回数
Cells(5502, t + 225) = UBound(allmydataArr, 1) - tt(t)
Cells(5504, t + 225) = tt(t)
Next t

For r = 44 To 62 'ミニプラス各回号出力
For jj = k(r) To 1 Step -1
　Cells(5501 - jj, 236 + r) = gokk(r, k(r) - jj + 1)
Next jj
Next r

For t = 44 To 62
Cells(5501, t + 236) = k(t) '出現回数
Cells(5502, t + 236) = UBound(allmydataArr, 1) - tt(t)
Cells(5504, t + 236) = tt(t)
Next t

For r = 63 To 72 'ミニスペース各回号出力
For jj = k(r) To 1 Step -1
　Cells(5501 - jj, 247 + r) = gokk(r, k(r) - jj + 1)
Next jj
Next r

For t = 63 To 72
Cells(5501, t + 247) = k(t) '出現回数
Cells(5502, t + 247) = UBound(allmydataArr, 1) - tt(t)
Cells(5504, t + 247) = tt(t)
Next t

Cells(5501, 320) = 5501 - WorksheetFunction.Max(Range(Cells(5501, 200), Cells(5501, 319)))
Call saikeisanon
stoptime = Timer
MsgBox "件数は" & k(13) & " 時間は" & Round(stoptime - starttime, 4)

End Sub