例文を使って繰り返し業務を時短

【マクロ・VBA】都道府県でセルを分割する

エクセル、マクロ、VBAの住所の都道府県を抽出する方法 VBA

都道府県でわける

これは、都道府県を含む住所データを都道府県と市区町村に分けて、それぞれ隣接するセルに書き込むエクセルマクロです。

Sub 都道府県抽出()
    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
MsgBox "完了"
End Sub

解説

  • まず、最初にエクセルの最後の行を取得するために、endRow変数を宣言し、Cells(Rows.Count, 1).End(xlUp).Rowで計算しています。これは、エクセルの最後の行を取得するための一般的な方法です。
  • 次に、Forループを使用して、2行目から最後の行までの各行の住所データを処理します。Select Case文を使用して、住所データの先頭3文字が特定の文字列である場合には、都道府県名の4文字目以降を市区町村名として、都道府県名を2列目に書き込みます。それ以外の場合には、都道府県名の3文字目以降を市区町村名として、都道府県名を2列目に書き込みます。
  • 最後に、MsgBox関数を使用して、処理が完了したことを示します。
  • このマクロは、ループ処理を使用して住所データを処理しているため、比較的大きなデータセットに対しても効率的に動作することができます。ただし、選択範囲に限定されていないため、誤って他のセルを上書きする可能性があることに注意してください。

都道府県でわける（パート２）

これは、選択した列のセルの文字列を都道府県と市区町村に分割して、その結果を隣接する2つの列に表示するエクセルマクロです。

Sub 都道府県抽出2()
    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
MsgBox "完了"
End Sub

解説

  • まず、変数の宣言が行われます。endRowはデータが入っている最終行を示す変数です。adAryは住所データを格納する配列変数で、adAry1は抽出した都道府県名を格納する配列変数です。そして、iはループ処理に使用する変数です。
  • 次に、adAryに住所データを格納します。Range(Cells(2, 1), Cells(endRow, 1)).Valueで、A列の2行目から最終行までの範囲を指定し、その範囲内の値をadAryに代入しています。
  • その後、adAry1の要素数を変更します。ReDim adAry1(1 To endRow - 1, 1 To 2)で、adAry1の要素数を2列のデータがある行数と同じに設定しています。
  • その後、Forループで、adAryの各要素の先頭3文字（都道府県名）を調べ、Case文で条件分岐しています。3文字が「神奈川」「和歌山」「鹿児島」の場合は、都道府県名が4文字になっているので、先頭4文字を抽出してadAry1(i, 1)に代入し、残りの文字列をadAry1(i, 2)に代入しています。それ以外の場合は、先頭3文字をadAry1(i, 1)に、残りの文字列をadAry1(i, 2)に代入しています。
  • 最後に、Range(Cells(2, 2), Cells(endRow, 3)).Value = adAry1で、B列とC列のセルに配列変数adAry1の値を書き出しています。
  • 最後に、MsgBoxで「完了」というメッセージを表示しています。

都道府県で分ける（パート3）

これは、選択した列のセルの文字列を都道府県と市区町村に分割して、その結果を隣接する2つの列に表示するエクセルマクロです。

Sub 都道府県_市区町村抽出()
    If TypeName(Selection) <> "Range" Then Exit Sub 'セルを選択していなければ終了
    If Selection.Areas(1).Columns.Count <> 1 Then Exit Sub '選択セル範囲が2列以上だと終了

    Dim varArray As Variant, varArea As Variant, rngArea As Range
    Dim i As Long, n As Long, v As Variant
    
    varArray = Array("府", "県", "都", "道") '区切り文字列
    
    Set rngArea = Intersect(Selection.Areas(1), ActiveSheet.UsedRange).Resize(, 3)
    varArea = rngArea
    For i = 1 To UBound(varArea)
        For Each v In varArray
            n = InStr(1, CStr(varArea(i, 1)), CStr(v)) '区切り文字列の位置を検索
            If 0 < n Then
                varArea(i, 2) = Left$(CStr(varArea(i, 1)), n)
                varArea(i, 3) = Mid$(CStr(varArea(i, 1)), n + 1)
                Exit For
            End If
        Next
    Next
    rngArea = varArea
    Set rngArea = Nothing
MsgBox "完了"
End Sub

解説

  • varArray = Array("府", "県", "都", "道")：区切り文字列を配列に格納します。
  • Set rngArea = Intersect(Selection.Areas(1), ActiveSheet.UsedRange).Resize(, 3)：選択したセル範囲とシート内の使用されているセル範囲を重ね合わせて、その範囲を3列に拡張した範囲を取得します。これにより、後で抽出された都道府県と市区町村のデータを書き込むための余分な列が確保されます。
  • varArea = rngArea：取得した範囲を配列変数に格納します。
  • For i = 1 To UBound(varArea)：配列変数の要素数分ループを回します。
  • For Each v In varArray：区切り文字列の配列に含まれる文字列分ループを回します。
  • n = InStr(1, CStr(varArea(i, 1)), CStr(v))：区切り文字列を検索し、位置を取得します。
  • If 0 < n Then：区切り文字列が見つかった場合に以下の処理を実行します。
  • varArea(i, 2) = Left$(CStr(varArea(i, 1)), n)：区切り文字列より左側の文字列を取得して、配列変数の2列目に格納します。
  • varArea(i, 3) = Mid$(CStr(varArea(i, 1)), n + 1)：区切り文字列より右側の文字列を取得して、配列変数の3列目に格納します。
  • Exit For：最初に見つかった区切り文字列のみを処理するため、ループを終了します。
  • rngArea = varArea：変更した配列変数を元の範囲に書き戻します。
  • Set rngArea = Nothing：範囲オブジェクトを解放します。
  • MsgBox "完了"：処理が完了したことを示すメッセージボックスを表示します。

コメント

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