EXCEL: 1万件のテスト用データを簡単に作成する方法【VBAマクロ】

大量のデータを使ってマクロで遊んでみたかったのですが、会社のデータを使う訳にもいかず、どうしたものかと考えていました。
ふと、マクロで準備しちゃえば良いと気付いたので、今回は大量のテスト用データを準備するプログラムを書いてみました。
このマクロを使うと、下の画像のようなデータを1万件用意することができます。



取引先や担当者の情報は固定(同じ組み合わせ)で、購入製品や購入個数、購入日をランダムで記入するようにしています。

概要

Rnd関数により乱数を生成し、データセットの中から、ランダムで選んで、別のワークシートにまとめていきます。

このマクロで出来ること
・決まった組み合わせ(取引会社-担当者など)を崩すことなく、その他の要素はランダムにデータ生成可能
・購入個数も1〜100まで、ランダムな数を記入
・単価x購入個数を計算して、購入額を表示
・購入日として、2021年~2022年の2年間でランダムな日付を挿入

事前準備(ワークシートの1枚目)

最初に以下のようなデータセットをワークシートの1枚目(1つ目のタブ)に作成します。

データセット


A〜C列:取引先会社名と取引先担当者と弊社担当者で、同じ組み合わせにしたいもの。
E~F列:製品名と単価で、同じ組み合わせにしたいもの。

A~Cのセットの中からランダムで1つ、E~Fのセットの中からランダムで1つ、購入個数は1〜100個の中からランダム、購入日は2021年~2022年の2年間のランダムとなるようなプログラムです。
ランダムな乱数を生成する方法は、以下のブログをご覧ください。
EXCEL: 5~10の範囲でランダムな整数を生成する方法【VBAマクロ】

事前準備(ワークシートの2枚目)

ワークシートの2枚目(2つ目のタブ)の1行目に、以下の画像のように、各項目を記入します。



F列には購入個数、G列には購入額を計算する項目、H列には購入日の項目を追加しています。
これで、準備は完了です。




コード【VBAマクロ】

  1.  Sub demo_data_()
  2.        
  3.     Randomize '乱数系列を初期化
  4.  
  5.     For i = 2 To 10001
  6.         
  7.         With Worksheets(2)
  8.             .Cells(i, 1) = Worksheets(1).Cells(Int(17 * Rnd + 2), 1)
  9.             .Cells(i, 2) = Worksheets(1).Cells(Int(17 * Rnd(0) + 2), 2) 'Rnd(0)は直前の乱数の結果を返す
  10.             .Cells(i, 3) = Worksheets(1).Cells(Int(17 * Rnd(0) + 2), 3)
  11.             .Cells(i, 4) = Worksheets(1).Cells(Int(5 * Rnd + 2), 5)
  12.             .Cells(i, 5) = Worksheets(1).Cells(Int(5 * Rnd(0) + 2), 6)
  13.             .Cells(i, 6) = Int(100 * Rnd + 1)
  14.             .Cells(i, 7) = .Cells(i, 5).Value * Cells(i, 6).Value
  15.             .Cells(i, 8) = CDate(WorksheetFunction.RandBetween("2021/01/01", "2022/12/31"))
  16.         End With
  17.         
  18.     Next i
  19.  
  20.  End Sub

VBA実行後



画像のようなデータが、1万行続いたデータの完成です。

コード【VBAマクロ】の解説

3.
Randomizeで乱数系列を初期化しています。
5.
For Next 構文でループさせています。
見出しを除いて、2行目から10001行目までなので、計1万件のデータが作成されます。
必要なデータ件数に合わせて、好きな数に変更可能です。
7.
Withで、8行目以降の作業は、Worksheets(2)に関して行っています。
8.
Worksheets(2)のA列i行目のセルに、右辺の結果を代入します。
Int(17 * Rnd + 2)の部分で、2〜18までの乱数を生成しています。
※範囲指定の乱数の生成方法は、「EXCEL: 5~10の範囲でランダムな整数を生成する方法【VBAマクロ】」をご覧ください。

これを行番号として、Cells(Int(17 * Rnd + 2), 1)とすることで、A列の2〜18行目をランダムに選択するコードができます。
Worksheets(1)の上記のセルを指定することで、1枚目のタブのデータセットのA列の中から、ランダムに1個選択できます。
9.
Worksheets(2)のB列i行目のセルに、右辺の結果を代入します。
右辺の考え方は、8行目と同じですが、違う点はRnd(0)を使用します。
これは、直前の乱数(8行目で生成した数)の結果を返します。
8行目で「Int(17 * Rnd + 2)」の値が5だった場合は、9行目の「Int(17 * Rnd(0) + 2)」も5になります。
これにより、データセットのB列から、A列と同じ組み合わせのデータを選択することができます。
例)8行目でA2のA株式会社を選んだ場合、B2は山田太郎を選択します。
10.
Worksheets(2)のC列i行目のセルに、右辺の結果を代入します。
9行目と全く同じです。
例)8行目でA2のA株式会社を選んだ場合、B2は山田太郎、C2は田中A男を選択します。
11.
基本的に8行目と同じ考え方で、今度はE列の製品をランダムで選ぶために、2〜6の乱数を生成しています。
データセットから選択した製品名を、Worksheets(2)のD列i行目に代入します。
12.
こちらは、9行目と同じで、Rnd(0)を使って、先ほどの製品名とセットの単価を選んで、Worksheets(2)のE列i行目に代入します。
13.
Worksheets(2)のF列i行目には、購入個数として1〜100のランダムな数を入れています。
14.
Worksheets(2)のG列i行目では、単価と購入個数を掛け算しています。
15.
Worksheets(2)のH列i行目のセルに、右辺の結果を代入します。
右辺は、WorksheetFunctionの、RandBetween関数を使うことにより、2021/01/01〜2022/12/31までの日付をランダムで1日選びます。

感想

プログラムとしては、シンプルかなと思います。
1つ欠点としては、実行結果が出るまでに割と時間がかかります。
私の環境だと、1万件のデータを生成するのに数分程度かかりました。
もっと高速で処理できる方法があるでしょうが、まだ皆目検討もつきません笑。
改善できたら、またまとめてみます。