住所から郵便番号を抽出したいケースがあると思います。
その際にどのように郵便番号を抽出するかの方法を解説いたします。
①郵便番号を検索するためのデータの準備
まずは郵便番号を以下のURLよりダウンロードしましょう。
郵便番号リストを以下の手順で加工します。
A列に結合住所を作成します。
=C2&D2&E2
”&”はセルをつなぐための記号です。
A列に”=C2&D2&E2”を挿入することで以下の２行目の通り『和歌山県和歌山市葵町』が生成されます。
これで郵便番号を検索するためのデータは完了です。
②次に郵便番号を抽出するための元データの作成方法
B列は検索したい住所です。
C列は検索するために今回作成した住所
D列は検索して抽出した郵便番号
ではC列の作業を以下の通りすすめましょう
=LEFT(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(B2)&1234567890))-1)
上記の青字は上記B列、つまり検索したい対象セルです。
LEFT関数
指定した文字列を、左からカウントして指定した文字数分取得する関数です。
例えば=left(b2,12)とすれば”和歌山県和歌山市小松原通”を抽出します。
しかし、住所により文字数が違いますよね？それで必要になる関数が次です。
MIN関数
番地の数字のなかで一番最初(MIN)に出てくる数字は何文字目？です。
”和歌山県和歌山市小松原通1”の例だと13文字目となります。
FIND関数
該当する文字を探すための関数です。
今回は数字を探したいので{0,1,2,3,4,5,6,7,8,9}となります。
ASC関数
全角文字を半角にするための関数です。
最後の"-1"は上記の例で説明すると
MIN関数で、一番最初に数字が出てくる文字は何番目ですか？と指示しました。
”和歌山県和歌山市小松原通1”
13番目に”1”が出てきましたよね？
しかし13番目だと”和歌山県和歌山市小松原通1”まで抽出してしまいます。
従ってマイナスすることで12文字を抽出しなさいと指示してるのです。
③該当する郵便番号を抽出
=VLOOKUP("*"&C2&"*",KEN_ALL_ROME!$A$2:$B$1603,2,FALSE)
上記の式をC列、つまり郵便番号を抽出したい列に貼り付けてください。
VLOOKUP関数
指定の文字(C列：”和歌山県和歌山市小松原通”)から該当する情報を抽出したい場合に使います。
今回の場合は上記セルBの住所を元を①で作成した郵便番号リストの結合住所より該当する郵便番号を抽出するということになります。
=vlookup("*"&C2&"*",
検索したい文字(セル)をここに設定します。ここではC列の2行目なので”C2”となります。
また、*=ワイルドカードで検索文字の前後をくくりたいため、それをつなぐために
C2を”&”でつなぎます。関数内においてテキストとセルをつなぐ際に使います。
ワイルドカードの意味は以下の通りです。
>>この意味は【*和歌山県和歌山市小松原通*】なります。
*はワイルドカードと呼びまして、文字の前にワイルドカードが来ると、
日本和歌山県和歌山市という住所があっても前の”日本”は考慮されません。
逆に後ろにワイルドカードがあると小松原通りとなっても”り”は考慮されません。
従って該当する範囲が広げることが可能となります。
=VLOOKUP("*"&C2&"*",KEN_ALL_ROME!$A$2:$B$1603
カンマの次は検索範囲を指定します。
①郵便番号を検索するためのデータの準備したデータのシート名が”KEN_ALL_ROME”
シート名を意味する記号が”！”となります。
次に何列目の何行目から何列目の何行目という範囲を指定します。
この場合はA列の２行目からB列の1603行目が検索範囲ということになります。
また、列と行の前に付いている”$”は絶対値といい、行と列を固定するための記号となります。
=VLOOKUP("*"&C2&"*",KEN_ALL_ROME!$A$2:$B$1603,2
範囲の次の”２”は検索対象から何列目の値を抽出するかを指示します。
今回の場合は郵便番号の列は検索対象の隣の列のため”２”となります。
=VLOOKUP("*"&C2&"*",KEN_ALL_ROME!$A$2:$B$1603,2,FALSE)
大抵は”FALSE”と覚えておいてください。意味は以下の通りです。
FALSE：完全一致
TRUE：近似一致
これで抽出したかった郵便番号が抽出されたと思います。
どうですか？上手く抽出できましたか？
エクセルって本当に便利ですよね。
Komentarze