VBAで複数シートを参照テーブル経由で1つのシートにまとめる

VBAで複数シート上のレコードを、参照テーブルを介して1つのシートに集計します。今回は集計対象となる複数シートの項目を、集計先のシートの項目に合わせるために、転記の途中で参照テーブルを見にいかせます。参照テーブルは、項目コードと項目名を一覧にした対照表です。

さらに、複数の集計元ファイルの一部の項目は各担当者が手入力をするため、入力漏れした際にエラーリストに出力する機能を追加しました。エラー処理の内容は、エラーチェックシートに、入力漏れしている伝票番号別にエラー内容と担当者をリスト表示します。

まずは、本記事の内容が分かる3分半動画（音声無し）を用意しましたので、宜しければ先にご覧ください。

エラーチェックシート含むExcelサンプルファイル

サンプルファイルのシート構成についてご紹介します。今回はシート数が8シート、シートの役割として大きく4つに分かれますので、役割別にシート色を変えております。

担当者入力用シート（取引記録用シート）

3枚の青いシートは、担当者3人分の取引記録用シートです。

各担当者が取引を記録するためのシートを想定しています。項目は「伝票番号」、「日付」、「取引先」、「商品」、「個数」です。「伝票番号」と「日付」は、自動で入力されるものとします。また残りの「取引先」、「商品」、「個数」は、各担当者が手入力して日々の取引記録を付けるものとします。

データ変換用参照テーブル

3枚の緑色のシートには、データ変換用参照テーブルがあります。担当者用シートの「取引先」、「商品」、「担当者」データを、それぞれ「取引先コード」、「商品コード」、「担当者コード」に変換してから、集計先シートに転記するための参照テーブルです。

なお、「M_価格マスタ」シートについては、商品コードのほかに商品の価格データがあります。この価格データも商品をキーに参照された後、集計先の売上を計算する際に利用されます。

集計先シート（集約シート）

3人の担当者の取引記録を1つにまとめて集計するシートになります。項目のうち赤枠の「取引先コード」、「商品コード」、「担当者コード」は、各担当者の取引記録シートの「取引先」、「商品」、「担当者」がデータ変換されて転記される列になります。

青枠で囲った「売上」は、各担当者の取引記録の「個数」と「商品価格」を乗じて算出されます。商品価格は、「M_価格マスタ」シートから商品名をキーにして得られるデータです。

エラーチェックシート

エラーチェックシートは、担当者が手入力する項目「取引先」、「商品」、「個数」に対し入力漏れをした場合に、該当する伝票番号、エラー内容、および担当者名を伝票番号別にリスト表示するシートです。

赤枠は、入力漏れなどによるエラーを「×」表示する項目です。これらのうち、項目「商品価格取得不可」については、商品の入力漏れのため参照テーブルで価格を取得できなかったことによるエラー表示になります。

ダウンロード用サンプルファイル

これまで解説しましたサンプルシートを含むサンプルファイルをダウンロードできますので、宜しければご利用ください。

参照テーブル経由のデータ変換、エラー処理などを含むVBAサンプルコード

VBAで3人の担当者の取引記録を、一部のデータを参照テーブルを介してデータ変換した後、集計先シートに転記するサンプルコードをご紹介します。また担当者の記録漏れがあった場合に、該当する伝票番号と担当者名つきでエラー内容を表示するコードも含まれています。

VBAサンプルコード

VBAサンプルコードになります。先にご紹介したサンプルファイルをダウンロードした後に、標準モジュールへコピペして実行させることで実際の集計結果を確認できます。

Tips
標準モジュールの挿入方法

VBAサンプルコードの実行結果の確認

VBAサンプルコードの実行結果を2通り確認します。1つは、データの入力漏れがないケースで、サンプルファイルそのままで実行した結果になります。

もう1つは、担当者シートから意図的に入力漏れの状況を作った上で、VBAを実行した結果です。

データの入力漏れがないケース

サンプルファイルをそのままVBAで実行させた場合の集計結果が下図左になります。図には確認のために、担当者の一人（鈴木）の取引記録データを並べています。

青枠を見比べると、伝票番号と日付が間違いなく転記されているのが確認できます。また、緑枠については、担当者シート上の「取引先」、「商品」がそれぞれ「取引先コード」、「商品コード」として、集計元の内容と異なるデータが転記されています。

異なる理由は、これらのデータが転記される前に、それぞれ「M_価格マスタ」と「M_取引マスタ」の中でデータ変換されたためです。

つぎに担当者コードについて確認します（下図）。右の担当者テーブルから、鈴木担当者の担当者コードは、「S-001」ですよね。

その「S-001」が、右側の集計先シートの「担当者コード」に転記されているのが確認できます。

つづいて、集計先の売上データについて確認します（下図）。

売上データは、「M_価格マスタ」シート上の商品名に対応する「価格」データと、各担当者の取引記録シートの「個数」を乗じて計算されます。図は、鈴木担当者に関する商品の売り上げデータを確認するためのものですが、集計先の売上データが価格マスタテーブルの商品価格を参照し、対応する個数と乗じて計算され、集計先シートへ転記されているのが確認出来ます。

エラーチェックシートと比較確認したものが下図ですが、サンプルファイルは担当者の入力漏れがないので、ご覧の通りエラーチェックシートにも何も表示されません。

意図的にデータの入力漏れをさせたケース

では今度は担当者の取引記録シートを意図的に入力漏れ状態にしてからVBAを実行します。VBA実行前に取引記録シートの項目のうち、手入力データをいくつか消して（下図赤枠）、下図の例のような入力漏れ状態を作ります。

すると上図の状態でVBAを実行した場合の集計先データは下図のように、集計先のレコードもデータ漏れが発生しているのがわかりますよね。これらは取引記録シートの入力漏れが原因で起こる集計エラーです。

集計エラーが発生していることは見てすぐ分かりますが、誰のエラーで何がエラーの原因かがちょっと見ずらいですよね。ここで使えるのが「エラーチェックシート」になります（下図）。

エラーチェックシートでは、取引記録シート上で入力漏れがあった伝票番号と担当者がリストアップされ、それぞれのエラー原因が項目別に記載されるので、エラー原因が分かりやすくなっています。

例えば、伝票番号1に売上が計算されていないのは、個数漏れが原因だからです。また伝票番号5は、商品入力漏れのため商品コードが取得できず、さらに商品価格も不明のため売り上げがない状態になっています。

VBAサンプルコードのアルゴリズム

今回のVBAサンプルコードは、メインプロシージャを含む2つのSubプロシージャと、3つのファンクションプロシージャから構成されています。

VBAサンプルコード内のワークシートオブジェクト名

担当者シートを除くワークシートは、オブジェクト名で取り扱います。下図は各ワークシートとそのオブジェクト名の関係を赤枠で示しています。

ここで各担当者シートについては特別なオブジェクト名をつけておりません。理由は、For Each文の中で、共通のワークシートオブジェクト変数「wstData」として別途設定しているからです。

VBAコード19～22行目は、集計先シート「T_取引レコード」の初期化処理です。

モジュール変数

ここで宣言した変数は、複数のプロシージャから呼び出されるため、モジュール変数とし宣言しています。

3行目：Dim wstData As Worksheet
各担当者シート用のワークシートオブジェクト変数です。For Each ～ Next文の中で処理対象のワークシートを格納するために使用します。

5行目：Dim rngTrgt As Range
各マスタシート上に設けた参照テーブルの範囲をセットするためのRangeオブジェクト変数です。

7行目：Dim intSlip As Integer
8行目：Dim intTSlp As Integer
伝票番号を保持するための変数です。エラー発生時に、エラーチェックシートに伝票番号を吐き出すため、メインプロシージャとブランクチェックプロシージャで使用されます。

9行目：Dim lngWERw As Long
エラーチェックシートへの書込み行番号を保持するための変数です。

メインプロシージャ

「エラーチェックつき複数シートを参照テーブル経由で集計」メインプロシージャの解説をします。

13行目：Dim lngWRow As Long
集計先シート「T_取引レコード」へ書き込む行番号を保持する変数です。

14行目：Dim lngERow As Long
担当者シート上のレコード最終行番号を保持するための変数です。

VBAコード19～22行目は、「T_取引レコード」の集計先範囲をクリアしてから集計先書込み行番号をリセットします。

VBAコード21行目は、クリアした後のデータ最終行に1を加算することで、レコード書込み開始の行番号を求めていますが、書込み開始行は常に2であることから、代わりにlngWRow = 2とすることも可能です。

VBAコード25～28行目は、「T_エラーチェックシート」の初期化処理です（下図）。

先に説明した「T_取引レコード」シートのときと異なり、書込み開始行番号を保持する変数「lngWERw」を1にリセットしています。ちなみに、開始行番号の初期値が違うのは、「T_エラーチェック」シートと「T_取引レコード」シートの書込み行のインクリメント処理のタイミングが異なるからです。

30行目：intTSlp = 0
「T_エラーチェック」シートの処理中の伝票番号を保持するための変数「intTSlp」を0で初期化しています。

各担当者シートを繰り返し処理

VBAコード33～59行目は、各担当者シートのレコード（取引記録）を「T_取引レコード」シートに転記する処理です。まずVBAコード33行目のFor Each ～ In Worksheetsで、すべてのワークシートを対象に繰り返し処理をします。

そして、VBAコード35行目の条件式により担当者シートだけに絞った上で、VBAコード39行目以降の処理をさせます。ここでVBAコード35行目の条件式の考え方について、下図を用意しました。

まず、Not( )の部分はあとまわしにして、上図赤字の条件について先に考えると、先頭1文字が「T」か「M」がつくシートが抽出されることがわかります。

その上で、赤字の条件をNot付きの()で囲いますので、結果的に赤字の条件文で対象となるシートとは反対（Not）の各担当者シートが、If文の条件を満たすワークシートになります。

36行目：lngERow = .Range("A" & .Rows.Count).End(xlUp).Row
ここからは、担当者シートに対する処理になります。まずここで担当者シートの最終レコード行番号を求めます。

最終レコード行番号を求めた上で、つづくVBAコード39行目でExcelシート2行目のレコードから、VBAコード36行目で求めたExcel行番号（最終レコードが格納されている行）までのすべてのレコードを繰り返し処理します。

40行目：intSlip = .Cells(r, 1)
42行目：TRNSAC.Cells(lngWRow, 1) = intSlip
処理対象の伝票番号（担当者シート1列目データ）を、変数「intSlip」にセットします。その後、集計先の「T_取引レコード」シート1列目に伝票番号を転記します。

43行目：TRNSAC.Cells(lngWRow, 2) = .Cells(r, 2)
担当者シート2列目の日付データを、「T_取引レコード」シートの2列目（日付データ列）に転記します。

VBAコード45～47行目では、それぞれ3つのファンクションプロシージャが登場しますが、処理の詳細は後述になります。ここでは取り合えず、プロシージャ名から推測されるような処理の概要だけを捉えてください。

45行目：TRNSAC.Cells(lngWRow, 3) = 取引先コードに変換(.Cells(r, 3)) 
ファンクションプロシージャ「取引先コードに変換」に担当者シートの取引先データを渡します。そして、戻ってきた取引先コードを、「T_取引先レコード」シートの取引先コード欄（3列目）に転記する処理です。

46行目：TRNSAC.Cells(lngWRow, 4) = 商品コード_価格に変換(.Cells(r, 4), "商品コード") 
ファンクションプロシージャ「商品コード_価格に変換」に引数として「商品」と文字列"商品コード"を渡し、戻り値「商品コード」を取得します。その取得した「商品コード」を「T_取引レコード」シートの商品コード欄（4列目）に転記します。

47行目：TRNSAC.Cells(lngWRow, 5) = 担当者コードに変換(.Name)
ファンクションプロシージャ「担当者コードに変換」に、引数としてワークシート名を渡します。そして戻された担当者コードを、「T_取引レコード」シートの担当者コード欄（5列目）に転記する処理です。

VBAコード49～53行目では、担当者シートの個数データをIf文で条件判定し処理を分けています。まずVBAコード49行目で、担当者シートの個数データが0かどうかを判定し、0（もしくは""(ブランク））でなければ、VBAコード50行目を実行します。

VBAコード49行目で、担当者レコードの個数データが0（もしくは""（ブランク））の場合、VBAコード52行目が実行されます。

50行目：TRNSAC.Cells(lngWRow, 6) = 商品コード_価格に変換(.Cells(r, 4), "商品価格") * .Cells(r, 5)
担当者レコードの個数データが正しく入力されていた場合に実行されるVBAコードです。ファンクションプロシージャ「商品コード_価格に変換」に、担当者レコードの商品データと文字列"商品価格"を引数として渡します。そして戻された商品価格と、個数を乗じて得られた売上を「T_取引レコード」シートの売上欄（6列目）に転記します。

52行目：Call エラーチェックシートに出力(5) 
担当者レコードの個数データが0（もしくは""（ブランク））の場合に実行されるVBAコードです。ファンクションプロシージャ「エラーチェックシートに出力」は、入力漏れしているレコードを「エラーチェックシート」にリストアップするユーザ定義関数です。

55行目：lngWRow = lngWRow + 1
ここまでで担当者シートのレコード1件分の転記が終わりましたので、「T_取引レコード」シートに書き込む行を次の行へ進めるための処理をしています。

「取引先コードに変換」ファンクションプロシージャ

64行目：Private Function 取引先コードに変換(ByVal v取引先名 As String) As String
プロシージャ宣言部です。引数として取引先名を受取り、戻り値として取引先コードを返します。

VBAコード66行目で、下図赤枠で囲った範囲の中から、引数で受け取った取引先名を探して一致したセルを、オブジェクト変数「rngTrgt」へセットします。

68行目：If Not (rngTrgt Is Nothing) Then
「rngTrgt is Nothing」は、直前のVBAコード66行目で、一致する取引先名が無かった（Nothing）場合という意味になりますので、このIf文の条件式「Not ( rngTrgt is Nothing)」 はその反対の意味、すなわち「一致する取引先名があった場合」になります。

69行目：取引先コードに変換 = rngTrgt.Offset(, -1)
VBAコード68行目の条件がTrueの場合、すなわち参照テーブルの中に取引先名があった場合に実行されるVBAコードです。処理結果は、そのまま関数の戻り値、すなわち取引先コードになります。

具体的な例をあげて、もう1度VBAコード66行目からの処理の流れを図解してみます。例えば、鈴木シートの伝票番号4番の処理について考えてみます。まず、VBAコード66行目（赤字）で、鈴木シートの取引先「C商店」を「M_取引マスタ」シート上の参照テーブルから探し、変数「rngTrgt」へセットします。

その後、VBAコード68行目のIf文の条件式を経て（参照テーブルにC商店があったので）、VBAコード69行目に処理が移ります。VBAコード69行目では、参照テーブル上で該当する取引先コードを返します。

ここで、参照テーブル上で「取引先」から「取引先コード」に変換する仕組みですが、上図青字で示した「.Offset(,-1)」プロパティを使い、rngTrgtに対し列方向で-1オフセットしたRangeオブジェクト（rngTrgt.Offset(,-1))を取得しています。

これにより、取引先から取引先コードに変換されたデータが、ファンクションプロシージャの戻り値になります。

Tips
Offsetプロパティの使い方

71行目：Call エラーチェックシートに出力(2)
「M_取引先マスタ」で担当者レコード上の取引先データが見つからなかった場合に実行されるVBAコードになります。ここでは見つからなかったということ = 入力漏れということになります。

よって、エラーチェックシートに入力漏れ情報を書き出すファンクションプロシージャ「エラーチェックシートに出力」を呼び出し、「T_エラーチェック」シートにエラー内容を書き出す処理をさせます。

72行目：取引先コードに変換 = ""
対象の取引先コードが見つからなかったので、「T_取引レコード」にはブランクを入力する処理をさせています。

「商品コード_価格に変換」ファンクションプロシージャ

81行目：Private Function 商品コード_価格に変換(ByVal v商品名 As String, ByVal vMode As String) As Variant
プロシージャ宣言部です。引数は商品（名）と、「商品コード」もしくは「価格」のどちらを取得したいのかを指定する文字列になります。また戻り値は、商品コードもしくは価格になります。

ここで、戻り値のデータ型を「Variant」としているのは、戻り値が商品コードのときは文字列となり、戻り値が価格のときは数値になるからです。仮に戻り値のデータ型を「String」とすると、戻り値が価格のときにエラーとなりますので注意してください。

83行目：Set rngTrgt = .Range("A1").CurrentRegion.Find(v商品名, lookat:=xlWhole)
VBAコード66行目の考え方と同じで、引数として受け取った商品名を「M_価格マスタ」シート上の参照テーブルの中から探します。そして、一致した参照テーブル上のセルをオブジェクト変数「rngTrgt」へセットします。

85行目：If Not (rngTrgt Is Nothing) Then
こちらもVBAコード68行目と同じ考え方になります。VBAコード83行目で一致するセルがあった場合にThen以降が実行されます。

そのThen以降に実行されるコードを下図で考えます。まず第二引数の文字列が"商品コード"か"商品価格"で処理が分かれます（VBAコード86行目のIf文）。第二引数が、"商品コード"の場合、参照テーブルの青枠の中から該当する商品コードを戻り値として取得します。

また第二引数が"商品価格"の場合、上図の緑枠の中から該当の価格を戻り値として取得します。いづれの値も同じ表から取得していますが、ポイントはオブジェクト変数「rngTrgt」のプロパティ「.Offset」を使うことで、選択する列を変えていることです。

このように、1つのキー列に対し複数の値を取得したい場合やキー列が一番左の列にないような参照テーブルを使う場合、ExcelのVlookUp関数では難しいですよね。

つぎに、参照テーブルで該当する商品名が得られなかった場合の処理の説明です（下図）。こちらも、VBAコード71, 72行目の処理と考え方は同じになります。

もし第二引数が"商品コード"であれば、VBAコード93行目で「T_エラーチェック」に入力漏れエラーを表示し、そのあと関数の戻り値として""（ブランク）を返します。また、もし第二引数が"商品価格"の場合、VBAコード96行目で同じくエラー処理をしたあと、関数の戻り値として数字の0を返します。

「担当者コードに変換」ファンクションプロシージャ

107行目：Private Function 担当者コードに変換(ByVal v担当者名 As String) As String
プロシージャ宣言部です。引数は、担当者名で戻り値は担当者コードになります。

109行目：Set rngTrgt = .Range("A1").CurrentRegion.Find(v担当者名, lookat:=xlWhole)
「M_担当者マスタ」上の参照テーブルから、引数で渡された担当者名を探して、一致したセルをオブジェクト変数「rngTrgt」にセットします。

111行目：If Not (rngTrgt Is Nothing) Then
112行目：担当者コードに変換 = rngTrgt.Offset(, -1)
前2つのファンクションプロシージャと同様、参照テーブルに一致するセルが見つかった場合の処理になります。

114行目：Call エラーチェックシートに出力(6)
115行目：担当者コードに変換 = ""
これら2行についても、これまで解説してきました2つのファンクションプロシージャと同様の考え方による処理内容です。

「エラーチェックシートに出力」サブプロシージャ

124行目：Private Sub エラーチェックシートに出力(ByVal vFlg As Integer)
プロシージャ宣言部です。引数は、「T_エラーチェック」シート上のExcelシート書込み列番号です（下図）。これまで、「Call エラーチェックシートに出力（数値）」でたびたび登場しましたが、この時の( )内の数値（引数）は下図のExcelシートの列番号と同じになるようにしています。

125行目：Dim intWCol As Integer
127行目：intWCol = vFlg
変数「intWcol」は、エラー表示「×」を転記する列番号を保持します。

VBAコード130～132行目は、「T_エラーチェック」シートにエラー情報を出力する際に、1伝票につき1行で出力させるための制御を行っています。VBAコード130行目で、担当者シート上の（処理中の）伝票番号を保持する変数「intSlip」（赤）と、「T_エラーチェック」シート上の（書込み対象の）伝票番号を保持する変数「intTSlp」（青）を比較しています。

もし2つの伝票番号が異なる場合、「T_エラーチェック」の書込み行番号を1つ進め（VBAコード131行目）、さらに「T_エラーチェック」の書込み行番号を保持する変数「intTSlp」の値を、処理中の伝票番号に更新（VBAコード132行目）します。

またもしVBAコード130行目で2つの伝票番号が同じ条件の場合は、「T_エラーチェック」シートの書込み行番号のインクリメントも、処理中の伝票番号の更新もされません。これにより同じ伝票番号の各エラーは、同じ行の中の異なる列に表示されます。

VBAコード136～138行目は、実際に「T_エラーチェック」シートに書き込む処理になります。VBAコード136行目では、処理中の伝票番号が入力されます。VBAコード137行目では、書き込む列番号を保持する変数「intWCol」に応じた列へ、エラー発生の目印「×」を入力します。

またVBAコード138行目では、処理中のワークシート名を保持するオブジェクト変数「wstData」のNameプロパティを利用して担当者名を入力しています。

まとめ

本記事では、複数シートのレコードを1つの集計先シートに集約するVBAサンプルコードをご紹介しました。また、集計先シートに転記する前に、各集計元シートの一部については、参照テーブルを介してから転記しました。

今回の参照テーブルを使うアルゴリズムは、Excel機能のVlookUp関数では難しいキー列が1列目にない場合や、1つのキーに対して複数の値を取得する場合などにも対応可能です。

さらに、担当者のデータ入力漏れに対するエラーリストの作成アルゴリズムについても組み入れました。ここでご紹介したエラーリスト作成のアルゴリズムを応用することで、様々なエラー内容を、1つのExcelシートに出力し一覧表示できるようになります。