はじめに
多くの企業では、日々の業務でExcelが中心的な役割を担っています。
しかし、次のような課題を抱えている現場も少なくありません。
- 複数ファイルの手作業転記
- 入力ミスの頻発
- 属人化によるブラックボックス化
- 作業時間の増大
本記事では、実際の現場でよくある
「複数拠点から送付されるExcelファイルの統合業務」
を例に、保守性と拡張性を意識したVBA自動化の設計と実装方法を紹介します。
想定業務シナリオ
営業部門では、各拠点から毎日売上レポート（Excel）が送付されます。
本社ではそれらを集計し、管理用ファイルへまとめる必要があります。
処理イメージ
処理の全体像は以下の通りです。
フォルダ構成
SalesData
├─ 東京.xlsx
├─ 大阪.xlsx
├─ 名古屋.xlsx
└─ 集計.xlsm
各ファイルの構造
| 日付 | 商品 | 売上 | 担当 |
|---|
設計方針
本記事では以下を重視しています。
- モジュール分離
- 設定の外部化
- 再利用可能な構造
- 可読性の高いコード
1. 設定の外部化（Configシート）
フォルダパスなどの可変情報は、コードではなくシートに保持します。
Configシート
| Key | Value |
|---|---|
| FolderPath | C:\SalesData |
設定値を取得する関数
Configシートから指定キーの値を取得します。
Option Explicit
Public Function GetConfig(key As String) As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Config")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim i As Long
For i = 2 To lastRow
If ws.Cells(i, 1).Value = key Then
GetConfig = ws.Cells(i, 2).Value
Exit Function
End If
Next i
End Function
2. メイン制御（Controller）
フォルダ取得 → 対象ファイル一覧取得 → 各ファイル処理を実行します。
Public Sub RunAggregation()
' 処理速度向上のため画面更新を停止
Application.ScreenUpdating = False
' 不要な確認ダイアログを表示しない
Application.DisplayAlerts = False
Dim folderPath As String
folderPath = GetConfig("FolderPath")
Dim files As Collection
Set files = GetExcelFiles(folderPath)
Dim filePath As Variant
For Each filePath In files
ProcessFile filePath
Next filePath
' 集計結果を並び替え（日付 → 商品）
SortSummary
' 画面更新を元に戻す
Application.ScreenUpdating = True
' アラート表示を元に戻す
Application.DisplayAlerts = True
MsgBox "集計が完了しました。"
End Sub
3. 対象ファイルの取得
指定フォルダ内のExcelファイル一覧を取得します。
Public Function GetExcelFiles(folderPath As String) As Collection
Dim col As New Collection
Dim fileName As String
fileName = Dir(folderPath & "\*.xlsx")
Do While fileName <> ""
col.Add folderPath & "\" & fileName
fileName = Dir
Loop
Set GetExcelFiles = col
End Function
4. 各ファイルの処理
対象ファイルを開き、データ行を読み取り、Summaryシートへ転記します。
Public Sub ProcessFile(filePath As String)
Dim wb As Workbook
Set wb = Workbooks.Open(filePath, ReadOnly:=True)
Dim ws As Worksheet
Set ws = wb.Sheets(1)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim target As Worksheet
Set target = ThisWorkbook.Sheets("Summary")
Dim i As Long
For i = 2 To lastRow
AppendRow ws, target, i
Next i
wb.Close SaveChanges:=False
End Sub
5. データ転記処理
1行ずつSummaryシートへ追加します。
Public Sub AppendRow(src As Worksheet, dest As Worksheet, rowIndex As Long)
Dim nextRow As Long
nextRow = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1
dest.Cells(nextRow, 1).Value = src.Cells(rowIndex, 1).Value
dest.Cells(nextRow, 2).Value = src.Cells(rowIndex, 2).Value
dest.Cells(nextRow, 3).Value = src.Cells(rowIndex, 3).Value
dest.Cells(nextRow, 4).Value = src.Cells(rowIndex, 4).Value
End Sub
6. 集計結果の並び替え処理
集計後のデータを
日付 → 商品 の昇順 で並び替えます。
処理内容：
Summaryシートのデータ範囲を取得
日付（1列目）昇順
商品（2列目）昇順
Public Sub SortSummary()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Summary")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
With ws.Sort
.SortFields.Clear
' 日付で昇順ソート
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), Order:=xlAscending
' 商品で昇順ソート
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), Order:=xlAscending
.SetRange ws.Range("A1:D" & lastRow)
.Header = xlYes
.Apply
End With
End Sub
実行結果
- 手作業： 約１時間
- 自動化後： 数分
主な効果：
- 作業時間削減
- 入力ミス防止
- 手順の標準化
- 属人化解消
集計処理について
今回のサンプルでは、複数ファイルのデータをSummaryシートへ集約し、
日付 → 商品の順で並び替えを行っています。
実際の業務では、同一日付・同一商品の売上を合計するような
集計処理を追加するケースも多くあります。
その場合は、Dictionary やピボットテーブルを利用した
集計ロジックを実装することで対応可能です。
保守性を高めるポイント
今回の設計では以下を意識しています。
- 処理単位ごとにSub / Functionを分離
- 設定値の外部化
- 拡張可能な構造
例えば以下の拡張も容易です。
- CSV対応
- データベース連携
- エラーログ出力
- 処理条件の追加
- VBA自動化の限界
おわりに
VBAは現在でも、多くの企業現場で即効性の高い自動化ツールです。
重要なのは「とりあえず書く」ことではなく、
設計を意識すること
保守性を考えること
将来拡張を見据えること
実務VBAシリーズ
- 第1回：複数Excelファイルを自動集計する方法（今回）
- 第2回：Dictionaryで売上データを集計する方法
- 第3回：100万行Excelを3秒で処理する方法（高速化完全ガイド）
- 第4回：現場で使えるVBA便利ツール5選
- 第5回：エラー処理の基本と実践｜On Error完全解説