Excel VBA超入門　住所から県名を取り出すには？　できそうなことからやってみるのがいい

2018年5月30日2018年9月14日

URLをコピーしました！

「Excel、住所」という検索をしていただいていたようでした。

そこでExcelで住所のデータから「県」だけを抜くにはどうすればいいかを考えてみました。

Excelでできること

Excelはスキルアップがしやすいツールだと考えています。

そのExcelで何ができるのかをいろいろ研究しています。

特にひとり仕事になると、PCを使っての効率化は欠かせないものです。

今回、顧客データでもよくある、住所のデータから「県」の部分だけを抽出するということをやってみました。

最初は通常のExcel関数で操作した場合、それとExcel VBAでやってみた場合と比較しながら。

マクロを書くとなると、ハードルも上がりますが、関数などは似ていると感じる部分もあります。

まずはできるものから1つずつやってみるのがおすすめで、私もそうしています。

県だけ関数でデータ抽出するなら？

住所のデータから「県」だけを分割するにはどうすればいいか？

単純に「愛知県」、「三重県」、「静岡県」という3文字の県ならLeft関数を使えば、県名だけを抽出することができます。

Left関数で

=LEFT(D2,3)　→ 　D2セルの文字、左から3つを抽出する　→　愛知県

ところが県名は必ずしも3文字とは限りません。

「神奈川県」だってあるし、「和歌山県」もあります。

特にデータが200とか、たくさんある場合に、それぞれの県を見ながら式を変えるというのは避けたいものです。

補足
県ではない、北海道とか東京都とかもあるのですが、複雑になるのでここでは省略します。

できるだけカンタンにやろうと思ったらどうすればいいか？

県の部分を抽出するのに、ここでは次の2つの関数を使います。

今回使う関数

FIND関数
LEFT関数

❶　FIND関数を使って「県」が何文字目にあるかを探す

FIND関数を使うと便利です。

まずはE列を挿入しておき、そこにFIND関数を入力します。

何番目？

=FIND(”県”,D2)　→　D2セルのうち、「県」は何文字目にある？

文字列「県」は指定のセルの文字列の何番目に表示あるか？を教えてくれる関数です。

この場合は左から3番目なので、「3」が表示されます。

❷　LEFT関数で「県」までのデータを抽出

これに先ほどのLEFT関数を合わせると、県を抽出することができます。

LEFT関数＋FIND関数

=LEFT(D2,E2)

E列は❶でFIND関数を使って出した数字、つまりE列で県が何番目にあるかを探し、F列のLEFT関数で1文字目からE列指定の文字までを抽出するということになります。

❸　「市町村」を表示させるならMID関数で

ちなみに「県」よりあとを抽出するならMID関数を使います。

MID関数

=MID(D2,E2+1,100)

「E2+1」というのは、県の次の文字からということを意味しています。

例えば、E2セルで「県」は3文字目ということがわかったので、「市町村」の情報は3＋1＝4文字目からということです。

関数の最後の「100」というのは、D2セルの4文字目から100文字を抽出するということを意味しています。

100自体に意味はありません。抽出もれがないようにあえて100と。さすがに100文字ってことはないので。

マクロを書いて見ると実は似ているところもある

「Excel VBA」、「マクロ」というと、それだけで敷居が高くなりがちです。

意外にも似ているところもあります。

基本的な設定については、こちらの記事を見ていただければ。

あわせて読みたい

Excel VBA超入門　マクロを書いてみたい人集合！　仕事ときどきアウトプットがおすすめ ExcelなどPCのスキルはアウトプットがしやすいです。 Excelで効率化するなら、マクロの記述は必要だと感じ練習しています。自分なりにポイントまとめてみました...

前述のことをマクロでやろうとした場合、マクロにも同様にLEFT（）があります。その使い方も同じです。

FIND関数の部分に関しては、マクロでは、今回、「Instr」という関数を使って表現してみます。

よく見ると似ている？

FIND（”県”,D2）をマクロで書くとInstr（D2,”県”）となりますから、実は順番が違うだけだったりします。

Sub sample()

     '変数定義
      Dim ken

     'D2セルのうち「県」は何番目にあるかを回答
      ken = InStr(Range("D2").Value, "県")

     'E2に「〇〇県」を抽出
      Range("E2").Value = Left(Range("D2").Value, ken)

End Sub

Sub sample()

'変数定義

Dim ken

'D2セルのうち「県」は何番目にあるかを回答

ken = InStr(Range("D2").Value, "県")

'E2に「〇〇県」を抽出

Range("E2").Value = Left(Range("D2").Value, ken)

End Sub

さらにE2だけでなく、この処理をE列すべてに適用したいわけです。

その場合、For 〜 NEXT構文というものを使います。

例えば、今回はデータが200個あり、2行目から201行目にデータがあるので、

For i
i = 2 to 201

とすることで、「変数(2〜201）の範囲で同じ処理を繰り返して」という意味になります。

ちなみに、[i]は変数といい、方程式でいうところの[X]のようなもの。

結果、これを反映させると、こうなります。

D2とかE2としていたところを、「”D”＆i」や「”E”＆i」に変えるわけです。

この「i」には前述の2〜201が入るわけです。（上の方で変数があるよと宣言をしています。）

Sub sample()

        '変数定義
        Dim ken

         Dim i

          For i = 2 To 201

            '「県」はD列の何文字目にあるかを回答
            ken = InStr(Range("D" & i).Value, "県")

            'E列にD○セルより「〇〇県」を抽出して表示する
            Range("E" & i).Value = Left(Range("D" & i).Value, ken)

          Next

End Sub

Sub sample()

'変数定義

Dim ken

Dim i

For i = 2 To 201

'「県」はD列の何文字目にあるかを回答

ken = InStr(Range("D" & i).Value, "県")

'E列にD○セルより「〇〇県」を抽出して表示する

Range("E" & i).Value = Left(Range("D" & i).Value, ken)

End Sub

さらに市町村まで反映させると最終的にこうなります。

Sub sample()

        '変数定義
        Dim ken

        Dim shi

         Dim i

          For i = 2 To 201

            '「県」は何番目にあるかを回答
            ken = InStr(Range("D" & i).Value, "県")

            '市町村を表示するために変数「ken+1」番目を回答
            shi = InStr(Range("D" & i).Value, ken + 1)

            'E列にD列より「〇〇県」を抽出して表示する
            Range("E" & i).Value = Left(Range("D" & i).Value, ken)

            'F列にD列より「〇〇市〇〇」を抽出して表示する
            Range("F" & i).Value = Mid(Range("D" & i).Value, ken + 1)

         Next

End Sub