ねらい：VLOOKUPの“遅い・不安定”を捨て、配列I/O＋Dictionaryで爆速・堅牢にする
VLOOKUPは大量行や複合キーで遅く、式管理が崩れがちです。実務では「片側を辞書化→一括照合→配列で結果へ」の型にすると、数万〜数十万行でも高速・安定に動きます。初心者でも貼って動く“超再利用部品”を、単一キー・複合キー・多段参照・逆引き・欠損処理までまとめて用意します。
重要ポイントの深掘り
- 核設計: シート→配列（一次読み込み）→辞書（キー→値）→結果配列→一括書き戻し。セルを行き来しないことが速度の源泉です。
- キー正規化: Trim＋LCase（必要なら全半角統一）。入口で揺らぎを潰して、誤マッチと再計算コストを防ぎます。
- 存在分岐の最小化: d(key)=value で追加・更新を統一。Exists は「ヒット時のみ特別処理」に限定すると速く・読みやすくなります。
基本部品：辞書生成・正規化・安全な比較モード
NewDict（参照設定不要）とキー整形
' ModVL_Base.bas
Option Explicit
Public Function NewDict(Optional ByVal textCompare As Boolean = True) As Object
Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = IIf(textCompare, 1, 0) ' 1=Text（大小無視）, 0=Binary
Set NewDict = d
End Function
Public Function NormKey(ByVal v As Variant) As String
NormKey = LCase$(Trim$(CStr(v)))
End Function
Range⇔配列の一括I/O
Public Function ReadRegion(ByVal ws As Worksheet, Optional ByVal topLeft As String = "A1") As Variant
ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function
Public Sub WriteRegion(ByVal ws As Worksheet, ByVal a As Variant, Optional ByVal topLeft As String = "A1")
ws.Range(topLeft).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
単一キー：VLOOKUP高速テンプレ（片側辞書→即参照）
片側辞書化＋結果一括書き戻し（貼って動く）
' ModVL_Single.bas
Option Explicit
' srcSheet: 照合元（キーがA列）、masterSheet: マスタ（キーA列、取りたい値はB列）
Public Sub FastVlookup(ByVal srcSheet As String, ByVal masterSheet As String, _
Optional ByVal outColLetter As String = "Z")
Dim wsS As Worksheet: Set wsS = Worksheets(srcSheet)
Dim wsM As Worksheet: Set wsM = Worksheets(masterSheet)
Dim aS As Variant: aS = ReadRegion(wsS) ' 元データ
Dim aM As Variant: aM = ReadRegion(wsM) ' マスタ
Dim d As Object: Set d = NewDict(True)
Dim r As Long
For r = 2 To UBound(aM, 1)
d(NormKey(aM(r, 1))) = aM(r, 2) ' キーA→値B
Next
Dim out() As Variant: ReDim out(1 To UBound(aS, 1), 1 To 1)
out(1, 1) = "Lookup" ' ヘッダー
For r = 2 To UBound(aS, 1)
Dim k As String: k = NormKey(aS(r, 1))
out(r, 1) = IIf(d.Exists(k), d(k), "") ' 欠損は空文字（仕様に合わせて変更）
Next
wsS.Range(outColLetter & "1").Resize(UBound(out, 1), 1).Value = out ' 一括書き戻し
End Sub
重要ポイントの深掘り
- ヘッダー固定: 1行目は見出し扱い。データは2行目から走査すると安全です。
- 欠損の扱い: 空文字・0・“N/A”など、運用と下流仕様に合わせて統一し、後続集計が崩れないようにします。
複合キー：2列以上で一意化するテンプレ
安全な区切り文字でキーを束ねる（Chr(30)推奨）
' ModVL_Composite.bas
Option Explicit
Private Const SEP As String = Chr$(30)
Private Function MakeKey2(ByVal v1 As Variant, ByVal v2 As Variant) As String
MakeKey2 = NormKey(v1) & SEP & NormKey(v2)
End Function
Public Sub FastVlookup2Keys(ByVal srcSheet As String, ByVal masterSheet As String, _
Optional ByVal outColLetter As String = "Z")
Dim wsS As Worksheet: Set wsS = Worksheets(srcSheet)
Dim wsM As Worksheet: Set wsM = Worksheets(masterSheet)
Dim aS As Variant: aS = ReadRegion(wsS)
Dim aM As Variant: aM = ReadRegion(wsM)
Dim d As Object: Set d = NewDict(True)
Dim r As Long
For r = 2 To UBound(aM, 1)
d(MakeKey2(aM(r, 1), aM(r, 2))) = aM(r, 3) ' キーA,B → 値C
Next
Dim out() As Variant: ReDim out(1 To UBound(aS, 1), 1 To 1)
out(1, 1) = "Lookup"
For r = 2 To UBound(aS, 1)
Dim k As String: k = MakeKey2(aS(r, 1), aS(r, 2))
out(r, 1) = IIf(d.Exists(k), d(k), "")
Next
wsS.Range(outColLetter & "1").Resize(UBound(out, 1), 1).Value = out
End Sub
重要ポイントの深掘り
- 衝突回避: 区切りに“ありえない文字”を使うと、値の連結による誤結合を防げます。
- 正規化の一貫: 片側だけ正規化しても意味がありません。辞書登録時も参照時も同じ正規化関数を通します。
多段参照：コード→ID→属性の二段VLOOKUPを線形時間で
段積み辞書で遅い式連鎖を廃止
' ModVL_MultiStage.bas
Option Explicit
Public Sub FastVlookup2Stage(ByVal srcSheet As String, ByVal master1Sheet As String, ByVal master2Sheet As String, _
Optional ByVal outColLetter As String = "Z")
' 段1：コードA→ID、段2：ID→属性
Dim aS As Variant: aS = ReadRegion(Worksheets(srcSheet))
Dim aM1 As Variant: aM1 = ReadRegion(Worksheets(master1Sheet))
Dim aM2 As Variant: aM2 = ReadRegion(Worksheets(master2Sheet))
Dim d1 As Object: Set d1 = NewDict(True)
Dim d2 As Object: Set d2 = NewDict(True)
Dim r As Long
For r = 2 To UBound(aM1, 1): d1(NormKey(aM1(r, 1))) = aM1(r, 2): Next ' Code→ID
For r = 2 To UBound(aM2, 1): d2(NormKey(aM2(r, 1))) = aM2(r, 2): Next ' ID→Attr
Dim out() As Variant: ReDim out(1 To UBound(aS, 1), 1 To 1)
out(1, 1) = "Attr"
For r = 2 To UBound(aS, 1)
Dim code As String: code = NormKey(aS(r, 1))
If d1.Exists(code) Then
Dim id As String: id = NormKey(d1(code))
out(r, 1) = IIf(d2.Exists(id), d2(id), "")
Else
out(r, 1) = ""
End If
Next
Worksheets(srcSheet).Range(outColLetter & "1").Resize(UBound(out, 1), 1).Value = out
End Sub
重要ポイントの深掘り
- 式連鎖の置換: VLOOKUPの入れ子は列追加で壊れます。辞書段積みなら列変更に強く、計算量も線形に抑えられます。
- 中間結果の正規化: 段1の出力（ID）も必ず NormKey を通してから段2に渡します。
逆引き：値→複数のキー（多対一・一対多）を返す
値からキー一覧を返すテンプレ（重複保持）
' ModVL_Reverse.bas
Option Explicit
Public Function BuildReverseIndex(ByVal wsName As String) As Object
Dim a As Variant: a = ReadRegion(Worksheets(wsName))
Dim idx As Object: Set idx = NewDict(True)
Dim r As Long
For r = 2 To UBound(a, 1)
Dim k As String: k = NormKey(a(r, 1)) ' キー
Dim v As String: v = NormKey(a(r, 2)) ' 値
If Not idx.Exists(v) Then
Dim col As Collection: Set col = New Collection
col.Add k: Set idx(v) = col
Else
idx(v).Add k
End If
Next
Set BuildReverseIndex = idx
End Function
重要ポイントの深掘り
- 多対一の現実: 実務では「同じ値に複数キー」が普通。Collection でキー群を保持すると“逆引き”や重複監査が簡単になります。
- 返却型の取り扱い: Items をさらに配列化してシートへ出すなど、出口を決めておくと使い回しやすいです。
欠損・型混在の扱い：安全なデフォルトと数値変換
欠損時のポリシーと数値変換補助
' ModVL_Safety.bas
Option Explicit
Public Function DefaultIfMissing(ByVal exists As Boolean, ByVal found As Variant, Optional ByVal defVal As Variant = "") As Variant
DefaultIfMissing = IIf(exists, found, defVal)
End Function
Public Function ToNumberOrZero(ByVal v As Variant) As Double
If IsNumeric(v) Then ToNumberOrZero = CDbl(v) Else ToNumberOrZero = 0#
End Function
重要ポイントの深掘り
- 欠損は仕様化: 「空文字」「0」「N/A」など、下流のピボットや集計が壊れない値に統一します。
- 数値は数値で: 文字数値のまま比較すると順序や集計が崩れます。CDbl/Valで明示変換してから使います。
例題の通し方：顧客コードから顧客名を高速付与、未登録は空に
手順（単一キー）
' ModVL_Example.bas
Option Explicit
Public Sub Demo_FastVlookup()
' Input: Data（A:顧客コード）、Master（A:顧客コード, B:顧客名）
FastVlookup "Data", "Master", "Z" ' DataのZ列へ顧客名を高速付与
MsgBox "顧客名付与が完了しました。", vbInformation
End Sub
期待動作の確認ポイント
- 正しく付与: 登録済みコードに顧客名が入り、未登録は空。
- 速度体感: 数万行でも「一瞬」。セル関数より桁違いに速いはずです。
運用の深掘り：拡張・安全装置・見える化
拡張と保守
- 列の変更耐性: マスタ列が増減しても、辞書化の列指定を変えるだけで安全に追随できます。
- 設定外出し: シート名・出力列・キー列の定義は config.ini に外出しすると、非エンジニアでも変更可能になります。
安全装置
- 重複検知: マスタ側でキー重複があれば警告ログを残す。辞書化前に d.Exists(key) を見て“二重登録”の一覧を作ると、品質が上がります。
- ログ: 何件ヒット/何件欠損を Log に記録して、品質メトリクスを管理する。
見える化
- 色分け: 結果列で欠損（空）を薄赤、ヒットを薄緑に条件付き書式。レビュー時の確認が楽になります。
落とし穴と対策（深掘り）
正規化の不足で誤ミスヒット
半角/全角・大小・余分スペースが揺らぎの原因。入口で NormKey を必ず通す。
逐次セル書きで遅い
結果は配列へ作って最後に一括書き戻し。セル毎書きは避ける。
複合キーの衝突
“安全な区切り”を使う。SEPを Chr(30) などに固定して誤連結を根絶。
多段参照で中間値を未正規化
段1出力（ID）も必ず正規化して段2へ。ここを怠るとヒット漏れが起きます。
まとめ：配列I/O＋辞書化＋正規化で、VLOOKUPを“速く・壊れない”部品にする
- 片側辞書→一括照合→結果配列→一括書き戻しが定番の型。
- 正規化・欠損ポリシー・区切り文字を先に決めて、運用品質を守る。
- 複合キーと多段辞書で複雑な参照も線形時間。逆引きで監査も容易。