塵芥回顧録

無気力

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

以前エクセルでサイコロ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進数を使って、その桁数でそれぞれのサイコロの出目を出力します。

つまりこゆこと
f:id:nupepon:20200810193238p:plain

6進数変換はこの式で変換することができます。
f:id:nupepon:20200811004144p:plain

エクセル関数だとこんな感じ
=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

サイコロの個数 処理時間
1個 0.618 sec
2個 0.700 sec
3個 1.050 sec
4個 1.141 sec
5個 2.844 sec
6個 14.229 sec
7個 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
トップページ - 碧色工房

サイコロの個数 処理時間
1個 0.76 sec
2個 0.75 sec
3個 2.05 sec
4個 8.76 sec
5個 46.19 sec
6個 61.50 sec
7個 458.52 sec

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


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

サイコロの個数 処理時間
1個 0.47 sec
2個 0.43 sec
3個 0.60 sec
4個 0.81 sec
5個 1.21 sec
6個 0.86 sec
7個 1.54 sec
8個 5.56 sec
9個 33.94 sec

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

次やるとしたら高速化ですかね……。多分やらないと思いますが。





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

printf出力での処理時間

サイコロの個数 処理時間
1個 0.01 sec
2個 0.01 sec
3個 0.14 sec
4個 0.75 sec
5個 5.37 sec
6個 32.59 sec
7個 221.44 sec

csv出力時の約1/2の処理時間です。速い!

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

サイコロの個数 処理時間
1個 0.00 sec
2個 0.00 sec
3個 0.00 sec
4個 0.00 sec
5個 0.03 sec
6個 0.10 sec
7個 0.66 sec
8個 4.47 sec
9個 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が無いらしい(?)