Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 3段JOINテンプレ

Excel VBA
スポンサーリンク

ねらい：明細に「3つのマスタ」を段階的にくっつける“3段JOIN”の型

2段JOINまではよくやっていると思いますが、実務だとこういうケースが出てきます。

売上明細に「商品名」を付けたい。
商品から「カテゴリ名」も付けたい。
カテゴリから「部門名」も付けたい。

つまり、明細 → 商品マスタ → カテゴリマスタ → 部門マスタ、という三段階のJOINです。
これをシート関数でやると、VLOOKUPやXLOOKUPが何列にも渡って並び、壊れやすくて遅い。
ここで作る「3段JOINテンプレ」は、これを VBA で

明細を配列に読み込む
3つのマスタを Dictionary に載せる
1段目 → 2段目 → 3段目と順番にJOINして配列に書き込み
最後に一括でシートに書き戻す

という“プロっぽい型”にしてしまうものです。

全体設計：明細＋3マスタの関係をはっきりさせる

想定するシート構成とJOINの流れ

例として、次のような構成を想定します。

Detail シート
A列：伝票番号
B列：商品コード
C列：数量
D列以降：JOIN結果を書き込む列

MstItem シート（商品マスタ）
A列：商品コード
B列：商品名
C列：カテゴリコード

MstCategory シート（カテゴリマスタ）
A列：カテゴリコード
B列：カテゴリ名
C列：部門コード

MstDept シート（部門マスタ）
A列：部門コード
B列：部門名

やりたい3段JOINはこうです。

1段目：Detail.B（商品コード） → MstItem から「商品名」「カテゴリコード」をJOIN
2段目：1段目で得たカテゴリコード → MstCategory から「カテゴリ名」「部門コード」をJOIN
3段目：2段目で得た部門コード → MstDept から「部門名」をJOIN

キーが「商品コード → カテゴリコード → 部門コード」と段階的に変わっていくのがポイントです。

コア部品：3つのマスタをDictionaryに読み込む

商品マスタを Dictionary に載せる（商品コード → 商品名・カテゴリコード）

商品マスタは、1つのキーに複数の値（商品名とカテゴリコード）を持たせたいので、配列で持たせます。

' ModJoin3Stage.bas
Option Explicit

Private Function LoadItemMaster() As Object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("MstItem")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    Dim arr(1 To 2) As Variant   ' 1:商品名, 2:カテゴリコード
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, 1).Value)          ' 商品コード
        If key <> "" Then
            arr(1) = CStr(ws.Cells(r, 2).Value)   ' 商品名
            arr(2) = CStr(ws.Cells(r, 3).Value)   ' カテゴリコード
            If Not dict.Exists(key) Then
                dict.Add key, arr
            End If
        End If
    Next
    
    Set LoadItemMaster = dict
End Function
VB

ここでの重要ポイントは、「DictionaryのItemに配列を入れている」ことです。
これで、商品コードから「商品名」と「カテゴリコード」を一度に取り出せます。

カテゴリマスタを Dictionary に載せる（カテゴリコード → カテゴリ名・部門コード）

同じ考え方で、カテゴリマスタも配列で持たせます。

Private Function LoadCategoryMaster() As Object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("MstCategory")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    Dim arr(1 To 2) As Variant   ' 1:カテゴリ名, 2:部門コード
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, 1).Value)          ' カテゴリコード
        If key <> "" Then
            arr(1) = CStr(ws.Cells(r, 2).Value)   ' カテゴリ名
            arr(2) = CStr(ws.Cells(r, 3).Value)   ' 部門コード
            If Not dict.Exists(key) Then
                dict.Add key, arr
            End If
        End If
    Next
    
    Set LoadCategoryMaster = dict
End Function
VB

部門マスタを Dictionary に載せる（部門コード → 部門名）

部門マスタは、部門名だけ持てばよいので、単純なキー→値です。

Private Function LoadDeptMaster() As Object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("MstDept")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    Dim deptName As String
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, 1).Value)          ' 部門コード
        If key <> "" Then
            deptName = CStr(ws.Cells(r, 2).Value) ' 部門名
            If Not dict.Exists(key) Then
                dict.Add key, deptName
            End If
        End If
    Next
    
    Set LoadDeptMaster = dict
End Function
VB

明細を配列に読み込み、3段JOINして一括書き戻し

明細を配列に読み込む準備

3段JOINを高速に回すために、Detail シートを丸ごと配列に読み込みます。

Public Sub Run_3StageJoin()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Detail")
    
    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    If lastRow < 2 Then
        MsgBox "明細にデータがありません。", vbInformation
        Exit Sub
    End If
    
    Dim data As Variant
    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value
VB

ここで data(行, 列) という二次元配列に、ヘッダ行も含めて明細が入ります。

JOIN結果を書き込む列を決める

商品名、カテゴリコード、カテゴリ名、部門コード、部門名をどこに書くかを決めます。
ここでは、D列以降に順番に書くことにします。

    Dim colItemName As Long, colCatCode As Long
    Dim colCatName As Long, colDeptCode As Long, colDeptName As Long
    
    colItemName = 4   ' 商品名
    colCatCode = 5    ' カテゴリコード
    colCatName = 6    ' カテゴリ名
    colDeptCode = 7   ' 部門コード
    colDeptName = 8   ' 部門名
    
    data(1, colItemName) = "商品名"
    data(1, colCatCode) = "カテゴリコード"
    data(1, colCatName) = "カテゴリ名"
    data(1, colDeptCode) = "部門コード"
    data(1, colDeptName) = "部門名"
VB

3つのマスタを読み込み、3段JOINを実行する

ここからが3段JOINの本体です。

    Dim dictItem As Object, dictCat As Object, dictDept As Object
    Set dictItem = LoadItemMaster()
    Set dictCat = LoadCategoryMaster()
    Set dictDept = LoadDeptMaster()
    
    Dim r As Long
    Dim keyItem As String
    Dim arrItem As Variant
    Dim keyCat As String
    Dim arrCat As Variant
    Dim keyDept As String
    Dim deptName As String
    
    For r = 2 To lastRow
        keyItem = CStr(data(r, 2))   ' 商品コード（DetailのB列）
        
        If keyItem <> "" Then
            If dictItem.Exists(keyItem) Then
                arrItem = dictItem(keyItem)
                data(r, colItemName) = arrItem(1)   ' 商品名
                data(r, colCatCode) = arrItem(2)    ' カテゴリコード
                
                keyCat = CStr(arrItem(2))
                If keyCat <> "" And dictCat.Exists(keyCat) Then
                    arrCat = dictCat(keyCat)
                    data(r, colCatName) = arrCat(1)   ' カテゴリ名
                    data(r, colDeptCode) = arrCat(2)  ' 部門コード
                    
                    keyDept = CStr(arrCat(2))
                    If keyDept <> "" And dictDept.Exists(keyDept) Then
                        deptName = dictDept(keyDept)
                        data(r, colDeptName) = deptName
                    Else
                        data(r, colDeptName) = "#部門未登録"
                    End If
                Else
                    data(r, colCatName) = "#カテゴリ未登録"
                    data(r, colDeptCode) = ""
                    data(r, colDeptName) = ""
                End If
            Else
                data(r, colItemName) = "#商品未登録"
                data(r, colCatCode) = ""
                data(r, colCatName) = ""
                data(r, colDeptCode) = ""
                data(r, colDeptName) = ""
            End If
        End If
    Next
VB

ここでの重要ポイントを整理します。

1段目：商品コードが商品マスタに存在するかをチェックし、存在すれば商品名とカテゴリコードを配列から取り出す。
2段目：カテゴリコードがカテゴリマスタに存在するかをチェックし、存在すればカテゴリ名と部門コードを取り出す。
3段目：部門コードが部門マスタに存在するかをチェックし、存在すれば部門名を取り出す。

どこかの段でマスタに存在しなかった場合は、
「#商品未登録」「#カテゴリ未登録」「#部門未登録」のような印を入れておき、
あとからフィルタで確認できるようにしています。

配列を一括でシートに書き戻す

最後に、JOIN結果を含んだ配列をシートに戻します。

    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value = data
    
    MsgBox "3段JOINが完了しました。", vbInformation
End Sub
VB

これで、Detail シートには

D列：商品名
E列：カテゴリコード
F列：カテゴリ名
G列：部門コード
H列：部門名

が一気に埋まります。

例題のイメージ：売上明細に商品→カテゴリ→部門を付ける

例えば、Detail に次のような行があるとします。

商品コード P001
商品マスタでは P001 → 「りんご」・カテゴリコード C10
カテゴリマスタでは C10 → 「果物」・部門コード D01
部門マスタでは D01 → 「食品部」

3段JOIN後の1行は、こうなります。

商品コード P001
商品名 りんご
カテゴリコード C10
カテゴリ名 果物
部門コード D01
部門名 食品部

このように、「明細にはコードしかないけれど、分析やレポートでは名前や上位分類が欲しい」という場面で、3段JOINテンプレはそのまま使えます。

重要ポイントの深掘り：3段JOINを“壊れない部品”にするための考え方

段ごとに「未一致の扱い」を決めておく

3段JOINでは、どこかの段でマスタに存在しないキーが出てくることがよくあります。
そのときにどうするかを、最初から決めておくことが大事です。

今回のテンプレでは、
商品が未登録なら、以降のカテゴリ・部門も空欄にして「#商品未登録」とだけ書く。
カテゴリが未登録なら、部門コード・部門名は空欄にして「#カテゴリ未登録」と書く。

というルールにしています。
こうしておくと、「どの段で止まったか」が一目で分かり、データクレンジングの手が打ちやすくなります。

マスタ側のキーの正規化をどうするか

商品コードやカテゴリコードがきれいなコードなら問題ありませんが、
メールアドレスや会社名などをキーにするJOINでは、
全角・半角、大文字・小文字、前後スペースなどを正規化してから Dictionary に載せる必要があります。

その場合は、LoadItemMaster や LoadCategoryMaster の中で

key = NormalizeKey(CStr(ws.Cells(r, 1).Value))

のように、共通の正規化関数を通すようにします。
明細側も同じ関数で正規化してからキーを作ることで、JOINの精度が安定します。

行数が多いときのパフォーマンス

今回のテンプレは、明細を配列に読み込み、マスタは Dictionary に載せ、
JOIN結果を配列に書き込んでから一括で書き戻す構造なので、数万行〜数十万行でもかなり高速です。

さらに体感速度を上げたい場合は、3段JOIN全体を

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

で挟み、最後に元に戻すようにすると良いです。
（SpeedOn / SpeedOff のような共通プロシージャにしておくと便利です）

まとめ：3段JOINテンプレは「配列 × Dictionary × 段ごとのキー設計」でどこまでも拡張できる

3段JOINの本質は、次の3つです。

明細は配列に読み込んで一括処理する。
各マスタは Dictionary に載せ、キーから必要な情報を配列で取り出す。
1段目の結果を2段目のキーに、2段目の結果を3段目のキーに、という“連鎖”を意識して設計する。

この型さえ押さえておけば、

顧客 → ランク → 特典 → 担当者
商品 → カテゴリ → 部門 → 事業本部

のように、3段どころか4段JOINにも発展させられます。

スポンサーリンク
タイトルとURLをコピーしました