トップ > VBA・VBS > Excel VBA > 複数シートのデータを1枚に縦に統合する

複数シートのデータを1枚に縦に統合する

Excel VBA(月別・支店別シートを1枚にまとめる)

月ごとにシートが分かれた売上表、支店ごとに分かれた日報。分析しようとすると、まず「1枚にまとめる」作業が発生します。コピペをシートの枚数分くり返すのは、ミスの温床です。このレシピでは、同じ形式のシート全部を、1枚の「統合」シートに縦に積み上げるマクロを作ります。見出し行は先頭に1回だけ付き、統合先のシート自身と、除外したいシートは自動で飛ばします。

✅ これができます:ボタン1つで、複数シートのデータを1枚に縦統合できるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)は月別に分かれたシート。実行すると(右)、見出し1回+全月分のデータが縦に積み上がった「統合」シートができます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 統合先のシート名
Const OUTPUT_SHEET As String = "統合"
' ② 統合から外すシート名(, 区切り。統合先は自動で外れます)
Const EXCLUDE_SHEETS As String = "設定,目次"
' ③ 各シートの見出し行の数
Const HEADER_ROWS As Long = 1
' ===== ここから下は変えなくてOK =====
 
Sub シートを統合する()
    Dim wsOut As Worksheet, ws As Worksheet
    Dim outRow As Long, lastRow As Long, lastCol As Long
    Dim headerDone As Boolean, copied As Long
 
    Set wsOut = シートを用意する(OUTPUT_SHEET)
    wsOut.Cells.Clear
    outRow = 1
    headerDone = False
    copied = 0
 
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> OUTPUT_SHEET And Not 除外シートか(ws.Name) Then
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            lastCol = ws.Cells(HEADER_ROWS, ws.Columns.Count).End(xlToLeft).Column
            If lastRow > HEADER_ROWS Then
                If Not headerDone Then
                    ws.Range(ws.Cells(1, 1), ws.Cells(HEADER_ROWS, lastCol)).Copy wsOut.Cells(outRow, 1)
                    outRow = outRow + HEADER_ROWS
                    headerDone = True
                End If
                ws.Range(ws.Cells(HEADER_ROWS + 1, 1), ws.Cells(lastRow, lastCol)).Copy wsOut.Cells(outRow, 1)
                outRow = outRow + (lastRow - HEADER_ROWS)
                copied = copied + 1
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
 
    wsOut.Activate
    MsgBox copied & " 枚のシートを「" & OUTPUT_SHEET & "」に統合しました。", vbInformation, "シート統合"
End Sub
 
Private Function 除外シートか(ByVal sheetName As String) As Boolean
    Dim parts As Variant, p As Variant
    除外シートか = False
    parts = Split(EXCLUDE_SHEETS, ",")
    For Each p In parts
        If Trim(CStr(p)) = sheetName Then
            除外シートか = True
            Exit Function
        End If
    Next p
End Function
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの3か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1OUTPUT_SHEET統合先のシート名無ければ自動で作られます。このシート自身は統合もとから自動で外れます
2EXCLUDE_SHEETS統合から外すシート名(カンマ区切り)集計表や目次など、データでないシートを書きます。例:"設定,目次,集計"
3HEADER_ROWS各シートの見出し行の数見出しが1行なら 1。全シートで見出し行の数が揃っている前提です

組み込み手順

  1. 統合したいシート同士で、列の並びと見出し行の数が揃っているか確認します(このレシピの前提条件です)
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①〜③を自分のブックに書き換えます
  4. ファイルを .xlsm で保存し、マクロを実行します(ボタン登録が便利です)
  5. 「統合」シートに全シート分のデータが縦に積み上がっていれば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

実行のたびに統合シートはまっさらな状態から作り直されます。元シートには一切触らないので、失敗してもデータは無事です。もう一度実行すれば、その時点の最新状態で統合し直せます。

カスタマイズ例

どのシートのデータか分かる列を足したい

統合後に「出典」列を足すカスタマイズができます。まずは統合だけを済ませ、必要になったら出典列つきに発展させるのがおすすめです。元シートは残るので、行数で当たりを付けることもできます。

特定の月のシートだけ統合したい

②の除外リストで調整するのが簡単です。逆に「4月・5月・6月だけ」のように選びたい場合は、除外側に残りの月を並べます。

統合した表をそのまま集計したい

統合シートを「集計ボタン」レシピのデータシートに指定すれば、統合→集計まで一気に行けます。

つまずきどころ

関連レシピ

次はこれ:フォルダ内の複数ブックを開いて1つに統合する
シート単位の統合の次は、支店から届くファイル単位の統合へ進みましょう

よくある質問

Q. シートの枚数が増えたら、コードの直しは必要?

A. 不要です。ブック内の全シート(統合先と除外分を除く)を自動で拾います。月が増える運用でもそのまま使えます。

Q. 統合の順番はどうなる?

A. シート見出しの並び順(左から右)です。月別シートなら、見出しを月順に並べておけば、その順で積み上がります。

Q. 別のブック同士を統合したい

A. それは「フォルダ内の複数ブックを開いて1つに統合する」のレシピの担当です。フォルダにファイルを置くだけで統合できます。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る