Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – キー変換テンプレ

Excel VBA
スポンサーリンク

ねらい：システムごとに違う「キー・コード」を、表で定義して一括変換する

同じ顧客・商品・部署でも、システムAとシステムBで「ID／コード」が違うことはよくあります。
手作業で「VLOOKUP」したり、毎回コピペで置換していると、いつか必ずミスが出ます。

キー変換テンプレは、

  • 変換表（マスタ）に「元コード→先コード」を一覧で持つ
  • データ側の列を、その表を見ながら一括変換する

という仕組みを“汎用VBA部品”にしたものです。
顧客ID・商品コード・部署コード・税区分・支払区分など、さまざまなキーに流用できます。

ここでは、

  • 変換表の作り方
  • 高速に変換するVBAテンプレ
  • 未定義コードの扱い（重要）
  • 例題（商品コードを外部システム用コードへ変換）

までを、初心者向けにかみ砕いて解説します。

共通基盤：配列I/Oと変換表（Dictionary）の基本ユーティリティ

配列で一気に読み書きするユーティリティ

' ModKey_Base.bas
Option Explicit

Public Function ReadRegion(ws As Worksheet, Optional topLeft As String = "A1") As Variant
    ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function

Public Sub WriteBlock(ws As Worksheet, a As Variant, startCell As String)
    ws.Range(startCell).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Public Sub FormatBlock(ws As Worksheet, startCell As String)
    With ws.Range(startCell).CurrentRegion
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
End Sub
VB

セルを1つずつ触らず、「表全体を配列として扱う」のがポイントです。
これだけでスピードと安定性が段違いになります。

変換表を辞書（Dictionary）に読み込む

Public Function BuildKeyMap(ByVal mapSheet As String, _
                            ByVal srcCol As Long, ByVal dstCol As Long, _
                            Optional ByVal normalize As Boolean = True) As Object
    Dim ws As Worksheet
    Set ws = Worksheets(mapSheet)
    
    Dim a As Variant
    a = ReadRegion(ws)
    
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = 1   ' 大文字小文字を無視
    
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim srcKey As String
        Dim dstKey As String
        
        srcKey = CStr(a(r, srcCol))
        dstKey = CStr(a(r, dstCol))
        
        If normalize Then
            srcKey = Trim$(srcKey)
            dstKey = Trim$(dstKey)
        End If
        
        If Len(srcKey) > 0 Then
            d(srcKey) = dstKey
        End If
    Next
    
    Set BuildKeyMap = d
End Function
VB

変換表シート（例：KeyMap）に、

  • A列: 元コード（SystemA）
  • B列: 変換後コード（SystemB）

のように並べておき、この関数で辞書に読み込みます。
Dictionary は「キー→値」の高速な対応表と思ってください。VLOOKUP の超高速版です。

キー変換の本体：元データの列を、変換表に従って一括で置き換える

単一列を変換するテンプレ（最小パターン）

' ModKey_Transform.bas
Option Explicit

Public Sub ConvertKeyColumn(ByVal dataSheet As String, _
                            ByVal keyCol As Long, _
                            ByVal mapSheet As String, _
                            ByVal mapSrcCol As Long, _
                            ByVal mapDstCol As Long, _
                            ByVal outStart As String, _
                            Optional ByVal markUnknown As Boolean = True)
    Dim wsData As Worksheet
    Set wsData = Worksheets(dataSheet)
    
    Dim data As Variant
    data = ReadRegion(wsData)   ' 元データ全体
    
    Dim map As Object
    Set map = BuildKeyMap(mapSheet, mapSrcCol, mapDstCol, True)
    
    Dim out() As Variant
    ReDim out(1 To UBound(data, 1), 1 To UBound(data, 2) + 1)
    
    Dim r As Long, c As Long
    For c = 1 To UBound(data, 2)
        out(1, c) = data(1, c)
    Next
    out(1, UBound(data, 2) + 1) = "ConvertedKey"
    
    For r = 2 To UBound(data, 1)
        For c = 1 To UBound(data, 2)
            out(r, c) = data(r, c)
        Next
        
        Dim srcKey As String
        srcKey = Trim$(CStr(data(r, keyCol)))
        
        Dim newKey As String
        If Len(srcKey) = 0 Then
            newKey = ""
        ElseIf map.Exists(srcKey) Then
            newKey = map(srcKey)
        Else
            If markUnknown Then
                newKey = "#UNKNOWN:" & srcKey
            Else
                newKey = srcKey   ' そのまま残す
            End If
        End If
        
        out(r, UBound(data, 2) + 1) = newKey
    Next
    
    WriteBlock wsData, out, outStart
    FormatBlock wsData, outStart
End Sub
VB

この Sub のイメージはこうです。

  • Data シートの「商品コード列」などを読み込む
  • KeyMap シートの「元→先コード表」を辞書に読み込む
  • 対象列の値を辞書で引いて、見つかったら変換
  • 新しい列「ConvertedKey」として右側に追加する

重要なのは、変換できなかったコードの扱いです。
markUnknown:=True のときは #UNKNOWN:元コード という形で残します。

これにより、

  • どのコードが未定義だったか
  • 変換表に何を追加すべきか

が一目で分かります。
「黙って空白にしてしまう」のは、一番危険なので避けます。

応用：複数列をまとめて変換するテンプレ

現場では「顧客ID」と「商品コード」と「部署コード」など、複数の列を一度に変換したいことが多いです。
そのための汎用テンプレも用意しておきます。

複数列のキー変換（列ごとに違う変換表もOK）

Public Sub ConvertMultipleKeys(ByVal dataSheet As String, ByVal outStart As String)
    Dim wsData As Worksheet
    Set wsData = Worksheets(dataSheet)
    
    Dim data As Variant
    data = ReadRegion(wsData)
    
    ' 例として：
    ' 商品コード → KeyMap_Product（A=元, B=先）
    ' 顧客ID     → KeyMap_Customer（A=元, B=先）
    ' 部署コード → KeyMap_Dept（A=元, B=先）
    
    Dim mapProd As Object
    Dim mapCust As Object
    Dim mapDept As Object
    
    Set mapProd = BuildKeyMap("KeyMap_Product", 1, 2, True)
    Set mapCust = BuildKeyMap("KeyMap_Customer", 1, 2, True)
    Set mapDept = BuildKeyMap("KeyMap_Dept", 1, 2, True)
    
    ' 列番号は実データに合わせて
    Dim colProd As Long: colProd = 3   ' 商品コード列
    Dim colCust As Long: colCust = 2   ' 顧客ID列
    Dim colDept As Long: colDept = 5   ' 部署コード列
    
    Dim out() As Variant
    Dim addCols As Long
    addCols = 3   ' 変換後列を3つ追加
    
    ReDim out(1 To UBound(data, 1), 1 To UBound(data, 2) + addCols)
    
    Dim r As Long, c As Long
    
    For c = 1 To UBound(data, 2)
        out(1, c) = data(1, c)
    Next
    out(1, UBound(data, 2) + 1) = "ProdKey_Conv"
    out(1, UBound(data, 2) + 2) = "CustKey_Conv"
    out(1, UBound(data, 2) + 3) = "DeptKey_Conv"
    
    For r = 2 To UBound(data, 1)
        For c = 1 To UBound(data, 2)
            out(r, c) = data(r, c)
        Next
        
        Dim srcProd As String
        Dim srcCust As String
        Dim srcDept As String
        
        srcProd = Trim$(CStr(data(r, colProd)))
        srcCust = Trim$(CStr(data(r, colCust)))
        srcDept = Trim$(CStr(data(r, colDept)))
        
        out(r, UBound(data, 2) + 1) = ConvertOneKey(srcProd, mapProd)
        out(r, UBound(data, 2) + 2) = ConvertOneKey(srcCust, mapCust)
        out(r, UBound(data, 2) + 3) = ConvertOneKey(srcDept, mapDept)
    Next
    
    WriteBlock wsData, out, outStart
    FormatBlock wsData, outStart
End Sub

Private Function ConvertOneKey(ByVal srcKey As String, ByVal map As Object) As String
    Dim k As String
    k = Trim$(srcKey)
    If Len(k) = 0 Then
        ConvertOneKey = ""
    ElseIf map.Exists(k) Then
        ConvertOneKey = map(k)
    Else
        ConvertOneKey = "#UNKNOWN:" & k
    End If
End Function
VB

この形式にしておくと、

  • 列番号と変換表シート名だけ変える
  • ConvertOneKey は共通で使う

という形で、他のキーにも流用しやすくなります。

例題：商品コードを別システム用コードへ一括変換する

ここまでを「具体的な例」でイメージしてみます。

Data シート：

  • A列 = 日付
  • B列 = 顧客コード（SystemA）
  • C列 = 商品コード（SystemA）
  • D列 = 数量
  • E列 = 金額

KeyMap_Product シート：

  • A列 = ProductA（SystemA の商品コード）
  • B列 = ProductB（SystemB の商品コード）

とします。

実行用のサンプルコード

' ModKey_Example.bas
Option Explicit

Public Sub Demo_ConvertProductCode()
    ' Data の C列（商品コード）を、KeyMap_Product の変換表で変換し、
    ' 右側に「ConvertedKey」列として出力するイメージ
    ConvertKeyColumn _
        dataSheet:="Data", _
        keyCol:=3, _
        mapSheet:="KeyMap_Product", _
        mapSrcCol:=1, _
        mapDstCol:=2, _
        outStart:="H1", _
        markUnknown:=True
        
    MsgBox "商品コードのキー変換が完了しました。", vbInformation
End Sub
VB

実行すると、Data シートの H1 から、

  • 元の表（A〜E列）
  • 新しい列「ConvertedKey」

が出力され、C列の商品コードが変換表に従って新コードに変わった結果が表示されます。
変換表に存在しないコードは「#UNKNOWN:元コード」として表示されるので、
KeyMap_Product に追記すべきコードが何か一目で分かります。

重要ポイントの深掘り：キー変換でやりがちな“事故”と防ぎ方

未定義コードを黙って空白にしない

一番危ないのが、「変換表に無いコードを空白にする」パターンです。
これをやると、

  • 元データに存在したレコード
  • 変換後に“存在しないように見えるレコード”

が混ざってしまい、後続の集計や取り込みで「どこに消えたのか」が分からなくなります。

ここではあえて「#UNKNOWN:元コード」として残し、

  • 未定義を見える化する
  • 後から変換表を補完する

という流れを強制しています。
この設計にしておけば、「変換漏れ」を見逃すことがほぼなくなります。

前後スペース・全角／半角の揺れでマッチしない問題

KeyMap 側にも Data 側にも、人間が入力した値が入っていると、ほぼ必ず

  • 前後の空白
  • 全角スペース
  • 全角英数
  • 大文字・小文字

の揺れが出ます。

BuildKeyMap で Trim をかけ、Dictionary の CompareMode を 1（TextCompare）にしているのは、

  • 少なくとも「前後スペース」と「大小文字」は吸収する

ためです。
さらに厳密にやりたい場合は、

  • NormText を “コード”にも適用して
  • 変換表にも同じルールで揺れを潰しておく

と、よりマッチ率が上がります。

変換方向を間違えない

変換表は、

  • A列：SystemA
  • B列：SystemB

とした場合、
A→B に変換するのか、B→A に変換するのかを間違えると、
一見“それっぽい”コードに変わるので、余計に気づきにくくなります。

BuildKeyMap に渡す srcCol/dstCol が「どちら向きの変換なのか」を仕様書に書いておき、
変える時はコードとセットで直すと安全です。

列番号でなく「列名」で指定する拡張もあり

今回のテンプレは分かりやすさを優先して keyCol を「列番号」で指定していますが、
実務で壊れにくくするには、「列名」で指定する形にするとさらに強くなります。

例えば ColumnIndex 関数を使って、

  • ヘッダ行から「商品コード」という文字列を探す
  • 見つかった列番号を keyCol として使う

のように書き換えると、「列の順番を入れ替えた」程度では壊れなくなります。

まとめ：キー変換は「辞書に載せる→配列で一括置換→未定義を見える化」が鉄板の型

システム間でコードが違う限り、キー変換は必ず何度も出てきます。
そのたびに VLOOKUP や手作業で置換するのではなく、

  • KeyMap シートに「元→先」を表で持つ
  • BuildKeyMap で辞書にする
  • ConvertKeyColumn（または ConvertMultipleKeys）で一括変換する
  • 未定義コードは #UNKNOWN で残して、表側を補完する

という“型”にしておくと、運用が一気に楽になります。

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