【VBAリファレンス】VBA再入門月別ブックより部署別シートに担当別に集計するNo3

VBA再入門：月別ブックから部署別シートへ担当者別に集計する高度な自動化手法

業務現場において、月ごとに作成されるExcelブックから必要なデータを抽出し、部署ごとのシートに担当者別の実績を集計する作業は、多くの事務職や管理職が頭を悩ませる定型業務です。本稿では、前回の基礎編から一歩踏み込み、より堅牢で保守性の高いVBAコードの実装方法を解説します。

単にデータを転記するだけでなく、エラーハンドリング、動的な範囲指定、そして将来的な仕様変更に強いコードの書き方をマスターすることで、あなたのExcel業務は劇的に効率化されます。

詳細解説：多層的なデータ処理のアーキテクチャ

今回のテーマである「月別ブックから部署別シートへの集計」を実現するためには、以下の4つのロジックを正確に構築する必要があります。

1. ソースデータの特定：月別ブックを開き、対象となるデータ範囲を正確に特定する。
2. 転記先の動的判定：部署名に応じて、どのシートのどの行にデータを配置するかを決定する。
3. 担当者別集計のロジック：Dictionaryオブジェクトを用いて、担当者単位で数値を蓄積する。
4. データのクリーンアップと出力：出力先を事前にクリアし、計算結果を書き出す。

特に重要なのは、Dictionaryオブジェクトの活用です。VBAにおいて「担当者ごと」の集計を行う際、If文を多用するとコードが複雑化し、メンテナンスが困難になります。Dictionaryを使用することで、重複排除と合計値の算出を驚くほどシンプルに記述できます。

サンプルコード：実務で使える堅牢な集計マクロ

以下のコードは、月別ブックの「データ」シートから、メインブックの「部署別」シートへ担当者別の合計値を転記する実用的なサンプルです。

Option Explicit

' 必要なライブラリ：Microsoft Scripting Runtime
Sub AggregateDataByDepartment()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim deptName As String, staffName As String
    Dim amount As Double
    Dim dict As Object
    
    ' 1. ソースブックの設定（実務ではファイル選択ダイアログを使用することを推奨）
    Set wbSource = Workbooks.Open("C:\Reports\2023_October.xlsx")
    Set wsSource = wbSource.Sheets("Data")
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    
    ' 2. 集計用Dictionaryの初期化
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 3. データ集計ループ
    For i = 2 To lastRow
        deptName = wsSource.Cells(i, 1).Value ' 部署名
        staffName = wsSource.Cells(i, 2).Value ' 担当者名
        amount = wsSource.Cells(i, 3).Value    ' 金額
        
        ' キーの作成：部署名と担当者を結合
        Dim key As String
        key = deptName & "|" & staffName
        
        If dict.Exists(key) Then
            dict(key) = dict(key) + amount
        Else
            dict.Add key, amount
        End If
    Next i
    
    ' 4. 出力処理
    For Each wsTarget In ThisWorkbook.Worksheets
        If wsTarget.Name <> "Menu" Then
            wsTarget.Range("B2:C100").ClearContents
            Dim rowIdx As Long: rowIdx = 2
            
            Dim k As Variant
            For Each k In dict.Keys
                If Split(k, "|")(0) = wsTarget.Name Then
                    wsTarget.Cells(rowIdx, 2).Value = Split(k, "|")(1)
                    wsTarget.Cells(rowIdx, 3).Value = dict(k)
                    rowIdx = rowIdx + 1
                End If
            Next k
        End If
    Next wsTarget
    
    wbSource.Close False
    MsgBox "集計が完了しました。", vbInformation
End Sub

実務アドバイス：保守性と拡張性を高めるためのポイント

VBAを業務で活用する際、最も注意すべきは「ハードコーディング」の回避です。上記のコードではファイルパスを直接記述していますが、実務では「ファイル選択ダイアログ（FileDialog）」を使用してユーザーにファイルを選ばせる形にすべきです。これにより、ファイル名が変わるたびにコードを修正する必要がなくなります。

また、エラーハンドリングについても意識してください。例えば、ブックが開けない場合や、指定したシートが存在しない場合にマクロが途中で停止してしまうと、ユーザーは混乱します。On Error GoTo ラベル を使用し、例外発生時にはメッセージを表示して安全に終了させる設計が、プロフェッショナルには求められます。

さらに、パフォーマンスの最適化も重要です。画面の更新を抑制する「Application.ScreenUpdating = False」や、自動計算を一時停止する「Application.Calculation = xlCalculationManual」をコードの冒頭と末尾に配置するだけで、処理速度は劇的に向上します。特に数万行を超えるデータを扱う場合は、これらの処理は必須です。

まとめ：VBAによる自動化の先にあるもの

月別ブックから部署別シートへの集計は、単なる転記作業ではありません。これは、組織内のデータフローを可視化し、意思決定のスピードを上げるための重要なプロセスです。

今回紹介したDictionaryオブジェクトや動的なシート操作、エラーハンドリングの手法は、VBAにおける「標準的なベストプラクティス」です。これらを習得することで、あなたは単に「動くコードを書く人」から、「業務の仕組みそのものを改善できるエンジニア」へと成長することができます。

VBAはレガシーな言語と言われることもありますが、Excelがビジネスの現場で使われ続ける限り、その有用性は失われません。今回紹介したコードをベースに、ご自身の環境に合わせてカスタマイズを繰り返し、ぜひ「自分だけの最強の集計エンジン」を構築してください。継続的な改善こそが、自動化の真髄です。