Excel2016で郵便番号入力で住所

質問

OS：Windows10 Pro 64bit
Office2016 Home & Business Premium

Excel2016で郵便番号を入れただけで住所を出力する方法を教えてください。
9F：9I　郵便番号、9J：9Z　住所

Microsoft IMEIMEのシステム辞書には郵便番号辞書は入っています。

出来れば関数を使った方法はないでしょうか？

回答No.13

Prome_Lin · ベストアンサー

一応、マクロを組みました。

あえて、いろいろなチェック作業はしていません。

チェック作業というのは、ちゃんと７桁入力されているか、とか、すべて半角の数字で入力されているか、など一切のチェックを行っていませんので、郵便番号の数字を入力する際は注意してください。

エクセルを立ち上げ、郵便番号のファイルを開きます。

「Ｃ」列と「Ｄ」列は削除しておいてください。

「Alt（「オルト」と読みます）」キーを押しながら、「F11」キーを押します。

「Visual Basic」の画面が現れますので、メニューから「挿入」→「標準モジュール」を選択します。

すると、右側の画面が白くなりますから、その新たに白くなった部分にカーソルを移動して、下のマクロをコピー＆ペーストします。

「F5」を押して、マクロを走らせます。

入力を求めてくる画面になりますから、「-」のない、７桁の郵便番号を入力します。

すると、「Ｃ」列に該当の住所が表示されます（今回は文字列ですので、「F2」による訂正ができます）。

何も入力せず、「Enter」だけを押すと、入力が終了しますが、それまでは、７桁の数字を入力し続けられ、１行ずつ下げて住所を入れていきます。

通常のエクセルの画面に戻って、「F12」（名前を付けて保存）を押して、「～.xlsm」を選んで（「～」の部分は何でも構いません）保存します。

注意事項としては、次、このファイルを開くと、前回の文字（住所）がそのまま残っています。

しかも、マクロを走らせると、また、１行目からスタートしますので、気をつけてください（ファイルを閉じるときか、立ち上げるたびに「Ｃ」列を削除する、とか）。

Option Explicit
Sub zip()
Dim c As Integer
Dim i, r As Long
Dim z As String
r = Range("A1").End(xlDown).Row
c = 0
z = "0"
Do Until z = ""
z = InputBox("郵便番号を入力してください" & vbCrLf & "（ -は入力しないでください）。" & vbCrLf & "何も入力しなければ終わります")
For i = 1 To r
If z = Cells(i, 1).Value Then
c = c + 1
Cells(c, 3).Value = Cells(i, 2).Value
End If
Next i
Loop
End Sub

以下は、読む必要はありません。

私の回答を見た、他の人に対して書いているようなものですから。

通常は「Find」などを使って検索するのが普通だとは分かっていますが、それでは、エラーが出てしまうので、あえて、泥臭い「For」ループを使って、入力された郵便番号を探しています。

大昔のパソコンと違って、たかが１２万件ほどのループぐらい、全く時間はかかりませんから。

しかもこれなら、見つからなかった場合、何もしないので、そのまま再入力が可能だからです。

お礼

ちゃんと出来ました。
これをどのように加工して私の望みの格好にするか、これからが楽しみです。
Prome_Linさん、6回も回答してくれてありがとうございました。

そのほかの回答者も拙い質問に真摯に答えてくれて、全員をベストアンサーにしたいほどです。

今回はProme_Linさんをベストアンサーに致しますが、これからもよろしくお願いいたします。

回答No.12

Prome_Lin

うっ！
そんな使い方をしようとすると、「ＶＢＡ」でマクロを組まないと出来ません。

そうか、「Ｄ１」をシングルクリックして選択した状態で「Ctrl＋c」（コピー）→他のセルで右クリックから「形式を選択して貼り付け」→「値の貼り付け」をすると、住所が文字として、すなわち、「=IF～」などという式ではなく、「京都府京都市～」などという文字として貼り付けられるので、そこで入力するか、メモ帳などで「Ctrl＋v」（貼り付け）してもできます。

しかし、そんなまどろっこしいことをしたくはありませんよね。

う～ん、私の知識では、やはりマクロを組むしか方法が・・・

回答No.11

Prome_Lin

No.5です。

補足説明、読みました。

えっ！
こちらでは私の家の郵便番号を「Ｃ１」に入れると、ちゃんと「京都府京都市～」という私の住所が町名までちゃんと入りました。

また、一番上の「0600000」を打っても、一番下の「9071801」を打っても、その横の住所がちゃんと入るのですが・・・

きのう、自分で書いた文章に間違いないか確認して、そのとおりにすれば、できました。

質問者の方も「Ａ」列に「郵便番号」、「Ｂ」列に「住所」と書かれているので、きっと間違ってはおられないと思います。

すみません、ファイルのやり取りなどができないので、こちらでは原因が分かりません。

お礼

ちゃんとできました。
この利用方法を考えていて、連絡が遅れて申し訳ありませんでした。

J9には表示させる式が入りますよね。
住所が表示された後番地などを続けて入力しようとして、[F2]なりダブルクリックをすると
式が表れて番地が入力できません。

どうすればいいんだろう？

回答No.10

Prome_Lin

セル「Ｃ１」を「文字列」に設定されましたか？

私も、最初、「Ｃ１」に自分の住所の郵便番号を入力してもダメだったので、慌てたのですが、「Ｃ１」のところをクリックし選択した状態で「Ctrl＋1」（この「１」はテンキーはダメで、キーボード左上の「１」）で「セルの書式設定」を出し、「表示形式」から「文字列」を選択してください。

そうすれば、出るはずなのですが（きのう、私自身、試しながら回答しましたので、間違いないと思うのですが）・・・

回答No.9

NotFound404

Excel2016でも2010のアドイン郵便番号変換ウィザードが使えるようです。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12152333787

https://excelzipcode7japan.codeplex.com/releases/view/48005
ご参考まで。

回答No.8

Prome_Lin

すみません、「No.5」です。

きのうは、自分の書いた手順が合っているかどうかに集中してしまって、ご質問の最後の分を失念していました。

「=IF(C1 = "", "", VLOOKUP(C1, A1:B123907, 2, FALSE))」を
「J9」セルに入力し、「C1」を「F9」にします。

すなわち、「=IF(F1 = "", "", VLOOKUP(F1, A1:B123907, 2, FALSE))」です。

あとは、実際に郵便番号のデータを置いたセルの場所を
「A1:B123907」のところに入れてください。

回答No.7

ushi2015

こんにちは
もしＩＥが使える環境なら、新規シートで試して下さい。
シートタブを右クリックして、コードの表示でVBE画面を出して、
Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address = "$A$2" And Target.Count = 1 Then
　　　　With ActiveSheet.QueryTables.Add(Connection:= _
　　　　　　"URL;http://www.post.japanpost.jp/cgi-zip/zipcode.php?zip=" & _
　　　　　　　　Target.Value & "&x=98&y=14", _
　　　　　　Destination:=Range("$A$1"))
　　　　　　.Name = "zipcode.php?zip=1200001&x=98&y=14_1"
　　　　　　.FieldNames = True
　　　　　　.RowNumbers = False
　　　　　　.FillAdjacentFormulas = False
　　　　　　.PreserveFormatting = True
　　　　　　.RefreshOnFileOpen = False
　　　　　　.BackgroundQuery = True
　　　　　　.RefreshStyle = xlOverwriteCells
　　　　　　.SavePassword = False
　　　　　　.SaveData = True
　　　　　　.AdjustColumnWidth = True
　　　　　　.RefreshPeriod = 0
　　　　　　.WebSelectionType = xlSpecifiedTables
　　　　　　.WebFormatting = xlWebFormattingNone
　　　　　　.WebTables = "2"
　　　　　　.WebPreFormattedTextToColumns = True
　　　　　　.WebConsecutiveDelimitersAsOne = True
　　　　　　.WebSingleBlockTextImport = False
　　　　　　.WebDisableDateRecognition = False
　　　　　　.WebDisableRedirections = False
　　　　　　.Refresh BackgroundQuery:=False
　　　　End With
　　End If
End Sub
を貼り付けて、セルＡ2に郵便番号を入力してみて下さい。

お礼

上記のコードをVBAに貼り付けてみましたら、
　郵便番号あり：A1に”郵便番号、郵便番号なし：A1スペース
　B2：都道府県
　C2：市区町村
　D2：町域
となります。

B4=IF(A1="","",B2&C2&D2)にして住所を出力しようと思います。

ありがとうございます。助かりました。

回答No.6

MackyNo1

郵便番号のcsvファイルをダウンロードして、それを成型してVLOOKUP関数で表引きする方法もありますが、実戦的には以下のような手順が簡単です。

まずMS-IMEツールバーの「般」の部分をクリックして「人名/地名」にします。
次に郵便番号を入力するF9セルには「=PHONETIC(J9)」と入力しておきます。
最後に住所を入力するセル（J9セル）に郵便番号を入力し、変換キーを押しEnterして住所を入力します。

回答No.5

Prome_Lin

http://www.post.japanpost.jp/zipcode/download.html
から、「住所の郵便番号（CSV形式）」をダウンロードします。

例えば、「読み仮名データの促音・拗音を小書きで表記するもの」の方をクリックして、「都道府県一覧」から右下の「全国一括」をクリックすると、ダウンロードが始まります。

ダウンロードした「ken_all.zip」を解凍します。

そのままダブルクリックしても「KEN_ALL.CSV」は表示され、その「KEN_ALL.CSV」をダブルクリックすると、エクセルが立ち上がり、読み込まれるのですが、この方法ですと、頭が「０」から始まる郵便番号の頭の「０」が欠落してしまいますので、仕方なく解凍します。

解凍して出来た「KEN_ALL.CSV」をシングルクリックして「F2」、最後の「CSV」を「txt」に変更します。

拡張子を変更しようとすると、メッセージが表示されますが、そのまま「はい」で大丈夫です。

エクセルを立ち上げ、メニュー「ファイル」から「参照」（最近使ったアイテムの下の方）をクリックして、先ほどのファイルが存在する場所を指定します。

下の方の「ファイル名」の横「すべてのExcelファイル」の横の「▽」みたいなのをクリックして「テキストファイル」を選択すると、先ほどの「KEN_ALL.txt」が表示されますので、それを開きます。

次に現れた画面は「次へ」で大丈夫です。

次は、「タブ」のチェックをはずし、「カンマ」にチェックを入れます（「文字列の引用符」が「"」になっていることを念のため確認しておく）。→「次へ」

ここが重要です。

今、左端が黒くなっていますが、この列が選択さていることを示しています。

「Shift」キーを押しながら、「以下に掲載がない場合」をクリックすると、最初の列から「以下に～」の列までが範囲指定されますので、上の「文字列」にチェックを入れます（最初は「標準」に入っています）。

「完了」でエクセルに読み込まれます。

「Ｊ」～「Ｏ」列、「Ｄ」～「Ｆ」列、「Ａ」・「Ｂ」列は不要なので削除します。

「Ｅ」列に「=b1&c1&d1」と打ち、「Ctrl＋c」（コピー・記憶）、今選択している「E1」セル以外に絶対に他のセルをクリックすることなく、一番下の「E123907」を「Shift」キーを押しながらクリックし、「E1」から「E123907」を範囲指定して、「Enter」を押します（範囲指定された状態のまま保持しておくこと）。

「Ctrl＋c」をもう一度押し、今の「E1～E123907」を記憶します。

メモ帳を立ち上げ、「Ctrl＋v」（ペースト・貼り付け）します。

エクセルの「Ｂ」列から「Ｅ」列までを削除し、「Ａ」列のみ残します。

メモ帳上で、「Ctrl＋a」（すべて選択）し、「Ctrl＋c」→エクセルに戻って「B1」セル上で、「Ctrl＋v」。

これでようやく、郵便番号簿が出来ました。

あとは、「D1」セルに「=IF(C1 = "", "", VLOOKUP(C1, A1:B123907, 2, FALSE))」と入力します。

最後に、「Ｃ」列を指定して「Ctrl＋1」（この「１」はキーボード左上の「１」で、テンキーの「１」はダメ）で、「表示形式」から「文字列」を選択します。

これで、「C1」に郵便番号を「-（ハイフン）なし」で入力してみてください。

該当する住所が「D」列に表示されます。

あとは、これを応用してください。

補足

ありがとうございます。

A列：郵便番号、B列：該当の住所、・・・A1~B123907まで郵便番号、該当の住所で埋まっています。
C1：（郵便番号入力用）、
D1：=IF(C1 = "", "", VLOOKUP(C1, A1:B123907, 2, FALSE))
C列の書式設定：文字列

上記の通りにC1にハイフンなしの郵便番号を入れましたが、D1には何も変化がありません。

回答No.4

mitarashi

#3です。余分なフリガナとか、意味不明（調べる気が無いだけですが）の数値の列を除去すると、4M弱になりました。これなら使う気になるかも。ご参考まで。

回答No.3

mitarashi

下記郵便局のWebサイトから全国の郵便番号リストを取得して、目的のブックに組み込めばVlookupで可能です。郵便番号は例えば3000000と、7桁つながった形で入っています（ハイフンが入って居る場合は、Vookupにかける前に削除すれば良いですね）。また、3000000の例では、茨城県,土浦市,以下に掲載がない場合（普通は町名等が入る）と、3列に分かれています。
http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
なお、123907行あります。これだけでブックのファイルサイズは10M弱となります。もう一つの問題はこのリストの郵便番号が「ユニーク」ではなく、若干の重複がある事です。(ユニークなリストを取得すると、119751件でした）Vlookupの様な関数ではご存じの通り、一つしか取得できません。
注）普通にCSVを読み込むと郵便番号の頭の00や0が消えてしまうので、データ/外部データの読込で行い、文字列形式に指定して読み込むのが良いでしょう。
以上、ご参考まで。