【Excelマクロ】複数のExcelファイルを自動で集計

毎回「コピー＆ペースト」していませんか？

実務上では、複数のファイルを一つのファイルに情報を集約する作業、多くないですか？

  • 営業部、経理部、総務部など、各部署から提出されるExcelファイル・・・
  • 毎回ファイルを開いて、必要なデータをコピー＆ペースト・・・
  • 気が付けば1時間、なんてことありませんか？

この作業、VBAで自動化できます。

ゴール

今回の想定としては、複数のファイルから1つのシートにまとめるイメージでVBAを考えていきます。

  • 指定フォルダ内のすべてのExcelファイルがある
  • 特定のシートのデータを読み取る
  • 1つの集計シートにまとめる！

具体例

想定

  • 各部署が月末に”売上データ.xlsx”のようなファイルを提出
  • ファイルのシート名はすべて”売上”で統一
  • データの範囲は”A1:D10″
  • それを1つのExcelファイルにまとめたい！

フォルダ選択からデータ統合までまとめたマクロ

次のマクロで、フォルダ選択→ファイル読込→データ統合ができます。

Sub ConsolidateData()

    Dim folderPath As String
    Dim fileName As String
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim pasteRow As Long

    ' フォルダ選択ダイアログ
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
        Else
            MsgBox "フォルダが選択されていません"
            Exit Sub
        End If
    End With

    ' 集計先シートの準備
    Set wsTarget = ThisWorkbook.Sheets("集計")
        With wsTarget
            .Cells.ClearContents
            .Range("A1").Value = "部署名"
            .Range("B1").Value = "商品"
            .Range("C1").Value = "数量"
            .Range("D1").Value = "金額"
        End With
    pasteRow = 2

    ' フォルダ内のファイルを順に処理
    fileName = Dir(folderPath & "¥*.xlsx")
    Do While fileName <> ""
        Set wbSource = Workbooks.Open(folderPath & "¥" & fileName)
        Set wsSource = wbSource.Sheets("売上")

        lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

        ' データをコピー（部署名を追加して転記）
        wsTarget.Cells(pasteRow, 1).Resize(lastRow - 1).Value = wbSource.Name
        wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(lastRow, 4)).Copy _
　　　　　　wsTarget.Cells(pasteRow, 2)

        pasteRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1

        wbSource.Close False
        fileName = Dir()
    Loop

    MsgBox "データの統合が完了しました！"
End Sub

コードの説明

  • With Application.FileDialog でフォルダ選択をダイアログ表示
  • Dir関数でフォルダ内のすべての.xlsxファイルを取得
  • 1つずつ開いて、”売上”シートのデータをコピー
  • 集計先のシート”集計”に順次貼り付け
  • ファイル名（部署名の代わり）も併せて記載

なお、「.Show = -1」は、ダイアログ（ファイルの選択時のポップアップ）で、「はい」は「-1」、「いいえ」は「0」でVBA上は把握しています。

よくある質問（Q&A）

Q1. シート名が違う場合は？

例えば、1シート目とルールを決めておけば、名前が違っても、Set wsSource = wbSource.Sheets(1) とすれば1枚目のシートを指定できます。

Q2. ファイルが開かない・壊れている？

エラー回避処理（On Error Resume Next）を使って、開けないファイルをスキップする方法があります。

まとめ

今回は、フォルダ内にある各部署から提出されたExcelファイルを、自動で開き、それぞれの”売上”シートから必要なデータを読み取り、1つの”集計”シートにまとめる方法を学びました。

手作業で行っていたコピー＆ペーストの作業をマクロで効率化し、処理時間を大幅に短縮する実用的なテクニックです。

  • 各部署から集まるファイルの統合はVBAで時短！
  • 毎月同じ作業なら自動化するのが効率的
  • フォルダを選ぶだけで、集計作業が完了！

コメント