趣味のエクセルマクロ

ナンバーズ4などの数字選択式宝くじデータ分析用の自作マクロおよびナンバーズ4の各種データリストなどをブログにしています。

マクロ作成方法 2 (N4ボックス集計)

ナンバーズ4のボックス出現集計表を作成します。


(当選番号が2318としたら小さい順に並べて、ボックス番号は1238とします。)



まずは考え方としては、ストレート集計より少し面倒だと言うことです。



処理手順(例) (ストレートと少し違う処理)


1. 当選番号を小さい順に並び変える。


   (1)並び替えるために4つにわける。
      左から1つ目
      左から2つ目
      右から1つ目
      右から2つ目
     
    
   (2)並び替える
      ソートする為、マクロ自動記録使用
      


2. 出力位置計算


      出力位置の補正をする。(例 09の次は11となるので)
      



3. 表とマクロを作成する。


表は上のようにします。10、20、30などの欄は無いですがボックス番号は小さい順に
並び替えたので当然ですね。



  
マクロとしては、いろんなパターンで作れると思いますが?出力結果が同じなら
マクロ作成は人によって違いますが、どれが正解とは言えないでしょうね。


自分にとって分かりやすい作り方が良いと思います。
      
----------------------------------------------------------------------------------------------------------------------



 下のマクロで上の様な表にボックス番号での計算しましたが時間が掛かります。
 ソートとかに時間が掛かるのでしょうね。


 ワークシート関数を使って4つに分けることや、小さい順にするとかしてから計算をし    
 たのが早いかも知れません。


    
Sub n4bx_count() ’N4ボックス番号の集計マクロ


Dim i, sen_hyaku, jyuu_iti, senhyaku, jyuuiti, x_f, y_f  As Integer
 Sheets("出現数").Select
  Range("dp5:fr59").Select ’出力部の値を消す
  Selection.ClearContents


i = 5


Call saikeisanoff ’  再計算等を停止 マクロ作成方法  1 と同様
Do Until Cells(i, 2) = ""
   Cells(1, 120) = Val(Left(Cells(i, 2), 1)) '4つに分ける千桁 セルDP1に
   Cells(1, 121) = Val(Mid(Cells(i, 2), 2, 1)) '4つに分ける百桁
   Cells(1, 122) = Val(Mid(Cells(i, 2), 3, 1)) '4つに分ける十桁
   Cells(1, 123) = Val(Right(Cells(i, 2), 1)) '4つに分ける一桁 セルDS1に
 
  With ActiveWorkbook.Worksheets("出現数").Sort  '小さい順にソートする
        .SetRange Range("DP1:DS1") ’4つに分けたセル
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With


sen_hyaku = Val(Str(Cells(1, 120)) & Str(Cells(1, 121))) ’数字→文字→数字にする千百桁
jyuu_iti = Val(Str(Cells(1, 122)) & Str(Cells(1, 123))) ’数字→文字→数字にする十一桁


Select Case jyuu_iti ’表への出力位置補正


       Case 11 To 19 ’ jyuu_itiが11~19の時 x_fを1にする
         x_f = 1
       Case 22 To 29
         x_f = 3
       Case 33 To 39
         x_f = 6
       Case 44 To 49
         x_f = 10
       Case 55 To 59
         x_f = 15
        Case 66 To 69
         x_f = 21
       Case 77 To 79
          x_f = 28
       Case 88 To 89
         x_f = 36
       Case 99
          x_f = 45
       Case Else ’上記以外の時
        x_f = 0
        
End Select


Select Case sen_hyaku ’表への出力位置補正値


       Case 11 To 19
         y_f = 1
       Case 22 To 29
        y_f = 3
       Case 33 To 39
         y_f = 6
        Case 44 To 49
         y_f = 10
       Case 55 To 59
         y_f = 15
       Case 66 To 69
         y_f = 21
       Case 77 To 79
        y_f = 28
       Case 88 To 89
         y_f = 36
       Case 99
        y_f = 45
       Case Else
        y_f = 0
        
End Select


senhyaku = sen_hyaku + 5 - y_f ’位置補正をして千百桁のセル位置を決める
jyuuiti = jyuu_iti + 120 - x_f ’位置補正をして十一桁のセル位置を決める


Cells(senhyaku, jyuuiti) = Cells(senhyaku, jyuuiti) + 1 ’該当セルで累計計算し表に出力


i = i + 1


Loop


Call saikeisanon ’ストレート集計の時と同様とする
Cells(1, 120).Select
End Sub
      



 Val(Str(Cells(1, 120)) & Str(Cells(1, 121)))について 
Strでセルの数字を文字にする。(2つのセル)
&で2つのセルの文字同志を連結し、さらにValでその文字を1つの数字にする。  




-------------------------------------------------------------------------------------------------------    


ワークシート関数small使用の場合


こちらの方が処理速度が少し早くなります。


Sub n4bx_countk()
Dim i, sen_hyaku, jyuu_iti, senhyaku, jyuuiti, x_f, y_f As Integer
 Sheets("出現数").Select


   Range("dp5:fr59").Select
    Selection.ClearContents


i = 5


Call saikeisanoff


Do Until Cells(i, 2) = ""
   Cells(1, 120) = Val(Left(Cells(i, 2), 1))
   Cells(1, 121) = Val(Mid(Cells(i, 2), 2, 1))
   Cells(1, 122) = Val(Mid(Cells(i, 2), 3, 1))
   Cells(1, 123) = Val(Right(Cells(i, 2), 1))
  
    Cells(1, 126) = "=small(dp1:ds1, 1) & small(dp1:ds1, 2)"
    Cells(1, 127) = "=small(dp1:ds1,3) & small(dp1:ds1,4)"
   
  
sen_hyaku = Cells(1, 126)
jyuu_iti = Cells(1, 127)


Select Case jyuu_iti


       Case 11 To 19
         x_f = 1
       Case 22 To 29
         x_f = 3
       Case 33 To 39
         x_f = 6
        
       Case 44 To 49
         x_f = 10
       Case 55 To 59
         x_f = 15
        
       Case 66 To 69
         x_f = 21
       Case 77 To 79
      
         x_f = 28
       Case 88 To 89
         x_f = 36
       Case 99
         x_f = 45
     
       Case Else
        x_f = 0
        
End Select


Select Case sen_hyaku
       Case 11 To 19
         y_f = 1
       Case 22 To 29
        y_f = 3
       Case 33 To 39
         y_f = 6
        
       Case 44 To 49
         y_f = 10
       Case 55 To 59
         y_f = 15
       Case 66 To 69
         y_f = 21
       Case 77 To 79
      
         y_f = 28
       Case 88 To 89
         y_f = 36
       Case 99
         y_f = 45
        
       Case Else
        y_f = 0
        
End Select
 
senhyaku = sen_hyaku + 5 - y_f
jyuuiti = jyuu_iti + 120 - x_f


Cells(senhyaku, jyuuiti) = Cells(senhyaku, jyuuiti) + 1


i = i + 1
Loop


Call saikeisanon
Cells(1, 120).Select


End Sub



出力セルの位置補正が結構面倒な気がします。それも同じことを2回もしてます。


これは、何とか改善の余地があるような気がします。


マクロはデータ全体の集計ですが、直近50回、100回とかで選択処理出来るようになれば
なお良いと思います。マクロは少し複雑になりますが、それを考えることが勉強ですね。