「集約」ボタンを押すだけで、全支店のデータが1つになる
②パワークエリ編では、フォルダに入れた支店ごとのファイルを「すべて更新」で1つの表にまとめました。この記事では、まったく同じ結果をマクロで作ります。
コードはコピーして貼り付けるだけです。書き換えるのはフォルダの場所など、先頭の3行だけです。
使う前(支店ごとにバラバラ)
売上データ(フォルダ)
├ 東京支店_202610.xlsx
├ 大阪支店_202610.xlsx
└ 福岡支店_202610.xlsx
どのファイルにも「売上」シートがあり、次の形でデータが入っています。

使った後(集計ブックの「集約」シート)
ボタンを押すと、全支店のデータが縦につながります。A列には、ファイル名から取り出した支店名が入ります。
ボタンを押すたびに、前回の結果を消してから作り直します。何度押してもデータが二重になることはありません。
用意するもの
始める前に、次の4つがそろっているか確認しましょう。
- Windows版のExcel(デスクトップ版)。Web版のExcelではマクロを実行できません
- 支店ごとのファイルを入れたフォルダ(例:C:\売上データ)
- どのファイルも「同じシート名」「同じ列の並び」「見出しは1行目」になっている
- ファイル名が「支店名_年月.xlsx」の形になっている(支店名を取り出すため)
フォルダやファイルのルールは、②パワークエリ編と同じです。すでに②で準備したフォルダがあれば、そのまま使えます。
手順
STEP1:集計用のブックを作る
- Excelで新しいブックを開きます。(すでにデータがあるファイルではありません)
- シート名(画面下の「Sheet1」)をダブルクリックして、「集約」に変えます。
- A1~E1セルに、「支店」「日付」「商品コード」「数量」「金額」と見出しを入力します。
- B列を選んでCtrl + 1を押し、表示形式を「日付」にしておきます(日付が数字で表示されるのを防ぐため)。
注意:この集計ブックは、「売上データ」フォルダの外に保存してください。
STEP2:「開発」タブを表示する
初めてマクロを使うときだけ必要な設定です。すでに「開発」タブがある場合はSTEP2は不要です。
- 「ファイル」→「オプション」を開きます。
- 左の一覧から「リボンのユーザー設定」を選びます。
- 右側の一覧で「開発」にチェックを入れ、OKを押します。
STEP3:コードを貼り付ける
- 「開発」→「Visual Basic」(Alt + F11)を押して、VBAの画面(VBE)を開きます。
- メニューの「挿入」→「標準モジュール」を選びます。
- 右側に出た白い画面に、次のコードをすべてコピーして貼り付けます。
Option Explicit
Sub 売上データを集約()
' ===== 設定(ここだけ自分の環境に合わせて書き換える)=====
Const SRC_FOLDER As String = "C:\売上データ\" ' 支店ファイルを入れたフォルダ(最後の\を忘れずに)
Const SRC_SHEET As String = "売上" ' 各ファイルの読み込むシート名
Const OUT_SHEET As String = "集約" ' 結果を出すシート名
' ==========================================================
Dim wsOut As Worksheet, wb As Workbook, wsSrc As Worksheet
Dim fileName As String, branch As String
Dim lastRow As Long, outRow As Long, n As Long, fileCount As Long
Set wsOut = ThisWorkbook.Worksheets(OUT_SHEET)
' (1) 前回の結果を消す(見出しの1行目は残す)
wsOut.Range("A2:E" & wsOut.Rows.Count).ClearContents
outRow = 2
Application.ScreenUpdating = False
' (2) フォルダ内の .xlsx を順に開く
fileName = Dir(SRC_FOLDER & "*.xlsx")
Do While fileName <> ""
If Left(fileName, 2) <> "~$" Then ' 開いている途中の一時ファイルは飛ばす
Set wb = Workbooks.Open(SRC_FOLDER & fileName, ReadOnly:=True)
Set wsSrc = wb.Worksheets(SRC_SHEET)
' (3) データの最終行を調べる
lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
n = lastRow - 1 ' 見出しを除いたデータの行数
If n > 0 Then
' (4) ファイル名から支店名を取り出す(東京支店_202610.xlsx → 東京支店)
branch = Split(fileName, "_")(0)
' (5) A列に支店名、B~E列にデータを「値」で書き込む
wsOut.Cells(outRow, "A").Resize(n, 1).Value = branch
wsOut.Cells(outRow, "B").Resize(n, 4).Value = wsSrc.Range("A2:D" & lastRow).Value
outRow = outRow + n
End If
wb.Close SaveChanges:=False ' 元のファイルは保存せずに閉じる
fileCount = fileCount + 1
End If
fileName = Dir()
Loop
Application.ScreenUpdating = True
MsgBox fileCount & "件のファイルから、" & (outRow - 2) & "行を集約しました。", vbInformation
End Sub
STEP4:設定の3行を書き換える
コードの先頭にある「設定」の3行を、自分の環境に合わせて書き換えます。それ以外の行は触らなくて大丈夫です。
| 書き換える場所 | 入れるもの | 例 |
| SRC_FOLDER | 支店ファイルを入れたフォルダの場所。最後に「\」を付ける | C:\共有\売上データ\ |
| SRC_SHEET | 各ファイルの、読み込むシート名 | 売上 |
| OUT_SHEET | 結果を出すシート名(STEP1で作ったもの) | 集約 |
フォルダの場所の調べ方:エクスプローラーでフォルダを開き、上部のアドレスバーをクリックすると「C:\…」の形で表示されます。それをコピーして、最後に「\」を足します。
STEP5:マクロ有効ブックで保存し、ボタンを置く
- VBEを閉じてExcelに戻り、F12(名前を付けて保存)を押します。
- 「ファイルの種類」を「Excelマクロ有効ブック(*.xlsm)」にして保存します。(.xlsxで保存するとコードが消えます)
- 「集約」シートで、「開発」タブ→「挿入」→「フォームコントロール」の「ボタン」を選びます。
- 見出しの右側(G1セルあたり)でドラッグして、ボタンを描きます。
- 「マクロの登録」画面で「売上データを集約」を選び、OKを押します。
- ボタンを右クリック→「テキストの編集」で、文字を「集約する」に変えます。
STEP6:実行して結果を確かめる
- 「集約する」ボタンを押します。
- 「3件のファイルから、○行を集約しました。」と表示されれば成功です。
- 各支店ファイルの行数の合計と、集約された行数が合っているか確認しましょう。

翌月からの使い方
毎月の作業は、3つだけです。
- 「売上データ」フォルダの先月分のファイルを、別のフォルダ(例:「過去分」)に移します。
- 今月届いたファイルを、「売上データ」フォルダに入れます。
- 集計ブックを開いて、「集約する」ボタンを押します。
フォルダにあるファイルをすべて読み込む仕組みなので、先月分も含めて累計したい場合は、手順1を飛ばしてください。支店が増えても、ファイルをフォルダに入れるだけで集約されます。
集約したデータは、ピボットテーブルや①関数編のSUMIFSで、支店別・商品別に集計できます。
ぜひ一度お試しください。


コメント