ExcelVBAでテストデータ作成

「テストデータ作るときどうせ『テストデータ001～999』でいいのにいちいち膨らますの面倒だな」ってことありますよね。

そんな時に使えるマクロを作りました！「テストデータ作成.xlsm」！
作り方をつらつらと書いていく記事になります。

完成品をアップするのではなく作り方をQiitaに書くのは、
「どっかのだれかが作ってアップロードしたマクロなんか怖すぎて実行できないでしょうが！！」と思っているからです。
転職するかも分からんし転職した先で使いたくはならないかも分からんけれど自分用としてメモっとこ、くらいの気分で書いているので読みやすくはないかもしれないです。

ちなみに、7年前に作りました。1文字1文字丁寧に打ち込んでおります。
作った当時は「天才だ！」と思ってたんですけどねえ……(´・ω・｀)

テストデータ作成.xlsm

テストデータ作成.xlsmは3つのシートで構成されています。

• テストデータの定義を記載するDataシート
• 区分値の設定に使用するMasterシート
• 作成したテストデータを出力するResultシート

マクロの方ではシート名を定数にしているので、変更も可能です。
以降に各シートの作り方を記載します。

Dataシート

作成するデータの定義を入力するシートになります。

各項目の説明です。

• B1：連番開始値。
  「テストデータ001」みたいな文字を作りたいとき、最初の１件目が「1」からスタートしてほしい時は「1」を設定します。「50」を入れれば「テストデータ050」からスタートします。
• B2：件数。
  画像の例だと2750件のデータを生成します。
  マクロの方ではLongですが、あまり大きな数値を指定しても実装が耐えられないと思います。
• 生成ボタン
  クリックされたらCreateTestData()が実行されるように設定します。
• 4行目：列名
  結果が出力されるシートの1行目に見出し行として使います。
  C列以降右に、一応いくらでも設定できることになっています。
  6行目以降を設定していても、ここの記載がなければ無視されます。
• 5行目：無効行
  A5Mk2等からデータ貼り付けて、データ見ながら以降の行を設定したいなと思ったので1行分空けてあります。作られるデータには何の影響も及ぼしません。

---

6行目～16行目がテストデータに設定したい値の定義を書いていく行になります。

• 6～8行目：連番
  例えば「テストデータ001Test」みたいな文字列を設定したいカラムであれば、
  接頭辞に"テストデータ"を、連番フォーマットに"000"を、接尾辞に"Test"を設定します。
  シンプルに連番が欲しい時は連番フォーマットに"0"を設定して、接頭辞/接尾辞は空にします。

• 9～11行目：ランダム数値
  バラけた数値が欲しい時に使います。
  最小値1最大値10小数桁0にすると1～10の整数がランダムに入ります。
  最小値1最大値100小数桁0にすると1～100の整数がランダムに入ります。
  最小値1最大値5小数桁1にすると0.1,0.2,0.3,0.4,0.5のいずれかがランダムに入ります。
  最小値1最大値5小数桁-1にすると10,20,30,40,50のいずれかがランダムに入ります。
  小数桁の扱いがなんかキモいんですが、名案が思い浮かばなかったので……

• 12,13,14行目：マスタ
  Masterシートに設定した値を設定します。
  詳しい説明はMasterシートの説明に記載します。

• 15行目：式
  式が書けます。=TODAY()みたいな関数を書いてもよいです。
  前後の列を参照した式が描きたかったのでr1c1形式の式が使えるようになっています。
  「2列前の値×1列前の値」みたいなことがやりたいときに=RC[-2]*RC[-1]を設定するとｲｲｶﾝｼﾞ
  RC[-1]→１列前、RC[1]→１列後、R[-1]C→１行、R[1]C→１行下です。
  テストデータ作成した後直接Resultシートいじればいいのであんまり使っていません

• 16行目：固定値
  全レコード同じ値を設定したい場合に設定します。

複数指定した場合はより上の行にある設定が採用されます。
6行目～16行目まで全て未設定の場合は「16行目に空文字が設定されている」扱いです。

Masterシート

B1セルはシート名、B2セルは6を指定します。
B3セルには=IF(B6="","","B")を設定します。
B4セルに設定する式の結果は、6行目の入力で変化します。
B6～G6の各セルに値を入れた場合は「G」になります。
=LEFT(ADDRESS(ROW(),COUNTA(6:6),2), FIND("$",ADDRESS(ROW(),COUNTA(6:6),2))-1)
B5セルの式はB1～B4セルまでの内容をくっつけています。
=IF(B3="","",CONCATENATE("'",$B$1,"'!$",$B$3,"$",$B$2,":$",$B$4,"$",$B$2))

式を入力し終えたら1行目～5行目は非表示にしてしまってOKです。
触ると壊れちゃうんで隠しといてください。

DataシートのC列より右の12,13,14行目の入力規則を以下の画像のように設定します。

Masterシート6行目の値をドロップダウンから選択できるようになります。

B6に「性別」C6に「都道府県」が設定されている場合は「性別」「都道府県」が選べるような入力規則です。
あとからD6に「職業」を設定したら「職業」も選べるようになります。
MasterシートB7に「男」B8に「女」を設定したうえで、DataシートのC14に「性別」を選ぶと「男」か「女」のいずれかがランダムに設定されるカラムとなります。
交互に出したいときは13行目、男500件のあと女500件のようにしたいときは12行目を使います。
MasterシートB9に「答えたくない」とか追加すれば3つの値をなんかｲｲｶﾝｼﾞに設定してくれます。

Resultシート

生成ボタンを押すたびにリセットしてしまうので、シートだけ作ってあればOKです。
作ったデータをとっておきたい場合は別シートに退避するなりなんなりしてください。

以下の記事でINSERT文を作るマクロの作り方を記載しています。
あわせてそちらも使うとハッピー、に、なれるように作ったはずなのですが。
ExcelVBAでINSERT文を作る #ExcelVBA - Qiita
なんかどっちも中途半端になってしまっているものの、直す気力もございません。

マクロ

割と丁寧にコメント書いてあるっぽくね？

'************************
' テストデータ作成
'************************
Option Explicit
'定義シート
Const DATA_SHEET_NAME As String = "Data"
'(定義シート)連番開始値のセル
Const ST_VAL_CELL As String = "B1"
'(定義シート)作成件数のセル
Const RC_VAL_CELL As String = "B2"
'マスタシート
Const MASTER_SHEET_NAME As String = "Master"
'(マスタシート)名称行番号のセル
Const MASTER_NAME_ROW_CELL As String = "B2"
'結果シート
Const RSLT_SHEET_NAME As String = "Result"
Enum GenerateType
    SerialNumberFormat = 8
    RandomNumber = 11
    MasterStraight = 12
    MasterRepetition = 13
    MasterRandom = 14
    Formula = 15    
    FixedValue = 16
End Enum
 
Sub CreateTestData()
    '列定義が始まる列インデックス
    Const COL_START_INDEX As Integer = 3
    '生成情報の行インデックス-----------
    '列名
    Const COL_NAME_INDEX As Integer = 4
    '接頭辞
    Const COL_PREFIX_INDEX As Integer = 6
    '連番フォーマット
    Const COL_FORMAT_INDEX As Integer = 7
    '接尾辞
    Const COL_SUFFIX_INDEX As Integer = 8
    '最小値
    Const COL_MINVAL_INDEX As Integer = 9
    '最大値
    Const COL_MAXVAL_INDEX As Integer = 10
    '桁数
    Const COL_DIGIT_INDEX As Integer = 11
    'マスタ(連続)
    Const COL_MASTER_STRAIGHT_INDEX As Integer = 12
    'マスタ(反復)
    Const COL_MASTER_REPETITION_INDEX As Integer = 13
    'マスタ(ランダム)
    Const COL_MASTER_RANDOM_INDEX As Integer = 14
    '式
    Const COL_FORMULA_INDEX As Integer = 15    
    '固定値
    Const COL_FIXED_INDEX As Integer = 16
    '-----------生成情報の行インデックス

    Dim stTime As Date
    stTime = Now()
    '画面の更新をOFF
    Application.ScreenUpdating = False
    '自動計算をOFF
    Application.Calculation = xlCalculationManual
    '結果シート
    Dim rsltSheet As Worksheet
    Set rsltSheet = Worksheets(RSLT_SHEET_NAME)
    rsltSheet.Cells.Clear
    rsltSheet.Cells.NumberFormatLocal = "@"
    With Worksheets(DATA_SHEET_NAME)
        '連番開始値
        Dim stVal As Long
        stVal = CLng(.Range(ST_VAL_CELL).Value)
        '作成件数
        Dim rcVal As Long
        rcVal = CLng(.Range(RC_VAL_CELL).Value)
        '生成タイプ
        Dim generateTypeIndex As Integer
        '定義シートの現在列インデックス
        Dim currentIndex As Integer
        currentIndex = COL_START_INDEX
        '結果シートの現在列インデックス
        Dim dtColIndex As Integer
        dtColIndex = 1
        '現在の生成対象範囲
        Dim dtCol As Range
 
        Do Until .Cells(COL_NAME_INDEX, currentIndex).Value = ""
            '生成タイプ取得
            For generateTypeIndex = COL_PREFIX_INDEX To COL_FORMULA_INDEX
                If .Cells(generateTypeIndex, currentIndex).Value <> "" Then
                    Exit For
                End If
            Next generateTypeIndex
 
            '列名セット
            rsltSheet.Cells(1, dtColIndex).Value = .Cells(COL_NAME_INDEX, currentIndex).Value
            '生成範囲
            Set dtCol = Range(rsltSheet.Cells(2, dtColIndex), rsltSheet.Cells(rcVal + 1, dtColIndex))
 
            '生成タイプ判定
            Select Case generateTypeIndex
                Case Is <= GenerateType.SerialNumberFormat
                    '文字列
                    GenerateSerialNumberFormatValue dtCol, _
                                                    stVal, _
                                                    .Cells(COL_PREFIX_INDEX, currentIndex).Value, _
                                                    .Cells(COL_SUFFIX_INDEX, currentIndex).Value, _
                                                    .Cells(COL_FORMAT_INDEX, currentIndex).Value
                Case Is = GenerateType.Formula
                    '式
                    GenerateFormula dtCol, .Cells(COL_FORMULA_INDEX, currentIndex).Value
                Case Is <= GenerateType.RandomNumber
                    'ランダム数値
                    GenerateRandomNumber dtCol, _
                                         .Cells(COL_MINVAL_INDEX, currentIndex).Value, _
                                         .Cells(COL_MAXVAL_INDEX, currentIndex).Value, _
                                         .Cells(COL_DIGIT_INDEX, currentIndex).Value
                Case Is = GenerateType.MasterStraight
                    'マスタ(連続)
                    GenerateMasterStraightValue dtCol, rcVal, .Cells(COL_MASTER_STRAIGHT_INDEX, currentIndex).Value
                Case Is = GenerateType.MasterRepetition
                    'マスタ(反復)
                    GenerateMasterRepetitionValue dtCol, .Cells(COL_MASTER_REPETITION_INDEX, currentIndex).Value
                Case Is = GenerateType.MasterRandom
                    'マスタ(ランダム)
                    GenerateMasterRandomValue dtCol, .Cells(COL_MASTER_RANDOM_INDEX, currentIndex).Value
                Case Is = GenerateType.FixedValue
                    '固定値
                    GenerateFixedValue dtCol, .Cells(COL_FIXED_INDEX, currentIndex).Value
            End Select
            dtColIndex = dtColIndex + 1
            currentIndex = currentIndex + 1
        Loop
    End With
    With Worksheets(RSLT_SHEET_NAME)
        '罫線の設定
        With Range(.Cells(1, 1), .Cells(rcVal + 1, dtColIndex - 1))
            .Borders.LineStyle = xlContinuous
        End With
        'ヘッダ行の設定
        With Range(.Cells(1, 1), .Cells(1, dtColIndex - 1))
            .Font.Color = vbWhite
            .Interior.Color = 12611584
        End With
        .Activate
    End With
    '再計算
    ActiveSheet.Calculate
    '画面の更新をON
    Application.ScreenUpdating = True
    '自動計算をON
    Application.Calculation = xlCalculationAutomatic
    MsgBox "生成完了(" & DateDiff("s", stTime, Now) & "秒)"
End Sub
 
Function GenerateSerialNumberFormatValue(col As Range, stVal As Long, prefixStr As String, suffixStr As String, formatStr As String)
'指定された範囲に指定フォーマットの文字列をセットする
    '連番
    Dim serialNumber As Long
    serialNumber = stVal
    'フォーマット文字列
    Dim fs As String
    fs = ""
    '接頭辞が指定された場合、ダブルクォーテーションでくくってフォーマット文字列に付加
    If prefixStr <> "" Then
        fs = Chr(34) & prefixStr & Chr(34)
    End If
    fs = fs & formatStr
    '接尾辞が指定された場合、ダブルクォーテーションでくくってフォーマット文字列に付加
    If suffixStr <> "" Then
        fs = fs & Chr(34) & suffixStr & Chr(34)
    End If
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
    For i = 1 To arrLen
        cellArr(i, 1) = format(serialNumber, fs)
        serialNumber = serialNumber + 1
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
End Function
 
Function GenerateFormula(col As Range, formulaStr As String)
'指定された範囲に式をセットする
    '式文字列に"rc["が含まれていたらR1C1形式にセット
    If InStr(LCase(formulaStr), "rc[") > 0 Then
        col.FormulaR1C1 = "=" & formulaStr
    Else
        col.Formula = "=" & formulaStr
    End If
    '書式を「標準」にする
    col.NumberFormatLocal = "G/標準"
 
End Function
 
Function GenerateFixedValue(col As Range, fixedStr As String)
'指定された範囲に固定値をセットする
    col.Value = fixedStr
End Function
 
Function GenerateRandomNumber(col As Range, minValStr As String, maxValStr As String, digitStr As String)
'指定された範囲にランダムな数値をセットする
 
    If Val(digitStr) < 0 Then
        GenerateRandomNumberWholeNumber col, minValStr, maxValStr, digitStr
    Else
        GenerateRandomNumberDecimal col, minValStr, maxValStr, digitStr
    End If
 
End Function
 
 
Function GenerateRandomNumberWholeNumber(col As Range, minValStr As String, maxValStr As String, digitStr As String)
'指定された範囲にランダムな数値をセットする(桁数に負数が指定されている場合)
 
    Dim divisor As Double
    divisor = 10 ^ Val(digitStr)
 
    '最小値の取得
    Dim minVal As Double
    minVal = Val(minValStr)
    '最小値と最大値の差分
    Dim diffval As Integer
    diffval = Val(maxValStr) - minVal + 1
 
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
 
    For i = 1 To arrLen
        '乱数ジェネレータ初期化
        Randomize
        cellArr(i, 1) = (minVal + Int(diffval * Rnd)) / divisor
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
 
End Function
 
Function GenerateRandomNumberDecimal(col As Range, minValStr As String, maxValStr As String, digitStr As String)
'指定された範囲にランダムな数値をセットする(桁数に正数が指定されている場合)
 
    Dim divisor As Double
    divisor = 10 ^ Val(digitStr)
    '上限値
    Dim maxVal As Double
    maxVal = Val(maxValStr)
    '下限値
    Dim minVal As Double
    minVal = Val(minValStr)
    '乱数にかける数
    Dim rndNum As Double
    rndNum = maxVal * divisor - minVal * divisor + 1
 
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
 
    For i = 1 To arrLen
        '乱数ジェネレータ初期化
        Randomize
        cellArr(i, 1) = Int((rndNum) * Rnd + minVal * divisor) / divisor
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
 
End Function
 
Function GenerateMasterRandomValue(col As Range, masterName As String)
'指定された範囲にマスタ値をランダムにセットする
 
    'マスタ値の取得
    Dim masterArr
    masterArr = GetMasterValArray(masterName)
 
    Dim ubInt As Integer
    Dim lbInt As Integer
    Dim rndNum As Integer
 
    ubInt = UBound(masterArr)
    lbInt = LBound(masterArr)
    '乱数にかける数
    rndNum = ubInt - lbInt + 1
    '乱数ジェネレータ初期化
    Randomize
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
    For i = 1 To arrLen
        cellArr(i, 1) = masterArr(Int(rndNum * Rnd) + lbInt)
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
 
End Function
 
Function GenerateMasterStraightValue(col As Range, rcVal As Long, masterName As String)
'指定された範囲にマスタ値を連続的にセットする
 
    'マスタ値の取得
    Dim masterArr
    masterArr = GetMasterValArray(masterName)
 
    Dim arrLength As Integer
    arrLength = UBound(masterArr)
    Dim divisor As Long
    divisor = Int(rcVal / arrLength)
    Dim soeji As Integer
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
    For i = 1 To arrLen
        soeji = Int((i - 1) / divisor) + 1
        If soeji > arrLength Then
            soeji = arrLength
        End If
        cellArr(i, 1) = masterArr(soeji)
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
 
End Function
 
Function GenerateMasterRepetitionValue(col As Range, masterName As String)
'指定された範囲にマスタ値を反復的にセットする
 
    'マスタ値の取得
    Dim masterArr
    masterArr = GetMasterValArray(masterName)
 
    Dim arrLength As Integer
    arrLength = UBound(masterArr)
    Dim i As Long
    '指定範囲を配列に変換
    Dim cellArr As Variant
    cellArr = col
    Dim arrLen As Long
    arrLen = UBound(cellArr)
    For i = 1 To arrLen
        cellArr(i, 1) = masterArr((i - 1) Mod arrLength + 1)
    Next i
    '配列の値を指定範囲にセット
    col = cellArr
 
End Function
 
Function GetMasterValArray(masterName As String) As Variant
    'マスタ値の取得
    With Worksheets(MASTER_SHEET_NAME)
        Dim rowNum As Integer
        Dim colNum As Integer
        rowNum = CInt(.Range(MASTER_NAME_ROW_CELL).Value)
        colNum = 2
        Do Until .Cells(rowNum, colNum).Value = ""
            If .Cells(rowNum, colNum).Value = masterName Then
                Exit Do
            End If
            colNum = colNum + 1
        Loop
        'マスタ値配列の作成
        GetMasterValArray = WorksheetFunction.Transpose(Range(.Cells(rowNum + 1, colNum), .Cells(rowNum + 1, colNum).End(xlDown)).Value)
    End With
End Function

50カラムくらいあるテーブルのレコード3000件くらいなら一瞬で作り終わります。
カラム数や作成件数がそれなりに大きくなればそれなりの時間がかかるようになります。
10万件作ろうとしたらExcelフリーズした！とかになっても責任とれません。自己責任でお願いします。
というか如何なる使い方であっても責任はとれません。
ネットで拾ったマクロ使おうってのに責任なんか求めるんじゃない！！！！

需要なくたっていいんだぼくが使うために作ってんだからな、と言いつつ記事にしたのは誰かに使ってほしかったからかもしれません。責任はとらんくせにな。

この記事がどこかの誰かのお役に立つようなことがあれば幸いです。