【Excel】VBAで数の異なるワードリストからデータ生成

こんにちは！今回はExcelのVBAを使って、あらかじめ用意したワードリストからランダムに選択し、新しいデータリストを自動で作成する方法をご紹介します。手動でデータを作成するのは手間がかかるので、このコードを使えば自動でランダムなデータを生成できます。例えば、ブログ記事用のデータ作成や、検証データ、計算問題の作成にも活用できます。

前回はワード数が同じ場合のコードをご紹介しましたが、今回は属性ごとに異なるワード数に対応したロジックについて解説します。さまざまな状況に対応できる柔軟なVBAコードの作り方を一緒に学んでいきましょう！

使用するワードリスト
作成するデータ数
ＶＢＡの実行結果
コード解説★★★（コピーはここ）★★★
解説
使用している技術
まとめ

使用するワードリスト

まず使用するワードリストについて解説します。
ワードリストの構成
ワードリストは「候補」のワークシート上に、「№」「品番」「工程」「開始時」「開始分」「終了時」「終了分」という構成となっており、今回はデータを列ごとに異なりますが、４～１２行分作成しました。

作成するデータ数

ワードリストから作成するデータの数は下図の赤枠で指定しています。

ＶＢＡの実行結果

ＶＢＡを実行した結果下図のような結果を得る事が出来ます。この組合せは実行の都度ランダムで違う結果を得ることが出来ます。

コード解説★★★（コピーはここ）★★★

Private WordWs As Worksheet, ResultWs As Worksheet
Private WordCell As String, Create_NoCell As String, ResultCell As String
Private Word_Column As Long, Word_Row As Long, DataCount As Long
Private Words() As Variant, Results() As Variant
Private Word_C As Long, Word_R As Long

Sub RANDAM()
    Call Setting
    Call Change
    Call Get_Info
    Call ReSize
    Call GetWord
    Call Lottery
    Call Paste
End Sub

Sub Setting()
'ユーザーごとに設定する項目
    'ワードリストのワークシート名
    Set WordWs = Worksheets("候補")
    'ワードリストのセル開始位置
    WordCell = "B4"
    '作成するデータリストのワークシート名
    Set ResultWs = Worksheets("結果")
    '作成するデータリストのセル開始位置
    ResultCell = "C3"
    '作成するデータ数情報
    Create_NoCell = "D2"
End Sub

Sub Change()
'セル位置を数字に変換する
    Word_R = WordWs.Range(WordCell).Row
    Word_C = WordWs.Range(WordCell).Column
End Sub

Sub Get_Info()
'作成するための情報を取得
    'ワードリストの行数と列数を取得
    Word_Column = WordWs.Range(WordCell).CurrentRegion.Columns.Count
    Word_Row = WordWs.Range(WordCell).CurrentRegion.Rows.Count
    '作成するデータ数の情報を取得
    DataCount = WordWs.Range(Create_NoCell).Value
End Sub

Sub ReSize()
    ' 配列サイズの設定
    ReDim Words(1 To Word_Row, 1 To Word_Column) As Variant
    ReDim Results(1 To DataCount, 1 To Word_Column) As Variant
End Sub

Sub GetWord()
    ' 候補のワードを配列へ格納
    With WordWs
        Words = .Range(.Cells(Word_R, Word_C), .Cells(Word_R + Word_Row, Word_C + Word_Column)).Value
    End With
End Sub

Sub Lottery()
' 列ごとに抽選
    For i = 1 To DataCount
        For j = 2 To Word_Column  '一番左は№なので&#91;2]から開始
            Do
                RandIndex = Int(Rnd() * Word_Row) + 2  'ヘッダー分を除く
            Loop While IsEmpty(Words(RandIndex, j))
            Results(i, j - 1) = Words(RandIndex, j)
        Next j
    Next i
End Sub

Sub Paste()
' 結果をシートに貼り付け
    ResultWs.Range(ResultCell).ReSize(DataCount, Word_Column).Value = Results
End Sub

Private WordWs As Worksheet, ResultWs As Worksheet

Private WordCell As String, Create_NoCell As String, ResultCell As String

Private Word_Column As Long, Word_Row As Long, DataCount As Long

Private Words() As Variant, Results() As Variant

Private Word_C As Long, Word_R As Long

Sub RANDAM()

Call Setting

Call Change

Call Get_Info

Call ReSize

Call GetWord

Call Lottery

Call Paste

End Sub

Sub Setting()

'ユーザーごとに設定する項目

'ワードリストのワークシート名

Set WordWs = Worksheets("候補")

'ワードリストのセル開始位置

WordCell = "B4"

'作成するデータリストのワークシート名

Set ResultWs = Worksheets("結果")

'作成するデータリストのセル開始位置

ResultCell = "C3"

'作成するデータ数情報

Create_NoCell = "D2"

End Sub

Sub Change()

'セル位置を数字に変換する

Word_R = WordWs.Range(WordCell).Row

Word_C = WordWs.Range(WordCell).Column

End Sub

Sub Get_Info()

'作成するための情報を取得

'ワードリストの行数と列数を取得

Word_Column = WordWs.Range(WordCell).CurrentRegion.Columns.Count

Word_Row = WordWs.Range(WordCell).CurrentRegion.Rows.Count

'作成するデータ数の情報を取得

DataCount = WordWs.Range(Create_NoCell).Value

End Sub

Sub ReSize()

' 配列サイズの設定

ReDim Words(1 To Word_Row, 1 To Word_Column) As Variant

ReDim Results(1 To DataCount, 1 To Word_Column) As Variant

End Sub

Sub GetWord()

' 候補のワードを配列へ格納

With WordWs

Words = .Range(.Cells(Word_R, Word_C), .Cells(Word_R + Word_Row, Word_C + Word_Column)).Value

End With

End Sub

Sub Lottery()

' 列ごとに抽選

For i = 1 To DataCount

For j = 2 To Word_Column '一番左は№なので[2]から開始

RandIndex = Int(Rnd() * Word_Row) + 2 'ヘッダー分を除く

Loop While IsEmpty(Words(RandIndex, j))

Results(i, j - 1) = Words(RandIndex, j)

Next j

Next i

End Sub

Sub Paste()

' 結果をシートに貼り付け

ResultWs.Range(ResultCell).ReSize(DataCount, Word_Column).Value = Results

End Sub

解説

このVBAコードは、各機能を独立したサブルーチンに分割し、メインサブルーチン「RANDAM」からそれらを順番に呼び出す構造で作成されています。以下はコードの流れです。

設定 (Setting): ワードリストやデータリストのシート名、セル位置を指定します。
セル情報の数値化 (Change): 指定したセルの位置を行・列の数値に変換します。
情報取得 (Get_Info): ワードリストの行数・列数や生成するデータの数を取得します。
配列の準備 (ReSize): ワードリストと結果データを格納する配列を設定します。
ワードの取得 (GetWord): ワードリストの内容を配列に格納します。
ランダム選択 (Lottery): ワードリストからランダムに選択し、新しいデータリストを作成します。
データの格納 (Paste): 作成したデータリストを指定のセルに出力します。

「RANDAM」サブルーチンはこれらの機能を順番に呼び出すことで、柔軟かつ効率的に新しいデータリストを作成します。各機能が分割されているため、コードのメンテナンスや拡張が容易です。

変数の宣言

Private WordWs As Worksheet, ResultWs As Worksheet
Private WordCell As String, Create_NoCell As String, ResultCell As String
Private Word_Column As Long, Word_Row As Long, DataCount As Long
Private Words() As Variant, Results() As Variant
Private Word_C As Long, Word_R As Long, Res_C As Long, Res_R As Long

Private WordWs As Worksheet, ResultWs As Worksheet

Private WordCell As String, Create_NoCell As String, ResultCell As String

Private Word_Column As Long, Word_Row As Long, DataCount As Long

Private Words() As Variant, Results() As Variant

Private Word_C As Long, Word_R As Long, Res_C As Long, Res_R As Long

使用する変数やワークシートオブジェクト、配列を宣言しています。今回は複数のSUBで利用する事から「Private」で宣言を行っています。

メインコード

Sub RANDAM()
Call Setting
Call Change
Call Get_Info
Call ReSize
Call GetWord
Call Lottery
Call Paste
End Sub

Sub RANDAM()

Call Setting

Call Change

Call Get_Info

Call ReSize

Call GetWord

Call Lottery

Call Paste

End Sub

この「RANDAM」マクロは、各処理を順に呼び出して実行します。ボタンなどからこのマクロを指定することで、ワードリストから無作為にデータを抽出してデータリストを生成するプロセスが開始されます。

設定入力セクション

Sub Setting()
　　Set WordWs = Worksheets("候補")
　　WordCell = "B4"
　　Set ResultWs = Worksheets("結果")
　　ResultCell = "C3"
　　Create_NoCell = "D2"
End Sub

Sub Setting()

　　Set WordWs = Worksheets("候補")

　　WordCell = "B4"

　　Set ResultWs = Worksheets("結果")

　　ResultCell = "C3"

　　Create_NoCell = "D2"

End Sub

この処理で使用するワークシート名やセル位置を指定します。コピーして使用する際は、自身のExcelに合わせて以下の項目を変更してください。

２行目：ワードリストのワークシート名　例：候補
３行目：ワードリストの開始セル　例：B4
４行目：作成したデータリストを出力するワークシート名　例：結果
５行目：作成したデータリスト開始セル　例：C3
６行目：データリストの作成行数を指定したセル　例：D2

セル情報の数値化

Sub Change()
　　Word_R = WordWs.Range(WordCell).Row
　　Word_C = WordWs.Range(WordCell).Column
End Sub

Sub Change()

　　Word_R = WordWs.Range(WordCell).Row

　　Word_C = WordWs.Range(WordCell).Column

End Sub

文字で指定したセルの位置を数値に変換しています。
２行目：ワードリストの開始セル「B4」の行数を変数に入力
３行目：ワードリストの開始セル「B4」の列数を変数に入力

データリスト作成に必要な情報の取得

Sub Get_Info()
　　Word_Column = WordWs.Range(WordCell).CurrentRegion.Columns.Count
　　Word_Row = WordWs.Range(WordCell).CurrentRegion.Rows.Count
　　DataCount = WordWs.Range(Create_NoCell).Value
End Sub

Sub Get_Info()

　　Word_Column = WordWs.Range(WordCell).CurrentRegion.Columns.Count

　　Word_Row = WordWs.Range(WordCell).CurrentRegion.Rows.Count

　　DataCount = WordWs.Range(Create_NoCell).Value

End Sub

ワードリストの行列数を取得しています。
２行目：「候補」シートの「B4」セルを含む表の行数を変数に格納する。
３行目：「候補」シートの「B4」セルを含む表の列数を変数に格納する。
４行目：作成するデータリストの行数を変数に格納する。

配列サイズの変更

Sub ReSize()
　　ReDim Words(1 To Word_Row, 1 To Word_Column) As Variant
　　ReDim Results(1 To DataCount, 1 To Word_Column) As Variant
End Sub

Sub ReSize()

　　ReDim Words(1 To Word_Row, 1 To Word_Column) As Variant

　　ReDim Results(1 To DataCount, 1 To Word_Column) As Variant

End Sub

処理の中で使用する配列のサイズを変更します。

２行目：ワードを格納する配列サイズを表に合わせて変更
３行目：データリストへ入力する結果を格納する配列サイズを作成するデータ数に合わせて変更

ワードリストの候補を配列に格納

Sub GetWord()
　　With WordWs
　　　　　Words = .Range(.Cells(Word_R, Word_C), .Cells(Word_R + Word_Row, Word_C + Word_Column)).Value
　　End With
End Sub

Sub GetWord()

　　With WordWs

　　　　　Words = .Range(.Cells(Word_R, Word_C), .Cells(Word_R + Word_Row, Word_C + Word_Column)).Value

　　End With

End Sub

Wordsという配列へワードリストのデータを一括取得しています。
２行目：３行目の処理はWordWsのワークシートが対象である。
３行目：ワードリストの内容を一括で配列に格納する。
４行目：２行目のシート指定はここまで。

ワードリストからランダムに選択しデータリストを作成

Sub Lottery()
　　For i = 1 To DataCount
　　　　　For j = 2 To Word_Column
　　　　　　　　Do
　　　　　　　　　　RandIndex = Int(Rnd() * Word_Row) + 2
　　　　　　　　Loop While IsEmpty(Words(RandIndex, j))
　　　　　　　　Results(i, j - 1) = Words(RandIndex, j)
　　　　　Next j
　　Next i
End Sub

Sub Lottery()

　　For i = 1 To DataCount

　　　　　For j = 2 To Word_Column

　　　　　　　　　　RandIndex = Int(Rnd() * Word_Row) + 2

　　　　　　　　Loop While IsEmpty(Words(RandIndex, j))

　　　　　　　　Results(i, j - 1) = Words(RandIndex, j)

　　　　　Next j

　　Next i

End Sub

配列に格納したワードリストから列ごとに無作為抽出してデータリストを作成します。
２行目：変数 i を 1 から DataCount まで増やしながら、３～ 7 行目を繰り返します。
３行目：変数 j を 1 から Word_Column まで増やしながら、４～ 6 行目を繰り返します。

４行目：Do ループを開始し、4５行目の処理を繰り返します。条件が満たされるまでループします。
５行目：変数 RandomIndex に、２から Word_Row までの範囲でランダムな整数を設定します。【１はヘッダー情報】
６行目：配列 WORD の randomIndex, j に格納されている文字が空でないかを確認し、空の場合は４行目に戻って再度ランダムなインデックスを選びます。
７行目：Results(i, j-1) に WORD(randomIndex, j) の値を格納します。
８行目：変数 j を１増やし、３行目に戻って次の列を処理します。
９行目：変数 i を１増やし、２行目に戻って次の行を処理します。

データリストの格納

Sub Paste()
　　ResultWs.Range(ResultCell).ReSize(DataCount, Word_Column).Value = Results
End Sub

Sub Paste()

　　ResultWs.Range(ResultCell).ReSize(DataCount, Word_Column).Value = Results

End Sub

作成したデータリストを指定したシートのセルへ入力する。

使用している技術

基本：DO－LOOP、FOR－NEXT、二次元配列、配列、ＲＮＤ、ＲｅＤＩＭ

まとめ

今回の記事では、VBAを使用して異なる数のワードリストからランダムにデータを選択し、新しいデータリストを作成する方法について詳しく解説しました。コードは機能ごとに分割され、設定や情報取得、配列のサイズ変更、ワードの選択、データの格納といった一連のプロセスを順を追って説明しています。これにより、柔軟で再利用可能なVBAコードの作成方法を学ぶことができます。VBAを使ったデータ操作のスキルアップにぜひ役立ててください。