VBA集計業務では、複数のブックから1つのブックへデータを集計する作業があります。本記事では、5つの開かれた複数のブックから順次データを取り出し、1つの取りまとめ用ブックへ集計するVBAサンプルコードをご紹介します。
まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画をご確認ください。
目次
VBA取りまとめ用ブックと各部門別ブック
まずは会計用データを収めたサンプルファイルを準備してください。サンプルファイルは、1つの取りまとめ用ブックと5つの部門別ブックの合計6ファイル（ブック）あります。またそれらのブックには、各1枚のワークシートがあります（下図）。
各部レコードを集計させるVBAサンプルコード
5部門のレコードを1つのシートに集計させるVBAコードになります。それでは、「00 取りまとめ用.xlsx」に標準モジュールを追加して、以下のコードを真似して書いてください。また時間のない場合は、下のサンプルコードをコピペしてください。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | Option Explicit '集計対象の部門別ファイルを事前にすべて開いてから実行 Sub 各部対象レコード集計() Application.ScreenUpdating = False Dim wbkSelf As Workbook, wstSelf As Worksheet, lngWRow As Long '自ブックに関する変数 Dim wbkData As Workbook '開いているすべてのブック用変数 Dim wstData As Worksheet, lngERow As Long '各部門ファイルに関する変数 Dim r As Long '自ブックとその取りまとめシートをオブジェクト変数にセット Set wbkSelf = ThisWorkbook Set wstSelf = wbkSelf.Worksheets("各部取りまとめ") '取りまとめシートクリアと書込み行リセット With wstSelf .Rows("2:" & .Rows.Count).ClearContents lngWRow = 2 End With '開いているすべてのファイルに対する繰り返し処理 For Each wbkData In Workbooks With wbkData If Not (.Name Like "*取り*") Then 'ファイル名が取りまとめ用ブック以外の場合 Set wstData = .Worksheets(1) '部門ファイルの最初のシートをオブジェクト変数にセット With wstData lngERow = .Range("A" & .Rows.Count).End(xlUp).Row '各部門ファイルの最終行番号取得 For r = 2 To lngERow 'ワークシート2行目から最終行番号まで繰り返し処理 wstSelf.Cells(lngWRow, 1) = .Cells(r, 1) '伝票ID wstSelf.Cells(lngWRow, 2) = .Cells(r, 2) '会計年 wstSelf.Cells(lngWRow, 3) = .Cells(r, 3) '会計月 wstSelf.Cells(lngWRow, 4) = .Name '部門 wstSelf.Cells(lngWRow, 5) = .Cells(r, 4) '費目 wstSelf.Cells(lngWRow, 6) = .Cells(r, 5) '金額 wstSelf.Cells(lngWRow, 7) = .Cells(r, 6) '予実区分 lngWRow = lngWRow + 1 '書込み行を次の行へ Next End With End If End With Next Set wstData = Nothing Set wstSelf = Nothing Set wbkSelf = Nothing End Sub |
VBAサンプルコードの実行方法
「00 取りまとめ用.xlsx」に標準モジュールを追加して、サンプルコードを転記した後は、いよいよVBAの実行になります。
今回は、集計対象レコードをもつすべての部門別ブックを開いた状態で集計しますので、まだ開いていない場合は下記手順で部門別ブックを開いてください。
まず、Excel画面左上の「ファイル」メニューをクリックして、バックステージビュー（下図）を開きます。次に①の「開く」、② の「参照」をクリックすると、「ファイルを開く」画面が開きます。
そこで、図のように部門別ブックが保存されているフォルダから、5つの部門別ブックを複数選択（③）した上で、③の「開く」をクリックします。これで5つの部門別ブックが開きますので、開いているExcelブック数は、取りまとめ用と合わせて6個になります。
ここで注意いただきたいのですが、VBAコード実行前は上記6つのExcelブック以外は開いていない状態にしてください。
では、VBAコードを実行してみましょう。VBA実行後のサンプルファイルの結果は下のようになります。
図は、最初に集計された人事部データですが、下までスクロールすると5つの部門のデータが集計されていますので、実際に確認してみてくださいね。
VBAで開かれた複数の別ブックにあるデータを集計させるアルゴリズム
7行目： Dim wbkSelf As Workbook, wstSelf As Worksheet, lngWRow As Long
取りまとめブック用の変数を宣言しています。「Dim wbkSelf as workbook」でワークブックオブジェクト変数を宣言しています。今回は、自ブック以外の複数のブックを扱いますので、個々のブックを管理するためにワークブックオブジェクト変数を使います。
また、ワークブックオブジェクトに続いて、取りまとめシート用に「wstSelf as worksheet」、取りまとめシートへ書き込む行番号を保持する「lngWRow as long」をそれぞれ宣言しています。
このように、VBAでは1つのDimに対して「,（カンマ）」を複数使う事で、1行に複数の変数を宣言することもできます。
8行目： Dim wbkData As Workbook
9行目： Dim wstData As Worksheet, lngERow As Long
8行目は、部門別ブック用のワークブックオブジェクト変数を宣言しています。9行目は、各部のレコードが入力されているシート用のワークシートオブジェクト変数、レコードの最終行番号を保持する変数をそれぞれ宣言しています。
18～21行目：With wstSelf ～ End With
集計先の取りまとめ用シートをクリアして、書込み行変数「lngWRow」を取りまとめ用シートの2行目に初期化するVBAコードです。
5つの部門ファイルを繰り返し処理させるVBAコードのアルゴリズム
24～48行目： For Each wbkData In Workbooks ～ Next
取りまとめ用シートを含む開いているすべてのブックを繰り返し処理します。処理の内容については、次の図で詳しく説明をしますのでご覧ください。
6つの開いたブックから5つの部門ブックだけに絞るVBAコードのアルゴリズム
まずは、大きな流れの説明をしますね。VBAコード24行目は、For Eachを使って、開いているすべてのブックを1つずつ繰り返し処理します。この時点では、図の赤枠で囲った、取りまとめ用ブックを含めた6つのブックが処理対象です。
そして、VBAコード26行目は、If文を使ってレコードが入力されている部門用ブックだけを処理対象に絞ります。図で言いますと、青枠内の5つの部門別ブックだけに処理対象を絞っています。ではその If文 内でどのような抽出条件を設定しているのかを次の図で見ていきましょう。
前述の通り、VBAコード24行目では開いているすべてのブック（図の赤枠）が For Each 文の繰り返し処理の対象になります。そして、VBAコード26行目では、「.Name」、すなわち処理対象のブック名を取得し、than以降の処理をするかどうかを判定しています。
ブック名の条件判定式には、Like演算子を使いブック名に「取り」を含むものを抽出しています。それを、Not（論理演算子）で反対の条件にしています。まとめると、VBAコード26行目では「ブック名に「取り」を含まないブックをThen以降で処理する」という意味になり、結果的に5つの部門別ブックだけがThen以降で処理されることになります。
各部別ブックから取りまとめブックに転記するVBAコードのアルゴリズム
ここからVBAコード26行目で条件を満たした5つの部門別ブックの処理内容の解説になります。
27行目： Set wstData = .Worksheets(1)
各部門別ブックの1シート目を、ワークシートオブジェクト変数「wstData」に設定しています。
ここで「.Worksheets」は、VBAコード25行目のwbkDataのプロパティになります。.Worksheets(1)でwbkDataのワークシートの先頭から1つ目のワークシートを指定する書き方になります。
VBAでは、このワークシートを数字で指定する方法をIndexで指定するといいますが、具体的には、下図のようにExcelシートの先頭のワークシートから順番に数字で指定する方法です。
このIndexによる指定方法は、ワークシート名を指定してワークシートオブジェクト変数を設定する方法に対して、数字だけを使いますので簡単に定義できるのですが、意図せずワークシートの順番を変えられてしまうと、エラーや正しく集計がされませんので注意が必要です。
今回の各部門のファイルは、すべて1枚のシート構成ですので、wbkData.Worksheets(1)により、集計対象レコードが入力された1枚目のシートがワークシートオブジェクト変数「wsdData」に設定されます。
29～45行目：With wstData ～ End With
各部門の処理対象シート「wstData」から、すべてのレコードを取りまとめファイルに転記するVBAコードです。このあたりのVBAコードのアルゴリズムは、同一ブック内で複数のワークシートのデータを集計する方法と考え方は同じですので、次図で簡単に解説しますね。
青枠は、各部門ごとに処理されるVBAコードです。まず、VBAコード30行目で各部レコードの最終行番号を取得します。
32～44行目：For r=2 to lngERow ～ Next
各部門の1つのレコードに対する処理内容です。各部シートの2行目から、VBAコード30行目で取得済みのレコード最終行番号まで、この中で1つずつレコード単位で処理されます。
VBAコード33～41行目で、各部門の1つのレコードの持つ各データを、取りまとめシートの対応する書込み行lngWRowへ、対応する項目ごとに転記します。
この中でVBAコード37行目（赤字）では、取りまとめファイルの「部門」項目へ転記するデータとして、処理中の部門シートのワークシート名を取得しています。
まとめ
集計対象のレコードが複数の別ブックにあり、さらにすべてのブックを開いた状態で取りまとめ用ブックに集計させるVBAサンプルコードをご紹介しました。今回のように集計対象のブックが5個くらいで、ブックの容量が重くない複数ブックの集計では、すべてのブックを開いて集計するのも1つの方法です。
一方、集計対象のブックが数十個あり、また保存先のフォルダが複数ある場合は、すべてのブックを開いてから集計するのは非効率です。
この場合は、FSO（File System Object)やDir関数などを使ったフォルダ操作を使い、ブックを1つずつ開いて、データを読み取り、閉じるという繰り返し処理をしながらデータを集計する方法が一般的ですが、FSOやフォルダ操作の関数などの新たな知識が必要になります。
その点、今回の手法はこれまでの集計スキルの範囲内で、複数の別ブックにあるデータを集計できますので、ブック数が少ない場合は簡単に応用できるサンプルコードとしてご利用頂けます。