Excel VBA 逆引き集 | Key列の正規化

Excel VBA
スポンサーリンク

Key列の正規化

突合・JOIN・差分検出で「一致しない…」の8割はキーの表記揺れが原因。安全な正規化は「余分な空白や記号を除去」「大小／全半の揺れ吸収」「ゼロ埋めや日付フォーマット統一」「型を安定化」の4本柱です。初心者でも壊れないテンプレをまとめました。

正規化の基本方針

  • 表記揺れ吸収:
    Trim/UCase で前後空白・大小を統一。必要に応じて 半角化（StrConv vbNarrow）、全角英数・記号の揺れを吸収。
  • 不要文字の除去:
    Replace や RegExp でハイフン・スペース・括弧など突合に不要な装飾を落とす。
  • 桁・書式の統一:
    ゼロ埋め（LPAD）、日付フォーマット（yyyy-mm）、数値化（Val→CDbl） で比較可能な形へ。
  • 型の安定化:
    キーは必ず文字列化（CStr）してから正規化。数値キーは「文字列キーとしての表記」を決める。

使い回せる：キー正規化関数（単一キー）

Option Explicit

'記号の一括除去（必要に応じて追加）
Private Function StripSymbols(ByVal s As String) As String
    Dim bad As Variant: bad = Array(" ", vbTab, "-", "ー", "‐", "―", "_", "/", "\", "(", ")", "（", "）", "・", "．", ".", "　")
    Dim i As Long
    StripSymbols = s
    For i = LBound(bad) To UBound(bad)
        StripSymbols = Replace(StripSymbols, CStr(bad(i)), "")
    Next
End Function

'左側ゼロ埋め（桁を合わせたいときに）
Public Function LPad(ByVal s As String, ByVal totalLen As Long, Optional ByVal padChar As String = "0") As String
    s = CStr(s)
    If Len(s) >= totalLen Then
        LPad = s
    Else
        LPad = String(totalLen - Len(s), padChar) & s
    End If
End Function

'日付を yyyy-mm に統一（文字列やExcel日付に対応）
Public Function NormalizeYm(ByVal v As Variant) As String
    If IsDate(v) Then
        NormalizeYm = Format$(CDate(v), "yyyy-mm")
    Else
        '文字列の "yyyy/mm/dd" などから年月を拾いたい場合はここにパースを追加
        NormalizeYm = CStr(v)
    End If
End Function

'キー正規化（英数・記号系）
Public Function NormalizeKey(ByVal v As Variant, Optional ByVal strip As Boolean = True, _
                             Optional ByVal toHalf As Boolean = True, Optional ByVal toUpper As Boolean = True, _
                             Optional ByVal padLen As Long = 0) As String
    Dim s As String: s = CStr(v)
    s = Trim$(s)
    If toHalf Then s = StrConv(s, vbNarrow)            '全角英数→半角
    If strip Then s = StripSymbols(s)                  '不要記号除去
    If toUpper Then s = UCase$(s)                      '大小統一
    If padLen > 0 Then s = LPad(s, padLen, "0")        '桁統一（必要に応じて）
    NormalizeKey = s
End Function
VB
  • ポイント
    • 柔軟性: 引数で「記号除去」「半角化」「大文字化」「ゼロ埋め」をオン/オフ。
    • 安全運用: 比較前に必ず CStr→Trim→Narrow→Strip→UCase→Pad の順で正規化。

レコード側での一括適用（辞書化やJOINの前処理）

Sub ApplyNormalization_Example()
    'Sheet("明細") A=コード, B=名称
    Dim v As Variant: v = Worksheets("明細").Range("A1").CurrentRegion.Value

    '正規化したコードをC列に出力（監査用）
    Dim r As Long
    Worksheets("明細").Cells(1, "C").Value = "正規化コード"
    For r = 2 To UBound(v, 1)
        Worksheets("明細").Cells(r, "C").Value = NormalizeKey(v(r, 1), True, True, True, 8)
    Next
End Sub
VB
  • ポイント
    • 見える化: 正規化後のキーを列に出して一致しない原因を目視確認。
    • 桁統一例: padLen:=8 で「00012345」などに揃える。

突合テンプレへの組み込み（辞書のキー正規化）

Sub Join_WithNormalizedKeys()
    '明細: Sheet("明細") A=コード, B=数量
    'マスタ: Sheet("マスタ") A=コード, B=名称, C=単価
    Dim vD As Variant: vD = Worksheets("明細").Range("A1").CurrentRegion.Value
    Dim vM As Variant: vM = Worksheets("マスタ").Range("A1").CurrentRegion.Value

    'マスタ辞書（正規化キー→(名称,単価)）
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long, k As String
    For i = 2 To UBound(vM, 1)
        k = NormalizeKey(vM(i, 1), True, True, True, 8)
        If Len(k) > 0 Then dict(k) = Array(CStr(vM(i, 2)), CDbl(Val(vM(i, 3))))
    Next

    '明細出力（JOIN）
    Dim out() As Variant: ReDim out(1 To UBound(vD, 1), 1 To 4)
    out(1, 1) = "コード": out(1, 2) = "名称": out(1, 3) = "単価": out(1, 4) = "数量"
    Dim r As Long
    For r = 2 To UBound(vD, 1)
        Dim kd As String: kd = NormalizeKey(vD(r, 1), True, True, True, 8)
        out(r, 1) = vD(r, 1)
        out(r, 4) = CDbl(Val(vD(r, 2)))
        If dict.Exists(kd) Then
            out(r, 2) = dict(kd)(0)
            out(r, 3) = dict(kd)(1)
        Else
            out(r, 2) = "#N/A": out(r, 3) = 0
        End If
    Next

    Dim wsOut As Worksheet: Set wsOut = Worksheets.Add
    wsOut.Name = "正規化JOIN"
    wsOut.Range("A1").Resize(UBound(out, 1), UBound(out, 2)).Value = out
    wsOut.Columns.AutoFit
End Sub
VB
  • ポイント
    • 両側同じルール: 明細とマスタで同一の NormalizeKey を使う。
    • 未一致の見える化: 名称は「#N/A」、単価は0で補完。

複合キーの正規化（例：コード×年月）

Public Function BuildCompositeKey(ByVal code As Variant, ByVal ymd As Variant) As String
    Dim kCode As String: kCode = NormalizeKey(code, True, True, True, 8)
    Dim ym As String: ym = NormalizeYm(ymd)  'yyyy-mm 統一
    BuildCompositeKey = kCode & "|" & UCase$(StrConv(ym, vbNarrow))
End Function

Sub UseCompositeKey_Example()
    'A: A=コード, B=日付（または年月）, C=名称
    Dim vA As Variant: vA = Worksheets("A").Range("A1").CurrentRegion.Value

    '正規化複合キーをD列に出力
    Dim r As Long
    Worksheets("A").Cells(1, "D").Value = "複合キー（正規化）"
    For r = 2 To UBound(vA, 1)
        Worksheets("A").Cells(r, "D").Value = BuildCompositeKey(vA(r, 1), vA(r, 2))
    Next
End Sub
VB
  • ポイント
    • 区切り文字: "|" などデータに出ない安全な記号を使用。
    • 年月統一: Date型なら yyyy-mm に固定して比較ミス防止。

高度：RegExpで「数字だけ」「英数字だけ」抽出

ハイフンやスペースが混じるコードから「数字だけ」「英数字だけ」をキーに使うときのテンプレ。

Public Function OnlyDigits(ByVal s As String) As String
    Dim re As Object: Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[^\d]"    '数字以外
    re.Global = True
    OnlyDigits = re.Replace(s, "")
End Function

Public Function OnlyAlphaNum(ByVal s As String) As String
    Dim re As Object: Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[^A-Za-z0-9]" '英数字以外
    re.Global = True
    OnlyAlphaNum = re.Replace(s, "")
End Function

Sub Normalize_WithRegex_Example()
    Dim raw As String: raw = "ＡＢ-123 ー(45)"
    Dim s As String: s = StrConv(raw, vbNarrow) '半角化
    Debug.Print OnlyDigits(s)      '結果: 12345
    Debug.Print OnlyAlphaNum(s)    '結果: AB12345
End Sub
VB
  • ポイント
    • 強力な除去: パターンで不要文字を一掃。
    • 順序: 先に半角化→置換すると一貫性が出る。

よくある落とし穴と対策

  • 大文字・小文字が混在して一致しない
    • 対策: toUpper=True で必ず大文字へ統一。小文字統一なら UCase→LCase に変更。
  • 全角・半角の揺れ（英数／記号）
    • 対策: vbNarrow で半角化（必要ならカタカナは vbKatakana→vbHiragana で統一）。
  • 装飾記号の混入（ハイフン・括弧・スペース）
    • 対策: StripSymbols で除去。特殊記号は Replace のリストを現場に合わせて拡張。
  • 桁数が違って一致しない（00123 vs 123）
    • 対策: LPad で桁統一。ゼロ埋めの桁数はルール化する（例：8桁）。
  • 日付／年月が文字と日付型で混在
    • 対策: NormalizeYm で yyyy-mm 統一。文字列は事前変換かパターン抽出。
  • 比較の途中で数値が文字列になり判定ブレ
    • 対策: キーは「文字列として比較」する前提で正規化。数値計算は別途 Val→CDbl。

例題で練習

'例1：キー列の正規化（Trim/半角化/記号除去/大文字/ゼロ埋め）
Sub Example_NormalizeKey()
    Debug.Print NormalizeKey("  ab-１２３４  ", True, True, True, 8) '結果: AB1234 → 000AB1234（用途に応じ調整）
End Sub

'例2：シート全体に正規化列を追加して監査
Sub Example_ApplyNormalization()
    ApplyNormalization_Example
End Sub

'例3：正規化キーで辞書JOIN
Sub Example_NormalizedJoin()
    Join_WithNormalizedKeys
End Sub

'例4：複合キー（コード×年月）の正規化と出力
Sub Example_CompositeKey()
    UseCompositeKey_Example
End Sub

'例5：RegExpで「英数字だけ」「数字だけ」に正規化
Sub Example_Regex()
    Normalize_WithRegex_Example
End Sub
VB
スポンサーリンク
タイトルとURLをコピーしました