Excel VBAを利用した部署別売上報告書の自動作成方法

この記事では、Excel VBAを使用して部署別売上報告書を自動作成する方法について詳しく説明します。初心者でも実践的に使用できるよう、具体的なコード例とその解説、そして応用例を3つを提供します。

基本の部署別売上報告書の自動作成

Excel VBAを利用することで、時間をかけずに部署別の売上報告書を簡単に作成できます。以下のコードは、ある表から各部署の月別売上を自動的に集計し、新しいシートに報告書として出力するものです。

Sub CreateSalesReport()
    Dim wsData As Worksheet, wsReport As Worksheet
    Dim LastRow As Long

    ' 元のデータがあるシートを設定
    Set wsData = ThisWorkbook.Sheets("SalesData")

    ' 新しい報告書シートを作成
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "Report_" & Format(Date, "yyyy_mm_dd")

    ' タイトルを設定
    wsReport.Cells(1, 1).Value = "部署"
    wsReport.Cells(1, 2).Value = "月"
    wsReport.Cells(1, 3).Value = "売上"

    ' データの最後の行を取得
    LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row

    ' 部署別に集計
    wsData.Range("A2:C" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsReport.Range("A2"), Unique:=True

End Sub

コードの詳細解説

1. **Worksheet変数の定義**: `wsData`は元のデータがあるシート、`wsReport`は新しく作成する報告書用のシートを参照するための変数です。
2. **シートの設定**: `wsData`に”SalesData”という名前のシートを関連付けます。
3. **新しい報告書シートの作成**: 現在の日付を用いて報告書用の新しいシートを作成します。
4. **タイトルの設定**: 報告書のヘッダー（部署、月、売上）を設定します。
5. **データの最後の行の取得**: 元のデータの最後の行番号を取得します。
6. **部署別に集計**: `AdvancedFilter`メソッドを使用して、部署別の売上を集計し、新しいシートに出力します。

応用例1: 売上の合計を出力

部署別、月別の売上の合計を報告書に出力したい場合は、以下のようにコードを変更できます。

Sub CreateSalesReportWithSum()
    Dim wsData As Worksheet, wsReport As Worksheet
    Dim LastRow As Long
    Dim rng As Range, cell As Range
    Dim Sum As Double

    '（前のコードと同じ部分は省略）

    ' 部署別に集計
    Set rng = wsReport.Range("A2:A" & wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row)

    For Each cell In rng
        Sum = Application.WorksheetFunction.SumIfs(wsData.Range("C:C"), wsData.Range("A:A"), cell.Value, wsData.Range("B:B"), cell.Offset(0, 1).Value)
        cell.Offset(0, 2).Value = Sum
    Next cell

End Sub

応用例1の解説

新しい部分のみを解説します。
1. **売上の合計を出力**: `SumIfs`関数を使用して、特定の部署、月の売上の合計を求め、報告書の対応するセルに出力します。

応用例2: グラフの自動作成

部署別、月別の売上をグラフで表現したい場合は、以下の方法を用いることで簡単にグラフを挿入できます。

Sub CreateSalesReportWithGraph()
    Dim wsData As Worksheet, wsReport As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim rng As Range
    Dim chart As Chart

    '（前のコードと同じ部分は省略）

    ' データ範囲を設定
    LastCol = wsReport.Cells(1, wsReport.Columns.Count).End(xlToLeft).Column
    Set rng = wsReport.Range("A1:C" & LastCol)

    ' グラフの作成
    Set chart = wsReport.Shapes.AddChart2(251, xlColumnClustered).Chart
    chart.SetSourceData rng

End Sub

応用例2の解説

1. **データ範囲の設定**: 報告書のデータの範囲を設定します。
2. **グラフの作成**: データの範囲をもとに、グラフを作成し、報告書に挿入します。

応用例3: データのフィルタリングと集計
特定の条件（例：売上が100万以上の部署のみ）を満たす部署だけを報告書に出力したい場合、以下のコードを利用します。

Sub CreateFilteredSalesReport()
    Dim wsData As Worksheet, wsReport As Worksheet
    Dim LastRow As Long
    Dim rng As Range, cell As Range
    Dim Sum As Double

    '（前のコードと同じ部分は省略）

    ' 条件を満たす部署のみを集計
    Set rng = wsReport.Range("A2:A" & wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row)

    For Each cell In rng
        Sum = Application.WorksheetFunction.SumIfs(wsData.Range("C:C"), wsData.Range("A:A"), cell.Value, wsData.Range("B:B"), cell.Offset(0, 1).Value)
        If Sum >= 1000000 Then
            cell.Offset(0, 2).Value = Sum
        Else
            cell.EntireRow.Delete
        End If
    Next cell

End Sub

応用例3の解説

1. **条件を満たす部署のみを集計**: `SumIfs`関数で集計した売上が100万以上の部署のみを報告書に出力します。100万未満の部署は報告書から削除されます。

まとめ

Excel VBAを利用することで、部署別の売上報告書の自動作成が非常に簡単になります。上記の基本的な手法と応用例を組み合わせることで、実際の業務のニーズに合わせたカスタマイズが可能です。この機会に、VBAを使って日常の作業を効率化してみてはいかがでしょうか。