Excelで住所データから市の情報・行政コードを抽出する方法。

Excel

Excelでデータを抽出するとき、共通点を見つけるのがおすすめです。
住所データから「市」の部分だけを抽出する方法をまとめてみました。

住所のうち「市」の情報だけが欲しい

 

今回の事例は、住所のデータから「市」の情報を抽出するというものです。

 

別のシートにある行政コードをつかうために、データが必要になります。

 

この行政コードはネットからダウンロードできます。

総務省|地方行政のデジタル化|全国地方公共団体コード

 

住所のうちカギになるのは、C列の市区町村の部分です。

これをExcelでどのように抽出するかをまとめてみました。
(町や村のケースは複雑になるので省略)

 

パターンを見つけよう

 

Excelの関数を使って、複数のデータから必要な部分だけを抽出するには、パターンを見つけることです。

ここでは、「県名のほとんどは3文字(4文字は神奈川県、和歌山県、鹿児島県のみ)」という点に注目します。
(府や道はややこしくなるので省略)

 

「=Find(”県”,B2)」で県が何文字目にあるかを数字で返します。


すると神奈川県は「4」、それ以外は「3」。東京は「都」なのでエラーになっていますが、後述しますので、ひとまず無視で。

ということは、市の情報は4文字目からはじまると考えてよさそうです。

 

次に「市」の情報を抽出したいのですが、ここでは関数Mid(文字列, 開始位置, 文字数)を使います。

「開始位置」は「県」の文字の次から。つまり(find(“県”,B2)+1)ということになります。
ところが「市」の文字数はデータによって違います。

 

ここで考えたいのは、「市」の文字が住所データの何番目にあるのか?という点です。先程と同様に「find(“市”,B2)」とすれば、何番目にあるかはわかります。

 

これを踏まえて。
B2のデータで言えば、「県」の文字がある3番目と「市」の文字がある6番目の差が「市」の文字数は3文字(6−3=3)となります。

 

実際に、関数Midを使って「=MID(B2,C2+1,E2)」とすると、他のデータでも「市」のデータだけを抽出することができました。

 

これを1つの数式で直接あらわすと、「=MID(B2,FIND(“県”,B2)+1,FIND(“市”,B2)-FIND(“県”,B2))」
ややこしくなるので、先に数字を使って説明しました。やっていることは前述したものと同じです。

 

最後にエラーになっている「都」については、ifを使います。

もし、3文字目か4文字目が「県」ならを「IF(OR(MID(B2,3,1)=”県”,MID(B2,4,1)=”県”)」とし、該当すれば、前述したように「市」のデータを抽出し、そうでなければ、「区」のデータを抽出する。

 

「=IF(OR(MID(B2,3,1)=”県”,MID(B2,4,1)=”県”),MID(B2,FIND(“県”,B2)+1,FIND(“市”,B2)-FIND(“県”,B2)),MID(B2,FIND(“都”,B2)+1,FIND(“区”,B2)-FIND(“都”,B2)))」

そうすれば、練馬区が抽出され、エラーは表示されません。

まぁ、ここまで行くと関数はかなりややこしくなりますが、参考までに。

 

行政コードを抽出するには?

 

最後に「市」や「区」のデータから行政コードを抽出します。

別シート(行政コードのマスタ)を用意しておき、

XLOOKUPで「市」をカギにして、行政コードのシートから

同じ「長野市」を見つけて、その行政コードを抽出し、

GOforIT 7

表示させることができました。

GOforIT

 

こうしたパターンを見つけることができると、Excelでしくみをつくりやすくなり、トレーニングとしてもおすすめです。

GOforIT

ということで、参考にしていただければうれしいです。

 


【編集後記】
昨日はオフで3人の日。早朝にW杯の試合を観戦。その後も長女(14)は午前中に部活、午後から長男(7)が「あそんでくるね」とお友達と夕方まで。ひとりの時間を過ごしました。夕食は無印良品のカレーを2人で振る舞ってくれました。「〇〇ちゃんがやる!」と兄弟で若干もめながら、でしたけど。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
五郎島金時すいーとぽてと
羽二重餅
無印良品 グリーンカレー

【長男とW杯観戦記(期間限定コーナー:ネタバレなし)】
朝、イングランドとフランスの試合をダイジェストでチェック。「あれはファウルだよねー」と親子で語れるのはW杯で日々サッカーを見ているからこその収穫です。