全国事務作業員の皆さま、お待たせしました。
住所を市区町村で区切って分割する方法、しかも市川市や野々市市などの市が２つある自治体の他、余市町や村山市などの市区町村という単語が２つ含まれる自治体にも対応できる方法が用意できましたので、やり方を紹介いたします。
仕組みはいいから、もう完成してる式を使ってさっさと作業したいんだよお！という方は、下記の記事上でエクセルファイルを公開してますので、そちらをご使用ください。
はじめに注意点
３点、使用上の注意があります。
総務省の全国地方公共団体コードを使用
総務省が提供している、全国地方公共団体コードの、エクセルファイル版を使用します。
総務省｜電子自治体｜全国地方公共団体コード (soumu.go.jp)
実在する市区町村かをチェックするために使うのですが、たまに合併などで更新がかかるため、適当なタイミングでメンテナンスが必要になります。
郡問題
総務省のデータを使用している都合で、そのデータ上に無い住所に対応できていません。
たとえば、栃木県芳賀郡市貝町。
総務省のデータ上では、栃木県市貝町となっています。
郡もあるわ市と町が混在してるわで、ああもう！ってなるパターンですね。ああもう！
でも対策しました。
ただかなり複雑な作りになってきたので、エクセルファイル（ZIP）を公開しています。（2023/1/20 追記）
下記記事よりダウンロードして使ってください。
ファイルへの直リンクはコチラ。
住所分割エクセルファイル（ZIP圧縮ファイル）
https://excelkowaza.com/wp-content/uploads/2023/01/jusyo-bunkatu.zip
この下にある、ケとヶ問題も解決した状態で公開しています。
ケとヶ問題
現状、日本の市区町村は、ケとヶが統一されていません。
例：青森県外ヶ浜町、岩手県金ケ崎町
そのため、総務省提供の情報と、市区町村で区切りたい住所データが不一致である場合、正常に区切ることができません。
事前に対策をとるか、随時直していくかを予め決めておいてください。
対策案１：統一する
作業前に、地方公共団体コードのエクセルファイル、住所データともに、ケ→ヶ、もしくはヶ→ケに変換して統一しておく。
対策案２：地方公共団体コードに追記
地方公共団体コードのエクセルファイルに、ケがついてる市区町村はケとヶの両バージョンを手作業で追加する。
対策案３：関数を改良する
今回紹介する方法を、SUBSTITUTE関数（文字を置き換える関数）などを使って改良してください。
SUBSTITUTE関数 – 文字を置き換える | エクセル小技集 (excelkowaza.com)
総務省データがケかヶのどちらかに統一されていれば、今回対応しようと思いましたが、混在しているせいでもう面倒になりました。
解決しちゃった
なんだかんだ言いながら、ケとヶ問題を解決した式も用意しました。（2022/3/22追記）
まず読み進めていただき、使い方を理解されたうえで、記事の最後の方にあるケとヶ問題対応版の式をご利用ください。
住所データを市区町村で分割する手順
先にあげた注意点に気をつけつつ（特にケとヶ問題は要注意）、実際に住所データを市区町村までと、それ以降で分割してみましょう。
区切りたい住所データを用意
そもそも市区町村で区切りたい住所データがないと話が始まらないので。
今回は下図のデータをもとに進めていきます。
総務省からエクセルファイルを拝借
総務省ホームページから、全国地方公共団体コードのエクセルファイルをダウンロードします。
総務省｜電子自治体｜全国地方公共団体コード (soumu.go.jp)
現在の団体シートをコピー
全国地方公共団体コードのエクセルファイルから、現在の団体シートをコピー、または移動で持ってきます。
都道府県と市区町村を合体
現在の団体シートで、都道府県と市区町村を合体させておく。
今回は、F2に=B2&C2と式を入れ、オートフィルで最終行まで反映させます。
市区町村で区切る
まずは何も考えず、下記式の住所セル（8か所）と市区町村表（2か所）を置き換え、実際にエクセル上にその式を入れて、Ctrl＋Shift＋エンターで確定させてください。
今回の例の場合、住所セルをA2、市区町村表をR1.5.1現在の団体!F:Fに置き換えて、下記のような式になります。
そしてCtrl＋Shift＋Enterで式を確定させると、自動的に式が{}で囲まれます。
あとはオートフィルなどで、式を最終行まで反映させます。
ここで、もし住所が存在しない市区町村であったり、ケとヶ問題に引っかかった場合は、要チェックと表示されますので、住所データを手直ししましょう。
ケとヶ問題はエラーとして扱いたくない！という方のために、対応版の数式も用意しました。
市区町村のあと部分を抜き出す
最後に、住所の市区町村より後ろの部分を抜き出します。
ここは色々なパターンがありますが、今回は下記の式を使いましょう。
式：=MID(住所セル,LEN(市区町村セル)+1,LEN(住所セル))
今回の場合は住所セルがA2、市区町村セルがB2になるので、置き換えるとこうなります。
式：=MID(A2,LEN(B2)+1,LEN(A2))
この仕組みの考え方
今回のはやたらと長い式なので、式の解説ではなく、どういう事をしている式なのかをお話しましょう。
- 市、区、町、村の文字を探しだす
- 市、区、町、村の文字が見つかったところまでを自治体名とする
- 自治体名が本当に存在するかチェック
- 自治体が存在しなかった場合、次の市区町村の文字を探しだす
- 2つ目の市区町村の文字までを自治体名とする
- また自治体名が本当に存在するかチェック
- それでも存在しなかったら要チェックと表示する
少し掘り下げていきましょう。
市、区、町、村の文字を探しだす
最初に、市区町村の文字が何文字目にあるか探し出します。
たとえば、北海道札幌市なら6文字目、北海道余市町なら5文字目となります。
市、区、町、村の文字が見つかったところまでを自治体名とする
最初に市区町村の文字が見つかったところまでを、ひとまず自治体名（市区町村名）と仮定します。
北海道札幌市は北海道札幌市ですが、北海道余市町は北海道余市となります。
自治体名が本当に存在するかチェック
公共団体コードの一覧から、先ほど仮定した自治体名を探します。
北海道札幌市はもちろん存在するので、そのまま結果を表示して終わりです。
しかし、北海道余市は存在しないので、次の処理へ進みます。
ここから先が、北海道余市町や、千葉県市川市などの、市区町村の文字が複数存在する自治体対策となります。
自治体が存在しなかった場合、次の市区町村の文字を探しだす
北海道余市町から市区町村の文字を探し出した後、そこからもう一度、市区町村の文字を探します。
北海道余市町の場合、最初に5文字目に市が見つかります。
そして、5文字目より先から、再び市区町村の文字を探しだすので、6文字目の町を見つける事になります。
2つ目の市区町村の文字までを自治体名とする
今度は6文字目で町を見つけたので、北海道余市町を自治体名とします。
また自治体名が本当に存在するかチェック
今度は北海道余市町が、公共団体コードのリストに存在するかをチェックします。
これで無事、北海道余市町がちゃんと、北海道余市町と表示されるようになります。
それでも存在しなかったら要チェックと表示する
ここで要チェックと表示されるということは、公共団体コードに存在しない住所だという事になるので、そもそもの住所データを疑いましょう。
ケとヶ問題の他に、いつのまにか町が市になってましたーなんて事に気づくチャンスにもなります。
あとがき
市区町村で区切る方法を探すと、市原市などで引っかかってしまったり、もしくはマクロ使いましょうって話がほとんどです。
マクロ使うと住所データリストのレイアウトが変わってしまう（A列からB列に移動しただけでも）といちいち手直しが必要になるし、せっかく作ったマクロを消滅させてくる人（xlsxで保存されて、しかもご丁寧にxlsmを削除された）もいるので、マクロは避けたかった。
そして、市川市やら野々市市などを、わざわざ1件ずつ手直しするのも嫌だった。
で、今回の方法が作られたというわけです。
総務省から自治体データ拾ってくるという手間はありますが、一度用意してしまえば、行列の挿入・削除などにもそこそこ耐えられる仕組みになってますので、ぜひご活用ください。
参考までに、市区町村が複数ある自治体リスト
2021/4/7現在、市区町村という文字が2つ存在する自治体は24か所でした。
- 北海道余市町
- 宮城県村田町
- 山形県村山市
- 福島県田村市
- 栃木県市貝町
- 群馬県玉村町
- 千葉県市川市
- 千葉県市原市
- 東京都町田市
- 東京都東村山市
- 東京都武蔵村山市
- 東京都羽村市
- 新潟県十日町市
- 新潟県村上市
- 富山県上市町
- 石川県野々市市
- 山梨県市川三郷町
- 長野県大町市
- 三重県四日市市
- 兵庫県市川町
- 奈良県下市町
- 広島県廿日市市
- 佐賀県大町町
- 長崎県大村市
ケとヶ問題、解決版
ケとヶを同じものとして扱い、要チェック対象としない式も用意しました。
手修正箇所は増えましたが、下記式の住所セル（14か所）と市区町村表（4か所）を置き換え、実際にエクセル上にその式を入れて、Ctrl＋Shift＋エンターで確定させてください。
元の例と同様、住所セルをA2、市区町村表をR1.5.1現在の団体!F:Fに置き換えると、下記のような式になります。
そしてCtrl＋Shift＋Enterで式を確定させると、自動的に式が{}で囲まれます。
この式を使う事で、本来のケとヶが違う場合でも、同じものとみなして要チェック対象ではなくなります。
このように、竜ケ崎市は本来大きいケなのですが、小さいヶの場合でも検索してくれるようになりました。
さらに郡問題も解決版
郡問題の項目でも書いていますが、解決はできています。
ですが、ここまでくるともう、１つの式で一発変換というわけにはいかなくなってきたので、下記の記事よりこちらで用意したエクセルと使ってみてください。
やった事はわりとシンプルで
- まずは一度分割する
- 分割できなかった住所は都道府県と市区町村の間に郡がある可能性があるので、郡を取り除いた版の市区町村を作る
- 郡を取り除いた版の住所を活用しつつ、もう一度分割する
言うは易しというやつで、実際の式はだいぶゴチャゴチャしてます。
ですが、式をシンプルにすれば作業領域が増えるので、頭が痛くなりますよほんと。
まとめ
長々と仕組みについてなど書いてきましたが、つまるところ冒頭で紹介してるエクセル使ってください。
あと他に良い方法あれば教えてください。
日本の自治体名はややこしすぎる。
コメント