VBAで複数のExcelファイルのデータを集計する

「フォルダに入っている複数のExcelファイルを開いて、日付・商品名が同じデータを合算する」という集計作業をVBAで自動化します。ファイルを1つずつ開いて手計算しなくてよくなるコードを解説します。

単にデータを1つのシートに並べるだけの転記ではなく、Scripting.Dictionary を使って「同じ日付・同じ商品名」のレコードをその場で合算していきます。

想定するケース

以下のような状況を想定しています。

  • C:\集計\支店データ\ というフォルダに、各支店のExcelファイル(.xlsx)が入っている(支店ごとにファイルが分かれており、ファイル自体が支店の単位)
  • 各ファイルのフォーマットは統一されている(A〜D列にデータが入っている)
    • A列: 日付
    • B列: 商品名
    • C列: 数量
    • D列: 売上金額
  • 支店をまたいで、日付と商品名が同じデータは数量・売上金額をそれぞれ合算し、1つの集計ファイルにまとめたい

例えば「大阪支店.xlsx」と「東京支店.xlsx」がどちらも「2026/05/01・りんごジュース」の売上を持っていた場合、集計結果ではその2件の数量と売上金額をそれぞれ足し合わせた1行にまとめます。支店名(=ファイル名)は合算のキーに含めないため、集計結果には残りません。

基本コード

このマクロは「集計.xlsm」という別のファイルから実行することを想定しています。

Sub 複数ファイルを集計()

    Dim folderPath As String   ' 対象フォルダのパス
    Dim fileName As String     ' ファイル名(順番に取得)
    Dim wb As Workbook         ' 開くファイル
    Dim wsSrc As Worksheet     ' 転記元のシート
    Dim wsDest As Worksheet    ' 集計結果を書き出すシート
    Dim lastRowSrc As Long     ' 転記元の最終行
    Dim i As Long               ' 行のループ用カウンタ
    Dim dict As Object         ' 合算用のDictionary
    Dim key As String          ' 日付+商品名のキー
    Dim dateKey As String      ' キー用に整形した日付
    Dim productName As String  ' 商品名
    Dim qty As Double          ' 数量
    Dim amount As Double       ' 売上金額
    Dim vals As Variant        ' Dictionaryに格納する[数量, 売上金額]の配列

    ' 集計先シートの設定
    Set wsDest = ThisWorkbook.Worksheets("集計")

    ' 集計シートの既存データをクリア(1行目のヘッダーは残す)
    If wsDest.Cells(2, 1).Value <> "" Then
        wsDest.Range("A2:D" & wsDest.Rows.Count).ClearContents
    End If

    ' 日付+商品名をキーに数量・売上金額を合算するDictionary
    Set dict = CreateObject("Scripting.Dictionary")

    ' 対象フォルダのパス(末尾に\をつける)
    folderPath = "C:\集計\支店データ\"

    ' フォルダ内の最初のExcelファイルを取得
    fileName = Dir(folderPath & "*.xlsx")

    ' ファイルがなくなるまで繰り返す
    Do While fileName <> ""

        ' ファイルを非表示で開く
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & fileName)

        ' 1枚目のシートを対象にする
        Set wsSrc = wb.Worksheets(1)

        ' 転記元の最終行を取得
        lastRowSrc = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row

        ' 1行ずつ読み取り、日付+商品名をキーに数量・売上金額を合算する
        For i = 2 To lastRowSrc
            dateKey = Format(wsSrc.Cells(i, 1).Value, "yyyy/mm/dd")
            productName = wsSrc.Cells(i, 2).Value
            qty = wsSrc.Cells(i, 3).Value
            amount = wsSrc.Cells(i, 4).Value

            key = dateKey & "|" & productName

            If dict.Exists(key) Then
                vals = dict(key)
                vals(0) = vals(0) + qty
                vals(1) = vals(1) + amount
                dict(key) = vals
            Else
                dict.Add key, Array(qty, amount)
            End If
        Next i

        ' ファイルを保存せずに閉じる
        wb.Close SaveChanges:=False

        ' 次のファイルを取得
        fileName = Dir()

    Loop

    ' Dictionaryの中身を集計シートに書き出す
    Dim destRow As Long
    Dim k As Variant
    Dim parts() As String

    destRow = 2
    For Each k In dict.Keys
        parts = Split(k, "|")
        vals = dict(k)
        wsDest.Cells(destRow, 1).Value = parts(0)  ' 日付
        wsDest.Cells(destRow, 2).Value = parts(1)  ' 商品名
        wsDest.Cells(destRow, 3).Value = vals(0)   ' 合算した数量
        wsDest.Cells(destRow, 4).Value = vals(1)   ' 合算した売上金額
        destRow = destRow + 1
    Next k

    ' 日付の昇順に並び替える
    If destRow > 2 Then
        wsDest.Range("A2:D" & destRow - 1).Sort _
            Key1:=wsDest.Range("A2"), Order1:=xlAscending, Header:=xlNo
    End If

    Application.ScreenUpdating = True
    MsgBox "集計完了! " & (destRow - 2) & " 件のデータにまとめました。"

End Sub

コードの解説

Dir() 関数でフォルダ内のファイルを順番に取得する

fileName = Dir(folderPath & "*.xlsx")

Dir() はフォルダ内のファイル名を1件ずつ返す関数です。*.xlsx はExcelファイルのみを対象にするワイルドカード指定です。

fileName = Dir()  ' 引数なしで次のファイルを取得

2回目以降は引数なしで呼ぶと、次のファイル名を返します。ファイルがなくなると空文字("")を返すため、Do While fileName <> "" でループを回します。

Scripting.Dictionary で「日付+商品名」ごとに数量・売上金額を合算する

Set dict = CreateObject("Scripting.Dictionary")

Scripting.Dictionary はキーと値のペアを保持できるオブジェクトです。CreateObject で生成すれば、参照設定(VBEの「ツール」→「参照設定」)をしなくても late binding で使えます。今回は1つのキーに対して「数量」と「売上金額」の2つの値を持たせたいので、値には Array(qty, amount) を使っています。

key = dateKey & "|" & productName

If dict.Exists(key) Then
    vals = dict(key)
    vals(0) = vals(0) + qty
    vals(1) = vals(1) + amount
    dict(key) = vals
Else
    dict.Add key, Array(qty, amount)
End If

「日付」と「商品名」を | で連結した文字列をキーにしています。dict.Exists(key) で同じキーがすでに存在するか確認し、存在すれば数量と売上金額をそれぞれ加算、存在しなければ新規に登録します。これが「同じ日付・同じ商品名なら合算する」処理の核心です。

Array はDictionaryから読み出すとコピーが返るため、vals(0) = vals(0) + qty のように配列の中身を直接書き換えても、その結果を dict(key) = vals で書き戻さないとDictionary側には反映されません。

日付は Format(..., "yyyy/mm/dd") で文字列に統一してからキーにしています。セルの日付が Date型 と文字列型で混在していると、そのままではキーが一致しないことがあるためです。

Dictionaryの中身をシートに書き出す

For Each k In dict.Keys
    parts = Split(k, "|")
    vals = dict(k)
    wsDest.Cells(destRow, 1).Value = parts(0)
    wsDest.Cells(destRow, 2).Value = parts(1)
    wsDest.Cells(destRow, 3).Value = vals(0)
    wsDest.Cells(destRow, 4).Value = vals(1)
    destRow = destRow + 1
Next k

全ファイルを読み終えた後、dict.Keys でキー(日付+商品名)を1件ずつ取り出し、Split(k, "|") で日付と商品名に分解、dict(k) で合算済みの Array(数量, 売上金額) を取り出して、それぞれの列に書き出します。

書き出した後に日付の昇順で並び替える

If destRow > 2 Then
    wsDest.Range("A2:D" & destRow - 1).Sort _
        Key1:=wsDest.Range("A2"), Order1:=xlAscending, Header:=xlNo
End If

Dictionary はキーを登録した順序で Keys を返すため、そのままではファイルの読み込み順(=日付順とは限らない順序)でシートに書き出されてしまいます。そこで全件を書き出した後、A列(日付)を基準に Range.Sort で昇順に並び替えています。日付は yyyy/mm/dd の固定桁数の文字列で統一しているため、文字列としての並び替えでもそのまま日付順になります。destRow > 2 のチェックは、集計結果が1件もない場合に空範囲をソートしてエラーになるのを防ぐためです。

Application.ScreenUpdating = False で処理を高速化する

Application.ScreenUpdating = False

画面の更新を止めることで、ファイルを開いたり閉じたりする処理が画面に見えなくなり、大幅に処理速度が上がります。処理が完了したら必ず True に戻してください。

ファイルを閉じるときに保存しない

wb.Close SaveChanges:=False

SaveChanges:=False を指定することで、開いたファイルを変更なしで閉じます。集計元のファイルを誤って変更しないための安全策です。

支店(ファイル)ごとの内訳も残したい場合

合算のキーにファイル名(支店名)も加えれば、「支店ごと・日付ごと・商品ごと」の内訳を残したまま集計できます。

key = dateKey & "|" & fileName & "|" & productName

If dict.Exists(key) Then
    vals = dict(key)
    vals(0) = vals(0) + qty
    vals(1) = vals(1) + amount
    dict(key) = vals
Else
    dict.Add key, Array(qty, amount)
End If

書き出し側も parts(0) が日付、parts(1) がファイル名(支店名)、parts(2) が商品名となるように列を1つ増やして対応します。この場合、同じファイル・同じ日付・同じ商品の行だけが合算され、支店をまたいだ合算にはなりません。

特定のシート名を指定したい場合

1枚目のシートではなく、特定の名前のシートを対象にする場合は以下のように変更します。

' 「データ」という名前のシートを対象にする
On Error Resume Next
Set wsSrc = wb.Worksheets("データ")
On Error GoTo 0

' シートが存在しない場合はスキップ
If wsSrc Is Nothing Then
    wb.Close SaveChanges:=False
    fileName = Dir()
    GoTo NextFile  ' 次のファイルへ
End If

フォルダ選択ダイアログを出したい場合

フォルダパスをコードに直書きせず、実行時にダイアログで選べるようにする方法です。

' フォルダ選択ダイアログを表示
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Title = "集計するフォルダを選択してください"

If fd.Show = True Then
    folderPath = fd.SelectedItems(1) & "\"
Else
    MsgBox "キャンセルされました。"
    Exit Sub
End If

実行前の確認事項

  1. 「集計」という名前のシートがマクロを含むファイルに存在するか
  2. 集計シートの列構成が「日付・商品名・数量(合算)・売上金額(合算)」の4列(A〜D列)になっているか
  3. 集計対象のフォルダパスが正しいか
  4. 各ファイルのデータが同じ列構成(A〜D列: 日付・商品名・数量・売上金額)になっているか
  5. 集計元ファイルが他のユーザーによって開かれていないか(開かれているとエラーになります)

まとめ

Dir() 関数でフォルダ内のファイルを順番に取得し、Workbooks.Open で開いて1行ずつ読み取る——ここまでは転記処理と同じですが、Scripting.Dictionary で「日付+商品名」をキーに数量・売上金額を足し込んでいくことで、複数ファイル・複数支店にまたがるデータをその場で合算できます。書き出した後は Range.Sort で日付の昇順に並び替えるので、集計結果はそのまま日付順の一覧として使えます。ファイルを開いて閉じるたびに画面が切り替わらないよう ScreenUpdating = False を使うことで、処理速度も大幅に改善されます。