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

Excel

スポンサードリンク




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

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

Excelでできること

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

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

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

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

 

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

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

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

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

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

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

Left関数で

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

 

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

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

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

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

 

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

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

今回使う関数
  1. FIND関数
  2. 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超入門 マクロを書いてみたい人集合! 仕事ときどきアウトプットがおすすめ

2018.04.25

 

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

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

よく見ると似ている?
FIND(”県”,D2)をマクロで書くとInstr(D2,”県”)となりますから、実は順番が違うだけだったりします。
さらにE2だけでなく、この処理をE列すべてに適用したいわけです。

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

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

 

For i
i = 2 to 201

next

 

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

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

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

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

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

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

マクロは確かに敷居が高く感じるかもしれませんが、できることから一つずつやってみると、できることも意外にあるものです。

このマクロを書くのは、何がいいか?

やっていて感じるのは、どう書いていけばいいかを自分で考えて組み立てるところ。

一気に書けませんし、もし、一気に書いてもエラーがあった場合には、どこが違っているのかがわからなくなります。

1つずつF5やF8をクリックして、動作確認をしながら書くようにしています。

Excel関数を使ったほうが早くできることでも、覚えるためにあえてマクロで書いてみるというのはおすすめです。

動画でもUPしてみました。


【編集後記】
昨日はお客様訪問。お客様の報告会に出席させていただきました。その後はランチをご一緒し、楽しめました。外出から戻ってきてからジムに行ったり、ブログなど。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
お客様とランチ
プロテイン試飲


スポンサードリンク