住所の変換。都道府県の抜き出しや番地以降の抜き出しなど分かりやすい方法を考える
自動化の仕事だけじゃなく、顧客リストや社員リストを作ってると、住所を色々変換しないといけないことって出て来ますよね？
ググったら色々方法は出てくるんですが、なんやら超長い数式で一撃で行っているものが多いので、初心者の皆さんには、テクハラに感じることも少なく無いんじゃないかと思いますｗ
ということで、今回は極力テクハラにならないように、そして分かりやすい方法を目標に考えたいと思います！
※それでも、ややこしいところはありますので、先に謝っときます。すみません。
とりあえず先に、最終的な形です。
A列に元の住所がありますので、これを変換して、B列～F列のように変換します。
- B列…都道府県だけ
- C列…町より前の部分
- D列県～丁まで
- E列…市区町村から丁まで
- F列…番地以降
という感じですね。
あ、僕は一撃の数式に命かけていませんので、B列C列などで出した答えも使ったりします。
実際の場合は、”作業列”的な感じで列を作って処理します。
絶対に作業列があってはいけない場合ってほとんどないですし、その方が分りやすくて早く出来ることが多いです！
それでは順番にやっていきましょう！
都道府県だけを抜き出す
まずは都道府県だけを抜き出します。B列に数式を入れます。
ネットで検索してよく出てくるのは、神奈川県など３文字件名の処理ですね。４文字目が県だったら県だったら最初から４文字取り出す。
それ以外は３文字取り出す。
東京都・大阪府・京都府・北海道が奇跡的にすべて３文字なのでそれでもOKです。
=IF( MID(A2 ,4, 1)=”県”, LEFT(A2, 4), LEFT(A2, 3))
こんな感じですね。
ちゃんと取り出せます。
使っているのは次の3つの関数です。
文字列に対して、指定した位置から指定した文字数の文字列を返してくれる関数
= MID( 文字列, 開始位置, 文字数 )
文字列 （必須） 取り出したい文字を含む文字列を指定
開始位置 （必須） 文字列から取り出す先頭文字の位置を数値で指定。 ※文字列の先頭文字の位置が 1
文字数 （必須） 取り出す文字数を指定
条件式が真か偽かで、その後の処理を分けられる関数
= IF( 条件式, 真の場合, 偽の場合 )
これはそんなに説明の必要はないですね(^o^)
文字列の左から指定の文字数抜き出す関数
= LEFT( 文字列, 抜き出したい文字数 )
IF関数の条件式に
MID(A2 ,4, 1)=”県”
が入っています。
「A2セル（神奈川県平塚市●●0-1-2）の”4″番目から”1″文字が”県”です。」
なので、条件式は真（True）になりますね。
真の場合は、
LEFT(A2, 4)
なので、
「A2セル（神奈川県平塚市●●0-1-2）の左から”4″文字抜き出す」
が実行されます。
なので、”神奈川県”が抜き出されますね。
3行目（B3セル）に数式をコピペすると、数式のA2→A3になりますので、対象は
”東京都練馬区●●町0-1-2 △△ビル”
これは、4文字目が”県”になっていないので、IF関数の偽（False）の方の数式
LEFT(A2, 3)
つまり、A3の”左から3文字を抜き出す”が実行されます。
「A3セル（東京都練馬区●●町0-1-2 △△ビル）の左から”3″文字抜き出す」
なので、”東京都”が抜き出されますね。
これで全然OKです。
ひらちん的別案
ただ、もしかすると” 東京都・大阪府・京都府・北海道が奇跡的にすべて３文字 ”っていうのが、数式からは分かりにくいので、＝県で判定してるけど、なんで府とか道とかが抜き出せるんだろ？
みたいに思う方も居るかも知れないです。
ということで、見たらぱっと分かる数式を考えてみました！
これです！
= IF(COUNTIF(A2, “東京都*”)>0,”東京都”,
IF(COUNTIF(A2, “大阪府*“)>0,”大阪府”,
IF(COUNTIF(A2, “京都府*“)>0,”京都府”,
IF(COUNTIF(A2, “北海道*“)>0,”北海道”,
MID(A2, 1, FIND(“県”, A2 ))))))
使っている関数は、IF関数とMID関数に加えて
指定した範囲の中で、指定の文字列が入っているセルが何個あるか表示してくれる関数
= COUNTIF( 数える範囲, 数える文字列 )
数える範囲…数えたいセルの範囲を指定します。
数える文字列…ここに指定した文字列が入っているセルの個数を数えます。
指定された文字列が、対象の文字列の左から何文字目にあるのか表示してくれる関数
= FIND( 検索文字列, 対象 )
検索文字列…ども文字列を調べたいか
対象…調べたいセル
の3つを使っています。
日本語で言うと、
ということになります。
東京か大阪か京都か北海道。県じゃないのは、これだけなので明示的に調べて、そうじゃなければ”県”という文字が出てくるところまで抜き出す。
という、考え方としてはシンプルで、後から見ても何をやっているのか分かりやすい数式になっています。
どうでしょう？？
ちなみに、「 ”東京都”から始まる文字列だったなら 」
というのは、
COUNTIF(A2, “東京都*”)>0
IF関数の中の条件式にCOUNTIF関数で作っています。
COUNTIF関数の調べるセル範囲を、一つの範囲で指定しているので、あれば１なければ０ですね。
”東京都”の後についている「*」アスタリスクは、ワイルドカード文字といって、何でも当てはまる文字列を表現する記号です。
「東京都*」と書くことによって、東京都から始まる何でも文字列がOKになります。
せっかく考えたので、以降この数式で進めますｗ
丁より前の部分を抜き出す
では、次に「丁より前の部分」を抜き出していきます。
今回のサンプルで言うと、
- 神奈川県平塚市●●
- 東京都練馬区●●町
- 山形県鶴岡市●●
- 愛知県瀬戸市●●町
みたいな部分です。
パット見て分かりやすいのは、「数字が出てくる前まで」ってことになります。
シンプルにそれでやりましょう！
C2セルに入力する数式は次のようになります。
=LET(arr, IFERROR( FIND( {0,1,2,3,4,5,6,7,8,9},A2)-1 , “”) ,
MID(A2, 1, MIN(arr))
)
数式のメインは、
MID(A2, 1, MIN(arr))
この部分です。
MID関数でA2セル（神奈川県平塚市●●0-1-2）の1文字目から、数字が初めて出てくる文字数（MIN(arr)）まで抜き出しています。
LET関数は、
=LET(名前, 数式, 実際の計算)
といった、形で数式に名前を付けて、実際の計算の中で名前を使えるようにする関数です。
つまり、数式を見やすくするために使っています。
もし、LET関数を使わなかったとすると、この数式は次のようになります。
=MID(A2, 1, MIN(IFERROR( FIND( {0,1,2,3,4,5,6,7,8,9},A2)-1 , “”)))
この方が分かりやすいぞ！って方はどうぞこれを使って下さいｗ
入れ子が深くなるとわかりにくくなりますよね。
※LET関数について、もう少し知りたい方はこちらもどうぞ
今回は、 「数字が初めて出てくる文字数（MIN(arr)） 」を算出するのがポイントです。
MIN関数は、引数に渡した配列（参照セル範囲もOK）から、一番小さい数字を探してくれる関数です。
つまり、MIN関数の引数のarrは
IFERROR( FIND( {0,1,2,3,4,5,6,7,8,9}, A2)-1 , “”)
になっていますので、この数式で、A2セルの中にある０～９までの数字が、何番目にあるかを配列にデータとして作っています。
FIND関数の最初の引数（検索文字列）の部分が、｛｝くくりになっていますが、これは配列数式というものを使っています。
配列数式については、別の記事で紹介していますが、簡単に言うとFIND(0,A2) → FIND(1,A2) → FIND(0,A2) →といったぐあいに、1つの数式で順番に答えを返してくれる便利な数式です。
返る答えは、配列形式になります。
この場合、「 FIND( {0,1,2,3,4,5,6,7,8,9}, A2) 」の答えは
{ 10, 12, 14, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE! }
となります。
A2セルの中の文字列で、0は10番目、1は12番め、2は14番目にありますよ～ってことですね。
#VALUE!は見つからなかったということです。
3以降の数字は無いですからね。
ただし、 #VALUE! があると、MIN関数がエラーになってしまうので、 #VALUE! が出ないように、FIERROR関数でくくっています。
エラーが出た場合、””(空白)を返すようになっていますね。
そして、最終的に調べたいのは、数字の一つ前の文字までの文字数なので、－１をしています。
ということで、「 IFERROR( FIND( {0,1,2,3,4,5,6,7,8,9},A2)-1 , “”)」このようにすると
{ 10, 12, 14, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE! }
これが
{ 9, 11, 13, “”, “”, “”, “”, “”, “”, “”}
になります。
arrという名前にしているのはC2セルの数式内ではこれのことです。
MIN関数の引数にarrが渡されているので、答えは”9″ですね(^o^)
=MID( A2, 1, 9)
です。
A2は「神奈川県平塚市●●0-1-2」なんで、1文字目から9文字目までを抜き出すと
「 神奈川県平塚市●● 」となります。
C3セル以降にもコピーしましょう。
上手く数字の前の文字列まで抜き出すことが出来ました。
県～丁まで抜き出す
次は、県～丁まで抜き出します。
ん？さっきの「 丁より前の部分を抜き出す 」でやったのの、－１しなかったらいいんじゃん？
と思ったひとは、感は良いけど詰めが甘いですｗ
丁が1桁かどうかは分かりませんので（1桁しかなければ使えるけど。。その場合は、僕が詰めが甘いｗ）2桁だった場合正確に抜き出すことは出来ません。
なので、丁の後ろの「-」で文字数を数えることにしましょう！
※住所自体が、”丁”みたいに、漢字表記になっていたり、数字が全角になっていたりする場合は、先に調整しておく必要があります。
D2セルに入力する数式は、こちらです！
=IFERROR( LEFT(A2, FIND(“-“, A2)-1), A2 )
ここまで読み進んで来た方には、簡単ですね(^o^)
ポイントとなる数式は、LEFT関数です。
A2セル(神奈川県平塚市●●0-1-2)の一番左から、-の文字列の一個前まで(FIND関数で算出)を抜き出す。
エラーになった場合は、A2セルそのまま表示する
です。
IFERRORは、番地が無い住所も結構あるので、FIND関数で”-”を探しに行くと、ない場合エラーになりますので、その対応です。
はい、出来ました(^o^)
サクサクいきましょう！
市区町村から丁までを抜き出す
つまり、「県～丁まで」の都道府県がなければいいだけですね(^o^)
これは、簡単です。
SUBSTITUTE関数を使います。
= SUBSTITUTE(対象文字列, 検索文字列, 置換文字列 )
対象文字列にある、検索文字列を置換文字列に置き換えてくれる便利な関数です(^o^)
ということは、県～丁まで(D)列の文字列を対象文字列にして、
都道府県だけ（B列）を検索文字列にする。
そして、置換文字列を「””」とすることで、取り除くことが出来ます！
E2セルに入力する数式はこちらです。
=SUBSTITUTE( D2,B2, “” )
いい感じですね。
番地以降を抜き出す
最後に「番地以降」を抜きだします。
これはちょっと複雑になりますが、F2セルへの入力は次の数式です。
=LET( tmp, SUBSTITUTE(A2,D2, “”),
IF( LEFT(tmp, 1)=”-“,
MID(tmp, 2, LEN(tmp)),
tmp
))
また、LET関数を使っていますので、tmpというのは、「 SUBSTITUTE(A2,D2, “”) 」のことです。
A列から、D列を取り除いた結果になります。
2行目で言うと、「-1-2」となりますね。
その後が本体です。
tmp( -1-2 )がの一番最初の文字が、「-」だったら
tmp( -1-2 ) の2文字目から、 tmp( -1-2 ) の全体の文字数までを抜き取る
そうでなければ、 tmp( -1-2 ) をそのまま表示する
という感じです。
LEN関数は、引数に指定した文字の全体の文字数を返してくれる関数です。
結果は次のようになります。
完璧ですね(^o^)
まとめ
テクハラにならないように、簡単に。。。出来ませんでしたｗ
という話ですね。
やりたいことを実現するには、いろんな関数の組み合わせ。
日々精進しましょう！！(^o^)
コメント