Excelおさるくんのastro-notion-blog

Option Explicit

Sub GenerateSalesData()
    ' マスターデータの配列を定義
    Dim masterData As Variant
    masterData = Array(Array(1, "佐藤商事", "食品", "F001", "チョコレート", 150), _
                       Array(2, "田中電機", "家電", "E022", "スマートフォン", 80000), _
                       Array(3, "山田化粧品", "化粧品", "C003", "リップスティック", 2000), _
                       Array(4, "加藤衣料品", "衣料品", "A010", "ファッションTシャツ", 500), _
                       Array(5, "伊藤エンターテイメント", "エンターテイメント", "M007", "映画「スパイダーマン」", 1800), _
                       Array(6, "木村日用品", "日用品", "H031", "バスタオル", 1200), _
                       Array(7, "渡辺薬品", "医薬品", "P005", "風邪薬", 800), _
                       Array(8, "小林スポーツ用品", "スポーツ用品", "S019", "バスケットボール", 2500), _
                       Array(9, "鈴木書店", "書籍", "B006", "小説「夏目漱石」", 1200), _
                       Array(10, "高橋家具", "家具", "F055", "ベッド", 35000))
    
    ' 行数を取得
    Dim numRows As Long
    numRows = InputBox("何行の売上表を生成しますか？", "行数の入力")
    
    ' ヘッダー行を作成
    Range("A1").Value = "日付"
    Range("B1").Value = "企業名"
    Range("C1").Value = "商品部門"
    Range("D1").Value = "商品コード"
    Range("E1").Value = "商品名"
    Range("F1").Value = "単価"
    Range("G1").Value = "数量"
    Range("H1").Value = "売上金額"
    
    ' ランダムな日付の範囲を指定
    Dim startDate As Date
    startDate = DateSerial(2023, 1, 1)
    Dim endDate As Date
    endDate = DateSerial(2023, 12, 31)
    
    ' データ行を生成
    Dim i As Long
    For i = 1 To numRows
        ' ランダムな日付を生成
        Dim randomDate As Date
        randomDate = Int((endDate - startDate + 1) * Rnd + startDate)
        
        ' 企業名、商品部門、商品コード、商品名、単価をランダムに選択
        Dim randomIndex As Long
        randomIndex = Int((UBound(masterData) - LBound(masterData) + 1) * Rnd + LBound(masterData))
        Dim company As String
        company = masterData(randomIndex)(1)
        Dim department As String
        department = masterData(randomIndex)(2)
        Dim productCode As String
        productCode = masterData(randomIndex)(3)
        Dim productName As String
        productName = masterData(randomIndex)(4)
        Dim unitPrice As Long
        unitPrice = masterData(randomIndex)(5)
        
        ' 数量をランダムに生成
        Dim quantity As Long
        quantity = Int((10 - 1 + 1) * Rnd + 1)
        
        ' 売上金額を計算
        Dim salesAmount As Long
        salesAmount = unitPrice * quantity
        
        ' データを出力
        Range("A" & i + 1).Value = Format(randomDate, "yyyy/mm/dd")
        Range("B" & i + 1).Value = company
        Range("C" & i + 1).Value = department
        Range("D" & i + 1).Value = productCode
        Range("E" & i + 1).Value = productName
        Range("F" & i + 1).Value = unitPrice
        Range("G" & i + 1).Value = quantity
        Range("H" & i + 1).Value = salesAmount
    Next i
    
End Sub