数万行ある「売上データ」と「商品マスタ」を商品コードで突合し、商品名や単価を取得したい。そんなとき、セルを1行ずつ参照する処理や二重ループ、ワークシート上へ大量のVLOOKUP関数を埋め込む方法では、データ量が増えるほど処理時間が急激に長くなります。
「Excelが応答なしになった」「数万件を処理するだけなのに何十秒、何分も待たされる」という経験がある方も多いのではないでしょうか。
大量データを高速に突合したい場合のポイントは、ワークシートのセルを何度も直接読み書きしないことです。
この記事では、Excel VBAの2次元配列とScripting.Dictionary（連想配列）を組み合わせ、マスタデータをメモリ上へ読み込んで高速検索する実務向けの方法を解説します。
基本的な考え方は非常にシンプルです。
- 商品マスタを2次元配列へ一括読み込みする
- 商品コードをキーとしてDictionaryへ登録する
- 売上データも2次元配列へ一括読み込みする
- メモリ上でDictionaryを検索して商品情報を突合する
- 結果を配列からワークシートへ一括書き出しする
PC性能、データ内容、Excelのバージョンによって実行時間は変わりますが、セルを1件ずつ走査する処理と比較すると、数万件規模で数十倍〜100倍以上の差が出るケースもあります。条件が良ければ、数万件程度の突合が1秒前後、あるいは1秒未満で完了することもあります。
【結論】実務でそのまま使える！Dictionary×配列の超高速データ突合マクロ
まずは完成コードです。
次のような構成を想定しています。
| シート | 列 | 内容 |
|---|---|---|
| 商品マスタ | A列 | 商品コード |
| 商品マスタ | B列 | 商品名 |
| 商品マスタ | C列 | 単価 |
| 売上データ | A列 | 商品コード |
| 売上データ | B列 | 商品名を書き出す |
| 売上データ | C列 | 単価を書き出す |
1行目は見出し、2行目以降がデータという前提です。
'==================================================
' 機能：商品マスタと売上データをDictionary＋配列で高速突合
' 前提：
' 商品マスタ A列：商品コード
' 商品マスタ B列：商品名
' 商品マスタ C列：単価
' 売上データ A列：商品コード
' 売上データ B列：商品名の出力先
' 売上データ C列：単価の出力先
'==================================================
Sub HighSpeedDictionaryLookup()
'------------------------------
' 変数宣言
'------------------------------
Dim wsMaster As Worksheet
Dim wsData As Worksheet
Dim dict As Object
Dim vMaster As Variant
Dim vData As Variant
Dim vResult() As Variant
Dim itemData As Variant
Dim lastRowMaster As Long
Dim lastRowData As Long
Dim i As Long
Dim key As String
'------------------------------
' 対象シートを設定
'------------------------------
Set wsMaster = ThisWorkbook.Worksheets("商品マスタ")
Set wsData = ThisWorkbook.Worksheets("売上データ")
'------------------------------
' 最終行を取得
'------------------------------
lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
lastRowData = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
' データ行が存在しない場合は終了
If lastRowMaster < 2 Then
MsgBox "商品マスタにデータがありません。", vbExclamation
Exit Sub
End If
If lastRowData < 2 Then
MsgBox "売上データにデータがありません。", vbExclamation
Exit Sub
End If
'------------------------------
' Dictionaryを生成
' Late Bindingなので参照設定は不要
'------------------------------
Set dict = CreateObject("Scripting.Dictionary")
' 大文字・小文字を区別しない設定
' 必ずデータ追加前に設定する
dict.CompareMode = vbTextCompare
'------------------------------
' 商品マスタを一括で2次元配列へ読み込む
' セルを1件ずつ読むより高速
'------------------------------
vMaster = wsMaster.Range("A2:C" & lastRowMaster).Value
'------------------------------
' 商品マスタをDictionaryへ登録
' Key ：商品コード
' Item ：商品名と単価を格納した配列
'------------------------------
For i = 1 To UBound(vMaster, 1)
' 前後の半角スペースを除去して文字列化
key = Trim$(CStr(vMaster(i, 1)))
' 空のキーは登録しない
If Len(key) > 0 Then
' 重複キーがあった場合は後勝ちで上書き
dict(key) = Array(vMaster(i, 2), vMaster(i, 3))
End If
Next i
'------------------------------
' 売上データの商品コードを配列へ一括読み込み
'------------------------------
vData = wsData.Range("A2:A" & lastRowData).Value
' 出力用の2次元配列を確保
' 1列目：商品名
' 2列目：単価
ReDim vResult(1 To UBound(vData, 1), 1 To 2)
'------------------------------
' メモリ上で高速突合
'------------------------------
For i = 1 To UBound(vData, 1)
key = Trim$(CStr(vData(i, 1)))
' 商品コードがDictionaryに存在するか確認
If dict.Exists(key) Then
' 商品名・単価をまとめて取得
itemData = dict(key)
vResult(i, 1) = itemData(0)
vResult(i, 2) = itemData(1)
Else
' マスタ未登録の場合は安全に文字列を設定
vResult(i, 1) = "未登録"
vResult(i, 2) = ""
End If
Next i
'------------------------------
' 結果をB:C列へ一括書き出し
' セルを1件ずつ更新しないのが高速化のポイント
'------------------------------
wsData.Range("B2").Resize(UBound(vResult, 1), 2).Value = vResult
' 後片付け
Set dict = Nothing
Set wsMaster = Nothing
Set wsData = Nothing
MsgBox "データ突合が完了しました。", vbInformation
End SubこのコードはLate Binding（遅延バインディング）を採用しています。
CreateObject("Scripting.Dictionary")でDictionaryを生成するため、Windows版Excelでは通常、VBEの「ツール」→「参照設定」から「Microsoft Scripting Runtime」を追加する必要はありません。
コードの詳しい解説
1. なぜ「配列＋Dictionary」は高速なのか？
大量データ処理で最も避けたいのが、VBAとワークシートの間を何万回も往復する処理です。
たとえば、次のようにセルを1行ずつ読み取るコードは分かりやすい反面、大量データでは速度が低下しやすくなります。
' セルを1件ずつ読み書きする例
For i = 2 To lastRow
Cells(i, 2).Value = Cells(i, 1).Value
Next i5万行なら、読み込みと書き込みだけで大量のセルアクセスが発生します。
一方、今回のコードでは範囲全体を次のように読み込みます。
vData = wsData.Range("A2:A" & lastRowData).Valueこれによって、ワークシート上のデータを一括してメモリへ取り込めます。
処理後も次の1命令でまとめて書き戻します。
wsData.Range("B2").Resize(UBound(vResult, 1), 2).Value = vResultつまり、ループ自体がなくなるわけではありません。
重要なのは、ループの中でワークシートへアクセスせず、VBAのメモリ上だけで計算することです。
大まかな処理イメージは次のとおりです。
- シート → 配列へまとめて読み込む
- 配列 → Dictionaryへマスタを登録する
- Dictionaryを使ってメモリ上で検索する
- 結果配列 → シートへまとめて書き戻す
この設計が、大量データ突合を高速化する最大のポイントです。
2. Dictionaryが二重ループより速い理由
Dictionaryを使わずに2つの表を単純な二重ループで照合すると、次のような処理になりがちです。
' 二重ループのイメージ
For i = 1 To dataCount
For j = 1 To masterCount
If vData(i, 1) = vMaster(j, 1) Then
' 一致したときの処理
Exit For
End If
Next j
Next iたとえば売上データが5万件、商品マスタが1万件なら、最悪の場合は非常に多くの比較処理が発生します。
Dictionaryでは、あらかじめ商品コードをキーとして登録します。
その後は、
If dict.Exists(key) Then
itemData = dict(key)
End Ifのようにキーから直接データを探せるため、毎回マスタ全件を先頭から検索する必要がありません。
Scripting.Dictionaryの基本操作を理解しよう
Scripting.Dictionaryは、「キー」と「値」をセットで管理するオブジェクトです。
Excelで考えるなら、「商品コードを指定すると商品情報が返ってくる、小さな検索データベース」のようなイメージです。
.Add：キーと値を登録する
基本構文は次のとおりです。
dict.Add key, itemたとえば、商品コード「A001」に商品名「ノートPC」を登録する場合は次のようになります。
dict.Add "A001", "ノートPC"第1引数がキー、第2引数が値です。
ただし、.Addではすでに存在するキーをもう一度追加するとエラーになります。
.Exists：キーが存在するか確認する
Existsは、指定したキーがDictionary内に存在するかどうかをBoolean型で返します。
If dict.Exists(key) Then
' キーが存在する場合の処理
End Ifマスタ突合では特に重要です。
存在確認をせずに未登録キーを取得しようとすると、意図しない値が登録されたり、コードの書き方によっては想定外の動作につながったりします。実務では取得前にExistsで確認する習慣をつけておくと安全です。
.Item：キーに対応する値を取得・設定する
Dictionaryでは次のように値を取得できます。
itemData = dict.Item(key)ItemはDictionaryの既定プロパティなので、通常は次のように省略して書けます。
itemData = dict(key)今回の完成コードでは、商品名と単価の2項目を配列として保存しています。
dict(key) = Array(vMaster(i, 2), vMaster(i, 3)).Count：登録件数を取得する
Countを使うと、Dictionaryへ登録されているキーの件数を取得できます。
Dim dataCount As Long
dataCount = dict.Count「マスタから何件登録されたか」を確認したいときや、デバッグ時に便利です。
Range.Valueでセル範囲を2次元配列へ一括変換する
今回の高速化でDictionaryと同じくらい重要なのが、Range.ValueをVariant型変数へ一括代入するテクニックです。
Dim vData As Variant
vData = Range("A2:C10000").Value複数セルのRangeをVariant型変数へ代入すると、通常は1始まりの2次元配列として受け取れます。
たとえば、
vMaster = wsMaster.Range("A2:C" & lastRowMaster).Valueとした場合、
vMaster(1, 1)：A2セルvMaster(1, 2)：B2セルvMaster(1, 3)：C2セルvMaster(2, 1)：A3セル
という対応になります。
配列の行数を取得するときは、次のようにUBoundを使います。
UBound(vMaster, 1)第2引数の1は「第1次元」、つまり行方向の最大添字を取得するという意味です。
列数を取得したい場合は次のようにします。
UBound(vMaster, 2)使用している主要なオブジェクト・プロパティ・関数
| 機能 | 役割 | 注意点 |
|---|---|---|
| CreateObject | 外部オブジェクトをLate Bindingで生成します。 | 今回はCreateObject("Scripting.Dictionary")で使用します。 |
| Range.Value | セル範囲の値を取得・設定します。 | 複数セルをVariantへ代入すると2次元配列として扱えます。 |
| UBound | 配列の最大添字を返します。 | 2次元配列では第2引数で次元を指定します。 |
| ReDim | 動的配列のサイズを設定します。 | 結果件数に合わせて出力配列を確保しています。 |
| Resize | Rangeの行数・列数を変更します。 | 配列と出力範囲のサイズを一致させる必要があります。 |
| Trim$ | 文字列の先頭・末尾にある半角スペースを除去します。 | 全角スペースは除去しないため、必要なら別途Replaceなどで正規化します。 |
| CStr | 値をString型へ変換します。 | 商品コードの型をそろえる目的で使用しています。 |
| Len | 文字列の長さを返します。 | Len(key) > 0で空キーを除外しています。 |
Range.Resizeの引数
完成コードでは次の処理を使用しています。
wsData.Range("B2").Resize(UBound(vResult, 1), 2).Value = vResultResizeの第1引数は行数、第2引数は列数です。
今回の結果配列は、
- 行数：売上データと同じ件数
- 列数：商品名と単価の2列
なので、出力範囲も同じサイズにしています。
実務でよくある応用パターン
① マスタに存在しないキーを安全に処理する
実務データでは、売上データ側の商品コードが必ず商品マスタに存在するとは限りません。
マスタ未登録、入力ミス、削除済みコードなどは普通に発生します。
そこで、値を取得する前にExistsで存在確認を行います。
' 商品コードが登録されているか確認
If dict.Exists(key) Then
' 登録済みなら値を取得
vResult(i, 1) = dict(key)
Else
' 未登録ならエラーにせず、分かりやすい文字列を設定
vResult(i, 1) = "未登録"
End Ifこの書き方にしておけば、マスタに存在しない商品コードが含まれていてもマクロ全体を止めずに処理できます。
さらに実務では、「未登録」と表示するだけでなく、未登録件数をカウントして処理完了時に通知するのも有効です。
② 1つのキーから商品名・価格・カテゴリをまとめて取得する
Dictionaryは「1キーにつき1値」と考えると、商品名・価格・カテゴリを3つ同時に持てないように見えるかもしれません。
しかし、値側へ配列を格納することで、複数項目をまとめて保持できます。
たとえば商品マスタが次の構成だとします。
- A列：商品コード
- B列：商品名
- C列：単価
- D列：カテゴリ
登録側は次のようにします。
' 1つの商品コードに3項目を配列として保存
dict(key) = Array( _
vMaster(i, 2), _
vMaster(i, 3), _
vMaster(i, 4) _
)取得するときは次のようにします。
Dim itemData As Variant
If dict.Exists(key) Then
itemData = dict(key)
' Arrayは0始まりなので注意
vResult(i, 1) = itemData(0) ' 商品名
vResult(i, 2) = itemData(1) ' 単価
vResult(i, 3) = itemData(2) ' カテゴリ
End IfArray関数で作った配列は通常0始まりです。そのため、最初の要素はitemData(0)になります。
複数のDictionaryへ分ける方法もある
別の方法として、項目ごとにDictionaryを作ることもできます。
Dim dictName As Object
Dim dictPrice As Object
Dim dictCategory As Object
Set dictName = CreateObject("Scripting.Dictionary")
Set dictPrice = CreateObject("Scripting.Dictionary")
Set dictCategory = CreateObject("Scripting.Dictionary")
' 同じキーに対して、項目ごとに別Dictionaryへ保存
dictName(key) = vMaster(i, 2)
dictPrice(key) = vMaster(i, 3)
dictCategory(key) = vMaster(i, 4)項目ごとの用途が明確ならこちらも分かりやすいですが、Dictionaryが増えるため、商品情報をひとかたまりで扱いたい場合は値に配列を格納する方法が扱いやすいでしょう。
③ マスタ側にキーの重複がある場合
商品マスタに同じ商品コードが複数存在する場合、「どのデータを採用するのか」を明確に決める必要があります。
後から出てきた値で上書きする方法
今回の完成コードは次の書き方です。
' 同じキーがあれば後の行で上書き
dict(key) = Array(vMaster(i, 2), vMaster(i, 3))この方法では、同一キーが複数回登場した場合、最後に読み込んだデータが残ります。
最初に登録した値を保持する方法
最初の値を採用し、それ以降の重複を無視したい場合はExistsを使います。
' 未登録のキーだけ追加する
If Not dict.Exists(key) Then
dict.Add key, Array(vMaster(i, 2), vMaster(i, 3))
End Ifこの場合、最初に登録されたデータが保持されます。
なお、単純に次のようにAddだけを使用すると、同じキーを2回登録した時点でエラーになります。
' 重複キーがあるとエラーになるので注意
dict.Add key, vMaster(i, 2)マスタの仕様に合わせて、後勝ち・先勝ち・重複をエラー扱いのどれにするかを決めてください。
実務でよくある落とし穴と注意点
1. 大文字・小文字の違いで突合できない
Dictionaryでは、文字列の比較方法をCompareModeで指定できます。
英字の商品コードを扱う場合、
abc001ABC001
を同じコードとして扱いたいケースがあります。
その場合は、Dictionaryへデータを追加する前に次の設定を行います。
' 大文字・小文字を区別しない
dict.CompareMode = vbTextCompare代表的な比較モードは次のとおりです。
| 定数 | 意味 |
|---|---|
vbBinaryCompare |
バイナリ比較。通常、大文字・小文字を区別します。 |
vbTextCompare |
文字列として比較し、大文字・小文字を区別しません。 |
CompareModeはDictionaryへキーを登録する前に設定してください。すでにデータを登録した後で変更しようとするとエラーになることがあります。
2. 全角・半角はCompareModeだけでは統一できない
vbTextCompareを指定すれば、すべての表記ゆれを自動で吸収できるわけではありません。
特に、
- 全角英数字と半角英数字
- 全角スペースと半角スペース
- ハイフンの種類
- 数字として保存されたコードと文字列として保存されたコード
などは別問題です。
商品コードの表記ルールが一定でない場合は、Dictionaryへ登録する前と検索する前の両方で同じ正規化処理を行うことが重要です。
たとえば前後の全角スペースも除去するなら、次のようにできます。
' 全角スペースを半角スペースへ置換してから前後をTrim
key = Trim$(Replace(CStr(vData(i, 1)), " ", " "))3. 前後のスペースで一致しない
見た目では同じ「A001」でも、実データが次のようになっている場合があります。
"A001""A001 "" A001"
この違いは目視では気づきにくく、突合失敗の典型例です。
そのため、完成コードでは次のようにしています。
key = Trim$(CStr(vMaster(i, 1)))Trim$で先頭・末尾の半角スペースを除去し、CStrで文字列として扱うことでキーの型をそろえています。
4. 「00123」と「123」は同じとは限らない
商品コードや社員番号では、先頭ゼロが意味を持つ場合があります。
たとえば、
00123123
が別のコードとして扱われる業務では、Excel側で数値化されると先頭ゼロが失われます。
この場合、VBA側だけでなく、元データのセル書式やインポート方法も確認してください。
コード類は最初から文字列として管理するのが安全です。
5. エラー値がキー列に含まれている場合
今回のサンプルではキーを次のように文字列化しています。
key = Trim$(CStr(vData(i, 1)))しかし、セルに#N/Aや#VALUE!などのExcelエラー値が入っていると、CStrでエラーになる可能性があります。
外部データを扱うなど、キー列にエラー値が混ざる可能性がある場合は、事前にIsErrorで判定すると安全です。
If IsError(vData(i, 1)) Then
vResult(i, 1) = "キーエラー"
vResult(i, 2) = ""
Else
key = Trim$(CStr(vData(i, 1)))
End If6. Mac版ExcelではこのCreateObject方式をそのまま使えない場合がある
今回のコードは、Windows環境で利用されるWindows COMのScripting.DictionaryをCreateObject("Scripting.Dictionary")で生成する方法です。
Mac版ExcelではWindows COMを利用できないため、このLate Binding方式のコードはWindows版と同じようには動作しません。
WindowsとMacの両方で動かす必要がある場合は、次のような代替設計を検討してください。
- 2次元配列をソートして検索する
- VBAで独自のキー・値管理クラスを作る
- Collectionを用途に応じて利用する
- Power Queryなど、VBA以外の仕組みで結合処理を行う
社内PCがすべてWindowsであれば、Scripting.Dictionaryは大量データ突合で非常に使いやすい選択肢です。
さらに高速化したい場合のポイント
配列＋Dictionaryだけでも大幅な高速化を期待できますが、処理内容によってはExcelの画面更新や自動計算を一時停止することで、さらに余計な処理を減らせます。
たとえば次の設定です。
' 画面更新を停止
Application.ScreenUpdating = False
' イベントを停止
Application.EnableEvents = False
' 自動計算を停止
Application.Calculation = xlCalculationManual
'==================================================
' ここで大量データ処理を実行
'==================================================
' 最後に必ず元へ戻す
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = Trueただし、途中でエラーが発生した場合にも設定を元へ戻せるよう、実務ではエラー処理とセットで使用することをおすすめします。
特にApplication.Calculationを手動計算のまま残してしまうと、マクロ終了後も数式が自動更新されなくなるため注意が必要です。
VLOOKUPとDictionaryはどう使い分ける？
Dictionaryが高速だからといって、すべてのVLOOKUPを置き換える必要はありません。
| 用途 | 向いている方法 |
|---|---|
| 数十〜数百件程度を手作業で確認したい | VLOOKUP、XLOOKUP |
| 数式としてシート上に検索結果を残したい | VLOOKUP、XLOOKUP |
| 数万〜数十万件をVBAで一括処理したい | 配列＋Dictionary |
| 複数マスタを順番に照合する | Dictionary |
| 定型業務として毎日自動実行したい | VBA＋配列＋Dictionary |
大量データの自動処理では、セルに数式を入れて再計算させるより、必要な結果だけをメモリ上で作り、最後に値として一括出力するほうが扱いやすい場面が多くあります。
実務で高速化するときの考え方
今回の記事で覚えておきたいのは、「Dictionaryという特別な機能を使えば何でも速くなる」ということではありません。
本質は、次の3点です。
- ワークシートへのアクセス回数を減らす
- データを配列へ取り込み、メモリ上で処理する
- 検索に向いたデータ構造であるDictionaryを使う
VBAで処理が遅いと感じたときは、まずループ回数そのものよりも、ループの中でCellsやRangeへ何度アクセスしているかを確認してみてください。
たとえば、
For i = 2 To 50000
Cells(i, 2).Value = WorksheetFunction.VLookup( _
Cells(i, 1).Value, _
Worksheets("商品マスタ").Range("A:C"), _
2, _
False _
)
Next iのような処理があれば、高速化できる余地はかなりあります。
「シートから一括で読む → メモリ上で処理する → 一括で書く」という設計へ変更できないか検討してみてください。
まとめ
今回は、数万行以上の大量データを高速に照合するための2次元配列＋Scripting.Dictionaryの実務パターンを解説しました。
重要なポイントを整理すると、次のとおりです。
- 大量データでは、セルを1件ずつ読み書きしない
Range.Valueでセル範囲を2次元配列へ一括取得する- マスタデータは商品コードなどをキーにしてDictionaryへ登録する
dict.Exists(key)で未登録データを安全に判定する- 複数項目を返したい場合はDictionaryの値へ配列を格納する
- キーの重複は「先勝ち」「後勝ち」のルールを事前に決める
TrimやCompareModeで表記ゆれ対策を行う- 結果は出力用配列へためて、最後にRangeへ一括書き出しする
大量データを扱うVBAでは、「何回ループしているか」だけではなく、「何回シートへアクセスしているか」が処理速度を大きく左右します。
最終行の取得方法をもう少し詳しく整理したい方は、こちらの記事も参考にしてください。
大量データを別シートや別ブックへ高速転記したい場合は、こちらの記事もあわせて読むと、配列を使った高速処理の理解が深まります。
【VBA】転記処理の決定版！別シート・別ブックへデータを高速コピーする自動化コード
条件に一致したデータだけを抽出して転記したい場合は、AutoFilterを利用する方法も実務で非常に便利です。
【VBA】AutoFilterで条件抽出して別シートへ高速転記！コピペで動く決定版コード
そして、今回のような高速化コードを本番業務へ導入するときに欠かせないのがエラー処理です。
どれだけ高速なマクロでも、予想外のデータや存在しないシート、途中の実行時エラーで止まり、ScreenUpdating = Falseや手動計算設定が戻らなくなってしまっては実務では困ります。
次回は、「現場で動かしても止まらない！エラー処理（On Error）と実務デバッグ完全ガイド」として、On Error GoToの正しい使い方、後処理を必ず実行する設計、エラー番号・エラー内容の取得、デバッグ方法まで実務向けに詳しく解説します。
