ムラゴン ホーム
上の様に計算出力（ナンバーズ3の各属性と同様）
今までのファイルに入れて、配列で11秒は出力に使ってないからですが、ちょっと残念。テストファイルで0.5秒はファイルサイズが小さいから？
（前の配列不使用マクロは60秒以上でした。）
配列１部使用マクロ もっと改善したいですが、良く分からない？
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
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)
Select Case gk '合計
Case 0: k(0) = k(0) + 1: gok(0, k(0)) = allmydataArr(i, 1): tt(0) = gok(0, k(0))
Case 1: k(1) = k(1) + 1: gok(1, k(1)) = allmydataArr(i, 1): tt(1) = gok(1, k(1))
Case 2: k(2) = k(2) + 1: gok(2, k(2)) = allmydataArr(i, 1): tt(2) = gok(2, k(2))
Case 3: k(3) = k(3) + 1: gok(3, k(3)) = allmydataArr(i, 1): tt(3) = gok(3, k(3))
Case 4: k(4) = k(4) + 1: gok(4, k(4)) = allmydataArr(i, 1): tt(4) = gok(4, k(4))
Case 5: k(5) = k(5) + 1: gok(5, k(5)) = allmydataArr(i, 1): tt(5) = gok(5, k(5))
Case 6: k(6) = k(6) + 1: gok(6, k(6)) = allmydataArr(i, 1): tt(6) = gok(6, k(6))
Case 7: k(7) = k(7) + 1: gok(7, k(7)) = allmydataArr(i, 1): tt(7) = gok(7, k(7))
Case 8: k(8) = k(8) + 1: gok(8, k(8)) = allmydataArr(i, 1): tt(8) = gok(8, k(8))
Case 9: k(9) = k(9) + 1: gok(9, k(9)) = allmydataArr(i, 1): tt(9) = gok(9, k(9))
Case 10: k(10) = k(10) + 1: gok(10, k(10)) = allmydataArr(i, 1): tt(10) = gok(10, k(10))
Case 11: k(11) = k(11) + 1: gok(11, k(11)) = allmydataArr(i, 1): tt(11) = gok(11, k(11))
Case 12: k(12) = k(12) + 1: gok(12, k(12)) = allmydataArr(i, 1): tt(12) = gok(12, k(12))
Case 13: k(13) = k(13) + 1: gok(13, k(13)) = allmydataArr(i, 1): tt(13) = gok(13, k(13))
Case 14: k(14) = k(14) + 1: gok(14, k(14)) = allmydataArr(i, 1): tt(14) = gok(14, k(14))
Case 15: k(15) = k(15) + 1: gok(15, k(15)) = allmydataArr(i, 1): tt(15) = gok(15, k(15))
Case 16: k(16) = k(16) + 1: gok(16, k(16)) = allmydataArr(i, 1): tt(16) = gok(16, k(16))
Case 17: k(17) = k(17) + 1: gok(17, k(17)) = allmydataArr(i, 1): tt(17) = gok(17, k(17))
Case 18: k(18) = k(18) + 1: gok(18, k(18)) = allmydataArr(i, 1): tt(18) = gok(18, k(18))
Case 19: k(19) = k(19) + 1: gok(19, k(19)) = allmydataArr(i, 1): tt(19) = gok(19, k(19))
Case 20: k(20) = k(20) + 1: gok(20, k(20)) = allmydataArr(i, 1): tt(20) = gok(20, k(20))
Case 21: k(21) = k(21) + 1: gok(21, k(21)) = allmydataArr(i, 1): tt(21) = gok(21, k(21))
Case 22: k(22) = k(22) + 1: gok(22, k(22)) = allmydataArr(i, 1): tt(22) = gok(22, k(22))
Case 23: k(23) = k(23) + 1: gok(23, k(23)) = allmydataArr(i, 1): tt(23) = gok(23, k(23))
Case 24: k(24) = k(24) + 1: gok(24, k(24)) = allmydataArr(i, 1): tt(24) = gok(24, k(24))
Case 25: k(25) = k(25) + 1: gok(25, k(25)) = allmydataArr(i, 1): tt(25) = gok(25, k(25))
Case 26: k(26) = k(26) + 1: gok(26, k(26)) = allmydataArr(i, 1): tt(26) = gok(26, k(26))
Case 27: k(27) = k(27) + 1: gok(27, k(27)) = allmydataArr(i, 1): tt(27) = gok(27, k(27))
End Select
gk = allmydataArr(i, 3) + 27 '1～8奇遇
Select Case gk
Case 28: k(28) = k(28) + 1: gok(28, k(28)) = allmydataArr(i, 1): tt(28) = gok(28, k(28))
Case 29: k(29) = k(29) + 1: gok(29, k(29)) = allmydataArr(i, 1): tt(29) = gok(29, k(29))
Case 30: k(30) = k(30) + 1: gok(30, k(30)) = allmydataArr(i, 1): tt(30) = gok(30, k(30))
Case 31: k(31) = k(31) + 1: gok(31, k(31)) = allmydataArr(i, 1): tt(31) = gok(31, k(31))
Case 32: k(32) = k(32) + 1: gok(32, k(32)) = allmydataArr(i, 1): tt(32) = gok(32, k(32))
Case 33: k(33) = k(33) + 1: gok(33, k(33)) = allmydataArr(i, 1): tt(33) = gok(33, k(33))
Case 34: k(34) = k(34) + 1: gok(34, k(34)) = allmydataArr(i, 1): tt(34) = gok(34, k(34))
Case 35: k(35) = k(35) + 1: gok(35, k(35)) = allmydataArr(i, 1): tt(35) = gok(35, k(35))
End Select
gk = allmydataArr(i, 4) + 35 '1～8大小
Select Case gk
Case 36: k(36) = k(36) + 1: gok(36, k(36)) = allmydataArr(i, 1): tt(36) = gok(36, k(36))
Case 37: k(37) = k(37) + 1: gok(37, k(37)) = allmydataArr(i, 1): tt(37) = gok(37, k(37))
Case 38: k(38) = k(38) + 1: gok(38, k(38)) = allmydataArr(i, 1): tt(38) = gok(38, k(38))
Case 39: k(39) = k(39) + 1: gok(39, k(39)) = allmydataArr(i, 1): tt(39) = gok(39, k(39))
Case 40: k(40) = k(40) + 1: gok(40, k(40)) = allmydataArr(i, 1): tt(40) = gok(40, k(40))
Case 41: k(41) = k(41) + 1: gok(41, k(41)) = allmydataArr(i, 1): tt(41) = gok(41, k(41))
Case 42: k(42) = k(42) + 1: gok(42, k(42)) = allmydataArr(i, 1): tt(42) = gok(42, k(42))
Case 43: k(43) = k(43) + 1: gok(43, k(43)) = allmydataArr(i, 1): tt(43) = gok(43, k(43))
End Select
gk = allmydataArr(i, 5) + 44 '0～18ミニプラス
Select Case gk
Case 44: k(44) = k(44) + 1: gok(44, k(44)) = allmydataArr(i, 1): tt(44) = gok(44, k(44))
Case 45: k(45) = k(45) + 1: gok(45, k(45)) = allmydataArr(i, 1): tt(45) = gok(45, k(45))
Case 46: k(46) = k(46) + 1: gok(46, k(46)) = allmydataArr(i, 1): tt(46) = gok(46, k(46))
Case 47: k(47) = k(47) + 1: gok(47, k(47)) = allmydataArr(i, 1): tt(47) = gok(47, k(47))
Case 48: k(48) = k(48) + 1: gok(48, k(48)) = allmydataArr(i, 1): tt(48) = gok(48, k(48))
Case 49: k(49) = k(49) + 1: gok(49, k(49)) = allmydataArr(i, 1): tt(49) = gok(49, k(49))
Case 50: k(50) = k(50) + 1: gok(50, k(50)) = allmydataArr(i, 1): tt(50) = gok(50, k(50))
Case 51: k(51) = k(51) + 1: gok(51, k(51)) = allmydataArr(i, 1): tt(51) = gok(51, k(51))
Case 52: k(52) = k(52) + 1: gok(52, k(52)) = allmydataArr(i, 1): tt(52) = gok(52, k(52))
Case 53: k(53) = k(53) + 1: gok(53, k(53)) = allmydataArr(i, 1): tt(53) = gok(53, k(53))
Case 54: k(54) = k(54) + 1: gok(54, k(54)) = allmydataArr(i, 1): tt(54) = gok(54, k(54))
Case 55: k(55) = k(55) + 1: gok(55, k(55)) = allmydataArr(i, 1): tt(55) = gok(55, k(55))
Case 56: k(56) = k(56) + 1: gok(56, k(56)) = allmydataArr(i, 1): tt(56) = gok(56, k(56))
Case 57: k(57) = k(57) + 1: gok(57, k(57)) = allmydataArr(i, 1): tt(57) = gok(57, k(57))
Case 58: k(58) = k(58) + 1: gok(58, k(58)) = allmydataArr(i, 1): tt(58) = gok(58, k(58))
Case 59: k(59) = k(59) + 1: gok(59, k(59)) = allmydataArr(i, 1): tt(59) = gok(59, k(59))
Case 60: k(60) = k(60) + 1: gok(60, k(60)) = allmydataArr(i, 1): tt(60) = gok(60, k(60))
Case 61: k(61) = k(61) + 1: gok(61, k(61)) = allmydataArr(i, 1): tt(61) = gok(61, k(61))
Case 62: k(62) = k(62) + 1: gok(62, k(62)) = allmydataArr(i, 1): tt(62) = gok(62, k(62))
End Select
gk = allmydataArr(i, 6) + 63 '0～9ミニスペース
Select Case gk
Case 63: k(63) = k(63) + 1: gok(63, k(63)) = allmydataArr(i, 1): tt(63) = gok(63, k(63))
Case 64: k(64) = k(64) + 1: gok(64, k(64)) = allmydataArr(i, 1): tt(64) = gok(64, k(64))
Case 65: k(65) = k(65) + 1: gok(65, k(65)) = allmydataArr(i, 1): tt(65) = gok(65, k(65))
Case 66: k(66) = k(66) + 1: gok(66, k(66)) = allmydataArr(i, 1): tt(66) = gok(66, k(66))
Case 67: k(67) = k(67) + 1: gok(67, k(67)) = allmydataArr(i, 1): tt(67) = gok(67, k(67))
Case 68: k(68) = k(68) + 1: gok(68, k(68)) = allmydataArr(i, 1): tt(68) = gok(68, k(68))
Case 69: k(69) = k(69) + 1: gok(69, k(69)) = allmydataArr(i, 1): tt(69) = gok(69, k(69))
Case 70: k(70) = k(70) + 1: gok(70, k(70)) = allmydataArr(i, 1): tt(70) = gok(70, k(70))
Case 71: k(71) = k(71) + 1: gok(71, k(71)) = allmydataArr(i, 1): tt(71) = gok(71, k(71))
Case 72: k(72) = k(72) + 1: gok(72, k(72)) = allmydataArr(i, 1): tt(72) = gok(72, k(72))
End Select
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
Call saikeisanon
stoptime = Timer
MsgBox "件数は" & k(13) & " 時間は" & Round(stoptime - starttime, 4)
End Sub
-------------------------------------------------------------------
gk = allmydataArr(i, 2)
Select Case gk '合計
Case 0: k(0) = k(0) + 1: gok(0, k(0)) = allmydataArr(i, 1): tt(0) = gok(0, k(0))
上の式は
0の部分をgkに置き換えたら良いのかな？
よくよく考えたら？
Select Case は使わないで良いのかな？
Case 0: k(0) = k(0) + 1: gok(0, k(0)) = allmydataArr(i, 1): tt(0) = gok(0, k(0)) は？
下の式になるのかな？
k(gk) = k(gk) + 1: gok(gk, k(gk)) = allmydataArr(i, 1): tt(gk) = gok(gk, k(gk))
上は一行にしてるが、下と同じです。
k(gk) = k(gk) + 1
gok(gk, k(gk)) = allmydataArr(i, 1)
tt(gk) = gok(gk, k(gk))
前回の「エクセルマクロの配列研究 1」 をもっとシンプルにして、さらに間隔等も計算して見ました。エクセルマクロの配列研究 1 - 趣味のエクセルで当てよう！ロト・ナンバーズ
上の様に出現間隔、件数、などを出して見ました。
F列の0～9までの5828回分の集計（H列0 Q列9で出力）
処理時間もほぼ同じでした。(core-i7-8550u ノートPCで測定）
Sub データ() ’思考錯誤中のテストマクロを改良したマクロ
Dim allmydataArr As Variant
allmydataArr = Range("a1").CurrentRegion.Value
Range("h2:q2004").Clear
Dim starttime As Single, stoptime As Single
starttime = Timer’処理時間測定開始
Dim i As Long, j As Long, ms As Long, gok(27, 2000) As Long, gokk(27, 2000) As Long
Dim r As Long, s As Long, t(9) As Long, k(9) As Long
For i = LBound(allmydataArr, 1) To UBound(allmydataArr, 1)
ms = allmydataArr(i, 6)
Select Case ms '各回号を集計0～9に対応して
Case 0: k(0) = k(0) + 1: gok(0, k(0)) = allmydataArr(i, 1): t(0) = gok(0, k(0))
Case 1: k(1) = k(1) + 1: gok(1, k(1)) = allmydataArr(i, 1): t(1) = gok(1, k(1))
Case 2: k(2) = k(2) + 1: gok(2, k(2)) = allmydataArr(i, 1): t(2) = gok(2, k(2))
Case 3: k(3) = k(3) + 1: gok(3, k(3)) = allmydataArr(i, 1): t(3) = gok(3, k(3))
Case 4: k(4) = k(4) + 1: gok(4, k(4)) = allmydataArr(i, 1): t(4) = gok(4, k(4))
Case 5: k(5) = k(5) + 1: gok(5, k(5)) = allmydataArr(i, 1): t(5) = gok(5, k(5))
Case 6: k(6) = k(6) + 1: gok(6, k(6)) = allmydataArr(i, 1): t(6) = gok(6, k(6))
Case 7: k(7) = k(7) + 1: gok(7, k(7)) = allmydataArr(i, 1): t(7) = gok(7, k(7))
Case 8: k(8) = k(8) + 1: gok(8, k(8)) = allmydataArr(i, 1): t(8) = gok(8, k(8))
Case 9: k(9) = k(9) + 1: gok(9, k(9)) = allmydataArr(i, 1): t(9) = gok(9, k(9))
End Select
Next i
For s = 0 To 9 '回号から間隔にする
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 s = 0 To 9 '間隔などをシートに出力
For jj = k(s) To 1 Step -1
Cells(2001 - jj, 8 + s) = gokk(s, k(s) - jj + 1)
Next jj
Cells(2001, s + 8) = k(s) '各出現回数をシートに出力
Cells(2002, s + 8) = UBound(allmydataArr, 1) - t(s) '現在の休み間隔
Cells(2004, s + 8) = t(s) '最終出現回号
Next s
stoptime = Timer ’処理時間測定終了
MsgBox "件数は" & k(1) & " 時間は" & Round(stoptime - starttime, 4)
End Sub