Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
住所から郵便番号
投稿日時: 18/03/02 13:48:50
投稿者: ray3821

久しぶりの質問になります。
 
数千件のデータが掘り出されすべての住所に郵便番号が振られていません。
アドインを使用して郵便番号を取得しようとしましたが、
どうやら昨年末にアドインの公開が終わっているようで困り果てています。
 
今現在、zipcode7japan.xlaを入手する方法はないのでしょうか?
 
もしないとしたら、現状すべて手動で行わなくてはならないのでしょうか?
大変申し訳ないのですが、どなたかご教授いただければ幸いです。
 
よろしくお願い致します。

回答
投稿日時: 18/03/02 14:36:53
投稿者: mulberry

Windows 10 home で試してみました
 
zipcode7 2013 という文字列でgoogle検索する。
最初にヒットした121wareのサポートページ
Excel 2013で郵便番号から住所(住所から郵便番号)を変換する方法
にあるリンクから
excelzipcode7japan.zip
がダウンロードできました。

投稿日時: 18/03/02 16:54:06
投稿者: ray3821

ご返答ありがとうございます。
最初の質問の時点で足りない文章があり申し訳ありません。
 
codeplex様の方からexcelzipcode7japan.zipはDL出来たのですが、
その中にxlaは含まれてはいないようですが、これで使用できるのでしょうか?
 
使用できるようであれば、お手数ですがその方法をお願い致します。
 
出来ないのであれば引き続きご返答お待ちしております。

回答
投稿日時: 18/03/02 18:43:07
投稿者: mulberry

確かに excelzipcode7japan.zip アーカイブには
*.xla ファイルはありませんね。
 
この変換ウィザードの提供は終了しています。
ウェブ検索が主流になっているのでしょう。
 
日本郵便が毎月更新頒布している 郵便番号データ(csv形式)をダウンロードしてVBAで処理しては如何でしょうか
そんなに複雑なことではないので自習で可能でしょう。

投稿日時: 18/03/02 22:18:06
投稿者: ray3821

ご返答ありがとうございます。
  
自分にはそこまでの力量はないので、
恐縮ではありますがもし他の方法やxlaファイルがどこかでDL出来ればと思っております。
  
引き続きご返答お待ちしております。

回答
投稿日時: 18/03/04 15:54:03
投稿者: んなっと

●Sheet2 に、ダウンロードした KEN_ALL.CSV を読み込んでおいて(データ→テキストファイル→全フィールド文字列に)
    
    C D E F    G       H              I
 1 0600000      北海道 札幌市中央区    以下に掲載がない場合
 2 0640941      北海道 札幌市中央区           旭ケ丘
 3 0600041      北海道 札幌市中央区           大通東
 4 0600042      北海道 札幌市中央区   大通西(1〜19丁目)
 5 0640820      北海道 札幌市中央区  大通西(20〜28丁目)
 6 0600031      北海道 札幌市中央区          北一条東
 7 0600001      北海道 札幌市中央区  北一条西(1〜19丁目)
 8 0640821      北海道 札幌市中央区 北一条西(20〜28丁目)
 9 0600032      北海道 札幌市中央区          北二条東
10 0600002      北海道 札幌市中央区  北二条西(1〜19丁目)
11 0640822      北海道 札幌市中央区 北二条西(20〜28丁目)
12 0600033      北海道 札幌市中央区          北三条東
13 0600003      北海道 札幌市中央区  北三条西(1〜19丁目)
14 0640823      北海道 札幌市中央区 北三条西(20〜30丁目)
15 0600034      北海道 札幌市中央区   北四条東(1〜8丁目)
16 0600004      北海道 札幌市中央区  北四条西(1〜19丁目)
17 0640824      北海道 札幌市中央区 北四条西(20〜30丁目)
18 0600035      北海道 札幌市中央区          北五条東
  
●Sheet1 のA列に住所
  
               A    B    C       D      E
 1             住所    〒                 
 2   神戸市中央区御幸通7-1-15 6510087     神戸市中央区   御幸通
 3   東京都品川区東品川2-2-20 1400002 東京都    品川区   東品川
 4    仙台市青葉区本町2-15-1 9800014     仙台市青葉区    本町
 5     札幌市北区北7条西4-12 0600807      札幌市北区     北
 6     盛岡市盛岡駅前通15-20 0200034        盛岡市 盛岡駅前通
 7   東京都品川区東品川2-2-20 1400002 東京都    品川区   東品川
 8   群馬県高崎市あら町167番地 3700831 群馬県    高崎市   あら町
 9  新潟市中央区東大通り2-4-10 9500087     新潟市中央区   東大通
10      名古屋市中区栄2-3-1 4600008     名古屋市中区     栄
11 静岡県島田市細島字寺久保1700 4270012 静岡県    島田市    細島
12       金沢市本町2-15-1 9200853        金沢市    本町
13     大阪市北区堂島2-4-27 5300003      大阪市北区    堂島
  
     F   G   H    I   J
 1                  
 2      88783 88861  29.03  1
 3      39376 39435  51.03  1
 4      12843 13020 169.02  1
 5 北七条西   90  232  8.01 46
 6      10770 10932 152.05  1
 7      39376 39435  51.03  1
 8      29149 29385  8.03  1
 9      43355 43566 143.03  1
10      66415 66439  9.01  1
11      64168 64286  98.02  1
12      51861 52417 459.02  1
13   堂島 84854 85065 150.02  2
   
B2
=IFERROR(IF(E2="",INDEX(Sheet2!$C:$C,G2),TEXT(INDEX(Sheet2!$C:$C,G2+I2-2+IF(J2=1,1,MATCH(F2,INDEX(Sheet2!$I:$I,G2+I2-1):INDEX(Sheet2!$I:$I,G2+I2+J2-2),0))),"0000000")),"")
C2
=IF(OR(MID(A2,2,2)={"街道","宰府","諸県"}),"",LEFT(A2,IF(OR(MID(A2,3,1)={"都","道","府","県"}),3,IF(MID(A2,4,1)="県",4,))))
D2
=IFERROR(MID(A2,LEN(C2)+1,AGGREGATE(14,6,ROW($2:$10)/MATCH(MID(A2,LEN(C2)+1,ROW($2:$10)/(MID(A2,LEN(C2)+ROW($2:$10),1)={"市","区","町","村"})),Sheet2!$H:$H,0)^0,1)),"")
E2
=IFERROR(LEFT(INDEX(Sheet2!$I:$I,G2+I2-1),MOD(I2*100,100)),"")
F2
入力規則:リスト▼:元の値:
=OFFSET(Sheet2!$I$1,G2+I2-2,,J2)
G2
=IFERROR(MATCH(D2,Sheet2!$H:$H,0),"")
H2
=IFERROR(G2+MATCH(1,INDEX(0/(D2=INDEX(Sheet2!$H:$H,G2):INDEX(Sheet2!$H:$H,G2+1000)),))-1,"")
I2
=IFERROR(LOOKUP(10^9,MATCH(MID(A2,SUMPRODUCT(LEN(C2:D2))+1,ROW($1:$20))&"*",INDEX(Sheet2!$I:$I,G2):INDEX(Sheet2!$I:$I,H2),0)+ROW($1:$20)/100),"")
J2
=IFERROR(MATCH(1,INDEX(0/(E2=LEFT(INDEX(Sheet2!$I:$I,G2+I2-1):INDEX(Sheet2!$I:$I,H2),LEN(E2))),)),"")
 
 B2:J2を選択して下方向・↓
 
完全ではありません。
B列が空欄の行は、F列のリストから選択すれば表示されます。
A列に新しいデータを貼り付けたときは、F列全体を選択してDeleteキーを押しましょう。

回答
投稿日時: 18/03/04 19:49:27
投稿者: 半平太

んなっとさんの回答をみて、疑問が生じたのですが、
 

引用:
「数千件のデータが掘り出されすべての住所」
それって、都道府県名が無い住所もあるんですか?

回答
投稿日時: 18/03/04 20:06:13
投稿者: んなっと

ダウンロードデータのI列に面倒な記述がたくさん...
 
     C D E F    G        H
 1  600000      北海道  札幌市中央区
 2 9800065      宮城県  仙台市青葉区
 3 1000005      東京都    千代田区
 4 1006690      東京都    千代田区
 5 1006601      東京都    千代田区
 6 1006743      東京都    千代田区
 7 3994232      長野県    駒ヶ根市
 8 3994232      長野県    駒ヶ根市
 9 3994101      長野県    駒ヶ根市
10 3842304      長野県 北佐久郡立科町
11 4280049      静岡県     島田市
12 4280049      静岡県     島田市
13 6511102      兵庫県   神戸市北区
14 6511102      兵庫県   神戸市北区
 
                                       I
 1                            以下に掲載がない場合
 2                        土樋(1丁目「11を除く」)
 3                          丸の内(次のビルを除く)
 4            丸の内グラントウキョウサウスタワー(地階・階層不明)
 5                 丸の内グラントウキョウサウスタワー(1階)
 6                丸の内グラントウキョウノースタワー(43階)
 7 下平(2070〜2119、2309〜2311、2419、2421〜2426 ┐2レコードに
 8                           、2433〜2450) ┘分かれている
 9                               下平(その他)
10     茂田井(1〜500「211番地を除く」「古町」、2527〜2529
11    牧之原(250〜343番地「255、256、258、259、262、 ┐
12            276、294〜300、302〜304番地を除く」) ┘
13  山田町下谷上(大上谷、修法ケ原、中一里山「9番地の4、12番地を除く」長 ┐
14                              尾山、再度公園) ┘
 

回答
投稿日時: 18/03/04 20:23:54
投稿者: MMYS

んなっと さんの引用:
ダウンロードデータのI列に面倒な記述がたくさん...

加工して公開している所もあるみたい。
http://zipcloud.ibsnet.co.jp/
 
 
ところでこの問題、昔から各所で指摘されてるけど、
いつまでたっても修正されないですね。
 

投稿日時: 18/03/04 20:52:56
投稿者: ray3821

ご返答ありがとうございます。
 
最初はエラーが出ましたが、なんとか出来るようになりました。
非常に助かりました!
大変お手数をお掛けして申し訳ありませんでした。
 
以後このようなことにも対処できるようになりたいと思います。