この記事では、Excel VBAを使用して部署別売上報告書を自動作成する方法について詳しく説明します。初心者でも実践的に使用できるよう、具体的なコード例とその解説、そして応用例を3つを提供します。
Excel VBAの基本
Excel VBA（Visual Basic for Applications）は、Microsoft Excelに組み込まれたプログラミング言語です。これを用いると、単純作業の自動化だけでなく、高度なデータ分析やレポート作成も可能になります。
そもそも、どこにVBAコードを書いて、どう実行すれば良いのか分からない場合は、以下の記事をご参照ください。
基本の部署別売上報告書の自動作成
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の解説
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
1. **条件を満たす部署のみを集計**: `SumIfs`関数で集計した売上が100万以上の部署のみを報告書に出力します。100万未満の部署は報告書から削除されます。
まとめ
Excel VBAを利用することで、部署別の売上報告書の自動作成が非常に簡単になります。上記の基本的な手法と応用例を組み合わせることで、実際の業務のニーズに合わせたカスタマイズが可能です。この機会に、VBAを使って日常の作業を効率化してみてはいかがでしょうか。
VBAも良いけどパワークエリも良い
VBAの解説をしてきましたが、VBAは正直煩雑でメンテナンス性が悪いです。最近はモダンExcelと呼ばれるパワークエリやパワーピボットへのシフトが進んできています。本サイトでもパワークエリの特集をしており、サンプルデータを含む全11回の学習コンテンツでパワークエリを習得することができます。
クリックするとパワークエリの全11講座が表示されます。
-
【初心者向け】パワークエリ入門：ETLツールを使ってエクセルデータを簡単に整形・統合しよう！(1/11)
-
【実践ガイド】パワークエリでデータ収集：Excel、CSV、PDF、Webデータを簡単に取り込む方法をマスターしよう！(2/11)
-
【総力特集】パワークエリで列操作をマスター：選択、変更、移動、削除、結合、分割の詳細解説＆実践テクニック！(3/11)
-
【徹底解説】パワークエリで行操作をマスター！フィルター・保持・削除テクニックと練習用エクセルで実践学習(4/11)
-
パワークエリでデータクレンジング: 文字列結合、0埋め、テキスト関数をマスター(5/11)
-
パワークエリで四捨五入、切り捨て、切り上げをマスターする方法（6/11）
-
パワークエリで効率的なデータグループ化を実現する方法(7/11)
-
パワークエリで時間と日付の計算をマスター！便利な関数を使って効率アップ(8/11)
-
パワークエリで条件別集計をマスターする方法(9/11)
-
Excelパワークエリでクロス集計表とデータベース形式を瞬時に変換する方法(10/11)
-
Excelパワークエリ入門: 効率的なデータ整理をマスターしよう！(11/11)
パワーピボットの記事はありません。興味がある場合は、書籍で学んでみてください
コメント