マクロでランダムな席順を作る
マクロ講座79回
ランダムな席順を作る Excel2013 マクロ講座 79回
席替え用の席順を作る
前回は、机に順番に番号をふるというマクロを作りましたが、席替えをする場合は、ランダムな席順が良いということもあります。
あるいは、何かの作業グループなどを作るときは、ランダムな方がいいですよね。
そこで今回は乱数を発生させて、その数値を利用して席順を作ります。
(サンプルファイルは、こちらから マクロ79回サンプルデータ)
席替え用に乱数を生成する
さてランダムな値を発生させるには、RAND関数を使うことができます。
机に乱数をわりあてた席順用のシートと作業用のシートを使って、下図のようにランダムな席番号を入力するマクロを作成します。
サンプルでは、生徒が40人、机が40個という設定です。
A列には1から40までの番号がふってあり、隣の列にはRAND関数を使って乱数を入力します。
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列に入力します。
ワークシート関数の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マクロを実行すると下図のようになり、生徒番号の列に順番が入力されます。
座席表に乱数の順番を転記する
最後に作業用のシートに記入された順番を席順シートの机に転記しましょう。
これには、2013_macro_78.htmで使った2重構造のFor文をそのまま利用します。 その前に、生徒番号の順番を1次元配列として格納してしまいましょう。配列に番号を格納することでコードがシンプルになります。
これには、2013_macro_78.htmで使った2重構造のFor文をそのまま利用します。 その前に、生徒番号の順番を1次元配列として格納してしまいましょう。配列に番号を格納することでコードがシンプルになります。
配列を宣言して、格納するまでのコードです。
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
結果は、以下の図のように机とみなしたセルに生徒番号がランダムに入力されることになるので、
これをもとに席替えを行います。