マクロでランダムな席順を作る

マクロ講座79回　

ランダムな席順を作る Excel2013 マクロ講座 79回

席替え用の席順を作る

前回は、机に順番に番号をふるというマクロを作りましたが、席替えをする場合は、ランダムな席順が良いということもあります。
あるいは、何かの作業グループなどを作るときは、ランダムな方がいいですよね。
そこで今回は乱数を発生させて、その数値を利用して席順を作ります。
（サンプルファイルは、こちらからマクロ79回サンプルデータ）

席替え用に乱数を生成する

さてランダムな値を発生させるには、RAND関数を使うことができます。机に乱数をわりあてた席順用のシートと作業用のシートを使って、下図のようにランダムな席番号を入力するマクロを作成します。
ランダムな席順を作るマクロ1

サンプルでは、生徒が40人、机が40個という設定です。 A列には１から40までの番号がふってあり、隣の列にはRAND関数を使って乱数を入力します。
ランダムな席順を作るマクロ2

B列に乱数を生成するコードは以下のようになります。すべてのモジュールで使えるようにパブリック変数を宣言しています。

Public sheetobj1 As Worksheet 'ワークシートを代入するオブジェクト変数
Public sheetobj2 As Worksheet '〃
Sub 乱数だけ() 'ランダムな値を発生させる
  Dim ra As Integer '
  Set sheetobj2 = ThisWorkbook.Worksheets("sekijyun2")
  With sheetobj2
    For ra = 2 To 41 '2行目から最終行まで
        .Cells(ra, 2).Value = Rnd
    Next
  End With
End Sub

続いてC列には、B列の数値（乱数）にもとづいて順番をつけます。
ワークシート関数のRANK関数を使った数式を使いC列に入力します。

Sub 順番だけ() 'C列に順番を入力
  Dim ra As Integer '
  Set sheetobj2 = ThisWorkbook.Worksheets("sekijyun2")
  With sheetobj2
    For ra = 2 To 41
      .Cells(ra, 3).Value = WorksheetFunction.Rank(.Cells(ra, 2).Value, .Range("B2:B41"), 0)
    Next ra
  End With
End Sub

マクロを実行すると下図のようになり、生徒番号の列に順番が入力されます。
ランダムな席順を作るマクロ4

座席表に乱数の順番を転記する

最後に作業用のシートに記入された順番を席順シートの机に転記しましょう。
これには、2013_macro_78.htmで使った2重構造のFor文をそのまま利用します。その前に、生徒番号の順番を1次元配列として格納してしまいましょう。配列に番号を格納することでコードがシンプルになります。
ランダムな席順を作るマクロ5

配列を宣言して、格納するまでのコードです。

Sub 配列()
  Dim k As Integer
  Dim ban(1 To 40) As Integer '生徒番号を要素40個の配列にする
  Set sheetobj2 = ThisWorkbook.Worksheets("sekijyun2")
  With sheetobj2
    For k = 1 To 40
      ban(k) = .Cells(k + 1, 3) '順番に代入していく
    Next k
  End With
End Sub

今までのコードを一つにまとめると、以下のコードになります。前半では、With sheetobj2で、作業シート用のマクロを記述し、後半では、With sheetobj1で席順シート用のマクロを記述しています。

Public sheetobj1 As Object 'ワークシート
Public sheetobj2 As Object 'ワークシート
Sub 出席番号で席替え()
  Dim ra As Integer, k As Integer, i As Integer, j As Integer
  Dim ban(1 To 40) As Integer '生徒番号を要素40個の配列にする
  Set sheetobj2 = ThisWorkbook.Worksheets("sekijyun2")
  Set sheetobj1 = ThisWorkbook.Worksheets("sekijyun")
    With sheetobj2
      For ra = 2 To 41 '2行目から最終行まで
          .Cells(ra, 2).Value = Rnd '乱数を発生させる
      Next
      For ra = 2 To 41 '大きい順から番号をつける
        .Cells(ra, 3).Value = WorksheetFunction.Rank(.Cells(ra, 2).Value, .Range("B2:B41"), 0)
      Next ra
      For k = 1 To 40
        ban(k) = .Cells(k + 1, 3) '配列に番号を代入していく
      Next k
    End With
    k = 1
    With sheetobj1
      For i = 1 To 8
        For j = 1 To 5
          .Cells(i, j).Value = ban(k) '机に見立てたセルに配列の番号を入れる
          k = k + 1
        Next j
      Next i
    End With
End Sub

結果は、以下の図のように机とみなしたセルに生徒番号がランダムに入力されることになるので、これをもとに席替えを行います。
ランダムな席順を作るマクロ6