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

    ' ピボットテーブルを作成
    Dim rangeData As Range
    Set rangeData = Sheets("Sheet1").Range("A1").CurrentRegion
    Dim pivotSheet As Worksheet
    Set pivotSheet = ThisWorkbook.Sheets.Add
    pivotSheet.Name = "売上集計"
    Dim pivotTable As pivotTable
    Set pivotTable = pivotSheet.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rangeData), TableDestination:=pivotSheet.Range("A3"), TableName:="売上集計")
    pivotTable.PivotFields("商品部門").Orientation = xlRowField
    pivotTable.PivotFields("商品部門").Position = 1
    pivotTable.PivotFields("商品名").Orientation = xlRowField
    pivotTable.PivotFields("商品名").Position = 2
    pivotTable.PivotFields("日付").Orientation = xlColumnField
    pivotTable.PivotFields("日付").Position = 1
    pivotTable.AddDataField pivotTable.PivotFields("売上金額"), "売上金額の合計", xlSum


    ' ピボットテーブルからグラフを作成
    Dim chartSheet As Worksheet
    Set chartSheet = ThisWorkbook.Sheets.Add
    chartSheet.Name = "売上グラフ"
    Dim chartObj As ChartObject
    Set chartObj = chartSheet.ChartObjects.Add(0, 0, 500, 300)
    chartObj.Chart.SetSourceData Source:=pivotTable.TableRange1
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "商品部門別の売上"
    chartObj.Chart.HasLegend = False
End Sub