EXCEL VBA 大量データの結合（指定ワークシートからのデータ集計・各ワークシートからのデータ集計）テクニック

2023年3月1日

EXCEL VBA 大量データの結合（指定ワークシートからのデータ集計・各ワークシートからのデータ集計）テクニック

●はじめに

今回説明するのは、各ワークシートにある大量のデータを一つのワークシートにまとめる方法について説明します。業務上、複数のワークシートに分散しているデータを一つのワークシートにまとめる必要がある場合がありますが、この作業を自動化する方法を紹介します。ワークシートの数が多い場合でも、この方法を使えば便利にまとめることができます。以下、サンプルプログラムを交えて順番に説明します。

EXCEL VBA 各ワークシートに登録されている全てのデータをコピーして一つのワークシートに纏める。

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、各ワークシートに登録されている全てのデータをコピーして一つのワークシートに纏めるサンプルプログラムです。各シートのデータ範囲を取得してデータを纏めるシートに順番に貼り付けでデータを纏める方法です。この方法が一番理解し易いと思います。それでは、順番に説明いたします

【プログラムの動作説明】
①　最初に、集計先のワークシート「MergedData」を設定します。
②　集計先のワークシート内のデータをクリアします。
③　各ワークシートに対して、A列の最終行を取得してコピーするデータの範囲を決定し、それをシート「MergedData」にコピーします。
④　次のワークシートに移り、同じ手順を繰り返します。③へ戻る

【プログラム実行条件】
①　ワークシートの数が多い場合、実行に時間がかかる可能性があります。
②　コードを実行する前に、集計先のワークシート「MerageData」が存在することを確認してください。
③　集計先のワークシートに他のデータが存在する場合、それらのデータが上書きされることになるので、注意してください。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　MerageData00（サンプルプログラム）

'
'
Sub MergeData00() '各ワークシートに登録されている全てのデータをコピーして一つのワークシートに纏める。(データを結合する）

    Dim ws, ws01 As Worksheet
    Dim lRow, dRow As Long

    Set ws01 = Sheets("MergedData") 'データの集計先のシート設定
    dRow = 2 'シート「MergeData」集計先の位置を設定する。
    
    lRow = ws01.Cells(ws01.Rows.Count, "A").End(xlUp).Row  '集計先のワークシート「MerageData」A列の最終行を取得します。
    ws01.Range("A2:G" & lRow + 1).ClearContents  '集計先のワークシート「MerageData」の内容をクリアします。
     
    '各ワークシートのデータをコピーして貼り付ける
    For Each ws In ActiveWorkbook.Worksheets  '全てのワークシートに対して繰り返す。
        If ws.Name <> "MergedData" Then  'シート「MergeData」以外のシートが集計対象となる。
            lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row  'コピーするシートのA列の最終行を取得、コピー範囲を把握する。
            ws.Range("A2:G" & lRow).Copy ws01.Range("A" & dRow)  '順番に各ワークシートのデータをシート「MergeData」にコピーする。（A列からG列の間）
            dRow = dRow + lRow - 1  'シート「MergeData」内の次に貼り付け位置を設定する。
        End If
    Next ws
    
    ws01.Activate '各ワークシートのデータが纏められた「MerageData」が表示されす。

End Sub
'

Sub MergeData00() '各ワークシートに登録されている全てのデータをコピーして一つのワークシートに纏める。(データを結合する）

Dim ws, ws01 As Worksheet

Dim lRow, dRow As Long

Set ws01 = Sheets("MergedData") 'データの集計先のシート設定

dRow = 2 'シート「MergeData」集計先の位置を設定する。

lRow = ws01.Cells(ws01.Rows.Count, "A").End(xlUp).Row '集計先のワークシート「MerageData」A列の最終行を取得します。

ws01.Range("A2:G" & lRow + 1).ClearContents '集計先のワークシート「MerageData」の内容をクリアします。

'各ワークシートのデータをコピーして貼り付ける

For Each ws In ActiveWorkbook.Worksheets '全てのワークシートに対して繰り返す。

If ws.Name <> "MergedData" Then 'シート「MergeData」以外のシートが集計対象となる。

lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'コピーするシートのA列の最終行を取得、コピー範囲を把握する。

ws.Range("A2:G" & lRow).Copy ws01.Range("A" & dRow) '順番に各ワークシートのデータをシート「MergeData」にコピーする。（A列からG列の間）

dRow = dRow + lRow - 1 'シート「MergeData」内の次に貼り付け位置を設定する。

End If

Next ws

ws01.Activate '各ワークシートのデータが纏められた「MerageData」が表示されす。

End Sub

●実行前～実行後　※プログラム実行後、ブック内の各ワークシートにあるデータをコピーして、１つのワークシート「MerageData」に纏める事ができました。
（画面クリックして拡大）

EXCEL VBA　複数のワークシートに登録されているデータを、配列を使って一つのワークシートに纏める。

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、前回説明したサンプルプログラム①を応用したものです。サンプル①では、各ワークシートのデータをコピーして一つのワークシートにまとめる方法を紹介しましたが、今回のサンプル②では、配列を使用してデータをまとめる方法に変更しました。また、処理の高速化のため、画面の更新を停止したり計算モードを停止したりするようにしました。

具体的には、各ワークシートのデータを配列に読み込んでから、配列を操作してデータをまとめます。この方法は、コピーと貼り付けの回数が減るため、処理速度が向上します。

また、画面の更新を停止することで、処理が高速化されます。Excelは、画面を更新するために多くの処理時間を必要とするため、画面の更新を停止することで処理速度を向上させることができます。同様に、計算モードを手動に切り替えることで、処理速度を向上させることもできます。

以上のように、サンプルプログラム②では、配列を使用してデータをまとめる方法と、画面の更新と計算モードの制御による処理速度の向上を行っています。

【プログラムの動作説明】

①　「MergedData」という名前のワークシートを用意して、そこにデータを集計します。
②　画面更新を止め、計算モードを手動に変更することで、処理速度を高速化します。
③　各ワークシートのデータを、配列を使って1つの配列にまとめます。
④　配列にまとめたデータを、ワークシート「MergedData」に貼り付けます。

【プログラム実行条件】

①　集計先のワークシート「MergedData」がない場合はエラーになるため、あらかじめ作成しておく必要があります。
②　本プログラムでまとめることができるのは、全てのデータが同じ形式である場合に限られます。たとえば、各ワークシートの列数が異なる場合は、エラーが発生します
③　集計先のワークシートに他のデータが存在する場合、それらのデータが上書きされることになるので、注意してください。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　MerageData01（サンプルプログラム）

'
'
Sub MergeData01b() '各ワークシートに登録されている全てのデータを配列を利用して一つのワークシートに纏める。(高速版）

    Dim data() As Variant
    Dim I As Long, J As Long, dRow As Long, lRow As Long
    Dim ws As Worksheet, ws01 As Worksheet

    Set ws01 = ThisWorkbook.Worksheets("MergedData")  'データの集計先のシート設定
    dRow = 2

    Application.ScreenUpdating = False  '画面の更新を止める
    Application.Calculation = xlCalculationManual  '計算モードを止める（手動）

    '以下、配列にデータを取り込む
    For Each ws In ThisWorkbook.Worksheets  'すべてのワークシートに対して繰り返す。
        If ws.Name <> "MergedData" Then      'ワークシート「MergeData」以外のシートが集計対象となる。
            lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   'データを取り込む各ワークシートA列の最終行を取得、範囲を把握する。
            data = ws.Range("A2:G" & lRow).Value  '各ワークシートのデータを配列に取り込む
            ws01.Range("A" & dRow).Resize(UBound(data, 1), UBound(data, 2)).Value = data  '配列に取り込んだデータをワークシートに「MergeData」に転記する。
            dRow = dRow + lRow - 1  'ワークシートシート「MergeData」内の次に配列データから転記する位置を設定する。
        End If
    Next ws

    Application.ScreenUpdating = True  '画面の更新を再開する。
    Application.Calculation = xlCalculationAutomatic  '計算モードを再開（自動）

    ws01.Activate '各ワークシートのデータが纏められた「MerageData」が表示されす。

End Sub
'

Sub MergeData01b() '各ワークシートに登録されている全てのデータを配列を利用して一つのワークシートに纏める。(高速版）

Dim data() As Variant

Dim I As Long, J As Long, dRow As Long, lRow As Long

Dim ws As Worksheet, ws01 As Worksheet

Set ws01 = ThisWorkbook.Worksheets("MergedData") 'データの集計先のシート設定

dRow = 2

Application.ScreenUpdating = False '画面の更新を止める

Application.Calculation = xlCalculationManual '計算モードを止める（手動）

'以下、配列にデータを取り込む

For Each ws In ThisWorkbook.Worksheets 'すべてのワークシートに対して繰り返す。

If ws.Name <> "MergedData" Then 'ワークシート「MergeData」以外のシートが集計対象となる。

lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'データを取り込む各ワークシートA列の最終行を取得、範囲を把握する。

data = ws.Range("A2:G" & lRow).Value '各ワークシートのデータを配列に取り込む

ws01.Range("A" & dRow).Resize(UBound(data, 1), UBound(data, 2)).Value = data '配列に取り込んだデータをワークシートに「MergeData」に転記する。

dRow = dRow + lRow - 1 'ワークシートシート「MergeData」内の次に配列データから転記する位置を設定する。

End If

Next ws

Application.ScreenUpdating = True '画面の更新を再開する。

Application.Calculation = xlCalculationAutomatic '計算モードを再開（自動）

ws01.Activate '各ワークシートのデータが纏められた「MerageData」が表示されす。

End Sub

●実行前～実行後　※このプログラムを実行すると、ブック内の各ワークシートにあるデータを配列を利用して集計し、1つのワークシート「MergeData」にまとめることができます。今回は、配列や画面更新停止などの機能を使用することで高速にデータをまとめることができます。
（画面クリックして拡大）

EXCEL VBA 各ワークシートに異なる大きさの表が登録されているデータを一つのワークシートに纏める。

●プログラム説明　(サンプル③)

下記のサンプルプログラムは、前回説明したサンプルプログラム②を応用したものです。サンプルプログラム②と今回説明するサンプルプログラム③の違いとしては、各ワークシートに登録されている表の大きさ（列の数）が異なっていても、列位置が統一していれば配列を使用して表を統合することができます。統合されたデータは、ワークシート「MergeData」に転記されます。
ただし、データを集計するには、以下の条件が必要です。
【集計条件】
①　データが登録されている列データは各ワークシート共に統一されている。
・ワークシート「総務部」部署・氏名・氏名（カタカナ）・性別・電話番号　←登録されている項目
・ワークシート「企画部」部署・氏名・氏名（カタカナ）・性別・電話番号・年齢　←登録されている項目
・ワークシート「人事部」部署・氏名・氏名（カタカナ）・性別・電話番号・年齢・血液型　←登録されている項目
②　セルの「A2」を起点に表を取り込むためこの位置に表が作成されている必要があります。

【プログラムの流れ】

①　ワークシート「MergedData」を指定し、データの集計先のワークシートとして設定します。
②　ワークシート「MergedData」内のデータが格納されている最終行を把握し、次の行（dRow）を設定します。
③　ワークシート「MergedData」を削除します。
④　各ワークシートに対して、以下の手順を実行します。
⑤　ワークシート「MergedData」以外のシートが対象となります。
⑥　各ワークシートのA列の最終行を取得し、範囲を把握します。
⑦　各ワークシートのデータが登録されている最終列と最終行を把握します。
⑧　配列（data）に各ワークシートのデータを読み込みます。
⑨　配列（data）をワークシート「MergedData」に書き込みます。
⑩　次に書き込む行（dRow）を設定します。※登録したデータ分行を加算
⑪　最後にワークシート「MergedData」をアクティブにします。

【プログラム実行条件】
・ワークシート「MergeData」を事前に作成する必要がある。
・各ワークシートからデータを取り込む際にセル「A2」を起点としてデータを取り込む（A2の表が作成されている必要がある）
※上記【集計条件】も参照して下さい。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　MerageData02（サンプルプログラム）

※　ワークシート「MergeData」をクリアするコードも追加する。

'
'
Sub MergeData02()  '各ワークシートに登録されている全てのデータを配列を利用して一つのワークシートに纏める。（範囲は自由）A１を起点とする
    
    Dim data As Variant
    Dim I, J, dRow, lRow, lcol As Long
    Dim ws, ws01 As Worksheet
    
    Set ws01 = Sheets("MergedData") 'データの集計先のワークシート設定
    dRow = 2    'シート「MergeData」集計先の位置を設定する。
    
    lRow = ws01.Cells(ws01.Rows.Count, "A").End(xlUp).Row + 1 'データを集計するワークシート「MerageData」のデータが登録されている最終行＋１を把握する。
　　 ws01.Range("2:" & lRow).ClearContents 'ワークシート「MerageData」をクリアする。
    lcol = ws01.Cells(1, ws01.Columns.Count).End(xlToLeft).Column 'データを集計するワークシート「MerageData」のデータが登録されている最終列を把握する。
    ws01.Range(ws01.Cells(2, 1), ws01.Cells(lRow, lcol)).ClearContents 'ワークシート「MergeData」を削除すうする。
    
    '配列にデータを読み込む
    For Each ws In ActiveWorkbook.Worksheets 'すべてのワークシートに対して繰り返す。
        If ws.Name <> "MergedData" Then  'シート「MergeData」以外のシートが集計対象となる。
            lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   'データを取り込む各ワークシートA列の最終行を取得、範囲を把握する。
            
            lcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column '各ワークシートのデータを取り込む際にデータが登録されている最終列を把握する。
            lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row  '各ワークシートのデータを取り込む際にデータが登録されている最終行を把握する。
            
            data = ws.Range(ws.Cells(2, 1), ws.Cells(lRow, lcol)).Value  'ワークシートの選択範囲（行列）で配列（Data）にデータを格納します。
            
            ' 配列を一度に書き込む
            ws01.Range("A" & dRow).Resize(UBound(data, 1), UBound(data, 2)).Value = data  '格納した配列データをワークシート「MergeData」に出力します。
            
            
            dRow = dRow + lRow - 1  'シート「MergeData」内の次に配列データから転記する位置を設定する。
        End If
    Next ws
    
    ws01.Activate 'ワークシート「MerageData」をアクティブにします。

End Sub

'

Sub MergeData02() '各ワークシートに登録されている全てのデータを配列を利用して一つのワークシートに纏める。（範囲は自由）A１を起点とする

Dim data As Variant

Dim I, J, dRow, lRow, lcol As Long

Dim ws, ws01 As Worksheet

Set ws01 = Sheets("MergedData") 'データの集計先のワークシート設定

dRow = 2 'シート「MergeData」集計先の位置を設定する。

lRow = ws01.Cells(ws01.Rows.Count, "A").End(xlUp).Row + 1 'データを集計するワークシート「MerageData」のデータが登録されている最終行＋１を把握する。

　　 ws01.Range("2:" & lRow).ClearContents 'ワークシート「MerageData」をクリアする。

lcol = ws01.Cells(1, ws01.Columns.Count).End(xlToLeft).Column 'データを集計するワークシート「MerageData」のデータが登録されている最終列を把握する。

ws01.Range(ws01.Cells(2, 1), ws01.Cells(lRow, lcol)).ClearContents 'ワークシート「MergeData」を削除すうする。

'配列にデータを読み込む

For Each ws In ActiveWorkbook.Worksheets 'すべてのワークシートに対して繰り返す。

If ws.Name <> "MergedData" Then 'シート「MergeData」以外のシートが集計対象となる。

lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'データを取り込む各ワークシートA列の最終行を取得、範囲を把握する。

lcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column '各ワークシートのデータを取り込む際にデータが登録されている最終列を把握する。

lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row '各ワークシートのデータを取り込む際にデータが登録されている最終行を把握する。

data = ws.Range(ws.Cells(2, 1), ws.Cells(lRow, lcol)).Value 'ワークシートの選択範囲（行列）で配列（Data）にデータを格納します。

' 配列を一度に書き込む

ws01.Range("A" & dRow).Resize(UBound(data, 1), UBound(data, 2)).Value = data '格納した配列データをワークシート「MergeData」に出力します。

dRow = dRow + lRow - 1 'シート「MergeData」内の次に配列データから転記する位置を設定する。

End If

Next ws

ws01.Activate 'ワークシート「MerageData」をアクティブにします。

End Sub

●実行前～実行後　※ このプログラムを実行すると、ブック内の各ワークシートにあるデータを配列を使って集計し、1つのワークシート「MergeData」にまとめることができます。データの集計は、A列を起点にして行われ、各ワークシートの列項目が統一されていれば、異なる列の幅にも対応できます。つまり、列の項目が同じであれば、列の幅が異なっていてもデータをまとめることができます。
（画面クリックして拡大）