Excel(エクセル）に入力された住所をセル毎に『都道府県』と『市区町村』と『それ以外の住所』に分けて管理したいことありませんか？
しかし、住所にスペースなどの区切り文字が無い場合は分割するのが困難です。
今回は、Excelの複数の関数を使って、住所をセルごとに分ける方法を紹介します。
関数は少し長くなりがちですが、この記事で紹介する関数を貼り付けて利用すれば誰でも作成可能です。
下の住所変換シートを一度完成させてしまえば、A列の黄色塗りつぶしセルに住所を入力すれば、自動で住所が分解される仕組みです。
住所をセル毎に分解する処理の流れ
スペースがない住所を『都道府県』と『市区町村』と『それ以外の住所』に分ける手順について、まず全体の作業の流れを説明します。
今回作成する住所の分解シートは以下のようになります。
一番左の黄色塗りつぶしセルに住所を入力すると、自動で住所が分解される仕様です。
塗りつぶしがされていないセルは関数が挿入されているので、住所を貼り付けるだけで分解出来ます。
処理は大きく分けて、以下の5ステップに分かれます。各見出しでステップ別の手順を紹介してます。
- 住所から『都道府県』を抽出
- 『都道府県より下の住所』から文字を抽出
- 『市区町村』リストをWEBからダウンロード
- 『市区町村候補』を表示
- 『市区町村』と『それ以外の住所』を確定
今回作業する１つ目のポイントです。それは・・・
『都道府県』は、３文字か４文字しか存在しておらず、４文字は『和歌山県』『神奈川県』『鹿児島県』の３県だけ
だということです。
続いて、２つ目のポイントです。それは、
『市区町村』の最新リストはWEBからダウンロード可能で、文字数は２～７文字内に収まる
ということです。
そして、３つ目のポイントです。この点が最も大切かもしれませんが、
この記事で紹介する関数は長く複雑です。しかし、意味は分からなくても手順に沿って、関数を所定のセルに貼り付けるだけで完成します。
手順に沿いながら、焦らず作ってくださいね。適当に自己流で作ると完成が危うくなるかもです。
ステップ１．都道府県を抽出
まずこの見出しでは、「住所」から「都道府県」だけを抜き出す方法を解説します。
住所の３文字目が「都」か「道」か「府」か「県」であれば、３文字の都道府県です。
４文字目が「県」であれば４文字の都道府県だということを利用して関数を挿入していきます。
それでは、どうぞ！
手順１．入力フォーマットを作成
表に『住所』『都道府県(３桁)』『都道府県(４桁)』『都道府県』『都道府県チェック』『都道府県より下の住所』列を挿入します。
次以降のステップで、この記事で紹介する関数をそのまま貼り付けれるようにするために、下の図と同じセル位置に合わせて表を作成してください。
手順２．「都道府県（３桁）」列にIF関数を挿入
「都道府県(３桁）」列に、以下のIF関数を挿入してください。表の形を合わせているのであれば、引数の変更は不要です。
また、正しく都道府県が抽出されるか確認するために、「住所」列のセルにサンプルの住所を入力しておくことをお勧めします。
=IF(OR(MID(A4,3,1)="県",MID(A4,3,1)="道",MID(A4,3,1)="府",MID(A4,3,1)="都"),MID(A4,1,3),"")
関数の説明を簡単にします。
住所の３文字目をMID関数で取り出して、もしその文字が「都」「道」「府」「県」である場合は、住所の１文字目から３文字目までをMID関数で取り出し、３文字目が「都」「道」「府」「県」で無い場合は、「空白セル」のまま表示するという関数を組んでいます。
手順３．「都道府県（４桁）」列にIF関数を挿入
続いて「都道府県(４桁）」列に以下のIF関数を挿入します。
=IF(OR(MID(A4,4,1)="県"),MID(A4,1,4),"")
このIF関数で、住所の４文字目をMID関数で取り出して、もしその文字が「県」である場合は、住所の１文字目から４文字目までをMID関数で取り出し、４文字目が「県」で無い場合は、「空白セル」のまま表示するという関数を組んでいます。
手順４．「都道府県」列にIF関数を挿入
「都道府県(３桁）」列、「都道府県(４桁）」列のどちらかに都道府県が入力されています。「都道府県」列に以下のIF関数を挿入して都道府県を確定させます。
=IF(B4<>"",B4,C4)
手順５．「都道府県チェック」列にIF関数を挿入
都道府県が正しく入力されているか確認するために、以下のIF関数を挿入します。
=IF(OR(MID(A4,3,1)="県",MID(A4,3,1)="府",MID(A4,3,1)="都",MID(A4,3,1)="道",MID(A4,4,1)="県"),"OK",IF(A4="", "", "NG"))
住所の３文字目と４文字目をそれぞれMID関数で取り出して、もしその文字が「都」「道」「府」「県」である場合は「都道府県チェック」列に「OK」が表示されます。そうでない場合は「NG」と表示されます。「住所」列が空白の場合は、空白のままとなります。
手順６．「都道府県チェック」列にIF関数を挿入
手順５で都道府県が確定したので、住所から都道府県を抜き出した残りの住所を「都道府県より下の住所」列に表示します。
置換出来る以下のSUBSTITUTE関数を挿入して、住所から都道府県から削除した残りの住所を表示させます。
=SUBSTITUTE(A4,D4,"")
以上で、ステップ１の作業は終了です。
ステップ２．「都道府県より下の住所」から文字を抽出
続いて、ステップ１で抽出した「都道府県より下の住所」列のデータから文字を抽出します。
手順１．文字抽出用に６列を追加
「都道府県より下の住所」列の右横に「文字抽出」列を追加します。「市区町村名」は「都道府県より下の住所」の左から２文字から７文字の範囲に必ず存在しているので、左から「7」「6」「5」「4」「3」「2」の計６列を追加します。
手順２．LEFT関数を挿入
「文字抽出７」列に以下のLEFT関数を挿入し、「都道府県より下の住所」の先頭から”列タイトルの数値分の文字数”を抽出します。この関数を別のセルにもコピペできるように、以下の位置に絶対参照の”＄”を挿入してください。
=LEFT($F4,G$3)
LEFT関数の挿入により、「神戸市長田区３丁目１５」という住所の先頭から７文字の「神戸市長田区３」までの文字が抽出されます。
手順３．LEFT関数を右の列にもコピペ
手順２で作成したLEFT関数を「文字抽出６」列から「文字抽出２」列のセルにもコピペします。
LEFT関数の挿入により、「神戸市長田区３丁目１５」という住所の先頭から６文字から２文字までの文字が抽出されます。
以上でステップ２の作業は終了です。
ステップ３．市区町村リストをWEBから抽出
続いて、ステップ３では、【総務省】のWEBサイトから、市区町村リストをダウンロードする方法を紹介します。
市区町村名は市区町村の統合や廃止などにより、数年に一度程度、定期的に更新されます。
そのため、定期的に総務省のWEBサイトから最新のリストをダウンロードしてエクセルにアップロードする必要があります。
手順１．「都道府県コード及び市町村コード」のExcelファイルをクリック
総務省HPの以下のURLを開き、「全国地方公共団体コード」の「都道府県コード及び市町村コード」のExcelファイルをクリックします。
https://www.soumu.go.jp/denshijiti/code.html
引用元：総務省トップ>政策>地方行財政>全国地方公共団体コード
手順２．ダウンロードされた「Excelファイル」を開く
PC画面の左下に表示されたダウンロードファイルをクリックしてください。
表示されなかった場合は、ダウンロードしたExcelファイルはPCの「ダウンロード」フォルダに保存されているので確認してください。
手順３．シート全体のデータをコピー
ダウンロードしたExcelブックの「○○現在の団体」シートを開きます。画面左上の全セル選択ボタンを選択してから、「Ctrl＋C」のショートカットキーでワークシート全体のデータをコピーします。右クリックメニューの「コピー」からでもOKです。
手順３．シート全体のデータをコピー
ステップ１，２で使っていたExcelブックの利用してない新規のワークシート名を「市区町村名リスト」に変更します。
次に、画面左上の全セル選択ボタンを選択してから、「Ctrl＋V」のショートカットキーでワークシート全体にダウンロードした市区町村名データを貼り付けします。右クリックメニューの「貼り付け」からでもOKです。
以上でステップ３は完了です。
ステップ４．市町村リストを使って『市区町村候補』を表示
ステップ４では、ステップ３でダウンロードした市区町村名リストを使って、住所の「市区町村候補」を表示させます。
ステップ２ではLEFT関数を使って市区町村名の候補データを作成しました。
この候補データと、ステップ３でダウンロードした市区町村名が一致したセルが『市区町村名』の可能性が非常に高いということになります。
手順１．市区町村名の候補用に６列を追加
「文字抽出２」列の右横に「市区町村候補」列を追加します。「市区町村名」は「都道府県より下の住所」の左から２文字から７文字の範囲に必ず存在しているので、左から「7」「6」「5」「4」「3」「2」の計６列を追加します。
手順２．VLOOKUP関数を挿入
『市区町村名』が一致するセルを見つけるために、「市区町村候補7」列に、IFERROR関数とVLOOKUP関数を組み合わせた関数を挿入します。第１引数「検索値」は、「文字抽出7」のセルです。
この記事と同じシート名と同じ表の形で作成してくれていれば、手順４に飛んで、関数を貼り付けたら完成するよ。手順２～手順４は関数の作成を細かく分けて説明してます。
=IFERROR(VLOOKUP(G4,
手順３．VLOOKUP関数の第２引数を指定
VLOOKUP関数の第２引数を指定するため、「市区町村名」シートをクリックします。
市区町村名が入力されたC列の列番号をクリックして絶対参照に設定するため「F4」キーを押してから、第２引数の完了のための「,(カンマ）」を入力します。
=IFERROR(VLOOKUP(G4,市区町村リスト!$C:$C,
手順４．VLOOKUP関数の第３、４引数を指定
VLOOKUP関数の第３引数と第４引数を設定して、IFERROR関数の第２引数も設定します。
=IFERROR(VLOOKUP(G4,市区町村名リスト!$C:$C,1,FALSE),"")
設定し終えたら、「Enter」で関数を確定させてください。以下のように空白セルのままか、市町村名が表示されることを確認してください。
手順５．VLOOKUP関数を右の列にもコピペ
「市区町村候補7」に挿入したIFERROR関数&VLOOKUP関数を「市区町村候補６」列から「市区町村候補２」列のセルにもコピペします。
そうすると、最低１つ以上の「市区町村名」が表示されます。
もし、「市区町村候補」列のすべてが空白セルとなる場合は、A列に入力した住所自体がおかしいか、上のステップで挿入したVLOOKUP関数の引数がおかしいか再度確認してみてください。
以上でステップ４の終了です。
ステップ５．『市区町村』と『それ以外の住所』を確定
ここまでくれば後は簡単です。
ステップ５では、『市区町村』と『それ以外の住所』を確定させます。
手順１．『市区町村』列と『町名・番地』列を追加
「市区町村候補２」列の右横に『市区町村』列と『町名・番地』列を追加します。
手順２．『市区町村』列にIF関数を挿入
「市区町村」列に以下のIF関数を挿入し、市区町村を確定させます。
=IF(M4<>"",M4,IF(N4<>"",N4,IF(O4<>"",O4,IF(P4<>"",P4,IF(Q4<>"",Q4,IF(R4<>"",R4,""))))))
複数の「市区町村候補」がM列からR列に表示されている場合もあるかもしれませんが、左側が正解の可能性が高いでしょう。
そのため、IF関数を用いて、左側の候補セルを優先して「市区町村」列に表示させるようにしています。
手順３．『町名・番地』列にSUBSTITUTE関数を挿入
「町名・番地」列に以下のSUBSTITUTE関数を挿入し、「それ以外の住所」を確定させます。
=SUBSTITUTE(F4,S4,"")
手順４．『不要な列』は非表示
ステップ１からステップ５までで多くの列を挿入しましたが、必ず表示させておく必要のある列は「住所」列、「都道府県」列、「都道府県チェック」列、「市区町村」列、「町名・番地」列の５列です。
それ以外の列は、不要と感じるようであれば非表示にしておきましょう。
手順５．行をコピー
複数の住所も分解出来るようにするため、数式を挿入した行を「Ctrl＋C」のショートカットでコピーします。
手順６．「数式」で貼り付け
下の行に同じ数式を挿入したいので、複数の行を選択してから右クリックし、右クリックメニューの貼り付けオプションの「数式」を選択します。
手順７．住所分解シートの完成
新しく数式を挿入した行に、別の住所を入力しても正しく住所が分解出来れば、「住所分解シート」の完成です。
郵便番号から住所を自動で変換する方法
郵便番号を入力することで、自動で住所に変換し、『都道府県』と『市区町村』と『それ以外の住所』に分けて管理することも可能です。
まず、『【エクセル】郵便番号から住所、住所から郵便番号を自動で入力する２つの方法を解説！』の記事を参考に、日本郵便のWEBサイトからデータをダウンロードし、郵便番号&住所リストを別のワークシートに準備します。
引用元：日本郵便㈱ 郵便番号データダウンロード
https://www.post.japanpost.jp/zipcode/download.html
今回の記事で紹介した住所分解シートの一番左端に「郵便番号」列を挿入し、住所は「郵便番号&住所リスト」を使ってVLOOKUP関数から自動で入力させれば完成です。
郵便番号と住所の変換に関しては、以下の記事で詳しく紹介していますのでぜひ参考にしてみて下さい。
