Excel|XLOOKUPの複数条件によるデータ抽出 

s006|テキストから住所を都道府県・市町村に分割する|Excelのデータ抽出方法

住所を都道府県・市町村・番地に分割するには、都道府県をLEFT+MID関数、番地を半角を抽出する数式で抽出した後、都道府県と番地の文字数以外の残りの文字数を使ってMID+LEN関数で市区町村を抽出する。

目的

その他の基準で分割抽出

使用する関数

LEFT関数、MID関数、LEN関数、RIGHT関数、LENB関数、ASC関数

目次

例題1|住所の「都道府県」を抽出する

  1. 「都道府県」を求めるセル(B3セル)を選択し、「=LEFT(」と入力する。
  2. [文字列]…「住所」のセルを選択する。
  3. [文字数]…「都道府県」の文字数を求める数式「(MID(A3, 4, 1)=“県”)+3」を入力して、「Enter」キーで数式を確定する。
  4. 数式を必要な分だけ複写する。

【数式】=LEFT(A3, (MID(A3, 4, 1)=”県”)+3)

使用するExcel関数

LEFT、MID

数式の解説

「(MID(A3, 4, 1)=“県”」の数式は、A3セルの「住所」の左端より4文字目から1文字取り出した文字列が「県」の場合は「TRUE(1)」、異なる場合は「FALSE(0)」を求める。つまり、「=LEFT(A3, (MID(A3, 4, 1)=“県”)+3)」の数式を作成すると、「県」を含む「住所」は4文字、含まない「住所」は3文字分、「住所」の左端から抽出される。結果、「住所」から「都道府県」が抽出される。

[新方式]スピル で表現!

今まで : =LEFT(A3, (MID(A3, 4, 1)=”県”)+3)
      ↓
スピル : =LEFT(A3:A5, (MID(A3:A5, 4, 1)=”県”)+3)

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx

例題2|住所の「番地」を抽出する

  1. 「番地」を求めるセル(D3セル)を選択し、「=RIGHT(」と入力する。
  2. [文字列]…「住所」のセルを選択する。
  3. [文字数]…「番地」の文字数を求める数式「LEN(A3)*2-LENB(ASC(A3))」を入力して、「Enter」キーで数式を確定する。
  4. 数式を必要な分だけ複写する。

    【数式】=RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))
使用するExcel関数

RIGHT、LEN、LENB、ASC

数式の解説

「LEN(A3)*2-LENB(ASC(A3))」の数式は、A3セルの「住所」から半角の文字数、つまり「番地」の文字数を求める。この文字数をRIGHT関数の引数の[文字数]に指定して、「=RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))」と数式を作成すると、「住所」の右端から「番地」が抽出される。

[新方式] スピル で表現!

今まで : =RIGHT(A3, LEN(A3)*2-LENB(ASC(A3)))
      ↓
スピル : =RIGHT(A3:A5, LEN(A3:A5)*2-LENB(ASC(A3:A5)))

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx

例題3|住所の「市区町村」を抽出する

  1. 「市区町村」を求めるセル(C3セル)を選択し、「=MID(」と入力する。
  2. [文字列]…「住所」のセルを選択する。
  3. [開始位置]…「市区町村」の最初の位置を求める数式「LEN(B3)+1」を入力する。
  4. [文字数]…「市区町村」の文字数を求める数式「LEN(A3)-LEN(B3&D3)」を入力して、「Enter」キーで数式を確定する。
  5. 数式を必要な分だけ複写する。

【数式】=MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))

使用するExcel関数

MID、LEN

数式の解説

「LEN(B3)+1, LEN(A3)-LEN(B3&D3)」の数式は、A3セルの「住所」の「市区町村」の文字数を求める。この文字数をMID関数の引数の[文字数]に指定して、「=MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))」と数式を作成すると、B3セルの「都道府県」の文字数+1の位置から「市区町村」の文字数分が抽出され、結果、「住所」から「市区町村」が抽出される。

[新方式]スピル で表現!

今まで : =MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3&D3))
      ↓
スピル : =MID(A3:A5, LEN(B3:B5)+1, LEN(A3:A5)-LEN(B3:B5&D3:D5))

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-sample3.xlsx

応用1|

住所の番地が「●丁目」や「●番地」など文字付きの場合は、手順のように半角の文字数をもとに抽出できないので、RIGHT+LEN+MIN+FIND+ASC関数の数式を作成して抽出する。

  1. 「番地」を求めるセル(D3セル)を選択し、「=RIGHT(」と入力する。
  2. 「住所」のセル、「番地」の文字数を求める数式「ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1)」を入力して、「Enter」キーで数式を確定する。
  3. 数式を必要なだけ複写すると、それぞれの「住所」から「番地」が抽出される。

【数式】=RIGHT(A3, ASC(LEN(A3))-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, ASC(A3)&1234567890)-1))

使用するExcel関数

RIGHT、ASC、LEN、MIN、FIND、ASC

Excelデータダウンロード

以下のリンクをクリックするか右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

コメント

コメントする

目次