明細から月別×項目別の集計表を作る（ピボットテーブルを使わない方法）

どんな場面で使うか

毎月の会議資料で、月別・分類別の売上表を同じレイアウトで作る作業です。ピボットテーブルは便利ですが、元データの範囲が変わったときの更新忘れや、項目が増えたときのレイアウト崩れが起きやすく、決まった形式の帳票には向かないことがあります。

この記事では、集計の行（商品分類）と列（月）を Dictionary で管理し、配列の中で足し込んでから表に書き出します。

前提とするデータ

「明細」シートのA列が日付、C列が商品分類、E列が金額の表を想定します。集計結果は「月次集計」シートに、縦に分類、横に月を並べて出力します。

コード

まず分類と月の一覧を作って行・列の番号を割り振り、次にもう一度明細を回って該当するマスに金額を足します。

Sub MonthlyCross()
    Dim ws As Worksheet, outWs As Worksheet
    Dim data As Variant, tbl() As Double
    Dim rowDic As Object, colDic As Object
    Dim i As Long, r As Long, c As Long, k As Variant, ym As String

    Set ws = ThisWorkbook.Worksheets("明細")
    Set outWs = ThisWorkbook.Worksheets("月次集計")
    Set rowDic = CreateObject("Scripting.Dictionary")
    Set colDic = CreateObject("Scripting.Dictionary")
    data = ws.Range("A2:E" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Value

    ' 1回目：分類（行）と年月（列）の一覧を作る
    For i = 1 To UBound(data, 1)
        If IsDate(data(i, 1)) Then
            ym = Format(data(i, 1), "yyyy/mm")
            If Not colDic.Exists(ym) Then colDic.Add ym, colDic.Count + 1
            If Not rowDic.Exists(data(i, 3)) Then rowDic.Add data(i, 3), rowDic.Count + 1
        End If
    Next i
    If rowDic.Count = 0 Then Exit Sub

    ' 2回目：該当するマスに金額を足す
    ReDim tbl(1 To rowDic.Count, 1 To colDic.Count)
    For i = 1 To UBound(data, 1)
        If IsDate(data(i, 1)) And IsNumeric(data(i, 5)) Then
            r = rowDic(data(i, 3))
            c = colDic(Format(data(i, 1), "yyyy/mm"))
            tbl(r, c) = tbl(r, c) + data(i, 5)
        End If
    Next i

    outWs.Cells.Clear
    outWs.Range("A1").Value = "分類＼年月"
    For Each k In colDic.Keys: outWs.Cells(1, colDic(k) + 1).Value = "'" & k: Next k
    For Each k In rowDic.Keys: outWs.Cells(rowDic(k) + 1, 1).Value = k: Next k
    outWs.Range("B2").Resize(rowDic.Count, colDic.Count).Value = tbl
    outWs.Range("B2").Resize(rowDic.Count, colDic.Count).NumberFormat = "#,##0"
End Sub

コードのポイント

Dictionary の値に「何行目・何列目に出すか」の番号を持たせているのが工夫の部分です。こうすると、明細を1行読むたびに、どのマスに足せばよいかが即座に分かります。

年月の見出しは、先頭に「'」を付けて文字列として書き込んでいます。付けないと Excel が日付に変換し、表示が「Oct-26」のように変わってしまうことがあるためです。月の並び順は明細に出てきた順になるので、明細を日付順に並べてから実行するか、キーを並べ替えてから出力します。

応用

行の見出しを担当者や取引先に変えれば、担当者別・取引先別の月次表になります。また、合計の行や列が必要な場合は、配列 tbl を出力したあとで、SUM 関数を最後の行・列に入れるか、VBA で合計を計算して書き込みます。

ほかの記事