【VBAリファレンス】VBA再入門月別ブックから部署別シートへ担当者別集計を自動化する高度なデータ統合術

概要：データ集計のボトルネックをVBAで解消する

Excel業務において、毎月発生する「月別ブックからのデータ転記・集計」は、多くの現場で担当者の時間を奪う大きなボトルネックとなっています。特に、複数の部署が存在し、さらにその中で担当者ごとに細かく分類されたデータを整理する場合、手作業ではミスが頻発し、修正コストも膨大です。

本記事では、VBA再入門シリーズの第5弾として、月別のソースブックからデータを抽出し、部署ごとのシートに振り分け、さらに担当者別の売上や件数を自動集計する仕組みを解説します。単なるコピペコードではなく、実務で耐えうる堅牢なアーキテクチャの構築方法を学びます。

詳細解説：多次元データ処理のロジック構築

今回のテーマは「1対多のデータ構造をいかに効率的に振り分けるか」という点にあります。具体的には、以下の3つのステップで処理を構築します。

1. ソースデータの正規化：月別ブックからデータを配列に一括読み込みし、メモリ上で処理することで高速化を図ります。
2. 辞書オブジェクト（Dictionary）の活用：部署名や担当者名をキーとして、集計対象を一意に特定します。これにより、ループ回数を劇的に削減できます。
3. 動的シート生成と出力：集計結果を書き出す際に、対象シートが存在しない場合は自動生成し、既存データがある場合はクリアして再計算する「再実行可能性」を担保します。

なぜDictionaryオブジェクトを使うのか。それは、検索処理の計算量がO(1)であり、数万件のデータであっても瞬時に集計が可能だからです。RangeオブジェクトのFindメソッドを繰り返す手法は、データ量が増加した際に致命的な遅延を招きます。プロフェッショナルなVBA開発において、メモリ内でのデータ操作は必須のスキルです。

サンプルコード：高速集計のための設計図

以下に、月別ブックから部署・担当者別に集計を行うための堅牢なコードを提示します。


Option Explicit

Sub AggregateDataByDepartmentAndStaff()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim dict As Object
    Dim arrData As Variant
    Dim i As Long
    Dim key As String
    Dim wsDest As Worksheet
    Dim rngTarget As Range
    
    ' Dictionaryの初期化
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' ソースデータの読み込み
    Set wbSource = Workbooks.Open("C:\Data\MonthlyData.xlsx")
    Set wsSource = wbSource.Sheets(1)
    arrData = wsSource.Range("A2:D" & wsSource.Cells(Rows.Count, 1).End(xlUp).Row).Value
    wbSource.Close False
    
    ' データの集計ロジック
    For i = 1 To UBound(arrData, 1)
        ' キー生成: 部署名 & "_" & 担当者名
        key = arrData(i, 1) & "|" & arrData(i, 2)
        
        If Not dict.Exists(key) Then
            dict(key) = arrData(i, 3) ' 数値を格納
        Else
            dict(key) = dict(key) + arrData(i, 3)
        End If
    Next i
    
    ' 集計結果の出力
    Dim vKey As Variant
    Dim arrParts As Variant
    
    For Each vKey In dict.Keys
        arrParts = Split(vKey, "|")
        Set wsDest = GetOrCreateSheet(arrParts(0))
        
        ' 担当者行を探して書き込み（簡易的な実装）
        With wsDest
            Set rngTarget = .Columns(1).Find(arrParts(1), LookAt:=xlWhole)
            If rngTarget Is Nothing Then
                Dim lastRow As Long
                lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Cells(lastRow, 1).Value = arrParts(1)
                .Cells(lastRow, 2).Value = dict(vKey)
            Else
                rngTarget.Offset(0, 1).Value = dict(vKey)
            End If
        End With
    Next vKey
    
    MsgBox "集計が完了しました。", vbInformation
End Sub

Function GetOrCreateSheet(sheetName As String) As Worksheet
    On Error Resume Next
    Set GetOrCreateSheet = ThisWorkbook.Sheets(sheetName)
    If GetOrCreateSheet Is Nothing Then
        Set GetOrCreateSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        GetOrCreateSheet.Name = sheetName
    End If
    On Error GoTo 0
End Function

実務アドバイス：メンテナンス性を高める工夫

実務でコードを運用する際、最も重要なのは「エラーハンドリング」と「拡張性」です。

まず、ソースブックのパスやシート名が変更された場合、コードをいちいち書き換えるのは非効率です。設定シートを別途設け、そこからパスを読み込むように設計してください。また、データの中身が空だった場合や、予期せぬ文字列が含まれていた場合に処理が止まらないよう、`On Error GoTo`によるエラートラップを適切に配置することが重要です。

次に、「担当者別集計」の要件は頻繁に変更されます。例えば、「売上」だけでなく「件数」や「利益率」も算出したいという要望が後から必ず出てきます。そのため、集計ロジックを独立したプロシージャに切り出し、引数で集計対象列を指定できるようにしておくと、コードの再利用性が飛躍的に向上します。

さらに、VBAの実行ログをテキストファイルに出力する仕組みを組み込むことを推奨します。誰がいつ実行し、何件のデータが処理されたのかを記録しておくことで、万が一の数値不整合が発生した際の原因特定が容易になります。

まとめ：自動化がもたらす価値

月別ブックからの集計業務を自動化することは、単なる作業時間の短縮にとどまりません。人間が手作業で行うプロセスを排除することで、データ品質の安定化、つまり「常に正しい数値が経営層に届く環境」を実現できることに最大の価値があります。

今回紹介したDictionaryオブジェクトを用いた集計手法は、VBAにおけるデータ処理の基本でありながら、極めて強力な武器となります。ぜひお手元の環境でこのコードをベースに、自社の業務フローに適合するようカスタマイズを試みてください。

VBA再入門の道は、一つひとつのコードを書き換え、自分の業務に当てはめる試行錯誤の積み重ねです。エラーが出ても恐れる必要はありません。そのエラーこそが、あなたのスキルを一段上のステージへ引き上げるための課題なのです。次回の記事では、この集計結果をPDF化し、自動でメール送信するフローについて解説します。引き続き、実務に直結する自動化の技術を磨いていきましょう。