Excelで住所から郡名と町名を一括りで分割する方法【DM・宛名シールなどの印字の準備のために】

はじめに

関数で一括で郡市区町村名を取り出すのに苦労することが多いこの問題、以前取り上げた道府県名と東京都＋特別区を住所から順次抜き出した後、「郡町名」「郡村名」「政令指定都市と行政区」「町名（東京都で郡名のない）」「村名（東京都で郡名のない）」「市名」の順で各郡市区町村名を分割・抽出できるかも知れない。

この問題の根本は、「郡」「市」「区」「町」「村」が入っている郡市区町村名および地域名があることです。

例えば、市名では「郡山市」「市川市」「村上市」「町田市」など、地域名では「郡山」「市谷」など。

これらがあると、中途半端な抜き出しになってしまうのです。

例えば郡名を抜き出す時、「大和郡山市●●1丁目23-45」の場合、「大和郡山市」の3文字目の「郡」が引っかかり、「大和郡」が抜き出されてしまう。

ですが、一括でするのではなく、「郡町名」「郡村名」「政令指定都市と行政区」「市名」の順で随時取り出すという方法ならば、郡市区町村名に「郡」「区」「市」「町」「村」があるかどうかを確認しつつ、抜き出せるのです。

また、「郡町名」のように郡と町をまとめて取り出す方法にすれば、郡と町がより限定されて取り出しやすくなります。

郡と町は、そのあとに分割することにします。

では、その方法を紹介いたします。

目次

郡町名を調べる

都道府県名を取り出したので、郡町名は住所の最初から何文字かまでになります。
COUNTIF関数で「郡」「町」が各々n文字目にある時、LEFT関数でn文字取り出せば良いことになります。

最初に、全ての郡町名を収集します。

郡町名一覧は次の通り。


以下のサイトで調べました。


北海道に同じ郡名がありましたが、今回は分別する意味がないので1つにまとめました。

次に、郡町名内に「郡」「町」が使用されていたら郡町名を抜き出すのが難しくなるので、郡町名内に「郡」「町」が使用されているかどうかを、前記の「郡町名一覧」で調べます。

郡町名内に「郡」「町」が使用されているかどうかを調べる関数は以下の通り。

郡名内に「郡」「町」が使用されているかどうか
=IF(COUNTIF(E2,"*郡*郡*"),"YES","")
=IF(COUNTIF(E2,"*町*"),"YES","")
町名内に「郡」「町」が使用されているかどうか
=IF(COUNTIF(E2,"*町*町*"),"YES","")
=IF(COUNTIF(E2,"*郡*"),"YES","")


郡名に「郡」「町」がある場合は「YES」を表示し、1文字の場合は何も表示しない、という関数です。

「郡町名一覧」にこれら関数を挿入し調べた結果は以下の通り。

  • 郡名には「郡」が2文字以上無く、「町」は無し
  • 町名には「郡」は無く、「町」は2文字以上無し

それから、郡名の文字数と「郡」の来る位置を調べた結果、郡名の文字数は2〜5文字。「郡」の来る位置も2〜5文字目。
町名のの文字数と「町」の来る位置を調べた結果、町名の文字数は2〜6文字。「町」の来る位置も2〜6文字目。

ということは、郡町名の文字数は、4〜11文字になる。

最後に、道府県名と東京都＋特別区名を取り出した後の住所の左端は、「郡町名」「郡村名」「政令指定都市と行政区」「市名」、「地域名」が来ています。そのうち「郡村名」「政令指定都市と行政区」「市名」に「郡」や「町」がある場合、郡町名の抽出時に一緒に抜き出されてしまう可能性があります。

ですので、「郡村名」「政令指定都市と行政区」「市名」、「地域名」に「郡」「町」が使用されているかを確認します。その関数は以下の通りです。
=IF(COUNTIF(E2,"*郡*"),"YES","")
=IF(COUNTIF(E2,"*町*"),"YES","")
調べた結果、「郡村名」「政令指定都市と行政区」に「郡」や「町」は使用されていませんでした。

「市名」には、「郡」や「町」が使用されています。

「市名」に「郡」が使用されているのは次の5市。
  • 郡山市
  • 郡上市
  • 蒲郡市
  • 小郡市
  • 大和郡山市
これらは例えば「大和郡山市●●町」のように、市名と地域名としての町名が、「大和郡」「山市●●町」となって、郡名の文字数は2〜5文字、町名の文字数は2〜6文字に合致してしまいます。

ですので、上記5市は除外する関数で対処します。

「市名」に「町」が使用されているのは次の3市。
  • 町田市
  • 大町市
  • 十日町市
ただ今回は「郡町名」一体で抜き取るので、「郡」「町」共に含まない上記3市は必然的に除外されるので大丈夫です。

また、東京都に所属する伊豆諸島および小笠原諸島には郡名がなく、「東京都大島町」「東京都三宅村」のようになります。
「東京都」は抽出済なので、現時点で「大島町●●」「三宅村■■」となっており、これらも「町」が使用されているので考慮する必要があります。

伊豆諸島および小笠原諸島で「町」を含むのは以下の2つです。

  • 大島町
  • 八丈町

ただ今回は「郡町名」一体で抜き取るので、「郡」「町」共に含まない上記2町は必然的に除外されるので大丈夫です。

以上の結果をまとめると、
  • 郡名には「郡」が1文字のみ、「町」は無し
  • 町名には「郡」は無く、「町」は1文字のみ
  • 郡名の文字数は、2〜5文字
  • 「郡」の来る位置も2〜5文字目
  • 町名の文字数は2〜6文字
  • 「町」の来る位置も2〜6文字目
  • 「郡町」の文字数は4〜11文字
  • 「郡村名」「政令指定都市と行政区」に「郡」や「町」は無し
  • 「市名」に「郡」を含むのは5市。関数にて除外できる
  • 「市名」に「町」を含むのは3市。関数にて除外できる
  • 「郡名」のない町は2町。関数にて除外できる
住所から郡町名を抜き出すのは、少々複雑なことをする必要があります。

住所から郡町名を抜き取る

では住所から郡町名を抜き出します。

郡名の文字数は2〜5文字、町名の文字数は2〜6文字ですので、
郡町名の文字数は、前記の通り、4〜11文字になります。

  • ◎郡■町（郡名2文字＋町名2文字＝4文字）
  • ◎◎◎◎郡■■■■■町（郡名5文字＋町名6文字＝11文字）

ですが、汎用性を持たせるために、関数を作ります。

まず、郡町名を検索するにはCOUNTIF関数を使用します。
COUNTIF関数は以下の通りになります。
=COUNTIF(E2,"*郡*町*")
「*郡*町*」の意味は、「*」は任意の文字列（1文字以上）、次に「郡」、続いて「*」は任意の文字列（1文字以上）と「町」、それから「*」は任意の文字列（1文字以上）なので、「1文字以上の文字列と郡と1文字以上の文字列と町と1文字以上の文字列」となります。

これで、4〜11文字の郡町名を検索できます。

それから、前記した「市名」に「郡」のある5市を除外します。

その関数は以下の通り。
COUNTIF(E2,"郡山市*町*")=0
COUNTIF(E2,"郡上市*町*")=0
COUNTIF(E2,"小郡市*町*")=0
COUNTIF(E2,"蒲郡市*町*")=0
COUNTIF(E2,"大和郡山市*町*")=0
例えば「COUNTIF(E2,"郡山市*町*")=0」は、「セルE2が郡山市ではない」ことを意味します。

これで、上記5市は除外することができます。

「●●郡▲▲町」を抜き出すのは、LEFT関数を使用します。
=LEFT(E2,[文字数])
郡町名の文字数は、4〜11文字でした。
郡町名の最後の文字は「町」で、4文字目〜11文字目に現れます。

つまり、「町」の位置番号が郡町名の文字数になります。
これはFIND関数を使用して以下のようになります。
=FIND("町",E2,4)
「町」は、E2セル内の文字列の4文字目以降どの位置にあるのか、という意味になります。
これは、「市名」に「町」が使用されている3市、「町田市」「大町市」「十日町市」、および「郡名」のない2町「大島町」「八丈町」は、「町」の位置が1〜3文字目で該当しないので大丈夫です。

これらをまとめると、関数は以下のようになります。
=IF(AND(COUNTIF(E2,"*郡*町*"),COUNTIF(E2,"郡山市*町*")=0,COUNTIF(E2,"郡上市*町*")=0,COUNTIF(E2,"小郡市*町*")=0,COUNTIF(E2,"蒲郡市*町*")=0,COUNTIF(E2,"大和郡山市*町*")=0),LEFT(E2,FIND("町",E2,4)),"")









コメント

コメントを投稿

よく読まれている記事

CSSボタンでテキストを天地中央に揃えるとき、なぜボタン高と行高を一緒にするのか

イメージ
表題の件が気になり調べたものを共有した方がイイかなと思い公開してみる。 自身の中でまだよく噛み砕けていないので判りづらい箇所があるかと思うが、そこはご寛恕を。 参考 Cascading Style Sheets Level 2 Revision 1 (CSS 2.1) Specification Cascading Style Sheets Level 2 Revision 1 (CSS 2.1) Specification（日本語訳） Eric A. Meyer著. 株式会社クイープ訳. CSS完全ガイド第2版 . オライリージャパン, 2010 インライン要素とは何か 文書構造を構成するブロックレベル要素内の特定箇所にある特定の機能や論理的な意味を付加する要素をいう。それ自身の前後に改行を生成しない。例としては、a要素、em要素、span要素など。 インライン要素には非置換要素と置換要素とがある。今回は置換要素には触れない（置換要素の代表例はimg要素）。 非置換要素とは何か 要素に囲まれた内容がそのまま表示される要素。HTMLやXHTMLの要素の大半は非置換要素である。 インライン非置換要素の特徴 widthとheightは適用されない。 背景はコンテンツエリアとpaddingに適用される。 上下marginは、事実上適用されない。 左右paddingは、要素の先頭と末尾のみに適用される。 上下paddingは、要素の背景を上下に伸ばすが、行の高さに影響しないため、背景色がある場合は上下の行（実際は上の行）に重なってしまう。 line-heightは何を制御しているか 行高を指定するline-heightは単純に行間を制御しているのではなく、 leadingという、コンテンツエリアの上下にある余白を制御 している。 leadingはline-heightとfont-sizeとの差 であり、 この差を等分 して コンテンツエリアの上下に加えることによって行間が決まる （コンテンツエリア＋leadingをインラインボックスという）。 コンテンツエリアとは、インライン非置換要素では、要素内の全ての文字を繋ぎ合わせたボックスでfont-sizeの値と同じ高さになる。 図解すると以下の通り。 つまり、line-heightが設定され...
Read more »

FullCalendarの導入からカレンダー毎の色指定まで

イメージ
2012年7月の拙サイト改装2回目時に既に導入している「 FullCalendar 」について、導入からGoogleカレンダーの複数読込み、カレンダー毎の色指定までをおさらい。基本的な流れはFullCalendarサイトの解説を踏襲。 デフォルト状態 下記改修を施した後 1. ダウンロード Downloadページ から「fullcalendar-1.5.3.zip」をダウンロードして解凍後、自サーバーへアップ。 2. fullcalendarのCSSとJSを読込む <head> タグ内に以下を記述。 <link rel="stylesheet" type="text/css" href=" fullcalendar.css " /> <script type="text/javascript" src=" fullcalendar.js "></script> <script type="text/javascript" src=" fullcalendar.min.js "></script> <script type="text/javascript" src=" gcal.js "></script> 赤字部分は各自の階層に合うように適宜変更。 3. FullCalendarにGoogleカレンダーを読込むためのjQueryを <head> タグ内に記述 必ず <script> タグで囲むこと。 <script type="text/javascript"> $(document).ready(function() { $(' #calendar ').fullCalendar({ // あとでここに色々追記 }) }); </script> 4. <body> タグ内のFullCalendarを挿入したい箇所にID属性を挿入 <...
Read more »

FacebookページのフィードURLを取得しウォールを自サイトに表示

イメージ
結局未採用だけど、折角調べて試してみたので記録しておく。 参照サイトは jQueryでFacebookページのウォールを自分のサイトに表示する方法（簡易バージョン） | webOpixel FacebookページのフィードURL取得は（取得ツール付き） FacebookページのフィードURLを取得してフィードリーダーで購読する方法: 小粋空間 以上、深謝。 自分のFacebookページのフィードURLを取得 上記参照サイトでは、自FacebookページのID確認方法について書かれているが、小粋空間さんのページに「自FacebookページのフィードURL取得ツール」があるので、自FacebookページのURLを入力すれば、フィードURLを表示してくれる。但しフォーマットはAtom1.0なので、RSS2.0フォーマットは以下のように変更する。 小粋空間さんの取得ツールで取得したフィードURL https://www.facebook.com/feeds/page.php?format= atom10 &id= [取得したID] RSS2.0フォーマットに変更後 https://www.facebook.com/feeds/page.php?format= rss20 &id= [取得したID] jQueryを自サイトに貼込み webOpixelさんからjQueryをを拝借し、自サイトに貼り込む。 <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script> <script type="text/javascript"> $(function() { var formatData = function(date) { var d = new Date(date); return (d.getFullYear() + '.' + ("0"+(d.getMonth()+1)).slice(-2) + '.' ...
Read more »