Excelで住所から郵便番号のデータを取得したい


今、6万行ほどの住所のデータがあったのですが。
郵便番号のデータを誤って消してしまったのです…。

前回の質問 http://q.hatena.ne.jp/1479632661
使用しました「KEN_ALL_ROME」の使用を考えました。
(A列に郵便番号が入っているので)

例えば、C列にある、

茨城県稲敷郡阿見町岡崎1丁目23-1

という住所は、B列に、

稲敷郡 阿見町

という情報と紐づけできそうで、そこからA列の郵便番号を
取得することもできそうですが。

完全一致としても部分一致としても扱いが難しく、
どう紐づけしてよいのやら想像できません。

何とか、住所から郵便番号を取得する方法がありましたら、
お教えいただけますでしょうか。

よろしくお願い致します。

回答の条件
  • 1人30回まで
  • 登録:2017/12/31 00:22:35
  • 終了:2018/01/12 02:11:49

ベストアンサー

id:Asayuri No.3

Asayuri回答回数71ベストアンサー獲得回数152018/01/03 08:31:52

ポイント1200pt

 
1 郵便番号CSVデータを下記URLからダウンロードして
  拡張子をエクセルファイルにして保存します
  http://www.post.japanpost.jp/zipcode/download.html

2 郵便番号データ内の住所を 1つの列に合体します
  =都道府県&市&町村

3 顧客住所をコピーしてから 大部分は 町村名までに 加工します
  必要があれば 丁目までに 加工します
  https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14109642334
 
4 VLOOKUP関数で 郵便番号を 読み込みます
  http://www.excel-list.com/vlookup.html  

5 町村名までで決まらなかったエラーが出ている数百行だけを 並べ替えて
  別ファイルに コピーしてから
  VLOOKUP関数で 郵便番号を 関連付けるために
  顧客住所側と 郵便番号住所側を
  SUBSTITUTEや LEFT、FIND関数を 使用して
  1対1の 関係にしていきます
  https://yotsuba-navi.com/archives/3364
  http://www.relief.jp/docs/003758.html
  その後 VLOOKUP関数で 郵便番号を呼び込みます
 

  上記手順で作業してみたエクセルデータです  
  指定した文字を削除する関数を入れてみました
  ダウンロードしてください
  http://firestorage.jp/download/cfb47d90c1a58a2fd325971580afa1cc702e11f7
  
 
 

他3件のコメントを見る
id:moon-fondu

ありがとうございます、やってみます!

2018/01/12 02:08:33
id:Asayuri

1対1の関係にすると書いたのは
文字の種類、文字数や全角、半角が ぴったり一致しているように加工していく
という意味です
エラー行の数をできるだけ少なくしていくことができれば
残りは手作業にしてもかなり楽です

2018/01/12 07:31:21

その他の回答(2件)

id:kimudon No.1

kimudon回答回数1910ベストアンサー獲得回数1712017/12/31 10:20:12

ポイント30pt

郵便番号変換ウィザード【Excel・エクセル】
アドインを利用できませんか?

バージョンが判りませんので「郵便番号 逆引き excel」で検索してみてください

id:moon-fondu

遅くなりましてすみません。
ご回答ありがとうございます。
「Excel 2016」を使用しているのですが、どうも対応していないのでしょうか、
「アドイン」に表示されないのですが…。

2018/01/10 02:11:40
id:pogpi No.2

POGPI回答回数380ベストアンサー獲得回数502017/12/31 17:09:20

id:moon-fondu

遅くなりましてすみません。
すごいツールですね。
複数の住所を同時に検索できるとよいのですが…。

2018/01/10 02:12:27
id:Asayuri No.3

Asayuri回答回数71ベストアンサー獲得回数152018/01/03 08:31:52ここでベストアンサー

ポイント1200pt

 
1 郵便番号CSVデータを下記URLからダウンロードして
  拡張子をエクセルファイルにして保存します
  http://www.post.japanpost.jp/zipcode/download.html

2 郵便番号データ内の住所を 1つの列に合体します
  =都道府県&市&町村

3 顧客住所をコピーしてから 大部分は 町村名までに 加工します
  必要があれば 丁目までに 加工します
  https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14109642334
 
4 VLOOKUP関数で 郵便番号を 読み込みます
  http://www.excel-list.com/vlookup.html  

5 町村名までで決まらなかったエラーが出ている数百行だけを 並べ替えて
  別ファイルに コピーしてから
  VLOOKUP関数で 郵便番号を 関連付けるために
  顧客住所側と 郵便番号住所側を
  SUBSTITUTEや LEFT、FIND関数を 使用して
  1対1の 関係にしていきます
  https://yotsuba-navi.com/archives/3364
  http://www.relief.jp/docs/003758.html
  その後 VLOOKUP関数で 郵便番号を呼び込みます
 

  上記手順で作業してみたエクセルデータです  
  指定した文字を削除する関数を入れてみました
  ダウンロードしてください
  http://firestorage.jp/download/cfb47d90c1a58a2fd325971580afa1cc702e11f7
  
 
 

他3件のコメントを見る
id:moon-fondu

ありがとうございます、やってみます!

2018/01/12 02:08:33
id:Asayuri

1対1の関係にすると書いたのは
文字の種類、文字数や全角、半角が ぴったり一致しているように加工していく
という意味です
エラー行の数をできるだけ少なくしていくことができれば
残りは手作業にしてもかなり楽です

2018/01/12 07:31:21
  • id:a-kuma3
    あらかた復旧できたということですかね。
    であれば、良かった。

    ぼくがネックになるだろうと思ったのはふたつあって、ひとつは古い住所。
    6万件のデータだと、古い住所もありますよね。
    合併とか、政令指定都市になったとか。
    KEN_ALL_ROME.csv は、最新の状態でしょうから、古い住所が引き当てられない。

    もうひとつは、KEN_ALL_ROME.csv のデータの癖です。
    例えば、

    ・範囲指定
    0600001,北海道,札幌市 中央区,北一条西(1~19丁目)
    0640821,北海道,札幌市 中央区,北一条西(20~28丁目)

    ・住所が17文字で切れて、2行に分かれてる
    6011255,京都府,京都市 左京区,上高野東山(55、57-6、64、
    6011255,京都府,京都市 左京区,72、189、189-1番地)

    ・以下に掲載がない場合
    4000400,山梨県,南アルプス市,以下に掲載がない場合

    などなど。
    計算式での引き当てで、どこまでできるんだろうなあ、などと思っていました。
  • id:moon-fondu
    a-kuma3さんコメントありがとうございます。
    データの癖をなるべく無くして、何とか手修正で対応できる量でした。
    誤って消さないように気を付けたいと思います…。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません