Excel VBAで異なるテーブルに存在するデータをキーとなる値を使って大量・高速に結合する

こんな状況がある。

従業員うん万人、

システムAのテーブル1にユーザIDと所属部署の情報、

システムBのテーブル2にユーザIDと居住都道府県の情報、

がそれぞれ格納されている。

システムA、Bともに外部へのデータ連携手段としてはCSVファイルのダウンロードのみ提供している。

私はシステムCの担当者でキーとなるユーザIDに所属部署と、居住都道府県両方の情報を紐づけてシステムCへインポートしたい。

色々とやり方はあるが、予算もないのでExcelで何とかする。

ワークシートに関数を入力してVlookupやらMatch＋IndexやらやればVBAを使わずとも実現できるのだが、現実の場面ではシステムA,B共に他にも結合したいカラムが多数あってワークシート上に関数を大量に入力した神エクセルでは重くなって破綻するのが怖い。

データベースに取り込んじゃってjoinするってのもありだが、将来誰かに引き継ぐ事を考えると、裾野が広いExcelにしておきたい。

こんな場面をVBAでどう乗り切るかのアイデア。

完成版のサンプルデータとVBAの.xlsxファイルはこちら。

VBAのソースコードはこんな感じ。

Sub Macro1()
'処理時間計測開始'
startTime = Now()

'処理高速化開始'
With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
End With


Dim i As Long
i = 2
Do While Sheets("MargeTable").Cells(i, 1).Value <> ""

	With Application.WorksheetFunction
		'所属部署の検索とデータ結合'
		Sheets("MargeTable").Cells(i, 2).Value = .Index(Range("Table1!A:B"), .Match(Sheets("MargeTable").Cells(i, 1).Value, Range("Table1!A:A"), 0), 2)
		'居住都道府県の検索とデータ結合'
		Sheets("MargeTable").Cells(i, 3).Value = .Index(Range("Table2!A:B"), .Match(Sheets("MargeTable").Cells(i, 1).Value, Range("Table2!A:A"), 0), 2)
	End With

	i = i + 1
Loop


'処理高速化終了'
With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
End With

'処理時間計測終了＋表示'
endTime = Now()
processTime = endTime - startTime
MsgBox "処理時間＝" & Format(processTime, "hh:mm:ss")
End Sub

【解説】

①'処理高速化開始 '処理高速化終了のところは、Excelの画面描画、イベント監視、シートの自動再計算を停止している。これでかなり処理が高速化できる。

②「With Application.WorksheetFunction」で始まるブロックでワークシート関数のindexとmatchを使って、ユーザIDをキーとして、Table1とTable2からそれぞれ、ユーザIDに対応する、所属部署と居住都道府県のデータを引っ張ってきている。

※結果はMargeTableシートに記入している。

味噌は②のところでFor等のループでユーザIDを探すのではなくワークシート関数を使用している点。ロジック書くのが面倒なので比較していないが、Excelのワークシート関数は非常に優秀で、VBAでゴリゴリループ回してデータを探し当てるより、はるかに高速なマッチングが可能である。（別のやり方としては2次元配列としてセルの値を読み込んで処理すればもっと早いかもしれないが、Rangeの概念やワークシート関数が使えなくなりそうだったり、使えるかもしれないが面倒そうなので試していない。）

VlookupではなくIndex＋Matchを使用した理由はVlookupだとキーにする値＝検索対象列が一番左側にある必要があるため。このサンプルはユーザIDが一番左側にあるが、実際のケースでは必ずしもキーになる値が一番左側にあるファイルが入手できるとは限らないと思われるため、このようにしている。キーになる値が一番左側にあったり、そのようにデータ成型してから動かす様な場合はVlookupを使用した方がより一層早く動くと思われる（例によって試してはいません）。

それと画面描画、イベント監視、シートの自動再計算を処理中止める事で、Table1,2のサンプルデータを3万件に増やしても手元の数年前のCPU中程度、メモリそこそこ積んでるスペックのPC（Intel Core i7-7500U @ 2.70GHz 2.90GHz＝2コア4スレッド, メモリ16GB）で2分55秒で全件処理完了した。

実務で使用するには、もうちょっと処理の工夫やMATCH関数で見つからなかった場合のエラー処理等改変が必要と思われるが、まぁある程度実用には耐えうるスピードが出せたと思って良いだろう。

＋αで「.Index(Range("Table1!A:B")」とか「.Match(Sheets("MargeTable").Cells(i, 1).Value, Range("Table2!A:A"), 0)」の様にIndex＋Matchの引数として雑にA列やB列丸ごと突っ込んでいるが、これをUsedrangeの最終行などを取得して、必要な範囲に絞って行けば、ひょっとしたらもっと早く動くかもしれない。