今回は関数だけで住所から都道府県のみを抜き出す方法です。
都道府県を抜き出せるようになると、データの分析がかなりやりやすくなります。
都道府県の抜き出す関数はこれ！
=IF(LEFT(セル,1)=”京”,”京都府”,LEFT(JIS(セル),MIN(FIND({“都”,”道”,”府”,”県”},JIS(セル)&”都道府県”))))
※「セル」となっている部分は対象のセルに置き換えて使用してください。
※Find関数で配列数式を使用していますので、式を確定する時はCtrl ＋ Shift ＋ Enterキーで確定することを忘れないようにしてください。
※この数式を使用する場合は、都道府県が必ず一番左側にある状態で使用する必要があります。
例えば、郵便番号 ＋ 住所（〒 000-0000 東京都杉並区○○○２－２－１ アパート名○○○など）となっているようなデータに関しては、郵便番号をカウントしない式が必要になります。
郵便番号が入るデータは複雑な式になってしまいます。郵便番号と都道府県の間のスペースの有無によっても式が異なり、複雑化するため今回は都道府県のみを抜き出す式になっておりますので、予めご了承ください。
=IF(LEFT(セル,1)=”京”,”京都府”,LEFT(JIS(セル),MIN(FIND({“都”,”道”,”府”,”県”},JIS(セル)&”都道府県”))))はどうやって都道府県を抜き出しているのか
やっていることは以下の３つです。
- 京都府だけ例外処理を行う
- 「都」「道」「府」「県」の文字がそれぞれ何文字目にあるか計算する
- 対象文字列を２で判定した文字数を左から抽出
使用している関数の種類
合計５種類使用しています。
- IF：条件によって返す値を変更する
- LEFT：指定した文字列を指定した文字数分左から抽出
- JIS：文字列に含まれる半角を全て全角にする
- MIN：最小の値を返す
- FIND：指定した文字列が最初に現れる位置を左から数え、その数値を返す
1. 京都だけ例外処理を行う
今回、都道府県毎に文字を抜き出すため、「都」「道」「府」「県」の４文字を判定する対象の文字としています。
しかし、京都府だけは、府の前に「都」の文字があるため、正しく文字を抜き出すことができません。
そのため、例外処理が必要となります。
そこで、IF関数を使用し京都の場合は”京都府”とし、それ以外の都道府県を文字を抜き出すようにしています。
関数ではこのようになっています。
IF(LEFT(セル,1)=”京”,”京都府”
最初の１文字目が「京」だった場合は「京都府」としています。
2. 「都」「道」「府」「県」の文字がそれぞれ何文字目にあるか計算する
都道府県を含めると３文字か４文字になります。
これが何文字目に該当するのか計算するのが、
LEFT(JIS(セル),MIN(FIND({“都”,”道”,”府”,”県”},JIS(セル)&”都道府県”)))
となります。
順番としてはまずFIND({“都”,”道”,”府”,”県”},JIS(セル)&”都道府県”)
で都道府県の文字が何文字目にあるかを算出します。
例えば、「青森県青森市緑3-9-2」の場合JIS(セル)&”都道府県”で「青森県青森市緑3-9-2都道府県」となります。
都道府県それぞれ何文字目にあるか数えると
都：１３文字目
道：１４文字目
府：１５文字目
県：３文字目・１６文字目
（Find関数では３が返される）
となります。
下図はそのイメージです。
3. 対象文字列を２で判定した文字数を左から抽出
上記のようにFIND関数で文字数が判定できたら、次にMIN関数で、最小値のものを計算します。
つまり、最小の文字数は「３」となります。
最後にLEFT関数で文字列の左から３文字を抜き出し、青森県となります。
これが、文字列を抜き出す仕組みです。
まとめ
住所から都道府県を抜き出す式は以下の式になります。
=IF(LEFT(セル,1)=”京”,”京都府”,LEFT(JIS(セル),MIN(FIND({“都”,”道”,”府”,”県”},JIS(セル)&”都道府県”))))
「都道府県を住所から抜き出す」でやっていたことは以下の３つ。
- 京都府だけ例外処理を行う
- 「都」「道」「府」「県」の文字がそれぞれ何文字目にあるか計算する
- 対象文字列を２で判定した文字数を左から抽出
ぜひ活用してみてください。
コメント