[[20041008223005]] 『郵便番号から、住所を複数セルへ振り分ける方法』(コミキ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『郵便番号から、住所を複数セルへ振り分ける方法』(コミキ)

はじめて、質問させて頂きます。
過去ログに郵便番号→住所変換の方法はありましたが、変換の際にその住所を@都道府県A市郡B町村C町域 別で各セルに分かれるようにする方法がありましたら、教えて頂けないでしょうか?

例えば セルA1に(郵便番号)701-2213 を打ち、郵便番号変換を行うと
セル B1に岡山県  C1に赤磐郡 D1に赤坂町 E1に今井
というように分けて入るようにしたいのです。
是非是非よろしくお願い致します。  


 日本中の住所が対象であれば困難なことだと思いますが、
 運良く対象の住所が 郡市や町村の文字が必ず住所に含まれているおり、
 町田市、市川市などのような市や町が名前に含まれていないなどの好条件で
 あるのなら、つまり、提示された住所のように郡や市および町村で区切ること
 ができるのなら、以下のような式でどうでしょうか?
A1セルに住所が入力されているとします。
都道府県を取り出す
C1セル=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))
市郡名を取り出す
D1セル=IF(ISERROR(FIND("市",SUBSTITUTE(A1,C1,""))),LEFT(SUBSTITUTE(A1,C1,""),FIND("郡",SUBSTITUTE(A1,C1,""))),LEFT(SUBSTITUTE(A1,C1,""),FIND("市",SUBSTITUTE(A1,C1,""))))
町村名を取り出す
E1セル=IF(ISERROR(FIND("町",SUBSTITUTE(A1,C1&D1,""))),LEFT(SUBSTITUTE(A1,C1&D1,""),FIND("村",SUBSTITUTE(A1,C1&D1,""))),LEFT(SUBSTITUTE(A1,C1&D1,""),FIND("町",SUBSTITUTE(A1,C1&D1,""))))
残りの文字列
F1セル=SUBSTITUTE(A1,C1&D1&E1,"")
  (Hatch)


Hatchさん
お忙しい中、丁寧に教えていただいてありがとうございました!!
とってもとっても助かりました。

教えていただいたとおりにやったのですが・・・少し不都合が出てしまったので、もう少し教えていただけないでしょうか・

<質問1> E1セルに 町村以外に区も取りいれたいのですが、どのように追加したらよいでょうか?
(自分で追加して”区”を入れてやってみたのですが、カッコがあっていないのか、色々変えてみてもエラーになってしまいました)

<質問2>「栃木県 大田原市 末広」のように、町村区がない場合 E1&F1のセルに#VALUEが表示され、末広が入りませんでした。
「E1セルに空欄」「F1セルに末広」が入るようにできないでしょうか?
 
<質問3>今、郵便番号入力後 ツール→郵便番号変換を選択し「郵便番号変換ウィザード」という画面が出てきたら、住所を出力するセルを設定すると、住所が出てくるという手順をしています・・・
が、これは郵便番号が入力してあるセルにのみ有効で、後々郵便番号を変更したり空欄に記入した際は、住所セルには何も出てこないので、再度同じようにセルを設定しなくてはいけません。

郵便番号を入力すると、自動で住所がセルに排出できるように固定する方法はあるのでしょうか??もしあったら教えていただけないでしょう・・・・    

日本中の住所が対象で、市川市や四日市市などがあって困っています。
こういった住所はあきらめて、手で再入力しようと思ってます。

沢山、質問してしまって、本当に申し訳なく思っていますが、
是非是非、よろしくお願いします。


 過去に同じような質問がありましたが、参考になりますでしょうか。
[[20040831081709]]『住所セルの分割』(ピヨ)
 (川野鮎太郎)


 追加の質問に関しては(川野鮎太郎)さんが提示されているスレッドを
 参考にユーザー定義関数やVBAを書いた方が良さそうな気がいたします。
  
 実は、岡山県のそれもローカルな地名が提示されていましたので、
 ごく限られた住所が対象では?との淡い期待のもと数式を提示しました。
 そして、念のため『提示された住所のように郡や市および町村で区切ること
 ができるのなら』と前提条件を書いた次第です。←言い訳です(^_^;)

 >郵便番号を入力すると、自動で住所がセルに排出できるように固定する方法
 IMEやATOKの郵便番号変換を使ってB1セルに郵便番号を入力して住所に変換すると、
 ふりがなとして郵便番号が記録されますので、A1セルに=PHONETIC(B1) とすると
 郵便番号が取り出せます。
 (住所の並べ替えを50音順にするなどふりがなを使う場合にはこの方法はダメかと思います)
  (Hatch)


 これで勘弁なりませんか(^_^;)
	 A	            B	    C	     D       	   E
 1 滋賀県八日市市赤池町4番地	  滋賀県  八日市市  赤池町   	  4番地
 2 香川県大川郡引田町7番地	  香川県  大川郡   引田町   	  7番地
 3 栃木県大田原市末広   	  栃木県  大田原市         	   末広
 4 岡山県赤磐郡赤坂町今井  	  岡山県  赤磐郡   赤坂町    	   今井
 5 福島県郡山市朝日一丁目23番7号	  福島県  郡山市            	   朝日一丁目23番7号
 6 福岡県福岡市東区箱崎2丁目18番	  福岡県  福岡市東区         	   箱崎2丁目18番
 7 東京都港区虎ノ門1丁目3番2号	  東京都  港区                	   虎ノ門1丁目3番2号
 8 大阪府吹田市内本町1丁目 	  大阪府  吹田市   内本町    	   1丁目
 9 神奈川県横浜市中区桜木町1-1	  神奈川県 横浜市中区 桜木町 	   1-1
 10 京都府京都市右京区常盤段ノ上町15   京都府  京都市右京区 常盤段ノ上町 15
 11 北海道札幌市中央区北1条西2丁目	   北海道    札幌市中央区       	   北1条西2丁目

 B1セル=LEFT(A1,IF(ISERR(FIND(MID(A1,3,1),"道府都")),FIND("県",A1),3))	
 C1セル=LEFT(SUBSTITUTE(A1,B1,""),IF(ISERR(FIND("区",SUBSTITUTE(A1,B1,""))),IF(ISERR(FIND("市",SUBSTITUTE(A1,B1,""))),FIND("郡",SUBSTITUTE(A1,B1,"")),FIND("@",SUBSTITUTE(G1,"市","@",LEN(LEFT(G1,4))-LEN(SUBSTITUTE(LEFT(G1,4),"市",""))))),FIND("区",SUBSTITUTE(A1,B1,""))))			
 D1セル=LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""),IF(ISERR(FIND("町",SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))),0,FIND("町",SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))))			
 E1セル=MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+1,LEN(A1)-LEN(B1)+LEN(C1)+LEN(D1))	

 まだ駄目なケースが沢山あるんだろうな(^_^;)
 あああ・・・町田町なんかあったら駄目だし_/ ̄|○ il||li
 区分市なんかも駄目だな・・・ププッ (*^m^)o==3
 (川野鮎太郎)


 まったく別の方法ですが、ゆうびんのHPから郵便番号名簿(CSVファイル)を
 ダウンロードすると、初めから都道府県、市区、町村と分かれていますので、それを利用して
 VLOOKUP関数で引っぱってくるなんて方法はどうでしょう? 名簿に調整は必要と思いますが、
 数式はもっとシンプルになると思います。 (純丸)

 郵便番号ダウンロード
http://www.post.japanpost.jp/zipcode/download.html


 おぉ〜 これいいですね!...φ(._.) メモメモ   (Hatch)


Hatchさん、川野鮎太郎さん、純丸さん

色々アドバイス頂きましてありがとうございました。
初めて書き込みして、皆さんの素早い返答と、丁寧な返答に本当に感激してます。
ありがとうございました♪

しかし・・・・
まだ作りたい状態になっていないの、お力を貸してください。

 **********************************************
私としては、下記のように、各セルに分けたいと思っています。

<セルB>福岡県 愛知県 三重県  <セルC> 京都郡 名古屋市 四日市市 <セルD> 勝山町 港区 朝日町 <セルE> 矢山 桜通り

 *************************************

まず、川野鮎太郎さん!!

教えて頂いた、過去ログの「住所セルの分割」でデータを作ってみたのですが、結果はこんな風でした。

 <セルA>北海道積丹郡積丹町幌武意町 三重県四日市市朝日町 
 <セルB>北海道積丹郡  四日市市  
 <セルC>積丹町幌武意町 日市市朝日町

・出来たものは県郡で1つのセル、町とそれ以降で1つのセルになっていました。

・三重県の四日市市に関しては、県名が表記されず、セルCは日市が出てしまいました。

形としては「県市」「町区」分けで少しちがったのですが、マクロ設定をしたことがなかったので、とても勉強になりました。

その後の書き込みいただいた

B1セル=LEFT(A1,IF(ISERR(FIND(MID(A1,3,1),"道府都")),FIND("県",A1),3))

 C1セル=LEFT(SUBSTITUTE(A1,B1,""),IF(ISERR(FIND("区",SUBSTITUTE(A1,B1,""))),IF(ISERR(FIND("市",SUBSTITUTE(A1,B1,""))),FIND("郡",SUBSTITUTE(A1,B1,"")),FIND("@",SUBSTITUTE(G1,"市","@",LEN(LEFT(G1,4))-LEN(SUBSTITUTE(LEFT(G1,4),"市",""))))),FIND("区",SUBSTITUTE(A1,B1,""))))			
 D1セル=LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""),IF(ISERR(FIND("町",SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))),0,FIND("町",SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))))			
 E1セル=MID(A1,LEN(B1)+LEN(C1)+LEN(D1)+1,LEN(A1)-LEN(B1)+LEN(C1)+LEN(D1))	

コチラが私の欲しいスタイルにピッタリなので、やってみたのですが・・・・・私の結果は何故か、以下のようになってしまいました。
 
<セルA>北海道積丹郡積丹町幌武意町 三重県四日市市朝日町      <B>北海道 三重県 <C>積丹郡 #VALUE!  <D> 積丹町 #VALUE <E>幌武意町#VALUE

 ・セルBの都道府県はバッチリでした。
 ・セルCが郡はきちんと入るのですが、「市」がつくものが#VALUE!になってしまい、セルDEも表示されませんでした。

 コピペで貼り、22行始まりだったので A1→A22 B1→B22 G1→G22と、修正したのですが、コレがいけなかったのでしょうか?
 
 ・セルCの「京都市西京区」などの「区」はを、セルEに入れるようにしたいのですが、判定をどのように変更したらよいのでしょか?? 

 この形が一番求めているものに近いので、非常に非常に申し訳ないのですが
 変更の仕方を教えていただけないでしょうか・・・・・・。スミマセン。   

 *****************************************************
純丸さん

教えて頂いたHP、見せていただきました。

最初、各県だったので、何回もDLしないといけないかと思ったら、全国一括という便利なものがあり、早速DL&解凍して、見てみました。
このデータ、何度も追加更新されていて、いつも最新データが使用できるというのが
本当にいいですね♪全くしらないHPだったので、感動しました!!

VLOOKUP関数で引っぱってくる・・・というのをやったことがないので、家にある参考本を引き引き挑戦してみたいと思います。

こちらもまた、市と区が合体して同セルに記載されているので、VLOOKUPでひっぱて来たあとか・・・引っ張りつつ、市と区を別のセルに記載したいのですが、そんなことってできるのでしょうか?

 ********************************************************
詳しいアドバイス頂いてるのに、うまくできず、再び色々質問してしまってスミマセン。

まだまだ道のりは遠いようですがガンバリますので、ご指導よろしくお願いいたします。

 (コミキ)


 おはようございます。
 ご指摘の#VALUEになる件、確認しました。 
 作成中に参照させていたセルの書き換えが完全じゃなく、G1への参照がそのままになってたせいです。(^_^A;
 ご希望のものが出来るかやってみたのですが、そのままの配置では関数が7階層以上のネストになってしまうので無理でした。
 ※作業列として1列だけ使ってよければ以下の方法でどうでしょう。
 今回の式は作業列としてC列を使っています。

 B22セル=LEFT(A22,IF(ISERR(FIND(MID(A22,3,1),"道府都")),FIND("県",A22),3))
 C22セル=SUBSTITUTE(A22,B22,"")
 D22セル=LEFT(C22,IF(ISERR(FIND("市",C22)),IF(ISERR(FIND("郡",C22)),0,FIND("郡",SUBSTITUTE(A22,B22,""))),FIND("@",SUBSTITUTE(C22,"市","@",LEN(LEFT(C22,4))-LEN(SUBSTITUTE(LEFT(C22,4),"市",""))))))
 E22セル=LEFT(SUBSTITUTE(C22,D22,""),IF(ISERR(FIND("区",SUBSTITUTE(C22,D22,""))),IF(ISERR(FIND("町",SUBSTITUTE(C22,D22,""))),0,FIND("町",SUBSTITUTE(C22,D22,""))),FIND("区",SUBSTITUTE(C22,D22,""))))
 F22セル=MID(C22,LEN(D22)+LEN(E22)+1,LEN(C22)-LEN(D22)-LEN(E22))
  
※初めに作業列のC列を挿入して上記の式を全てコピペしてください。
その作業列はあとから列の移動でどこでもお好きな列へ移動して非表示にできます。

 ※これでも不具合のあるパターンが沢山あることと思いますのでその辺はお分かりいただいた上でお試しください。
 (川野鮎太郎)

 本筋から大きくはずれますが・・・こんな方法も有りかな?ということで・・・
 この問題の難しいところは、どのように区市/郡町を取り出すかだと思います。
 そこで、純丸さんに教えて頂いた郵政公社のデータを利用してみました。
 ところが、郵政公社のデータは12万行程もあり、Excelの1シートでは処理できないもののようです。
 2シートに分割して、VLOOKUPで検索する方法でやってみたところ、保存時にメモリ不足?のエラーが出て
 ちょっと不気味な感じがしましたので、早々と断念しました。
 そこで、Accessデータに加工して、検索する方法を利用して作ってみました。
 【Excelでお仕事!】「ユーザーフォームで郵便番号住所変換」
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_050.html
 が、原典です。(Accessファイル作成方法など詳細に説明してあります)
 ここのマクロを利用して、郵政公社のCVSデータをAccessデータに変換して
 (データ量が多いのでかなり時間がかかります)、
 郵便番号から住所データを取り込み、更に住所を修正後、住所の分割をマクロで行なう方法を
 考えてみました。
 住所分割のマクロはB列の住所を選択した部分だけが分割対象としてあります。
 難点は、一度郵便番号を入力して、区市/郡町を取り出しておく必要がある点です(^_^;)
 一応こちらにUpしてみます。(予告なしに、このファイルは削除します)
http://www.geocities.jp/hatch4700/index.html
 Accessデータファイル(KEN_ALL.mdb)を同一フォルダに作成してからでないと動きません。
 (Accessデータファイルへの取り込みマクロは含まれていませんので、【Excelでお仕事!】から
 サンプルをダウンロードしてください。)
 あと、マクロはよく理解していませんので、多々不都合なところがあるとは思いますが、
 私はフォローができませんので悪しからず・・・m(_ _)m
 あっ・・・Accessがなかったら動かないというのも致命的な欠点かも・・・
   自己満足の世界のような・・・(Hatch)
   

川野鮎太郎さん
教えて頂いた新しい方でやってみたら、今度は完璧な形で出来ました!!
めちゃめちゃ嬉しいーーーーです♪
本当に本当にありがとうございました!!

Hatchさん
VLOOKUPをしたことがなかったので、今参考本で勉強して試そうと思っていたら、既に試されて不都合が出たとのことで・・・早い行動に、ビックリしております。
そして、試していただいたことにも、ありがとうございました!!

しかし・・・悲しいことにAccessがないので、教えて頂いたことを試すことができないのです。
ログは読ませて頂き、勉強になりました。

でも、他にも同じように悩んでいる人がいると思うので、他の方でこのログを読んだ方は、私の変わりに是非是非参考にして活用ください!!

住所を振り分けるのに、色々な方法があり、とても勉強になりました!!!

皆さんがたくさんの意見・アドバイスを下さったこと、本当にありがとうございました!!!


うわ〜っ!

あれっ!?
 とんでもたっ!
 いやぁ、私のスペシャル関数を披露しようと目論んどったのに・・・。
 解決したんやったらしゃあないわなぁ。
 えっ?スペシャル関数ってどんなんやてでっか?
 えへへ、ナイショナイショ。
 別名yatarou関数とも言いますけどナ。
 要するに=yatarouと書き込んだら必要な範囲に、例えばこのスレのばやいやと、
 住所を分割したい範囲の先頭行に=yatarouを書き込むと全ての・・・
 え〜い、説明するん面倒や、次の機会にお披露目しま。
    yatarou関数の(弥太郎) アナドルナヨ


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.