フォルダ内にある複数のエクセルファイルを串刺し集計するにはVBAマクロが便利です

2023.09.232024.03.08

この記事は約6分で読めます。

フォルダ内にある複数のエクセルファイルを串刺し集計して合計した表を自動で作成するという処理についてはVBAマクロが一番よさそうです。
エクセルで、その都度手動で串刺し集計をするのはけっこうたいへんだし、間違いが起きやすいです。他のツールも検討してみましたが、エクセルを使える環境にある人なら追加料金なしで使えてメンテナンスもしやすいといういう特徴を踏まえると、VBAマクロが一番適していました。

このページの目次

複数ファイルのエクセルを串刺し集計するマクロを作りました

複数ファイルのエクセルを串刺し集計するマクロを作りました

ひとつひとつのエクセルは個別原価計算を含んだ受注表です

今回のケースは、受注型ビジネスなので、見積もりや受注はひとつひとつが個別対応が必要なので、ひとつのエクセルファイルになっています。

▼例えば以下のような構造です（サンプルデータ）

このようなエクセルファイルが毎月100件ほどあるとして、月次でその集計をどうするかが悩みのタネだったということです。（旅行代理店が個別の受注を管理するようなイメージです）

ここまで個別の受注管理を自動計算されているのなら、月次集計も自動計算したいですね。

エクセルの串刺し計算

エクセルの串刺し計算は、複数のファイルの同じセルを合計する処理ができます。しかし、その処理をいざやってみようとするとけっこう難しいです。

実行するための手順は以下のとおりです。
・計算したいすべてのエクセルを開いておく（100ファイルあれば100です）
・合計計算したい合計用エクセルを開き、串刺し集計したいセルごとにすべてのファイルの名前とセル名を入力します（100ファイルあれば100回で、セルの数だけ必要）
これらの処理が終われば、合計用エクセルファイルには自動計算できます。

しかし、課題がいくつかあります。
・開くファイルが多すぎるとPCのメモリが圧迫し動作が不安定になる
・毎月同じ処理をしなければならないのでかなり煩雑
・個別ファイルの追加や修正があると合計用エクセルファイルも修正が必要になる

毎月の作業なので、できるだけ完結に処理をしたいので、手作業による串刺し集計はあまり現実的ではありません。

マイクロソフトのヘルプを参考記事として紹介しておきます。

複数のワークシートで同じセル範囲への 3-D 参照を作成する - Microsoft サポート

Excel の 3-D 参照を使用して、organization内のさまざまな部門の予算データを統合する場合など、いくつかのワークシートを参照します。

実際に串刺し集計する手順なども紹介されています。
なお、マイクロソフトは「串刺し集計」のことは「3-D参照」という表現にしているようです。つまり「3-D参照」＝「串刺し集計」ととらえてよさそうです。

同じフォルダ内にあれば自動的に集計してくれるマクロ

というわけで、ここはVBAマクロを使って自動実行することにしました。

VBAとかマクロとか聞き慣れない言葉を聞くとちょっと拒否感があるかもしれませんが、やってみると意外に簡単だったりします。自動実行したい処理が定番のルーチンだったりするとマクロがわりとはまります。

サンプルデータを使って串刺し集計した結果は以下のとおりです。

▼自動集計マクロは以下のような仕様です
「Ctrl+k」というショートカットキーで実行します
実行手順
1.対象とするフォルダを選択します（ファイルではないので注意ください）
2.フォルダ内のエクセルファイルをすべて開きます
3.指定したセルを串刺し集計します
（いちおう色をつけて区別していますが、マクロではセル指定です）

もう少し便利にするための機能追加もしたいところですが、まずはここまです。

作成したマクロは以下のとおりです

Sub 串刺し集計()
‘
‘ 串刺し集計 Macro
‘ フォルダ内のエクセルをすべて串刺し集計をする
‘
Dim wsSummary As Worksheet
Dim cell As Range
Dim targetRng As Range
Dim folderPath As String
Dim fileName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim targetCell As Range

‘ 集計用のブックとシートを設定
Set wsSummary = ThisWorkbook.Sheets(“Sheet1”) ‘ Sheet1を集計用シートとして指定
Set targetRng = wsSummary.UsedRange

‘ フォルダを選択
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = “フォルダを選択してください”
.AllowMultiSelect = False
If .Show = -1 Then
folderPath = .SelectedItems(1)
Else
MsgBox “フォルダが選択されませんでした”
Exit Sub
End If
End With

‘ フォルダ内のすべてのエクセルファイルを開く
fileName = Dir(folderPath & “\*.xls*”)
Do While fileName <> “”
Set wb = Workbooks.Open(folderPath & “\” & fileName)
Set ws = wb.Sheets(1) ‘ 1つ目のシートを対象とする

‘ 集計を行いたいセル・範囲を指定
Set targetRange = Union(wsSummary.Range(“D8:D24”), wsSummary.Range(“G3”), wsSummary.Range(“H3”))

For Each specificCell In targetRange
Set targetCell = ws.Range(specificCell.Address)
specificCell.Value = specificCell.Value + targetCell.Value
Next specificCell

‘ ブックを閉じる
wb.Close SaveChanges:=False
fileName = Dir
Loop

MsgBox “集計が完了しました”

‘
End Sub