記事の詳細
瞬間解決!エクセルで住所の番地手前までの情報を取得する方法!統計データを取る、個人情報データを伏せるのに役立つ!
こんにちは
火曜日ブログ担当の 田中 パラダイス です。
断固としてお伝えしておきますが、
頭の中が”パラダイス”なのかというとそういうわけではありません!!
まぁこうなった経緯は話せば長くなるので割愛します。
今日は先日の唐澤社長のブログ
「瞬間解決!エクセル住所録の郵便番号ハイフンありまたはなしで統一する方法」
に対抗し、
「瞬間解決!住所の番地手前までの情報を取得する方法!」
です。
※例で住所を使っていますが、実在する住所との関係はございません。
今回のブログの見出しはコチラです!
こんなことありませんか?
このデータどの地域の人が多く来たか知りたいから、市でわけるのではなくてもっと詳細な地域別に出して~♪
あとグラフ化もよろしく~♪(・▽・)
はい。了解です(^^♪
(フィルターかけれて、抜き出せば楽勝♪)
…
……
ところがっ…!!
番地が邪魔で抜き出せねぇ!!!(;゚Д゚)
全部番地を取るとかマジ無理なんですけど~っ!!(;・∀・)
中央区中央が2件
中央区鹿沼台が2件
中央区上溝が3件
と、このデータは目視上で確認できますけど、実際はそんな簡単ではありませんよね。
やるにしても、いちいちフィルタもしくは並べ替えして抜き出して~なんてめんどくさいですよね。
しかも後々グラフにするとなると更にめんどくさいです。
なので、抜き出す関数のご紹介です。
初心者の人も、別に式詳しくないし…の人も
コピペでOK(^^♪
番地手前までを抜き出す関数はこれ!!
=LEFT(セル,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(セル)&1234567890))-1)
※こちらの方法ですとカタカナの濁点、半濁点(ガギグゲゴ、パピプペポ等)を含む住所の場合、正しく住所を抜き出すことができません。
例:南アルプス市等
南アルプス市等を含む場合は下記の数式を使用すると正しく抜き出すことができます。
=LEFT(JIS(セル),MIN(FIND({“0”,”1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”0123456789”))-1)
※コメントをいただきました「匿名さま」ありがとうございました。(2020年4月3日追記)
初めての方用に手順を↓で説明します。
1.まず、上の関数「=から-1)」までコピーをします。
2.Excel表の貼り付けをしたい場所に貼り付けをします。
3.「セル」となっている部分を調べたい住所のセル値に置き換えてあげます。(例:セル→D3など)
4.Enterを押せばOK!!
5.あとは複数行データがある場合
6.完了!!
はい。できました!!(^^)/
この関数は覚えなくても、
「インクループのホームページに行けば確か方法が載っていたはず…」
と思い出して頂ければ大丈夫です♪
関数の解説
今回使っている関数は4種類の組み合わせです。
- LEFT
- MIN
- FIND
- ASC
です。
LEFT関数
これは指定した文字列を、左からカウントして指定した文字数分取得する関数です。
例えば、こんな感じです。
MIN関数
MIN関数は指定したセル内の最小値を探し出す関数です。
わかりやすいように、年齢部分を使ってみました。
FIND関数
FIND関数はよく使いますので、覚えておいて損はない関数です。
単体というより、今回のように組み合わせて使うケースが多いと思います。
ASC関数
この関数は半角に直す関数です。
たまに住所や電話番号とか番号が全角、半角混在しているともやもやしますよね。
それを一気に直してくれるのがこの関数です。
ちなみに全角はASCの部分をJISに変えてあげると全角になります。
この4つの関数を使うと、番地を除いた住所を抜き出せるのが不思議ですよね(^^
ではここからは、
=LEFT(セル,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(セル)&1234567890))-1)
↑の関数が何をやっているか説明していきたいと思います。
「=LEFT(セル,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(セル)&1234567890))-1)」って何をやってる?
簡単になにをやっているかといいますと…
下の画像をもとに説明をしていきます(^^♪
①FIND関数でのエラー回避
ASC関数で住所を半角に統一し住所の後ろに「1234567890」を追加しています。
追加する理由は、FIND関数を使う際に指定した数字(文字)にヒットしないと
「#VALUE!」
というエラーになってしまう為です。
番地は数字ですよね。
なので、数字で0~9のどの数字でもヒットする形にします。
②FIND関数でヒットする文字数を判定
続いてFind関数の頭にある{0,1,2,3,4,5,6,7,8,9}ですが、
「0」がヒットするのは何文字目?「1」がヒットするのは何文字目…「9」がヒットするのは何文字目?
というのを計算しています。
No1の住所「神奈川県相模原市中央区中央3-12-3相模原商工会館本館4階1234567890」で言いますと、
「0、1、2、3、4、5、6、7、8、9」
がそれぞれヒットするのは…
「40、16、17、14、29、35、36、37、38、39」
文字目です。
実際に数えてみてください(^^♪
数えるときは左からです。
③番地の特定
そして、FINDでそれぞれ該当する値をMIN関数で最小値を探しています。
その理由は
初めて数字が出てくる = 番地
だからです。
No1の住所でいうところの3ですね(^^♪
④番地の手前までの文字数を特定
-1をする理由はもうおわかりかと思いますが、(^^♪
このままですと番地の最初の数字
「〇丁目」の部分が含まれてしまう
ので-1をします。
⑤番地を除いた住所を特定
最後に
LEFT関数で(住所,〇丁目の1文字前)
とすることで、番地手前までの住所が取れるようになります(^^♪
終わりに
いかがでしたでしょうか?
わかりやすく書いたつもりですが、逆にわかりづらくなってしまっていたら申し訳ございません。
この関数はアンケート調査等に役立つほか、また個人情報の一つである住所をこれで、伏せることもできます。
使い方は色々です。
是非使ってみてください(^^♪
ここまで読んでいただいた方はわかるかと思いますが、〇丁目のところが漢数字だった場合
残念ながらこの関数は使えません!
その際はまず、漢数字の部分を
数字に直して
から行うことをお勧めします。
谷さまより、住所に漢数字が含まれている場合の数式を教えていただきました。
谷さまありがとうございます。
=LEFT(JIS(セル),MIN(FIND({“0”,”1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},IF(ISERROR(FIND(“丁目”,セル)),JIS(セル),JIS(SUBSTITUTE(セル,MID(セル,FIND(“丁目”,セル)-1,3),IFERROR(FIND(LEFT(MID(セル,FIND(“丁目”,セル)-1,3),1),”一二三四五六七八九十”),FIND(LEFT(MID(セル,FIND(“丁目”,セル)-1,3),1),”0123456789″))&”-“)))&”1234567890”))-1)
(2020年4月3日追記)
Thank you for reading my blog.
See you next time.
have a nice day!!
【PR】相模原市産業会館パソコン教室
相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。
コメント
この記事へのトラックバックはありません。
この関数ですと、カタカナの濁点が入った地名の時に失敗してしまいます。
「ガ」→ASC関数「カ ゛」で2文字カウントされてしまうことが原因
匿名さま
初めまして。
このブログの作成を担当しました田中と申します。
コメントをいただきましてありがとうございます。
匿名さまがおっしゃるとおり、カタカナの濁点が入った地名の時にうまく住所が抜き出せなくなることを確認いたしました。
原因もご指摘いただきました通りでした。
ASC関数をJIS関数に戻してなど、様々な方法を試してみましたが関数だけでは難しそうです。
今回はブログに注意事項を記載して対応をさせていただければと思います。
もし他の良い方法などが見つかりましたら改めてブログに記載をいたします。
改めましてコメントをいただき誠にありがとうございました。
=LEFT(JIS(セル),MIN(FIND({“0”,”1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”1234567890”))-1)
JIS関数を2つ使うことと全角の数字を検索することで、南アルプス市でも南アルプス市でも番地の手前までを抜き出せるようになります。
ただし、地名の一番町などが1番町、1番町となっていたら無理ですが。
=LEFT(JIS(セル),MIN(FIND({“0”,”1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},IF(ISERROR(FIND(“丁目”,セル)),JIS(セル),JIS(SUBSTITUTE(セル,MID(セル,FIND(“丁目”,セル)-1,3),IFERROR(FIND(LEFT(MID(セル,FIND(“丁目”,セル)-1,3),1),”一二三四五六七八九十”),FIND(LEFT(MID(セル,FIND(“丁目”,セル)-1,3),1),”0123456789″))&”-“)))&”1234567890”))-1)
抜き出したものは全角になってしまいますが、全角の文字列に変換してから数字を検索。
漢数字(ただし、〇丁目の漢数字のみ)への対応。
数字の〇丁目 〇丁目で抜き出し
漢数字の〇丁目 一丁目から十丁目までであれば〇丁目で抜き出し
〇丁目以前の数字 その数字で抜き出し
〇丁目以降の数字 〇丁目が抜き出し対象であれば〇丁目で、そうでなければその数字で抜き出し
匿名さま
こんばんは
インクループの田中です。
コメントありがとうございます。
=LEFT(JIS(セル),MIN(FIND({“0”,”1”,”2”,”3”,”4”,”5”,”6”,”7”,”8”,”9”},JIS(セル)&”1234567890”))-1)
こちらの関数で半角の濁音、半濁音が入っても取得できることが確認できました。
私もJIS関数を使用してみましたが、うまく取得できませんでした。
無事解決でき、ありがとうございました。
山さま
コメントありがとうございます。
住所の漢数字一丁目などで入力されていた場合の対応も教えていただきありがとうございました。
関数の解読に時間がかかりましたが、住所に「漢数字が入っている場合」の対応「数字が入っている場合」の対応両方とも考慮されており、とても勉強になりました。
ありがとうございました。