ピボットテーブルの練習に最適！Excel VBAでテストデータ作成【データダウンロード可】

Excel

今回は、VBAで5万行のテストデータを一気に作るマクロを紹介するね。テストデータはピボットテーブルの練習にも便利だよ。

「ピボットテーブルの練習したいけど、ちょうどいいデータが見つからない…」

そんな悩み、ありませんか？

この記事では、Excel VBAを使って、ピボットテーブルの練習・集計・フィルター・クロス集計のトレーニングに最適なテストデータを自動で作る方法をご紹介します。

すぐに使えるサンプルファイルもダウンロードOKなので、練習したいときにすぐ始められます。

ピボットテーブルをこれから覚えたい人にも、繰り返し操作を試したい人にもピッタリなのはもちろん、VBAのコードは簡単な解説付きなので、VBAの参考にもなりますよ！

「とりあえずデータ作って試してみたいな〜」って人、ぜひ気軽にどうぞ！

サンプルコード

このVBAマクロでは、2020年から2024年の期間でランダムな日付を生成し、架空の担当者、取引先、商品、数量などを組み合わせて5万件の売上データを作成しています。

Option Explicit

Sub CreateTestData()
    Dim ws As Worksheet
    
    Dim i As Long
    
    Dim currentDate As Date
    Dim dayOfWeek As String
    Dim sDate As Date, eDate As Date
    Dim staffList As Variant, clientList As Variant, productList As Variant
    Dim dataArray As Variant

    Dim staffMax As Long, clientsMax As Long, productsMax As Long
    Dim rndIndex As Long, quantity As Long, unitPrice As Long
    Dim productName As String
    Dim weekdayNum As Long

    Const ROW_COUNT As Long = 50000 ' 出力する行数を定数化

    ' 開始時間を記録
    Debug.Print "Start:" & Time

    ' 処理高速化設定
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' 乱数の初期化
    Randomize

    ' ワークシートを指定
    Set ws = Worksheets("Sheet1")
    ws.Cells.Clear ' 既存データをクリア

    ' 日付データの範囲
    sDate = DateSerial(2020, 1, 1)
    eDate = DateSerial(2024, 12, 31)

    ' データセット
    staffList = Array("フナノ ココ", "ヤギシ マロ", "クモダ リリ", "ノビラ チエ", "ミゾノ ユイ")
    clientList = Array("株式会社モフ", "有限会社ピコ", "合同会社ルナ", "株式会社ザク", "株式会社ペン", "有限会社グリ", "株式会社トポ")
    productList = Array( _
        Array("りんご", 120), Array("バナナ", 80), Array("オレンジ", 100), _
        Array("みかん", 90), Array("ぶどう", 200), Array("メロン", 500), _
        Array("スイカ", 800), Array("もも", 150), Array("すもも", 130), Array("パイナップル", 400) _
    )

    ' 配列の最大数を取得
    staffMax = UBound(staffList)
    clientsMax = UBound(clientList)
    productsMax = UBound(productList)
    
    ' 見出しの設定
    ws.Cells(1, 1).Resize(1, 8).Value = Array("日付", "曜日", "担当者名", "取引先", "商品", "単価", "数量", "売上")

    ' データ用配列のサイズ設定
    ReDim dataArray(1 To ROW_COUNT, 1 To 8)
    
    ' データ生成
    For i = 1 To ROW_COUNT
        ' ランダムな日付生成（土・日のデータ量の調整あり）
        Do
            currentDate = WorksheetFunction.RandBetween(sDate, eDate)
            weekdayNum = Weekday(currentDate)
        Loop While (weekdayNum = vbSunday And Rnd < 0.5) Or (weekdayNum = vbSaturday And Rnd < 0.3)
        
        ' 曜日の取得（日本語）
        dayOfWeek = Choose(weekdayNum, "日曜日", "月曜日", "火曜日", "水曜日", "木曜日", "金曜日", "土曜日")
        
        ' 商品選択
        rndIndex = Int((productsMax + 1) * Rnd)
        productName = productList(rndIndex)(0)
        unitPrice = productList(rndIndex)(1)
        
        ' 数量（1～10）
        quantity = Int(10 * Rnd) + 1

        ' データを配列に格納
        dataArray(i, 1) = currentDate
        dataArray(i, 2) = dayOfWeek
        dataArray(i, 3) = staffList(Int((staffMax + 1) * Rnd))
        dataArray(i, 4) = clientList(Int((clientsMax + 1) * Rnd))
        dataArray(i, 5) = productName
        dataArray(i, 6) = unitPrice
        dataArray(i, 7) = quantity
        dataArray(i, 8) = unitPrice * quantity
    Next i
    
    ' シートに一括書き込み
    ws.Range("A2").Resize(ROW_COUNT, 8).Value = dataArray
    
    ' 処理高速化設定を戻す
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ' 終了時間を記録
    Debug.Print "End:" & Time
    MsgBox "データ生成が完了しました！" & vbCrLf & "出力行数: " & ROW_COUNT, vbInformation
End Sub
初期設定で処理を高速化
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

大量のデータを処理する際は、画面の更新・再計算・イベント発生を一時的にオフにすることで処理が高速になります。

乱数の初期化とワークシートの準備
Randomize

Set ws = Worksheets("Sheet1")
ws.Cells.Clear

Randomize で毎回異なるランダムな数値を生成。これを設定しないと、毎回同じ乱数パターンになるため、データの偏りが発生する可能性があります。

ws.Cells.Clear で前回のデータをすっきりクリア。

データセットの定義
staffList = Array(...)
clientList = Array(...)
productList = Array(...)

担当者・取引先・商品と単価を配列で用意。これによりランダム選択が高速に行えます。

見出しとデータ格納用配列の準備
ws.Cells(1, 1).Resize(1, 8).Value = Array(...)

ReDim dataArray(1 To ROW_COUNT, 1 To 8)

A1～H1 に見出しを一括で設定。

作成したデータは一時的に dataArray に格納して後でまとめてシートに書き込みます。

メインのデータ生成ループ
For i = 1 To ROW_COUNT
    ' ランダムな日付
    ' 土日を少なくする工夫
    ' 商品・担当者・取引先をランダムに選択
    ' 数量と売上を計算
Next i

Do...Loop While で土日を減らし、平日中心のデータに。

各列のデータを整えて dataArray(i, 列) に格納。

Int((最大+1)*Rnd) で配列インデックスをランダム化。

一括でシートに書き込み
ws.Range("A2").Resize(ROW_COUNT, 8).Value = dataArray

この一行で、5万行 × 8列 のデータを一気に書き出します。ループで1行ずつ書くより圧倒的に高速。

処理を元に戻して完了
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

高速化の設定を戻し、通常モードに。

サンプルファイルダウンロード

このサンプルファイルは、VBAで生成した5万行の売上テストデータです。

日付・曜日・担当者名・取引先・商品・単価・数量・売上 の情報が含まれており、 データ分析やシステムテストなどに活用できます。

sales_test_data_50000rows.csvダウンロード
  • ファイル名: sales_test_data_50000rows.csv
  • データ件数: 50,000行
  • ファイル形式: CSV (Comma Separated Values)
  • エンコーディング: UTF-8
  • 区切り文字: カンマ ,
日付取引日付 (YYYY/MM/DD 形式)
曜日取引日付の曜日 (日曜日～土曜日)
担当者名取引を担当したスタッフの名前
取引先商品を購入した企業名
商品購入された商品の名前
単価商品の価格（円）
数量取引された商品の個数
売上単価 × 数量 で計算された売上金額（円）
データ使用の注意事項
  1. 本データは、架空の企業・スタッフ・商品の情報です。
  2. 本データは、個人用のテスト目的で使用してください。
  3. 本データは、ピボットテーブルやPower Queryの練習を目的として作成されたサンプルデータです。 情報の正確性や完全性について保証するものではありません。 本データを使用したことによるいかなる損害、誤動作、トラブルについて、作成者および提供者は 一切の責任を負いません。
  4. 本データを活用する際は、ご自身の責任で適切に検証・管理してください。

まとめ

今回は、Excel VBAを使って5万件のダミーの売上データを一括生成する方法をご紹介しました。

平日中心にデータを作るちょっとした工夫や、処理を速くする設定も入っているので、実用性もバッチリです。

今回のコードは、

  • ピボットテーブルの練習データが欲しいとき
  • VBAのサンプルとして参考にしたいとき

にぴったりな内容になっています。

「ちょうどいいデータが手元にない…」なんてときは、ぜひこの記事のコードやサンプルファイルを活用してみてくださいね！

こういうの作っておくと、ピボットテーブルや集計の練習がサクッとできて助かりますねっ！