以前、日本の都道府県（北海道以外）の住所を抜き出す方法の記事を作成しました。
今回は北海道編です。（北海道の住所が特殊で通常の方法では町名抽出ができませんでした）
こんな感じに抜き出すことができるようになります。
北海道の住所はなぜ抜き出すことができなかったのか？
住所は都道府県＋市区町村＋町名＋番地の組み合わせから住所が成り立っています。そのため番地の数字（算用数字）を起点として、住所を抜き出すことができました。
しかし北海道の住所は、町名に算用数字が入っている住所や「１条」「一条」など地域によっても書き方などが混在しており、他の都道府県でできる抜き出し方法だけではできませんでした。
今回は条件をピックアップし、それに合わせて数式を作りました。
郵便局の住所データを元に数式を作りましたが、抜けている部分もある場合がありますので、参考程度に捉えていただけますと幸いです。
抜き出すポイント
抜き出すポイントは大きく６つのパターンがありました。
- 通常パターン（番地を基準に抜き出す）
- 算用数字＋特定の文字パターン（例：１条）
- 特定の文字＋算用数字パターン（例：第１）
- 第＋算用数字＋特定の文字（例：第１町内）
- 算用数字＋区＋算用数字＋条パターン（例：４区１条）
- 市又は条＋区のパターン（例：政令指定都市）
使用する関数
- CHOOSE関数
抽出パターンを判定し、パターンによって抽出の方法変えています。 - MAX関数
文字の長さを比較し、一番長い文字数を出すのに使用しています。 - MIN関数
文字の長さを比較し、一番短い文字数を出すのに使用しています。 - FIND関数
特定の文字が何文字目にあるか計算するのに使用しています。 - LEN関数
文字の長さを求めるのに使用しています。 - COUNTIF関数
あいまい検索をするのに使用しています。 - JIS関数
住所を全角で統一するのに使用しています。 - CONCAT関数もしくは＆
文字の連結するのに使用しています。
その他にも検索用の別途テーブルを用意しています。
こちらはダウンロードできるようにしておきましたので、必要な方はご利用ください。
住所を抜き出す式
下図の例と住所抜き出し用テーブル（Sheet2）で抜き出す式を記載いたします。
適宜セルの値等を変えて、ご利用いただければと思います。
全体の式
=CHOOSE(MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*(テーブル2[Chooseカテゴリー])),LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1),LEFT([@住所],MIN(FIND(Sheet2!$D$4:$D$20,JIS([@住所])&CONCAT(Sheet2!$D$4:$D$20)))+MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*LEN(テーブル2[Find用検索値])-1)),LEFT([@住所],FIND(Sheet2!$D$22,[@住所])+1),LEFT([@住所],MIN(FIND(Sheet2!$D$23:$D$26,[@住所]&Sheet2!$D$23:$D$26))+MAX(COUNTIF([@住所],Sheet2!$C$23:$C$26)*LEN(Sheet2!$D$23:$D$26))-1),LEFT([@住所],MIN(FIND(Sheet2!$D$27,[@住所]&Sheet2!$D$27))+MAX(COUNTIF([@住所],Sheet2!$C$27)*LEN(Sheet2!$D$27))-1),LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1))
上記式を使用する際は、配列数式を使用していますので、必ずCtrl＋Shift＋Enterキーで確定させてください。
抽出パターンの判定方法
抽出パターンの判定方法で使用している式は以下の式です。
=CHOOSE(MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*(テーブル2[Chooseカテゴリー])),1,2,3,4,5,6)
北海道住所抜き出し用テーブルにchooseカテゴリーを設けており、このカテゴリー＝パターンの数字になります。
Countifであいまい検索をして、ヒットしたら１を立てる。
ヒット×Chooseカテゴリーとなり、複数ヒットした場合はMax関数で数値の高いものが抽出パターンとなるようにしています。（北海道の住所は「条」や「区」は複数のパターンが存在します）
1．通常パターン（番地を基準に抜き出す）解説
通常パターンは、他の都道府県を抽出する時と同じです。
LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1)
Find関数で番地の先頭の文字数を求め、Left関数で左から文字を抜き出しています。
他のパターンも「基本的にはFind関数で文字数を求めて、Left関数で抜き出す。」です。
最後の文字列結合は、Find関数はヒットしなかった場合にエラーとなってしまうため、エラーにならないように文字を結合しています。
ー１はFind関数で求めると番地の先頭の数字が返されますので、町名のみにするために「ー１」をします。
2．算用数字＋特定の文字パターン（例：１条）
算用数字＋文字パターンはこちらの式です。
LEFT([@住所],MIN(FIND(Sheet2!$D$4:$D$20,JIS([@住所])&CONCAT(Sheet2!$D$4:$D$20)))+MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*LEN(テーブル2[Find用検索値])-1))
このパターンが最も北海道の住所で多くあります。
抽出の仕方は一緒ですが、特定文字によって文字数が異なりますので、特定の文字パターンに合わせて抜き出す文字をLen関数で算出し、加算しています。
ポイントはCountifで使用パターンを判定しているところです。
これによって、式が６パターンだけで済むようになっています。
ちなみにパターン４・５もこの２のパターンと同じ抽出の仕方で、検索も元が異なるだけです。
3. 特定の文字＋算用数字パターン（例：第１）
特定の文字＋算用数字パターンの数式はこちらです。
LEFT([@住所],FIND(Sheet2!$D$22,[@住所])+1)
このパターンは「第」という文字が何番目にあたるかを求めて＋１文字しています。
第のあとに数字一桁が来ますので、＋１と固定にしています。
仮に第１０まである場合は、調整が必要になりますが、ここが一番難しいポイントです。
第１の後に番地が続くのですが、町名の１と番地の数字が違うという事を考慮する必要があるため、抽出するのが最も難しいと思われます。
4. 第＋算用数字＋特定の文字（例：第１町内）
第＋算用数字＋特定の文字の数式はこちらです。
LEFT([@住所],MIN(FIND(Sheet2!$D$23:$D$26,[@住所]&Sheet2!$D$23:$D$26))+MAX(COUNTIF([@住所],Sheet2!$C$23:$C$26)*LEN(Sheet2!$D$23:$D$26))-1)
２の算用数字＋特定の文字パターンの抽出方法と同じです。
北海道住所抜き出し用テーブルの検索する場所が異なります。
２の式と同じにも関わらず分けている理由は、「第」という特定文字が関係しています。
パターン抽出の際の判定で、うまく判定ができないため分けています。
5. 算用数字＋区＋算用数字＋条パターン（例：４区１条）
算用数字＋区＋算用数字＋条パターンの数式はこちらです。
LEFT([@住所],MIN(FIND(Sheet2!$D$27,[@住所]&Sheet2!$D$27))+MAX(COUNTIF([@住所],Sheet2!$C$27)*LEN(Sheet2!$D$27))-1)
抽出方法は２・４のパターンと同じで、検索元が異なります。
「〇条〇区」の他にも「〇区〇条」というパターンもあるので、パターンとして分けています。
6. 市又は条＋区のパターン（例：政令指定都市など）
市又は条＋区のパターンの数式はこちらです。
LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1)
この抽出パターンは１の通常パターンと同じです。
ですが〇市〇区、〇郡〇区と区の位置の判定が難しいため、分けています。
まとめ
関数だけで住所を番地前まで（住所が特殊すぎる北海道編）の抜き出し方をお届けしました。
計算の方法は３パターンぐらいしかないのですが、抽出パターンを求めるための検索がとても難しく、結果６パターンまで増えてしまいました。
とりあえずは町名まで抽出できるようになったので、よかったです。
ちなみに、全国の場合の式はいかになります。
よろしければご利用ください。
=IF(LEFT([@住所],3)=”北海道”,CHOOSE(MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*(テーブル2[Chooseカテゴリー])),LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1),LEFT([@住所],MIN(FIND(Sheet2!$D$4:$D$20,JIS([@住所])&CONCAT(Sheet2!$D$4:$D$20)))+MAX(COUNTIF([@住所],テーブル2[Countif用検索値])*LEN(テーブル2[Find用検索値])-1)),LEFT([@住所],FIND(Sheet2!$D$22,[@住所])+1),LEFT([@住所],MIN(FIND(Sheet2!$D$23:$D$26,[@住所]&Sheet2!$D$23:$D$26))+MAX(COUNTIF([@住所],Sheet2!$C$23:$C$26)*LEN(Sheet2!$D$23:$D$26))-1),LEFT([@住所],MIN(FIND(Sheet2!$D$27,[@住所]&Sheet2!$D$27))+MAX(COUNTIF([@住所],Sheet2!$C$27)*LEN(Sheet2!$D$27))-1),LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1)),LEFT(JIS([@住所]),MIN(FIND({“１”,”２”,”３”,”４”,”５”,”６”,”７”,”８”,”９”},JIS([@住所])&”１２３４５６７８９”))-1))
コメント