今回説明するのは、各ワークシートにある大量のデータを一つのワークシートにまとめる方法について説明します。業務上、複数のワークシートに分散しているデータを一つのワークシートにまとめる必要がある場合がありますが、この作業を自動化する方法を紹介します。ワークシートの数が多い場合でも、この方法を使えば便利にまとめることができます。以下、サンプルプログラムを交えて順番に説明します。
下記のサンプルプログラムは、各ワークシートに登録されている全てのデータをコピーして一つのワークシートに纏めるサンプルプログラムです。各シートのデータ範囲を取得してデータを纏めるシートに順番に貼り付けでデータを纏める方法です。この方法が一番理解し易いと思います。それでは、順番に説明いたします
【プログラムの動作説明】
① 最初に、集計先のワークシート「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
'
下記のサンプルプログラムは、前回説明したサンプルプログラム①を応用したものです。サンプル①では、各ワークシートのデータをコピーして一つのワークシートにまとめる方法を紹介しましたが、今回のサンプル②では、配列を使用してデータをまとめる方法に変更しました。また、処理の高速化のため、画面の更新を停止したり計算モードを停止したりするようにしました。
具体的には、各ワークシートのデータを配列に読み込んでから、配列を操作してデータをまとめます。この方法は、コピーと貼り付けの回数が減るため、処理速度が向上します。
また、画面の更新を停止することで、処理が高速化されます。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
'
下記のサンプルプログラムは、前回説明したサンプルプログラム②を応用したものです。サンプルプログラム②と今回説明するサンプルプログラム③の違いとしては、各ワークシートに登録されている表の大きさ（列の数）が異なっていても、列位置が統一していれば配列を使用して表を統合することができます。統合されたデータは、ワークシート「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
'
