Excel VBAを使用して曜日別の商品売上トレンドレポートを自動生成する方法

この記事では、Excel VBAを使用して曜日別の商品売上トレンドレポートを自動生成する方法について解説します。VBAを活用することで、手作業でのレポート作成から解放され、効率的にデータ分析を行うことが可能になります。

基本的なVBAの処理コード

ExcelのデータをVBAで操作する際の基本的な処理を以下に示します。

Sub CreateReportByWeekday()
    Dim LastRow As Long
    Dim i As Long
    Dim WeekdayName As String
    Dim TotalSales As Double
    
    ' 最終行を取得
    LastRow = ThisWorkbook.Sheets("SalesData").Cells(ThisWorkbook.Sheets("SalesData").Rows.Count, "A").End(xlUp).Row

    ' レポート用のシートをクリア
    ThisWorkbook.Sheets("Report").Cells.Clear

    ' 曜日別に売上を集計
    For i = 2 To LastRow
        WeekdayName = WeekdayName(Weekday(ThisWorkbook.Sheets("SalesData").Cells(i, 1).Value))
        TotalSales = TotalSales + ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value
        ThisWorkbook.Sheets("Report").Cells(Weekday(ThisWorkbook.Sheets("SalesData").Cells(i, 1).Value), 2).Value = TotalSales
    Next i
End Sub

コードの詳細解説

– 最初に、売上データの最終行を取得しています。これは、データの範囲を特定するためのものです。
– レポート用のシートを初期化して、前回のデータが残っている場合にはそれをクリアします。
– ループを使用して、曜日ごとの売上を集計します。曜日はWeekday関数を使用して取得し、WeekdayName関数で曜日名を取得しています。
– 最後に、集計した売上をレポートシートに出力します。

応用例

1. 曜日ごとの平均売上の算出

Sub CalculateAverageSalesByWeekday()
    Dim LastRow As Long
    Dim i As Long
    Dim WeekdayCount(1 To 7) As Integer
    Dim WeekdaySales(1 To 7) As Double
    Dim WeekdayName As String
    
    LastRow = ThisWorkbook.Sheets("SalesData").Cells(ThisWorkbook.Sheets("SalesData").Rows.Count, "A").End(xlUp).Row

    ' 曜日ごとの売上とカウントを集計
    For i = 2 To LastRow
        WeekdayName = Weekday(ThisWorkbook.Sheets("SalesData").Cells(i, 1).Value)
        WeekdaySales(WeekdayName) = WeekdaySales(WeekdayName) + ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value
        WeekdayCount(WeekdayName) = WeekdayCount(WeekdayName) + 1
    Next i

    ' 曜日ごとの平均売上を計算
    For i = 1 To 7
        ThisWorkbook.Sheets("Report").Cells(i, 2).Value = WeekdaySales(i) / WeekdayCount(i)
    Next i
End Sub

2. 曜日別の最高売上の算出

Sub CalculateMaxSalesByWeekday()
    Dim LastRow As Long
    Dim i As Long
    Dim WeekdayName As String
    Dim MaxSales(1 To 7) As Double
    
    LastRow = ThisWorkbook.Sheets("SalesData").Cells(ThisWorkbook.Sheets("SalesData").Rows.Count, "A").End(xlUp).Row

    For i = 2 To LastRow
        WeekdayName = Weekday(ThisWorkbook.Sheets("SalesData").Cells(i, 1).Value)
        If ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value > MaxSales(WeekdayName) Then
            MaxSales(WeekdayName) = ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value
        End If
    Next i

    For i = 1 To 7
        ThisWorkbook.Sheets("Report").Cells(i, 3).Value = MaxSales(i)
    Next i
End Sub

3. 曜日別の最低売上の算出

Sub CalculateMinSalesByWeekday()
    Dim LastRow As Long
    Dim i As Long
    Dim WeekdayName As String
    Dim MinSales(1 To 7) As Double
    
    LastRow = ThisWorkbook.Sheets("SalesData").Cells(ThisWorkbook.Sheets("SalesData").Rows.Count, "A").End(xlUp).Row
    ' 初期値を高く設定
    For i = 1 To 7
        MinSales(i) = 999999999
    Next i
    For i = 2 To LastRow
        WeekdayName = Weekday(ThisWorkbook.Sheets("SalesData").Cells(i, 1).Value)
        If ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value < MinSales(WeekdayName) Then
            MinSales(WeekdayName) = ThisWorkbook.Sheets("SalesData").Cells(i, 3).Value
        End If
    Next i
    For i = 1 To 7
        ThisWorkbook.Sheets("Report").Cells(i, 4).Value = MinSales(i)
    Next i
End Sub

まとめ

Excel

VBAを活用することで、曜日別の商品売上トレンドレポートの自動生成が容易になります。この技術を利用すれば、日々の業務効率が大幅にアップします。ぜひ、上記のコードを参考にして、独自のレポートを作成してみてください。