記事の詳細

こんにちは

火曜日ブログ担当の 田中 パラダイス です。
断固としてお伝えしておきますが、

頭の中が”パラダイス”なのかというとそういうわけではありません!!

まぁこうなった経緯は話せば長くなるので割愛します。

今日は先日の唐澤社長のブログ

「瞬間解決!エクセル住所録の郵便番号ハイフンありまたはなしで統一する方法」

haihun-toitu

に対抗し、

「瞬間解決!住所の番地手前までの情報を取得する方法!」

です。

※例で住所を使っていますが、実在する住所との関係はございません。

こんなことありませんか?

このデータどの地域の人が多く来たか知りたいから、市でわけるのではなくてもっと詳細な地域別に出して~♪
あとグラフ化もよろしく~♪(・▽・)

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_01

はい。了解です(^^♪
(フィルターかけれて、抜き出せば楽勝♪)

 

 

 

 

……

 

ところがっ…!!

 

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_02

番地が邪魔で抜き出せねぇ!!!(;゚Д゚)

全部番地を取るとかマジ無理なんですけど~っ!!(;・∀・)

 

中央区中央が2件
中央区鹿沼台が2件
中央区上溝が3件

と、このデータは目視上で確認できますけど、実際はそんな簡単ではありませんよね。
やるにしても、いちいちフィルタもしくは並べ替えして抜き出して~なんてめんどくさいですよね。
しかも後々グラフにするとなると更にめんどくさいです。

なので、抜き出す関数のご紹介です。

初心者の人も、別に式詳しくないし…の人も

 

コピペでOK(^^♪

番地手前までを抜き出す関数はこれ!!

=LEFT(セル,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(セル)&1234567890))-1)

初めての方用に手順を↓で説明します。

 

1.まず、上の関数「=から-1)」までコピーをします。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_12

2.Excel表の貼り付けをしたい場所に貼り付けをします。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_10

3.「セル」となっている部分を調べたい住所のセル値に置き換えてあげます。(例:セル→D3など)

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_13

 

4.Enterを押せばOK!!

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_14

 

5.あとは複数行データがある場合

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_15

 

6.完了!!

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_16

 

はい。できました!!(^^)/
この関数は覚えなくても、

「インクループのホームページに行けば確か方法が載っていたはず…」

と思い出して頂ければ大丈夫です♪

関数の解説

今回使っている関数は4種類の組み合わせです。

  • LEFT
  • MIN
  • FIND
  • ASC

です。

 

LEFT関数

これは指定した文字列を、左からカウントして指定した文字数分取得する関数です。
例えば、こんな感じです。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_03

 

MIN関数

MIN関数は指定したセル内の最小値を探し出す関数です。

わかりやすいように、年齢部分を使ってみました。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_04

 

FIND関数

FIND関数はよく使いますので、覚えておいて損はない関数です。
単体というより、今回のように組み合わせて使うケースが多いと思います。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_05

 

ASC関数

この関数は半角に直す関数です。

たまに住所や電話番号とか番号が全角、半角混在しているともやもやしますよね。
それを一気に直してくれるのがこの関数です。
ちなみに全角はASCの部分をJISに変えてあげると全角になります。

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_06

この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)」って何をやってる?

簡単になにをやっているかといいますと…
下の画像をもとに説明をしていきます(^^♪

%e3%81%93%e3%81%ae%e3%83%87%e3%83%bc%e3%82%bf_08

①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文字前)

とすることで、番地手前までの住所が取れるようになります(^^♪

 

終わりに

いかがでしたでしょうか?

わかりやすく書いたつもりですが、逆にわかりづらくなってしまっていたら申し訳ございません。

この関数はアンケート調査等に役立つほか、また個人情報の一つである住所をこれで、伏せることもできます。
使い方は色々です。

是非使ってみてください(^^♪

ここまで読んでいただいた方はわかるかと思いますが、〇丁目のところが漢数字だった場合

残念ながらこの関数は使えません!
その際はまず、漢数字の部分を

数字に直して

から行うことをお勧めします。

Thank you for reading my blog.
See you next time.
have a nice day!!

この記事であなたの課題を解決することができましたか?

疑問点があったり、解決できなかったことがありましたら、お気軽にご相談してください。

The following two tabs change content below.
田中 優樹

田中 優樹

価値あるコンテンツ制作部インクループ株式会社
助成金、社内事務、イベント運営と、製作部隊ではなく裏方作業担当の田中です(^^♪ 助成金は「自分達で調べ申請する」シリーズで作成しています。 やりたい事色々。相模原を盛り上げたい!世界と日本をつなぎたい!!以上!!

ほかの投稿も読んでみませんか?

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

特集

エクセルトラブルと解決策のまとめ アクセス数を増加させるための方法まとめ 初心者向けWordPressの設定方法まとめ

AD

お知らせ

平成28年度補正予算 小規模事業者持続化補助金
ページ上部へ戻る