EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字

2020年3月6日

EXCEL VBA テクニック　名字の旧字・新字の検索・置き換えの方法。

●はじめに

ここでは、文字の置き換え方法の説明をします。会社で働いていると、EXCELデータを人事システム等にデータを転送する際に、社員名に旧字があると転送エラーが発生する事があります、旧字の場合システムによりますが、文字として受け入れられないない場合が時々発生します。元データ（EXCEL）は、旧字で名字を管理しておき、旧字を受け入れないシステムでは、旧字をVBAプログラムで検索して新字に置き換える仕組みを作らなければなりません。ここでは、その旧字から新字へ置き換えを実行するプログラムの説明をいたします。

●EXCEL VBAプログラムの検索・置換説明①

プログラムの説明ですが、A列に名字の一覧が表示されています。この名字の一覧の中に、一部旧字が使われています。E・F列には、旧字・新字の対応表があります。この旧字・新字の対応表を使って名字の置き換えを行います。

●実行前　※下表の様にA列に旧字の名字一覧を作成します。E列・F列に新旧字の対照表を作成します。（クリックして拡大）

●プログラム説明

下記のプログラムの説明ですが、A列の名字データに対して、旧字があればE列の旧字に対してF列の新字に置換を行います。A列に登録されている名字全てが対象として実行されます。

Sub 検索置換01()
        
    Dim ws01 As Worksheet
    Dim KANJI, L As Long
    
    
    Set ws01 = Worksheets("Sheet1") 'ワークシートの設定 ※注意　sheet1に作成
    
    KANJI = ws01.Cells(Rows.Count, "E").End(xlUp).Row        'E列の最終行を設定する
    
    For L = 2 To KANJI '　E列の最終行までループ(旧字・新字データ）
    
        ws01.Columns("A").Replace what:=ws01.Cells(L, "E"), replacement:=ws01.Cells(L, "F"), lookat:=xlPart
        'A列の名字に旧字があれば新字に置換します。
    Next L
    
    
End Sub

Sub 検索置換01()

Dim ws01 As Worksheet

Dim KANJI, L As Long

Set ws01 = Worksheets("Sheet1") 'ワークシートの設定 ※注意　sheet1に作成

KANJI = ws01.Cells(Rows.Count, "E").End(xlUp).Row 'E列の最終行を設定する

For L = 2 To KANJI '　E列の最終行までループ(旧字・新字データ）

ws01.Columns("A").Replace what:=ws01.Cells(L, "E"), replacement:=ws01.Cells(L, "F"), lookat:=xlPart

'A列の名字に旧字があれば新字に置換します。

Next L

End Sub

●実行結果

実行後、実行前と実行後を見ていただければ、名字の旧字が新字に置換されています。上記のプログラムは、E列の旧字が増えても対応できますので、色々試して下さい。

●実行前　※A列の名字には、旧字が含んでいる名字の一覧があります。（クリックして拡大）

●実行後　※A列の名字が旧字⇒新字に変換されました。（クリックして拡大）

EXCEL VBAプログラムの検索・置換説明②

●プログラム説明

続きまして、上記プログラム①の応用プログラムになります。具体的なプログラムの説明ですが、①同様にA列に名字の一覧が表示されています。このA列の名字を見て、新字がB列に置き換わります。旧字から新字へ置換された文字のセル背景色に黄色で塗りつぶしされる仕組みになっています。色で、どの名字が置換されたか直ぐに分かる様に、一部追加したプログラムになります。

●実行前　※（クリックして拡大）

Sub 検索置換02()
    
    Dim ws01 As Worksheet
    Dim KANJI, MYOJI, I, L As Long
    
    Set ws01 = Worksheets("Sheet1") 'ワークシートの設定
    
    MYOJI = ws01.Cells(Rows.Count, "A").End(xlUp).Row        'A列の最終行を設定する
    KANJI = ws01.Cells(Rows.Count, "E").End(xlUp).Row        'E列の最終行を設定する
    
    For I = 2 To MYOJI
    
        ws01.Cells(I, "B") = ws01.Cells(I, "A") '名字をA列からB列に複写（A列に元名字を残すため）
    
        For L = 2 To KANJI '　E列の最終行までループ(旧字・新字データ）
        
            ws01.Cells(I, "B").Replace what:=ws01.Cells(L, "E"), replacement:=ws01.Cells(L, "F"), lookat:=xlPart
            'A列の名字を旧字があれば新字に置換します。
        Next L
        
        If ws01.Cells(I, "A") <> ws01.Cells(I, "B") Then  '旧字を新字に変えた場合、色を塗りつぶす
                ws01.Range("B" & I).Interior.ColorIndex = 6
        End If
        
    Next I
    
End Sub

Sub 検索置換02()

Dim ws01 As Worksheet

Dim KANJI, MYOJI, I, L As Long

Set ws01 = Worksheets("Sheet1") 'ワークシートの設定

MYOJI = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を設定する

KANJI = ws01.Cells(Rows.Count, "E").End(xlUp).Row 'E列の最終行を設定する

For I = 2 To MYOJI

ws01.Cells(I, "B") = ws01.Cells(I, "A") '名字をA列からB列に複写（A列に元名字を残すため）

For L = 2 To KANJI '　E列の最終行までループ(旧字・新字データ）

ws01.Cells(I, "B").Replace what:=ws01.Cells(L, "E"), replacement:=ws01.Cells(L, "F"), lookat:=xlPart

'A列の名字を旧字があれば新字に置換します。

Next L

If ws01.Cells(I, "A") <> ws01.Cells(I, "B") Then '旧字を新字に変えた場合、色を塗りつぶす

ws01.Range("B" & I).Interior.ColorIndex = 6

End If

Next I

End Sub

●実行後　※（クリックして拡大）

●実行結果

実行後、A列に旧字が登録されている名字については、新字に置換されて、B列に置換されたと分かる様に、黄色でセルが塗りつぶしされています。

最後まで、ご覧いただきまして誠に有難うございました。
置き換え作業は、名字以外でも例えば、住所名が一斉に変わった場合（政令指定都市）などには、この置き換え（プログラム）を利用すれは、そのまま利用する事ができます。（若干修正必要かも？）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

VBAプログラム（テクニック）EXCEL, VBA