Excelで都道府県名と市区町村名を抽出する方法

お久しぶりです。
エクセルごはんです。
こんにちは。
ずっと更新していなかったのですが、お伝えしたい題材があり、久しぶりに更新してみました。
 
今回は、「北海道札幌市○○」や「沖縄県与那国町○○」という都道府県データから「札幌市」や「与那国町」という市区町村名の抽出を、ネットから入手した都道府県データ一覧と配列数式を用いて行ってみようと思います。
 
「北海道」や「沖縄県」の抽出なら参照データを使わなくても、IF関数と文字列操作関数を利用して出来ますが、市区町村名の抽出となると、参照データを利用した方法が確実です。そして、参照データに加えて配列数式を利用することで、短い数式で処理ができるようになります。
 
まずは、ネットから入手する参照するデータですが、総務省のWEBサイトに行き、都道府県コード及び市区町村コードのエクセルファイルをコピーしてきます。「総務省　都道府県コード」で検索すれば出てくると思います。
 
セルA１から、エクセルファイルをコピーしたら、最初の行にフィルターを付けます。フィルターはデータタブのフィルターにあります。市区町村のフィルターをクリックし、「すべて選択」を選んでチェックを外します。次に、「空白のセル」にチェックを付けて、空白のセルのみを抽出します。そして空白の行を削除します。空白の行を削除したら、市区町村のフィルターをクリックして、「すべて選択」をチェックして、市区町村が表示されるように戻します。最初の行に付けたフィルターは外してしまって大丈夫です。データタブのフィルターを再度クリックすると、フィルターが外れます。
これで、セルA１からE１７４９までに都道府県データが入りました。
このうち使用するデータはB列とC列の都道府県名と市区町村名のデータなのですが、今回は、このまま全て入れておきましょう。
 
さて、では、さっそく抽出といきたいところですが、まずは抽出のための準備を行います。抽出の際に必要な参照データを作りましょう。
参照データは、CONCAT関数を利用するか&演算子を利用して、都道府県名と市町村名を結合して作ります。「北海道札幌市」から「沖縄県与那国町」まで全データ結合した表を１列作成しましょう。ここではセルF３からF１７４９までに参照する都道府県名と市区町村名を結合したデータ一覧を作ります。
 
さて、参照データ一覧が出来たら、次は、抽出したいサンプルデータを作成しましょう。これは本来、仕事なら与えられてあるはずですが、今回はないので、先程の総務省の都道府県データから作りましょう。
CONCAT関数を利用するか&演算子を利用して、都道府県名と市町村名と適当な文字列を結合します。ここでは、都道府県名と市町村名とサンプルデータという文字列を結合します。
分かりやすいように、「北海道札幌市サンプルデータ」から「沖縄県与那国町サンプルデータ」まで全データ結合した表を１列作成しましょう。これが抽出元のサンプルデータになります。
先程作ったデータが、抽出するための参照用のデータで、こちらが、本来仕事で与えられるはずの抽出元のデータになります。
ここでは、セルG３からG１７４９にサンプルデータを作ります。
 
では、準備が整いましたので、いよいよ抽出作業に取り掛かりましょう。
抽出元の「北海道札幌市サンプルデータ」から北海道札幌市の部分を抽出したいとします。これを、参照データと配列数式を利用して抽出します。
セルH３からセルH１７４９を使います。
セルH３に、配列数式を用いて、
=MATCH(1,COUNTIF(G3,$F$3:$F$1749&"*"),0)
と入力します。
ここは配列数式にする必要があるので、CtrlとShiftとEnterを同時に押します。
セルH4からセルH１７４９まではオートフィルでコピーします。
セルH３からセルH１７４９まで１、２、３，４・・・１７４７と表示されたかと思います。
これで、必要な行番号が抽出できています。
あとは、INDEX関数を用いて、
セルC３からセルC１７４９に市区町村名データが入っているとして、
=INDEX($C$3:$C$1749,H3)
とすればOKです。
セルC３からC１７４９のデータからセルH３の１行目のデータ、つまり、「札幌市」を抽出します。
 
ここで配列数式の解説を少しします。
=MATCH(1,COUNTIF(G3,$F$3:$F$1749&"*"),0)
ですが、まず、COUNTIF関数で、セルG３がセルF３からセルF１７４９までのデータで始まるデータが含まれていないか検索します。*はワイルドカードといって、何が入っていても問題ありません。「北海道札幌市サンプルデータ」でも、「北海道札幌市さんぷるでーた」でも、「北海道札幌市てきとうなデータ」でも、北海道札幌市が含まれていればCOUNTIF関数が1を返します。この１がどこで返されているのかをMATCH関数を使って調べています。
 
配列数式の中がどうなっているのかはCONCAT関数を使うと分かるのですが、試しに、配列数式を用いて、
=CONCAT(COUNTIF(G3,$F$3:$F$1749&"*"))
と入力してみてください。
配列数式にするには、CtrlとShiftとEnterを同時に押します。
セルG３を調べてみると、F３のデータが当てはまるので、先頭が１になります。１００００００･・・・・というデータになります。
この１をMATCH関数で探していたのです。
 
要は、INDEX関数に入れる必要な行番号が分かれば良いのです。
なお、作業セルのセルH３を使わず、そのまま、配列数式を用いて、
=INDEX($C$3:$C$1749,MATCH(1,COUNTIF(G3,$F$3:$F$1749&"*"),0))
と入力しても良いです。
ここは配列数式を使っているので、CtrlとShiftとEnterを同時に押します。
このように、作業列としてH列に行番号を求める式を入れても良いですし、作業列を使わなくても良いです。
 
なお、都道府県名の抽出もこの方法で行えます。
セルB３からセルB１７４９に都道府県名データが入っているとして、
=INDEX($B$3:$B$1749,H3)
とすればOKです。
ただし、都道府県名の抽出だけなら、IF関数と文字列操作関数を使っても出来ます。こちらの方法はよくネットに解説されていると思うので割愛します。
 
今回は以上になります。
久しぶりの更新でしたが、お楽しみいただけましたでしょうか？
また機会があれば更新してみようと思います。
気の向くままに、ふわっとやっていきます。
では。
おつかれさまでした。