マクロ組んでみた(サイコロのパターン列挙)

以前エクセルでサイコロ5個振った時のパターンを列挙するマクロを組んだのですが、そのマクロがこちらです↓

    '数値入力
    For i = 1 To 6
    For j = 1 To 6
    For k = 1 To 6
    For l = 1 To 6
    For m = 1 To 6
        ActiveCell.Value = i
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = j
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = k
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = l
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = m
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = i + j + k + l + m
        ActiveCell.Offset(1, -5).Select
    Next m
    Next l
    Next k
    Next j
    Next i

非常にシンプルですが、サイコロの個数を変えるたびにFor文を増やさなければならず、選択セルを動かしながら数値を代入しているため、実行時間が長いです。

そこで今回はこのマクロを少し弄ってサイコロの個数をメッセージボックスで入力し、個数に応じたパターン数を列挙できるようなプログラムを作っていきます。

ちなみにサイコロ8個だとエクセルの行数上限を超えてしまうためこのプログラムだと途中で止まります。この仕様については修正しませんが、方法としては上限かどうかを判定し上限に達したら隣のセルに移る方法が良いかと思います。

まず、サイコロの個数に応じて動作するようなプログラムを作るためには、For文をサイコロの個数に応じて増やすシステムを無くす必要があります。
そこで、 1~6まで繰り返すn個のFor文 から 1~6ⁿまで繰り返すFor文 の一つに纏めます。

サイコロn個に対するパターン数は6ⁿであるため、1~6ⁿの数値から全てのパターンを出力することができます。しかし、当然ながらそのままの数値だと出目として出力できません。
なので今回は6進数を使って、その桁数でそれぞれのサイコロの出目を出力します。

つまりこゆこと

6進数変換はこの式で変換することができます。

エクセル関数だとこんな感じ
=MOD(ROUNDDOWN(A / n^(k - 1),0), n)
VBAだとこうなります
A = Fix(A / (n ^ (k - 1))) Mod n

この方式を利用して作成したマクロがこちら↓

  '変数宣言
  Dim kizyun_c As Long: kizyun_c = Selection.Column '基準列
  Dim kizyun_r As Long: kizyun_r = Selection.Row '基準行
  Dim plus_c As Long: plus_c = 0 '列
  Dim plus_r As Long: plus_r = 0 '行
  Dim d_pt As Long 'サイコロ出現パターン数
  Dim i As Long 'forカウント用
  Dim j As Integer 'forカウント用
  Dim d_val As Long 'サイコロ出目(side_num進数)
  Dim d_sum As Integer 'サイコロ合計値
  Dim mozi As Integer: mozi = Asc("a") '文字コード(a)
  Dim out_val As Byte '出目
  Dim d_num As Integer 'サイコロの個数
  
  Dim side_num As Integer: side_num = 6 'サイコロの面数を簡単に変更できるよう変数で指定
    
  '個数入力
  d_num = Application.InputBox(Prompt:="サイコロの個数", Title:="数値入力", Type:=1)
  d_pt = 6 ^ d_num - 1 'n個のサイコロのパターン数-1をd_ptに代入
  
  For i = 0 To (d_num - 1)
    Cells(kizyun_r, kizyun_c + i).Value = "サイコロ" & Chr(mozi + i)
  Next i
  Cells(kizyun_r, kizyun_c + i).Value = "sum"
  plus_r = plus_r + 1

  For i = 0 To d_pt
    d_val = i
    d_sum = 0
    
    For j = d_num To 1 Step -1
      out_val = Fix(d_val / (side_num ^ (j - 1))) Mod side_num + 1 '出目計算
      Cells(kizyun_r + plus_r, kizyun_c + plus_c).Value = out_val '出目をセルに代入
      d_sum = d_sum + out_val
      plus_c = plus_c + 1
    Next j
    
    Cells(kizyun_r + plus_r, kizyun_c + plus_c).Value = d_sum '合計値をセルに代入
    plus_c = 0
    plus_r = plus_r + 1
  Next i

さきほどのプログラムよりも複雑ですが、入力したサイコロ個数に応じた出力が得られるようになりました。

VBAのTimer関数を使用して処理時間を計測しようとしたのですが、改良前のマクロは2連続でフリーズしたので諦めました。以前は動いていたのですが。
なので改良後のマクロで1個～7個までの処理時間を測定してみました。
Timer 関数 (Visual Basic for Applications) | Microsoft Docs

サイコロの個数	処理時間
１個	0.618 sec
２個	0.700 sec
３個	1.050 sec
４個	1.141 sec
５個	2.844 sec
６個	14.229 sec
７個	89.532 sec

6個以上は長いですね……。

（おまけ）こちらはc言語によるcsv出力バージョン

#include <stdio.h>
#include <math.h>
#include <sys/stat.h> //ファイルの有無を確認

void csv_out(char* mozi, char* csv_name);
int sinsu_out(long val, int sinsu, int ketasu);

int main(void)
{ 
  int d_num, d_sum, j, mozi;
  long d_pt, d_val, i;
  char csv_name[20];
  char moziretu[100];
  struct stat statBuf; //ファイルの有無を確認

  sprintf(moziretu, "");

  mozi = 'a';

  printf("サイコロ個数 : ");
  scanf("%d", &d_num);

  sprintf(csv_name, "dice_num%d.csv", d_num);

  if (stat(csv_name, &statBuf) == 0) {
    printf("同名のファイルがあります。 %s\n", csv_name);
    return 1;
  }

  d_pt = (long) powl(6 , d_num); //n個のサイコロのパターン数をd_ptに代入

  for (i = 0; i < d_num; i++) //文字コードをインクリメントしAから順に出力する
    sprintf(moziretu, "%sサイコロ%c,", moziretu, mozi + i); 

  sprintf(moziretu, "%ssum\n", moziretu); 
  csv_out(moziretu, csv_name); //csv出力
  
  for (i = 0; i < d_pt; i++){
    sprintf(moziretu, "");
    d_val = i;
    d_sum = 0;
    for(j = d_num; j > 0; j--){
      sprintf(moziretu, "%s%d,", moziretu, sinsu_out(d_val, 6, j) + 1);
      d_sum += sinsu_out(d_val, 6, j) + 1;
    }
    sprintf(moziretu, "%s%d\n", moziretu, d_sum);
    csv_out(moziretu, csv_name); //csv出力
  }

  return 0;
}

void csv_out(char* mozi, char* csv_name){ //csv出力
  FILE *fp; //csvファイルを扱う
  if ((fp = fopen(csv_name, "a")) != NULL) {
	  fprintf(fp, "%s",mozi);
	  fclose(fp);
  } else {
    printf("csv error\a\n");
  }
}

int sinsu_out(long val, int sinsu, int ketasu) { //数値valをsinsu進数に変換した場合のketasu桁目を出力
  val = val / (long) powl(sinsu , ketasu - 1);
  return (int) fmodl(val , sinsu);
}

c言語での時間測定 参考サイト
www.mm2d.net
トップページ - 碧色工房

サイコロの個数	処理時間
１個	0.76 sec
２個	0.75 sec
３個	2.05 sec
４個	8.76 sec
５個	46.19 sec
６個	61.50 sec
７個	458.52 sec

csv出力してるからか長い……。

csv出力を無くすとこうなります

サイコロの個数	処理時間
１個	0.47 sec
２個	0.43 sec
３個	0.60 sec
４個	0.81 sec
５個	1.21 sec
６個	0.86 sec
７個	1.54 sec
８個	5.56 sec
９個	33.94 sec

出力してないから意味ないけど

追記
printfでの処理時間を測定していなかったので測定
後から気付いたけど上3つの測定全部間違えて個数入力の時間も計ってた

printf出力での処理時間

サイコロの個数	処理時間
１個	0.01 sec
２個	0.01 sec
３個	0.14 sec
４個	0.75 sec
５個	5.37 sec
６個	32.59 sec
７個	221.44 sec

csv出力時の約1/2の処理時間

atoi(argv[1])でサイコロの個数を指定し、> file_name.csvで出力した場合の処理時間
※今までPowerShellを使用していましたがここではコマンドプロンプトを使用しています。
　コマンドプロンプトの方が速い気がします。

サイコロの個数	処理時間
１個	0.00 sec
２個	0.00 sec
３個	0.00 sec
４個	0.00 sec
５個	0.03 sec
６個	0.10 sec
７個	0.66 sec
８個	4.47 sec
９個	36.83 sec

速い

【PowerShellで> file_name.csvを行う場合の注意点】
コマンドプロンプトの出力はANSIですが、PowerShellで出力するとUTF-16 LEで出力されます。
WindowsのExcellだとUTFのcsvは正常に読み込めないので、Excelのデータ取得からcsvを読み込むか、メモ帳で開きANSIで保存してから読み込む必要があります。
csvファイルにBOMつける方法もあると思ったのですが、UTF-16 LEはBOMが無いらしい（？）