ChatGPTでVBAマクロを作成する方法をわかりやすく解説！

Sub SumDailySales()
    Dim wsSales As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long, i As Long, summaryRow As Long
    Dim dailySales As Double
    Dim salesDate As Variant ' Variant型に変更することで、任意の型のデータを保持できる
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' "日次売上データ"シートを設定
    Set wsSales = ThisWorkbook.Sheets("Sheet1") ' シート名をアップロードされたファイルに合わせて設定
    lastRow = wsSales.Cells(wsSales.Rows.Count, "A").End(xlUp).Row
    
    ' 日付ごとに売上を集計
    For i = 2 To lastRow
        salesDate = wsSales.Cells(i, 1).Text ' 日付をテキスト形式で取得
        dailySales = wsSales.Cells(i, 4).Value
        If dict.Exists(salesDate) Then
            dict(salesDate) = dict(salesDate) + dailySales
        Else
            dict.Add salesDate, dailySales
        End If
    Next i
    
    ' 集計結果を新しいシートに出力
    Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsSummary.Name = "日別売上合計"
    wsSummary.Cells(1, 1).Value = "日付"
    wsSummary.Cells(1, 2).Value = "売上合計"
    summaryRow = 2
    
    For Each salesDate In dict.Keys ' salesDateはVariant型なので、ここで問題なく使用できる
        wsSummary.Cells(summaryRow, 1).Value = salesDate
        wsSummary.Cells(summaryRow, 2).Value = dict(salesDate)
        summaryRow = summaryRow + 1
    Next salesDate
    
    ' 列の幅を自動調整
    wsSummary.Columns("A:B").AutoFit
End Sub

Sub CreateEstimate()
    Dim wsRequests As Worksheet, wsProducts As Worksheet, wsServices As Worksheet
    Dim wsEstimate As Worksheet
    Dim row As Long, productRow As Long, serviceRow As Long
    Dim totalPrice As Double
    
    ' シートを設定
    Set wsRequests = ThisWorkbook.Sheets("顧客要望リスト")
    Set wsProducts = ThisWorkbook.Sheets("商品リスト")
    Set wsServices = ThisWorkbook.Sheets("サービスリスト")
    ' 新しい見積りシートを作成
    Set wsEstimate = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsEstimate.Name = "見積り結果"
    
    ' 見積りシートのヘッダーを設定
    wsEstimate.Cells(1, 1).Value = "顧客ID"
    wsEstimate.Cells(1, 2).Value = "顧客名"
    wsEstimate.Cells(1, 3).Value = "見積り額"
    
    ' 顧客要望リストをループ
    row = 2 ' ヘッダーをスキップ
    Do While wsRequests.Cells(row, 1).Value <> ""
        Dim productId As String, serviceId As String
        Dim productQuantity As String, serviceQuantity As String
        
        productId = wsRequests.Cells(row, 3).Value
        productQuantity = wsRequests.Cells(row, 4).Value
        serviceId = wsRequests.Cells(row, 5).Value
        serviceQuantity = wsRequests.Cells(row, 6).Value
        
        ' 商品とサービスの見積りを計算
        totalPrice = CalculateTotal(productId, productQuantity, wsProducts) + _
                     CalculateTotal(serviceId, serviceQuantity, wsServices)
                     
        ' 結果を見積りシートに出力
        wsEstimate.Cells(row, 1).Value = wsRequests.Cells(row, 1).Value
        wsEstimate.Cells(row, 2).Value = wsRequests.Cells(row, 2).Value
        wsEstimate.Cells(row, 3).Value = totalPrice
        
        row = row + 1
    Loop
End Sub

Function CalculateTotal(idsStr As String, quantitiesStr As String, ws As Worksheet) As Double
    Dim ids As Variant
    Dim quantities As Variant
    Dim total As Double
    Dim i As Long, j As Long
    
    ' 文字列を配列に変換
    ids = StringToArray(idsStr)
    quantities = StringToArray(quantitiesStr)
    
    total = 0
    For i = LBound(ids) To UBound(ids)
        For j = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            If ws.Cells(j, 1).Value = ids(i) Then
                total = total + (ws.Cells(j, 3).Value * quantities(i))
                Exit For
            End If
        Next j
    Next i
    CalculateTotal = total
End Function

Function StringToArray(str As String) As Variant
    ' 不要な文字を削除
    str = Replace(str, "[", "")
    str = Replace(str, "]", "")
    str = Replace(str, "'", "")
    str = Replace(str, " ", "")
    
    ' カンマで分割して配列を返す
    StringToArray = Split(str, ",")
End Function