Excelで手軽に！市区町村の抽出ができる関数の使い方（四日市市・政令指定都市対応版）

今回は、関数だけで市区町村のみを抽出する方法です。
四日市市など市区町村が重なる市や政令指定都市○○市○○区の対応版となっています。

※現在判明しているもので、東京都町田市など「市区町村」の文字を１文字目に使った都市名が抜き出せません。
→現在抜き出す方法を調査中

※日本の住所に対応させたものですが、対応できていないところなどありましたら、コメント等で教えていただけますと幸いです。

市区町村を抜き出す式

=CHOOSE(IFNA(INDEX(表,MATCH(MID(JIS(Sheet1!$B2),LEN(Sheet1!$C2)+1,MIN(FIND({“市”,”区”,”町”,”村”},JIS(Sheet1!$B2)&”市区町村”)-LEN(Sheet1!$C2))),表[市区町村],0),2),1),MID(JIS(Sheet1!$B2),LEN(Sheet1!$C2)+1,MIN(FIND({“市”,”区”,”町”,”村”},JIS(Sheet1!$B2)&”市区町村”)-LEN(Sheet1!$C2))),MID(JIS(Sheet1!$B2),LEN(Sheet1!$C2)+1,MIN((FIND({“市”,”区”,”町”,”村”},JIS(Sheet1!$B2)&”市区町村”)-LEN(Sheet1!$C2)+1))),MID(JIS(Sheet1!$B2),LEN(Sheet1!$C2)+1,SMALL(FIND({“市”,”区”,”町”,”村”},JIS(Sheet1!$B2)&”市区町村”)-LEN(Sheet1!$C2),2)))

※１行目の北海道の住所を抽出している式になります。

※”市”,”区”,”町”,”村”などでついているダブルクォーテーションが貼り付け時に別の形式に置き換わる場合がありますので、その際はダブルクォーテーションを修正しなおしてください。

です。

実際に計算しているファイルは下記ボタンよりダウンロード可能です。

今回は複雑に見えますが、やっていることは大きく４つだけです。

• 抽出方法のパターンを算出
• 通常通り、「市」「区」「町」「村」のところを起点に抜き出す
• 四日市市など市が重なる地名を抜き出す
• 政令指定都市は区まで抜き出す

上記式を使用するにあたっての注意事項

• セルは該当のセルに置き換えてください。

• 市区町村表を別シートに作成してください。

下記リンクにあるところから表をコピーしてご利用ください。

市区町村表_抽出.xlsx

docs.google.com

※2023年6月16日に新しくデータを追加しましたので、6月以前より使用されていた方は、お手数をおかけいたしますが市区町村表の表を張り付けなおしてご使用ください。

こちらの表につきましては、後ほど詳しく説明いたします。

• 都道府県を予め抜きだした状態で行ってください。

※都道府県の抜き出し方は、こちらで解説していますので、ご参考ください。

関数だけで住所から「都道府県」を抜き出す方法

Excelの関数だけで住所から「都道府県」を抜き出す方法です。前回住所を番地前まで抜き出す方法の記事を投稿しましたが、その応用で都道府県も抜き出すことが可能です。 式もコピーして使用できるようにしておりますので、ぜひご利用くださいませ。

excel-spreadsheet-ninjya.com

2023.02.27

今回使用している関数とその役割

LEN

文字数をカウントしています。ここでは都道府県の文字数（例：北海道の場合3）をカウントしています。

MID

市区町村を抜き出しています。

FIND

「市」「区」「町」「村」がそれぞれ何文字目にあるかカウントします。

MIN

政令指定都市以外の市区町村を抜き出す際に使用しています。抜き出す文字数はFIND関数で抽出した文字数分で抜き出しています。

SMALL

政令指定都市の市区町村を抜き出す際に使用しています。抜き出す文字数はFIND関数で抽出した文字数分で抜き出しています。

JIS

強制的に全てを全角で表示させています。

INDEX＆MATCH

市区町村を抜き出すパターンを1～３のいずれかに選別します。
やっていることはVLOOKUP関数と同じことをしています。

IFNA

上記の選別時にエラー（N/Aエラー）対策です。

CHOOSE

INDEX＆MATCHで判定したパターンによって市区町村を抜き出しています。

式の作る手順

1. CHOOSE関数(1,1,2,3)を入力
2. 市区町村表の作成
3. 市区町村までを抽出（抽出パターン①）
4. CHOOSE関数のインデックス値の算出
5. INDEX関数・MATCH関数とエラー処理IFNA関数
6. パターン２（市町が連続するパターン）の市区町村を抽出
7. パターン３（政令指定都市のパターン）の市区町村を抽出

以上の手順にて式を作成していきます。

手順①：CHOOSE関数(1,1,2,3)を入力

まず、CHOOSE関数(1,1,2,3)を該当のセルに入力しておきます。
数字を入れておくことで、エラーになりません。

手順②：市区町村表の作成

市区町村表には以下の２つが必要です。

• 市区町村名
• CHOOSEインデックス

市区町村名

市区町村名には、市が続く「四日市市」、市と町が続く「焼津市」「篠山市」と政令指定都市（東京を除く）の「札幌市」「仙台市」「新潟市」「さいたま市」「千葉市」「横浜市」「川崎市」「相模原市」「静岡市」「浜松市」「名古屋市」「京都市」「大阪市」「堺市」「神戸市」「岡山市」「広島市」「北九州市」「福岡市」「熊本市」を記載します。

※「焼津市」・「篠山市」はそのまま市名でしたので、通常パターンになります。
市が続く名称として、石川県「野々市市」・広島県「廿日市市」を追加しました。
町市となる地名として新潟県「十日町市」・長野県「大町市」を追加しました。
村町となる地名として群馬県「佐波郡玉村」を追記しました。
村市となる地名として福島県「田村市」・東京都「羽村市」・長崎県「大村市」を追記しました。
市町となる地名として北海道余「市郡余市町」を追記しました。

東京都を除く理由は東京の区は独立しており、通常パターンで市区町村を抽出が可能です。例えば、横浜市旭区の場合（市の中の区）となっており抽出する方法が変わります。

Chooseインデックスには、市もしくは市町となるところを「2」、政令指定都市を「3」と入力します。

手順③：市区町村までを抽出（抽出パターン①）

まず、JIS、LEN、MID、FIND、MINを使って、市区町村名を抽出します。
基本的な考え方は都道府県の抽出の仕方と同じです。

都道府県の場合、左から文字を抽出すればよかったですが、今回の場合は都道府県を抜いた状態になります。そこで使用する関数がMID関数になります。
MID関数は（文字列、開始位置、文字数）の構成になっており、開始位置と文字数がわかれば意図した文字数をどこからでも抜き出す事が可能な関数です。

文字列 ＝ 住所

開始位置＝都道府県の文字数+1
都道府県の文字数に＋１するのは、都道府県の次１文字目からカウントするためです。

文字数＝市区町村それぞれの最小文字数‐都道府県の文字数
開始位置で都道府県を含まないようにしたので、こちらも都道府県を含まない文字数にしてあげます。

例えば上記を「青森県青森市緑3-9-2」に当てはめると

文字列＝青森県青森市緑3-9-2

開始位置＝4
青森県（３文字）＋１＝４

文字数＝３
青森県青森市（６文字）‐都道府県の文字数（３文字）＝３文字

となります。その結果「青森市」が抜き出せます。
下図はイメージです。

これが市区町村の抜き出し方法です。
ここから次が例外処理になります。

手順④：CHOOSE関数のインデックス値の算出

今回３つのパターンで市区町村を抽出します。
通常の市区町村抽出、市又は市町と連続する時の抽出、政令指定都市の抽出とします。

CHOOSE関数の構成は（インデックス,値１,値２,値３…）となっています。
インデックスが仮に１だった場合は、値１を出力します。

今回の市区町村抽出に当てはめると以下のようになります。

CHOOSE（抽出パターン判定,通常の市区町村抽出,市又は市町と連続する時の抽出,政令指定都市の抽出）

抽出パターンが「１」だったら通常の市区町村抽出

抽出パターンが「２」だったら市又は市町と連続する時の抽出

抽出パターンが「３」だったら政令指定都市の抽出

という感じです。

続いては、抽出パターン判定方法です。

手順⑤：INDEX関数・MATCH関数とエラー処理IFNA関数

市区町村を抜き出した文字を、市区町村表と照らし合わせます。
ここの表に掲載のある市だった場合、例外処理を起こない、掲載のない市区町村は通常処理を行います。

市区町村表と照合する時に使うのが、INDEX関数とMATCH関数を使います。
VLOOKUPがわかる方であれば、やっている事は同じ事です。

この市区町村表にない場合「N/A」エラーになるのですが、エラーにならないようにIFNA関数を使い、エラーの場合は「1」を返すようにします。

これでパターンの算出ができましたので、例外処理のパターン２とパターン３の式を入れて完成です。

手順⑥：パターン２（市町が連続するパターン）の市区町村を抽出

パターン２は「市」と「町」がつながるケースです。
三重県四日市市と静岡県焼津市町、兵庫県篠山市町の３つです。
※ほかにもありましたら教えていただけると助かります。

共通しているのが、市の次に「市」「町」のいずれかが来ていますので、市区町村を抜き出す文字数を＋１すると市町が連続するパターンも抜き出すことができます。

関数はMID関数の文字数に＋１してあげれば完了です。

手順⑦：パターン３（政令指定都市のように連続しないパターン）の市区町村を抽出

３つ目のパターンは横浜市旭区など市と区の間が連続していないパターンです。
※（政令指定都市と北海道余市郡余市町の抜き出しパターンです。）

これまで、文字数を最小値MIN関数で取得していましたが、今回の場合MIN関数を使うと一番最初に来る市区町村の文字数を取得してしまいます。そこで２番目に少ない文字数を取得するようにしたいので、SMALL関数を使います。n番目に小さい数字と指定することができるのがこの関数の強みです。

MIN関数をSMALL関数に置き換え、2番目を指定してあげれば終了です。

まとめ

いかがでしたでしょうか。
今一度、やっていることのおさらいです。

式

=CHOOSE(IFNA(INDEX(市区町村表,MATCH(MID(JIS(セル),LEN([@都道府県抜きだし])+1,MIN(FIND({“市”,”区”,”町”,”村”},JIS(セル)&”市区町村”)-LEN([@都道府県抜きだし]))),市区町村表[市区町村],0),2),1),MID(JIS(セル),LEN([@都道府県抜きだし])+1,MIN(FIND({“市”,”区”,”町”,”村”},JIS(セル)&”市区町村”)-LEN([@都道府県抜きだし]))),MID(JIS(セル),LEN([@都道府県抜きだし])+1,MIN(FIND({“市”,”区”,”町”,”村”},JIS(セル)&”市区町村”)-LEN([@都道府県抜きだし])+1)),MID(JIS(セル),LEN([@都道府県抜きだし])+1,SMALL(FIND({“市”,”区”,”町”,”村”},JIS(セル)&”市区町村”)-LEN([@都道府県抜きだし]),2)))

手順

1. CHOOSE関数(1,1,2,3)を入力
2. 市区町村表の作成
3. 市区町村までを抽出（抽出パターン①）
4. CHOOSE関数のインデックス値の算出
5. INDEX関数・MATCH関数とエラー処理IFNA関数
6. パターン２（市町が連続するパターン）の市区町村を抽出
7. パターン３（政令指定都市のパターン）の市区町村を抽出

式自体は複雑のように見えますが、やっていることは判定とパターン３つで文字の抽出方法を変えているだけです。

また、今回の方法は静岡県の行政区が令和６年に再編予定ですが、それも対応できる方法となっています。
基本的に市区町村以外の町ができない限り対応できるかと思います。

今回のブログが参考になりましたら幸いです。