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

Excel VBA
スポンサーリンク

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

「明細に部門名を付けて、さらにその部門から所属長名を付けたい」
「売上明細に商品マスタをJOINして、さらにカテゴリマスタもJOINしたい」

こういう“段階的な横持ち”を、手作業の VLOOKUP や XLOOKUP でやると、式だらけになって壊れやすくなります。
2段JOINテンプレは、これを VBA で

1段目：明細 → マスタA をJOIN
2段目：マスタAの情報 → マスタB をJOIN

という流れで一気にやる“型”です。

ポイントは、
「配列に読み込む → Dictionaryでキー検索 → 一括貼り付け」
という高速パターンを、2段に分けてきれいに書くことです。

全体設計：3つのシートと2つのJOIN

想定するシート構成

ここでは、次のような構成を例にします。

明細シート：Detail
マスタA：MstDept（部門マスタ）
マスタB：MstManager（部門ごとの所属長マスタ）

Detail には、部門コードだけが入っているとします。

Detail
A列：伝票番号
B列：部門コード
C列：金額
D列以降：JOINで付ける列（部門名、所属長名など）

MstDept
A列：部門コード
B列：部門名
C列：部門種別 など

MstManager
A列：部門コード
B列：所属長名

やりたいことは、

1段目：Detail.B（部門コード） → MstDept から部門名をJOIN
2段目：同じ部門コードで MstManager から所属長名をJOIN

という2段JOINです。

共通ユーティリティ：速度と安全のための“お作法”

画面更新・イベント・計算を止める

大量のJOINをするときは、毎回画面を描き直していると遅くなります。
まずは、よく使う“速度アップユーティリティ”を用意しておきます。

' ModCommon_Speed.bas
Option Explicit

Public Sub SpeedOn()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Public Sub SpeedOff()
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

JOIN系の処理をするときは、
最初に SpeedOn、最後に SpeedOff を呼ぶのを習慣にしておくと、体感速度がかなり変わります。

1段目JOIN：明細 → マスタA（部門名を付ける）

マスタAをDictionaryに載せる

まずは、部門マスタ（MstDept）を Dictionary に載せます。
キーは「部門コード」、値は「部門名」とします（必要なら他の列も持てます）。

' ModJoin_2Stage.bas
Option Explicit

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)          ' 部門コード
        deptName = CStr(ws.Cells(r, 2).Value)     ' 部門名
        If key <> "" Then
            If Not dict.Exists(key) Then
                dict.Add key, deptName
            End If
        End If
    Next
    
    Set LoadDeptMaster = dict
End Function
VB

ここでの重要ポイントは、「マスタを1回だけ読み込んで、Dictionaryに載せる」ことです。
これで、部門コードから部門名を一瞬で引けるようになります。

明細に部門名をJOINする処理

次に、Detail シートの部門コード列をなめながら、部門名をJOINします。

Public Sub JoinDeptToDetail()
    SpeedOn
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Detail")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim deptCol As Long
    deptCol = 2          ' 部門コード列（B）
    
    Dim outCol As Long
    outCol = 4           ' 部門名を書き出す列（D）
    
    Dim dictDept As Object
    Set dictDept = LoadDeptMaster()
    
    Dim r As Long
    Dim key As String
    Dim deptName As String
    
    ws.Cells(1, outCol).Value = "部門名"
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, deptCol).Value)
        If key <> "" Then
            If dictDept.Exists(key) Then
                deptName = dictDept(key)
                ws.Cells(r, outCol).Value = deptName
            Else
                ws.Cells(r, outCol).Value = "#未登録"
            End If
        End If
    Next
    
    SpeedOff
    MsgBox "1段目JOIN（部門名）が完了しました。", vbInformation
End Sub
VB

ここで深掘りしたいポイントは、「未一致の扱い」です。

部門コードがマスタに存在しない場合、
"#未登録" と書いておくことで、あとからフィルタで簡単に洗い出せます。
この「未一致をどう扱うか」は、JOIN系テンプレではとても重要な設計ポイントです。

2段目JOIN：明細 → マスタB（所属長名を付ける）

マスタB（所属長マスタ）をDictionaryに載せる

今度は、MstManager を Dictionary に載せます。
キーは同じく「部門コード」、値は「所属長名」です。

Private Function LoadManagerMaster() As Object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("MstManager")
    
    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 mgrName As String
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, 1).Value)          ' 部門コード
        mgrName = CStr(ws.Cells(r, 2).Value)      ' 所属長名
        If key <> "" Then
            If Not dict.Exists(key) Then
                dict.Add key, mgrName
            End If
        End If
    Next
    
    Set LoadManagerMaster = dict
End Function
VB

明細に所属長名をJOINする処理

1段目と同じように、Detail の部門コードをキーにして、所属長名をJOINします。

Public Sub JoinManagerToDetail()
    SpeedOn
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Detail")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim deptCol As Long
    deptCol = 2          ' 部門コード列（B）
    
    Dim outCol As Long
    outCol = 5           ' 所属長名を書き出す列（E）
    
    Dim dictMgr As Object
    Set dictMgr = LoadManagerMaster()
    
    Dim r As Long
    Dim key As String
    Dim mgrName As String
    
    ws.Cells(1, outCol).Value = "所属長名"
    
    For r = 2 To lastRow
        key = CStr(ws.Cells(r, deptCol).Value)
        If key <> "" Then
            If dictMgr.Exists(key) Then
                mgrName = dictMgr(key)
                ws.Cells(r, outCol).Value = mgrName
            Else
                ws.Cells(r, outCol).Value = "#未登録"
            End If
        End If
    Next
    
    SpeedOff
    MsgBox "2段目JOIN（所属長名）が完了しました。", vbInformation
End Sub
VB

これで、Detail シートには、

D列：部門名（1段目JOIN）
E列：所属長名（2段目JOIN）

が横に並ぶことになります。

2段JOINを「1本の流れ」として実行する

2段JOINをまとめて呼ぶラッパー

実務では、「1段目だけ」「2段目だけ」ではなく、
「2段まとめて一気にやりたい」ことが多いので、ラッパーを用意します。

Public Sub Run_2StageJoin_Dept()
    SpeedOn
    
    Call JoinDeptToDetail
    Call JoinManagerToDetail
    
    SpeedOff
    MsgBox "2段JOIN（部門名＋所属長名）が完了しました。", vbInformation
End Sub
VB

これをボタンに割り当てておけば、
「明細に部門名と所属長名を一気に付ける」処理がワンクリックになります。

応用：JOIN先のキーが違う“連鎖JOIN”にも対応する

例：明細 → 商品マスタ → カテゴリマスタ

もう少し実務寄りの例として、

Detail：商品コードだけ持っている
MstItem：商品コード → カテゴリコード
MstCategory：カテゴリコード → カテゴリ名

という構造を考えます。

この場合の2段JOINは、

1段目：Detail の商品コード → MstItem からカテゴリコードをJOIN
2段目：JOINしたカテゴリコード → MstCategory からカテゴリ名をJOIN

という“キーが変わる連鎖JOIN”になります。

ポイントは、「2段目のJOINは、1段目で付けた列をキーにする」ということです。

1段目で Detail にカテゴリコード列（たとえば F列）を付ける
2段目では、その F列をキーにして MstCategory をJOINする

という流れにすれば、ロジック自体は先ほどとほぼ同じです。

重要ポイントの深掘り：2段JOINを“壊れないテンプレ”にするコツ

キーの正規化をどこでやるか

JOINの精度は、「キーの揃え方」に大きく左右されます。

部門コードや商品コードのような“きれいなコード”ならそのままで良いですが、
メールアドレスや会社名などをキーにするJOINでは、

全角・半角
大文字・小文字
前後スペース

などを正規化してから Dictionary に載せる必要があります。

この場合は、

キーを作る前に NormalizeTextBasic のような関数を通す
マスタ側と明細側で、同じ正規化関数を使う

というルールを徹底することが重要です。

未一致・重複をどう扱うかを最初に決める

JOINで必ず出てくるのが、

明細にあるキーがマスタにない（未一致）
マスタ側に同じキーが複数ある（重複）

というケースです。

未一致は、今回のように "#未登録" と書いておくと、あとからフィルタで拾えます。
重複については、Dictionary に最初の1件だけを載せるのか、
重複を検出して別シートに出すのか、業務ルールとして決めておく必要があります。

2段JOINテンプレを“実務で使える”ものにするには、

未一致はどうするか（エラー扱いか、空欄か、特定文字か）
重複はどうするか（最初の1件だけ使うか、エラーとしてログに出すか）

を、最初に決めてコードに反映しておくことがとても大事です。

配列一括処理にするとさらに高速になる

今回のコードは「分かりやすさ優先」で、セルを1行ずつ回しています。
行数が数万行を超えるような場合は、

明細を配列に読み込む
配列の中でJOIN結果を書き込む
最後に一括でシートに書き戻す

という形にすると、さらに高速になります。

構造は少し複雑になりますが、
「配列＋Dictionary＋一括貼り付け」というパターンを覚えておくと、
JOIN系の処理はほぼ何でも高速に書けるようになります。

まとめ：2段JOINテンプレは「マスタをDictionary化して、段ごとにJOINする」だけ

2段JOINテンプレの本質は、とてもシンプルです。

1段目：明細のキー → マスタA（Dictionary）から値を引いてJOIN
2段目：明細（または1段目で付けた列）のキー → マスタB（Dictionary）から値を引いてJOIN

この“段ごとのJOIN”を、
SpeedOn/Off で包み、
未一致の扱いを決め、
必要ならキーの正規化を挟む。

ここまでをテンプレとして固めておけば、

部門 → 所属長
商品 → カテゴリ
顧客 → ランク → 特典

など、どんな2段JOINでも、
シート名と列番号を変えるだけで量産できます。

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