【VBAリファレンス】Excel VBAで実現する日別データからの欠損月補完と自動集計術

概要：データ分析の落とし穴「欠損月」をVBAで克服する

Excelでのデータ分析業務において、最も頻繁に遭遇する困難の一つが「時系列データの不連続性」です。例えば、売上データや在庫データなど、日別のトランザクションを月次に集計したい場合、ある特定の月に一つもデータが存在しないと、Excelの標準的なピボットテーブルや関数による集計では、その月が「存在しないもの」として扱われてしまいます。

ビジネス上のレポート作成において、欠損月を無視することは誤った経営判断を招く恐れがあります。前月比や移動平均を計算する際、月が飛んでしまうと計算ロジックが破綻するからです。本記事では、VBAを用いて日別データから「欠損している年月」を正確に検出し、集計テーブルに動的に挿入・補完し、最終的な月次サマリーを作成するまでの高度なテクニックを解説します。

詳細解説：ロジックの構築とアルゴリズムの設計

欠損月を補完するプロセスは、大きく分けて以下の4つのフェーズで構成されます。

1. データの走査と期間の特定：データセット全体の「開始年月」と「終了年月」を特定します。
2. 辞書オブジェクト（Dictionary）を用いた存在確認：各年月がデータ内に存在するかを高速に判定します。
3. カレンダーの生成と欠損判定：開始年月から終了年月まで1ヶ月ずつ進めながら、辞書に存在しない月をリストアップします。
4. 集計テーブルへの出力とゼロ埋め：リストアップした年月を既存の集計結果に結合し、値が欠損している箇所には「0」を代入します。

この手法の最大の利点は、データ量が数万件を超えても、Dictionaryオブジェクトを活用することで計算量を最小限に抑えられる点です。VBAにおける「連想配列」は、単なるループ処理よりも圧倒的に高速であり、実務レベルのデータ処理において必須のスキルと言えます。

サンプルコード：欠損月を補完するVBA実装

以下のコードは、A列に日付、B列に売上金額がある前提で、別シートに月次集計を出力する実装例です。

Sub GenerateMonthlySummaryWithMissingMonths()
    Dim wsData As Worksheet, wsOut As Worksheet
    Dim lastRow As Long, i As Long
    Dim dict As Object, dictSum As Object
    Dim startDate As Date, endDate As Date, currDate As Date
    Dim targetMonth As String
    
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsOut = ThisWorkbook.Sheets("Report")
    Set dictSum = CreateObject("Scripting.Dictionary")
    
    ' 最終行の取得と集計
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    startDate = DateSerial(Year(wsData.Cells(2, 1).Value), Month(wsData.Cells(2, 1).Value), 1)
    endDate = DateSerial(Year(wsData.Cells(lastRow, 1).Value), Month(wsData.Cells(lastRow, 1).Value), 1)
    
    ' 集計処理
    For i = 2 To lastRow
        targetMonth = Format(wsData.Cells(i, 1).Value, "yyyy/mm")
        dictSum(targetMonth) = dictSum(targetMonth) + wsData.Cells(i, 2).Value
    Next i
    
    ' 欠損月の補完と出力
    wsOut.Cells(1, 1).Value = "年月"
    wsOut.Cells(1, 2).Value = "売上金額"
    
    currDate = startDate
    i = 2
    Do While currDate <= endDate
        targetMonth = Format(currDate, "yyyy/mm")
        wsOut.Cells(i, 1).Value = targetMonth
        If dictSum.Exists(targetMonth) Then
            wsOut.Cells(i, 2).Value = dictSum(targetMonth)
        Else
            wsOut.Cells(i, 2).Value = 0 ' 欠損月は0とする
        End If
        currDate = DateAdd("m", 1, currDate)
        i = i + 1
    Loop
    
    MsgBox "月次集計が完了しました。欠損月も補完済みです。", vbInformation
End Sub

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

VBAでコードを書く際、単に「動けば良い」という考え方は禁物です。実務環境では、データのフォーマットが微妙に変わったり、期間が極端に長くなったりすることがあります。以下の3点に注意してください。

1. 動的範囲の確保：`Range("A2:A100")`のように固定値で指定せず、`Cells(Rows.Count, 1).End(xlUp)`を使用して、データ行数に依存しない柔軟なコードを記述してください。
2. エラーハンドリングの導入：データが存在しないシートを指定した場合や、日付形式が正しくないデータが混入した場合に備え、`On Error GoTo`を用いたエラー処理を記述することで、システムの堅牢性を高めることができます。
3. コードの可読性：`DateSerial`や`DateAdd`関数を積極的に利用してください。日付の加算を`+ 30`のように単純な数値で行うのは危険です。うるう年や月ごとの日数の違いを吸収するため、日付関数によるロジック構築を徹底してください。

まとめ：自動化がもたらす信頼性の高いデータ分析

今回解説した「欠損月の補完」は、単なるExcelの操作技術を超え、データ整合性を担保するための重要なエンジニアリングプロセスです。手作業で空行を追加したり、ピボットテーブルの設定をいじったりする作業から卒業しましょう。VBAを用いてこのプロセスを自動化することで、人的ミスを排除し、常に正確で美しいレポートを即座に出力することが可能になります。

データ分析のプロフェッショナルとして、常に「データが欠けている可能性」を想定し、それをシステム側でどう補完するかを考える姿勢が、あなたの業務効率を飛躍的に向上させます。このコードをベースに、ご自身の業務環境に合わせてカスタマイズを行い、ぜひ日々の業務に組み込んでみてください。VBAという武器を正しく使いこなすことで、Excelは単なる表計算ソフトから、強力なビジネスインテリジェンスツールへと進化するのです。