【Excel関数活用術】住所を都道府県と市町村名で分割する方法

Office365
スポンサーリンク
マネ妻
マネ妻

Excel関数を使い倒そう!
よくある実例を元に関数の使い方を紹介します。

住所から都道府県名を切り分ける

名前はテストデータ・ジェネレータを使用し、架空の個人情報を生成

上の表は、住所録でよくあるデータです。
「住所録から都道府県だけを取り出して。」と頼まれることありませんか?
このくらいの量なら手作業でも可能ですが、何千件何万件なんてあったら手作業では無理!
関数を使って、都道府県名を取り出してみましょう。

数式解説

都道府県名の共通ルールを見つけ、1つずつ数式を考えてみます。

【都道府県名の共通ルール】
 ①「神奈川県」「和歌山県」「鹿児島県」の3県だけが4文字
 ②その他の都道府県は3文字

都道府県名は3文字か4文字で構成されています。
よって、住所の4文字目に注目して関数を組み立てます。

 Step1:住所の左から4文字目が 『県』 かどうか
 Step2:『県』 の場合は、左から4文字を取り出す
 Step3:そうでない場合は、左から3文字を取り出す

数式にすると、次のようになります。

=IF(MID(E2,4,1)=”県”,LEFT(E2,4),LEFT(E2,3))

【MID関数】
=MID(文字列,開始位置,文字数)
[文字列]を[開始位置]から[文字数]分を取り出します。[文字数]が[文字列]より長い場合は、文字列の末尾まで取り出します。文字数は半角/全角を区別せず、1文字と数えます。

【LEFT関数】
=LEFT(文字列,文字数)
[文字列]の先頭から[文字数]を取り出します。[文字数]を省略すると、最初の1文字を取り出します。文字数は半角/全角を区別せず、1文字と数えます。

【IF関数】
=IF(論理式,真の場合,偽の場合)
[論理式]に指定する条件が成立する場合は、[真の場合]、成立しない場合は、[偽の場合]の処理をします。

住所から都道府県名より後ろの住所を取り出す

住所から都道府県名を切り分けたら、市町村名から始まる後ろの住所を取り出してみましょう。

数式解説

『住所から都道府県名を切り分ける』で使用したF列「都道府県名」を活用します。

[住所]から[都道府県名]を取り除くと考えて関数を組み立てます。

 Step1:『住所』から『都道府県』を検索する
 Step2:『都道府県』を空白に置き換える

数式にすると、次のようになります。

=SUBSTITUTE(E2,F2,””)

【SUBSTITUTE関数】
=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
[文字列]の中で[検索文字列]を[置換文字列]に置き換えます。
[置換対象]は、[検索文字列]が複数見つかった場合に、何番目の[検索文字列]を置き換えるかを指定します。省略するとすべての[検索文字列]を[置換文字列]に置き換えます。

住所から市町村名を取り出す

「住所録を【都道府県】【市町村区】【番地】に分けて欲しい。」と更に住所を分割したい時もExcel関数を使って解決することができます。

数式解説

市町村名の共通ルールを書き出してみます。

【市町村名の共通ルール】

 ①都道府県に続く住所の区分は、「郡」「町」「村」「市」「区」の5つ。
 住所区分は2文字目以降から現れる。

 ②「郡」には、「○○郡○○町」「○○郡○○町」のように、「町」と「村」が後ろに続く。

 ③「区」には、「東京都○○区」の特別区と、「横浜市○○区」のような政令指定都市に続く「区」の2パターンある。

 ④「東京都大島町」のように、「郡」がなかったり、「奈良県大和郡山市」のように、「市」名の一部に「郡」が入ったり、例外も存在する。

ちょっと複雑ですが、順を追って関数を組み立てていきましょう。

Step1:住所区分が含まれる文字の位置をFIND関数で2文字目以降を調べる。
 =IFERROR(FIND(住所区分文字,住所,2),エラーの場合は0)

Step2:「郡」が見つかったら、「町」もしくは「村」の文字位置まで取り出す。
「町」「村」の文字位置は、「郡」よりも大きい(後ろに位置する)。
=LEFT(住所,MAX(郡町村))

Step3:「区」が見つかったら、「区」の文字位置まで取り出す。
「区」の位置位置は、「市」よりも大きい(後ろに位置する)。
=LEFT(住所,MAX(市区))

Step4:例外対応として、「郡」の文字位置チェックを入れる。
「郡」の位置が「市」「区」より小さい(前に位置する)かどうか。
=郡<MAX(市区)

式にすると、次のようになります。

Step1:
=IFERROR(FIND($H$1,$G2,2),0)

Step2~4:
=IF(H2<MAX(K2:L2),LEFT(G2,MAX(K2:L2)),LEFT(G2,MAX(H2:J2)))

番地を表示したい場合は、『住所から都道府県名より後ろの住所を取り出す』で使用した、SUBSTITUTE関数を使って表示することができます。

【FIND関数】
=FIND(検索文字列,対象,開始位置)
[検索文字列]が[対象]に指定した文字列内で何文字目にあたるかを検索します。
[開始位置]は検索する[対象]の文字開始位置を指定します。

【IFERROR関数】
=IFERROR(値,エラーの場合の値)
[値]がエラーの場合は、[エラーの場合の値]を表示します。

【MAX関数】
=MAX(数値1,数値2,…)
指定した数値の最大値を求めます。

コメント

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