VBAで住所から都道府県名を抽出する方法

日本地図
この記事は約6分で読めます。

例えば顧客情報の住所データから「都道府県別」のデータを集計したいなど、住所から「都道府県名」だけを抽出したいケースがあると思います。

今回はVBAを使って住所から都道府県名だけを抽出する方法を紹介します。

都道府県名だけを抽出する方法

今回はA列に書かれた住所からB列に都道府県名、C列にそれ以降の住所に分けて記述する事例として紹介します。

住所データ見本

都道府県名の多くは先頭から2文字+「都,道,府,県」ですが、神奈川県和歌山県鹿児島県の3県だけが先頭から3文字 + 「県」となっています。

つまり先頭から3文字を抽出して条件分岐すればよいです。

先頭から指定の文字数を抽出するにはLeft関数を用います。

Left(string, length)

引数stringには元となる文字列、lengthには返す文字数を指定します。

A列のi行目の住所を元にB列のi行目に都道府県名を記述するには次のようにします。

Select Case Left(Cells(i, 1).Value, 3)
 Case “神奈川”, “和歌山”, “鹿児島”
  Cells(i, 2).Value = Left(Cells(i, 1), 4)
 Case Else
  Cells(i, 2).Value = Left(Cells(i, 1), 3)
End Select

都道府県名の後の住所をC列に記述する場合はMid関数を用います。

Mid(string, start, [length])

引数string は元になる文字列、start には取り出す文字列の先頭の位置を指定します。

引数length は取り出す文字数を指定しますが、省略した場合は最後までのすべての文字が返されます。

1行目が見出し行として2行目以降から最終行までを処理するマクロは次のようになります。

Sub sample1()
    Dim endRow As Long
    Dim i As Long
    endRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To endRow
        Select Case Left(Cells(i, 1).Value, 3)
            Case "神奈川", "和歌山", "鹿児島"
                Cells(i, 2).Value = Left(Cells(i, 1).Value, 4)
                Cells(i, 3).Value = Mid(Cells(i, 1).Value, 5)
            Case Else
                Cells(i, 2).Value = Left(Cells(i, 1).Value, 3)
                Cells(i, 3).Value = Mid(Cells(i, 1).Value, 4)
        End Select
    Next i
End Sub

2019年7月現在では都道府県名に続いて「県」の文字がくる住所はないので条件分岐を
If Mid(Cells(i, 1).Value, 4, 1) <> “県” Then
とすることも可能です。

データ数が多い場合のマクロの高速化

前述のマクロでは1行ずつセルのRangeオブジェクトを操作しているためデータ数が多くなると処理速度が低下してしまいます。

ちなみに10万件のデータで処理速度を測定した結果、約10.2秒かかりました。

処理速度は環境に依存するため測定結果の絶対値ではなく、あくまで他の方法との比較値として参考にしてください

画面更新、計算の一時停止

マクロの処理速度を改善する方法としてまず思いつくのがこれらの方法だと思います。

Application.ScreenUpdating = False

で画面更新を停止し、

Application.Calculation = xlCalculationManual

で計算を手動にすることでExcelの処理速度が改善できます。

これらはそれぞれ

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

で元に戻すことを忘れないでください。

この方法で測定すると処理速度は約7.63秒となりましたが、改善効果としては十分ではありません。

やはり一行ずつRangeオブジェクトを操作していることを見直す必要があります。

そこでセルの値を配列変数として格納し、配列変数を操作してから最後にまとめてRangeオブジェクトに書き出す方法があります。

配列に置き換えた処理

配列変数を用いてマクロを書き換えてみます。

Sub sample2()
    Dim endRow As Long
    Dim adAry() As Variant
    Dim adAry1() As Variant
    Dim i As Long
    endRow = Cells(Rows.Count, 1).End(xlUp).Row
    '//A列の住所データを配列変数 adAry に格納
    adAry = Range(Cells(2, 1), Cells(endRow, 1)).Value
    '//B,C列の住所データを格納する配列変数adAry1の要素数を変更
    ReDim adAry1(1 To endRow - 1, 1 To 2)
    '//B,C列のデータを配列変数adAry1に格納
    For i = 1 To endRow - 1
        Select Case Left(adAry(i, 1), 3)
            Case "神奈川", "和歌山", "鹿児島"
                adAry1(i, 1) = Left(adAry(i, 1), 4)
                adAry1(i, 2) = Mid(adAry(i, 1), 5)
            Case Else
                adAry1(i, 1) = Left(adAry(i, 1), 3)
                adAry1(i, 2) = Mid(adAry(i, 1), 4)
        End Select
    Next i
    '//B,C列のセルに配列変数 adAry1 の値を書き出し
    Range(Cells(2, 2), Cells(endRow, 3)).Value = adAry1
End Sub

元の住所データであるA列の値を配列変数 addAryに格納し、都道府県名のみのB列とその後の住所のC列のデータを配列変数addAry1に格納していきます。

最後にB、C列に配列変数addAry1をまとめて書き出します。

このマクロを実行すると処理速度は約0.85秒まで改善されました。

なおここではRangeオブジェクトを操作するのが最後に書き出す1回だけなので先ほどの画面更新および計算の一時停止をする必要はありません。

実際にそれらを追加したコードでも処理速度に変化はありません

まとめ

今回は住所データから都道府県名を抽出する方法を紹介しました。

神奈川県、和歌山県、鹿児島県は先頭から4文字を、それ以外は先頭から3文字を抽出することで実現できます。

取り扱うデータ数が多いときには配列を用いて処理速度を向上させるようにしましょう。

コメント

タイトルとURLをコピーしました