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
実行前の確認事項
- 「集計」という名前のシートがマクロを含むファイルに存在するか
- 集計シートの列構成が「日付・商品名・数量(合算)・売上金額(合算)」の4列(A〜D列)になっているか
- 集計対象のフォルダパスが正しいか
- 各ファイルのデータが同じ列構成(A〜D列: 日付・商品名・数量・売上金額)になっているか
- 集計元ファイルが他のユーザーによって開かれていないか(開かれているとエラーになります)
まとめ
Dir() 関数でフォルダ内のファイルを順番に取得し、Workbooks.Open で開いて1行ずつ読み取る——ここまでは転記処理と同じですが、Scripting.Dictionary で「日付+商品名」をキーに数量・売上金額を足し込んでいくことで、複数ファイル・複数支店にまたがるデータをその場で合算できます。書き出した後は Range.Sort で日付の昇順に並び替えるので、集計結果はそのまま日付順の一覧として使えます。ファイルを開いて閉じるたびに画面が切り替わらないよう ScreenUpdating = False を使うことで、処理速度も大幅に改善されます。