Excel

【Excel関数】住所情報から都道府県を抽出する方法

"東京都千代田区1-1-1"のように連結した住所情報から都道府県だけを抽出する処理をまとめました。都道府県を抽出できれば、それ以降の市郡区住所も別データとして分割することができます。

1.やり方

枠内の=(イコール)からコピーして[住所]の箇所を実際のセルの値(A2 など)に置き換えればそのまま使えます。

=LEFT([住所],IFERROR(SEARCH("道",[住所]),IFERROR(SEARCH("府",[住所]),IFERROR(SEARCH("都",[住所]),SEARCH("県",[住所])))))

 

2.解説

それでは考え方と使用した関数について解説してきます。

 2-1.考え方

  STEP1.都道府県の桁を判定

"都"、"道"、"府"、"県"の文字が何桁目に登場するか判定し、先頭からその桁までの文字列を抽出しています。

ここでポイントとなるのは"都"、"道"、"府"、"県"を判定する順番です。これら都道府県の文字が登場する桁を1文字ずつ順番に判定しますがが、"都"よりも"府"を先に判定する必要があります。

逆に"府"よりも先に"都"の桁を数えてしまうと、京都府***が京"都"と判定され、東京都と並ぶ"都"になってしまいます。

  STEP2.エラー処理

[住所]の中に判定している文字列(例えば"道")が含まれない場合もあり、そのような時にはエラーとなってしまいます。そこで桁数の判定においてIFERROR関数を用いてエラー処理を行います。

  STEP3.抽出

都道府県の登場する桁数が判定できたらLEFT関数を用いて、その桁数までを抽出します。

 2-2.SEARCH関数

今回の処理では、[住所]データの中に"都"、"道"、"府"、"県"が何桁目に登場するか判定するためSEARCH関数を使用しています。

=SEARCH([検索文字列],[対象セル],[開始桁数])

[対象セル]の中に、[検索文字列]が何桁目に登場するかを判定する関数です。[開始桁数]は省略可で、省略した場合は1(先頭からの桁数)となります。具体的には次のようなイメージです。

 2-3.IFERROR関数

今回の処理では[住所]データの中に都道府県のそれぞれが含まれない場合がありますので、IFERRORでエラー処理をしています。IFERROR関数のフォーマットは次の通り。

=IFERROR([条件],[真の場合の返す値],[偽の場合の返す値])

具体的には
=IFERROR(SERCH("道",[住所情報]),"")
という処理の仕方をしています。

都道府県の桁数判定において、北海道以外の住所は"道"が含まれない可能性もありエラーとなります。そのため、都道府県の桁数判定の都度エラー処理を行う必要があります。

"道"の桁数判定
=IFERROR(SAERCH("道",[住所情報]),①)

①に"府"の桁数判定
=IFERROR(SAERCH("府",[住所情報]),②)
を行い、
=IFERROR(SAERCH("道",[住所情報]),IFERROR(SAERCH("府",[住所情報]),②))

②に"都"の桁数判定
=IFERROR(SAERCH("都",[住所情報]),③)
を行い、
=IFERROR(SAERCH("道",[住所情報]),IFERROR(SAERCH("府",[住所情報]),=IFERROR(SAERCH("都",[住所情報]),③)))

残すは"県"の判定で、都道府県すべてが登場したことになるので③に"県"の桁数判定
=SAERCH("都",[住所情報])
を行い
=IFERROR(SAERCH("道",[住所情報]),IFERROR(SAERCH("府",[住所情報]),=IFERROR(SAERCH("都",[住所情報]),SAERCH("都",[住所情報]))))
となっています。

スポンサーリンク

3.応用編:市郡区以下を抽出する

都道府県を抽出できれば、それ以外の住所情報を抽出することも可能です。文字列の途中からの抽出ですのでMID関数を用いて、都道府県の次の桁から最後までの文字列を抽出することで実現できます。

 3-1.都道府県を用いて間接処理

=MID([住所],LEN([都道府県])+1,LEN([住所])-LEN([都道府県]))

ポイントは2つ目の引数で+1している点。都道府県の次の桁から、住所情報の都道府県を除いた文字数分の抽出をしています。

 3-2.都道府県を用いず直接処理

3-1で紹介した処理で[都道府県]の2箇所を、冒頭に紹介した処理に置き換えればよいです。処理としては長文になるので、他の人が解読する場合にとても難解となるためオススメしませんが、紹介しておきます。

=MID([住所],LEN(LEFT([住所],IFERROR(SEARCH("道",[住所]),IFERROR(SEARCH("府",[住所]),IFERROR(SEARCH("都",[住所]),SEARCH("県",[住所]))))))+1,LEN([住所])-LEN(LEFT([住所],IFERROR(SEARCH("道",[住所]),IFERROR(SEARCH("府",[住所]),IFERROR(SEARCH("都",[住所]),SEARCH("県",[住所])))))))

4.最後に

今回紹介した処理は、文字列から切り取る長さを変動させる処理でした。SEARCH関数はLEFT関数やMID関数の引数(桁数)として登場したもので、今回の処理の本質は実はLEFT関数やMID関数といった単なる文字列の切り取りです。

単純な関数でも引数に関数を組み合わせることで、今回のような一見高度と思える処理でも簡単な関数の組み合わせで実現できるようになります。

この記事が生産性向上につながり、成長の一助になれたら「いいねっ!」

スポンサーリンク
スポンサーリンク

関連記事

  1. vlookupサムネ

    Excel

    【Excel関数】応用の前にまずは基本!vlookupの使い方

    vlookup関数は、別シートや別表のデータを参照できる関数です。でも…

  2. 折れ線グラフ点線サムネ (2)

    Excel

    【Excel】実線の一部を点線に替える「折れ線グラフ」の作り方

    実績と将来予測をグラフで示したい場合など、一本の折れ線グラフの中を実線…

  3. vlookupErrorサムネ

    Excel

    【Excel関数】vlookupがエラーで使えない!原因と回避方法7パターン

    vlookup関数が#N/A、#NAME?などよくエラーになってしまい…

  4. Excel

    【Excel】見やすいきれいな折れ線グラフの作り方

    今回のビズ技は、基本中の基本。グラフを見やすくするコツを紹介します。基…

  5. サムネイル折線

    Excel

    【Excel】折れ線グラフを塗りつぶして見やすくする作り方

    サムネイルのグラフのような、折れ線グラフを塗りつぶして効果的に魅せる方…

  6. Excel

    【Excel】重複を抽出して削除する方法(Countif 他全4種)

    "重複チェック"や"重複削除"の方法は、ひとつではありません。その方法…

スポンサーリンク




特集

  1. 折れ線グラフ特集1
  2. 特集働き方改革サムネ
  3. SC特集サムネ

スポンサーリンク




仕事効率アップ系記事

  1. vlookupErrorサムネ
  2. Keyboad
  3. カレンダー
  4. サムネ-揃える
  5. ブレスト

スキルアップ系記事

  1. enphasis_top
  2. 3CPS
  3. サムネイル折線

スポンサーリンク




ピックアップ記事

  1. サムネイル
  2. プレミアムフライデー
  3. 愚痴-サムネ
  4. analysis
  5. 宝くじ
  1. SC特集サムネ

    PC

    【特集】ショートカットキーで仕事効率化!働き方改革の小さな一歩👣…
  2. 折れ線グラフ特集1

    PC

    【特集】"使い方"では足りない!「折れ線グラフ」の"使いこなし方"
  3. 特集働き方改革サムネ

    Column

    【特集】「働き方改革」を独り歩きさせない!「働き方改革」の実現に向けて
PAGE TOP