[[20141116012137]]　『年賀状のデータ』（年賀）　ページの最後に飛ぶ

『年賀状のデータ』（年賀）

お世話になります。
初心者です。

C2に○○○-○○○○　D2に都道府県から住所を
C2を入力すると○○○○県○○○市を表示することは出来るのでしょうか

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

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 こんなところでできないでしょうか。
http://office.microsoft.com/ja-jp/excel-help/HP010077514.aspx 現在参照不可
（Mook） 2014/11/16(日) 01:32

関数ではありませんが、日本語変換ソフトIMEで近い事はできます。

郵便番号から住所を自動入力できますか？↓ (tp0028)

http://www.excel.studio-kazu.jp/tips/0028/

（kazu） 2014/11/16(日) 23:50

http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

 この質問には、私は、

 上記から、データ（全国一括）をDLして活用する方法で行っています。

 という内容の投稿を何回かしてきました。

 MS社のツールは、確かにすばらしいですよね！！

 日本郵便のデータを活用することの良いところは、最新データが活用できる（更新が頻繁に行われるので）

 という点でしょうか！！

 DLした郵便番号データは、CSVですから、このままだと　検索が遅いので、
 私は、これをアクセスのMDBにテーブルとして登録し、VBAから活用してきました。

 現在もたまに、この郵便番号からの住所変換機能を付けるときは、このようにしています。

 2007から、Excelブックでこの郵便番号データが１シート収まるようになりましたよね！！

 まず、DLしたデータは、Excelブックに以下のような形式で保存します。

 シート名　KEN_ALL

    　　Ａ　　　　　Ｂ　　　　Ｃ　　　　　　Ｄ
 １ 　郵便番号　　　県　　　　市区　　　　　町村
 ２　　　10000　　北海道　　札幌市北区	　　以下に掲載がない場合
 ３　　　10010　　北海道　　札幌市北区　　　北十条西（１～４丁目）
 ４　　　10011　　北海道　　札幌市北区　　　北十一条西（１～４丁目）
 ５　　　10012　　北海道　　札幌市北区　　　北十二条西（１～４丁目）
 ６　　　10013　　北海道　　札幌市北区　　　北十三条西（１～４丁目）
 ７　　　10014　　北海道　　札幌市北区　　　北十四条西（１～４丁目）
 ８　　　10015　　北海道　　札幌市北区　　　北十五条西（１～５丁目）

 DLしたデータの郵便番号と住所列（３列）以外は、削除
 上記のように１行目に見出しを入れる
 郵便番号をキーに昇順に並び替え
 これで検索を行うことを考えます。

 上記のように加工したシートを

 １　ADOで接続して、必要なデータを取得する方法

 ２　対象シートを読み込んで　Match関数で管理する。

 ３　Dictionary等を使った検索を行う

 なんて方法が考えられますが、ここでは、２のMatch関数を使用する方法で考えてみました。

 Match関数は、照合の型という引数がありますよね！！

 この照合の型を0で検索しても対話型の仕様で使う場合は(例えば、セルＡ２の郵便番号を入力したら、直ちに
 住所を表示するような仕様）、
 全く問題ありません。

 が、一万行の郵便番号のあるセルの右隣のセルに住所を一括で表示するような仕様の場合は、
 照合の型が0のときと1のときとでは、１６秒も差がありました。
 これを速いとみるか、大したことないとみるかは　個人的見解の差になりますが、

 ロジックに差がないなら、速い方がよいですよね！！

 KEN_ALLのシートモジュールに

 Function get_address(pstno As Long, Optional fsw As Long = 1) As Variant
 '指定された郵便番号に対応する住所をかえします。
 ' Input   pstno  ---- 郵便番号　整数型　例4000053 1000000
 '         dsw         検索方法　0：順次検索　1：二分探索（既定値)
 ' output  get_address  郵便番号に対応した住所を配列形式で出力します。
 '                     （ひとつの郵便番号に複数の住所があるので）
 '                      False（Boolean型) 対応する住所がありません
    Dim f_data() As String
    Dim rng As Range
    Dim g0 As Long
    Dim g1 As Long
    Dim ans As Variant
    get_address = False
    Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
    ans = Application.Match(pstno, rng, fsw)
    If Not IsError(ans) Then
       Do While rng(ans).Value = pstno
          ReDim Preserve f_data(1 To g0 + 1)
          f_data(g0 + 1) = rng(ans).Offset(0, 1).Value & _
                           rng(ans).Offset(0, 2).Value & _
                           IIf(rng(ans).Offset(0, 3).Value = "以下に掲載がない場合", "", rng(ans).Offset(0, 3).Value)
          g0 = g0 + 1
          ans = ans - 1
          If ans < 1 Then Exit Do
       Loop
       If g0 > 0 Then
          get_address = f_data()
          Erase f_data()
       End If
    End If
    Set rng = Nothing
 End Function

 使用例

 標準モジュールに

 '=============================================================
 Option Explicit
 Sub 検索()
    Dim pst As Variant
    Dim ans As Variant
    Dim tm As Double
    pst = ""
    Do Until TypeName(pst) = "Boolean"
       pst = Application.InputBox("xxx-yyyy")
       tm = [now()]
       If TypeName(pst) <> "Boolean" Then
          ans = Worksheets("KEN_ALL").get_address(Val(Replace(pst, "-", "")))
          Debug.Print Application.Text([now()] - tm, "hh:mm:ss.00")
          If TypeName(ans) <> "Boolean" Then
             MsgBox Join(ans, vbCrLf)
          Else
             MsgBox "not find"
          End If
       End If
    Loop
 End Sub

 検索を実行すると、郵便番号の入力要求がありますから、郵便番号をXXX-YYYY形式で入力すると、
 対応する住所を表示します。複数の住所がある場合は、行を代えて表示します。

 Macth関数で試してみましたが、読み込み時の処理時間さえ目をつむれば、
 速い処理で変換してくれますね！！

 やっぱり、１シートにデータが収まるようになったのは大きですね

（ichinose） 2014/11/24(月) 17:13

コメント返信：

[ 一覧(最新更新順) ]

[[20141116012137]] 『年賀状のデータ』（年賀） ページの最後に飛ぶ

[[20141116012137]]　『年賀状のデータ』（年賀）　ページの最後に飛ぶ