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

 

 

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

 

 

 ●はじめに

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

 

 

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

 

 

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

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

 

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

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

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

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

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

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

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

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

【プログラム実行条件】

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

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

 

 

 

 

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

 

 

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

 

 

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

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

【プログラムの流れ】

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

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

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

 

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

 

 

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

 

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。