郵便番号から住所を出力

質問

エクセルで郵便番号を入力して住所を表示するのに下のようにやってます。これを一発で目的を達するマクロにしたいのですが、どなたかご指導ください。
セルA2に郵便番号が入力されるとセルB2にも同じ値を書き込むマクロをつくり、セルC2に「=PHONETIC(B2)]としています。
操作員の手順は以下のとおり。
１．セルA2に郵便番号を入力する。
２．セルB2に出た郵便番号を数式バー上で選択して「変換」キイを押してドロップダウンリストで住所を選択する。
３．数式バー上で、都道府県の文字の後ろにカーソルを置いて都道府県名を削除した後、エンターで確定する。
４．不足の住所の番地等を入力する。
この２と３をマクロ化したいと思いますが、どなたかご指導ください。
なお、３で都道府県名を削除していますが、こうしないとセルC2にふりがなが出ませんのでやむなく都道府県名を削除しています。かなが出るなら都道府県名の削除は必要ありません。
アドインや類似の質問の回答を試しましたが、いま一つ満足ができません。よろしくお願いします。

回答No.7

Wendy02 · ベストアンサー

こんばんは。

返事が遅くなりました。以下は、前のバージョンです。
Excel2000 用で開発されたものです。

こちらのExcel2003でも、可能になっています。
なお、ブレークポイントをかけたら、うまく行かないはずです。
それは、アクティブな画面(Pane)が違うからです。
------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
'前のバージョン　Ex2000用
　If Target.Column <> 1 Then Exit Sub
　If Target.Count > 1 Then Exit Sub
　　　Application.ScreenUpdating = False
　　　Application.EnableEvents = False
　　　With Target.Offset(0, 1).Validation
　　　.Delete
　　　.Add Type:=xlValidateInputOnly
　　　.IMEMode = xlIMEModeHiragana
　　　End With
　　 If Target Like "###-####" Then
　　　　Target.Offset(0, 1).Select
　　　　SendKeys Target.Value
　　　　SendKeys "{ }", True
　　　　SendKeys "{ENTER}{ENTER}", True
　　　　SendKeys "{Left}"
　　　End If
　　　Application.EnableEvents = True
　　　Application.ScreenUpdating = True
End Sub

なお、お礼側に、少し、コメントを入れてくれると、こちらの方に直接、メールで連絡が来ます。補足側でも、チェックはしますが、確認するサイクルが遅くなります。

回答No.6

Wendy02

こんばんは。

>セルBとCに郵便番号がコピーされただけで終わっています。

IMEの辞書のプロパティの郵便番号辞書は入っていますか？
そこにチェックはしていますか？

基本的に、IMEを使って、郵便番号を入れたものを変換して、住所を出すような仕組みになっています。そのままでは、単に、郵便番号をコピーするだけです。

なお、前に使っていたマクロも提示できますが、その前に、

例えば、100-0011　と入力して、東京都千代田区内幸町　と変換できないものは、住所には変換されません

補足

ご指導ありがとうございます。
IMEを確認しましたが間違なく郵便番号辞書が選択されています。セルBに郵便番号が出ているときに、F2→「変換」とするとドロップダウンに住所が出るので間違いないでしょう。勿論100-0011も正しく変換されます。
かな変換マクロは順調に動いています。セルＢにコピーされた郵便番号をF2→「変換」で住所に変えた後、ダブルクリックでカナ変換マクロを起動させてセルＣに仮名を出しています。これだけでもずいぶん便利になりました。
尚、Excel2002では、住所変換マクロを実行するとほとんどの場合、キイボードのNumLockが解除されます。解除されない時もあります。
更に、マクロの中のScreenUpdateをFalseにしないでブレークポイントをかけて実行してみましたら、セルBに郵便番号のコピーすらできません。またブレークポイントをかける場所によっては、入力した郵便番号がマクロの記述の中に出てしまってそのままではマクロの継続ができません。
以上悪戦苦闘の現況をご報告します。

回答No.5

Wendy02

こんばんは。

#3の回答者です。

ちょっと解決には結びつかない話を書きますが、情報としてお許しください。

実は、この件は、みんながほとんど研究尽くされているので、検索すれば、いろんなものが出てくるはずです。ただ、どれ一つとって、ご指摘のように、VB系のものには大したものがない、というのも事実です。

郵便番号ウィザードの本体のエンジンを、API関数で本当は使わせてくれれば、それに越したことはないのですが、著作権侵害まですることは出来ないので、今は、みんなあきらめ状態だと思います。（最近は知らないです）

もし、知人の方のExcel2000 で、もしかして、郵便番号ウィザードの古いものをお持ちではないでしょうか？もし、そうでしたら、別のマクロを提供できます。郵便番号ウィザードの2002年より以前のものなら、郵便番号関数が作れます。

それと、私の前のここのカテゴリで、Excel2000用を出したら、それはダメだったのです。今回のは、Excel2003用に作り直したものですが、Excel2003では動かないのが良く分かりません。

今の段階で、二つのマクロをつなぎ合わせるのは、ちょっと、自信がありません。というのは、そのマクロは、そもそも微妙なところで動いているので、途中で何かを入れるというのは、IMEの不安定な状態を誘発する可能性があります。

IE7 にしてから、不具合が生じて、私のIMEは、MS-IME 2003ですが、プロパティの詳細なテキストサービスをオフにするとか、Application で、IMEを制御しないような設定にしています。以前は、そのような問題はなかったのです。

もうそろそろ新しいものを、考えないといけない時代になったのかなって思います。

ただ、もうこの先は、せめて、ODBCからmdbファイルか、csv ファイルなどから取り出す、COMアドインを作りたいと思いながらも、そのままにしてしまいました。作る気になれば出来るとは思うのですが、今さら、カビの生えたVB6もないし、かといって、VB.Net もあまり興味が薄れてしまっています。

別の掲示板でも名の知られている　WinArrow　さんという方が、Excelのこの郵便番号では、6～7年という長い間携わっていて、私も、それにも刺激はされたのですが、結局、こちらは、最初に書いた郵便番号ウィザードの古いものが残っていたので、そこから、関数を取り出しすることに成功し、その後、開発をするのをやめてしまったのです。

以下のものも、たぶん、ExcelからADOを使っているはずですから、そんなに動きが早くないはずです。Access直接のようにはいきません。

http://blog.moug.net/archives/50717469.html
郵便番号住所変換アドイン (V1.000) WinArrow作
（要登録－ログイン）

お礼

興味深いお話ありがとうございました。この問題はそんなにも奥深い問題だったのですね。
我が家のExcelは、２００２でした。ご提示いただいたマクロを再度試してみましたがうまくゆきません。Vistaを使っていますので、OSの所為もあるのではとMeのPCでも試してみました。こちらはNumLockとかそういった不思議な現象は起きなかったのですが、セルBとCに郵便番号がコピーされただけで終わっています。また機会を見つけてほかのPCででも試してみます。

回答No.4

fmajin

入力作業の延長線上にあるから、ショートカットキーの活用でも行けるように思います。たぶん「2.」の手順で『「変換」キイを押して…』というのは、再変換機能を活用されているということですよね。それを前提にして、以下のような操作を行うのはいかがでしょう。

(1)セルA2に郵便番号を入力し、［Tab］キーで確定
(2)セルB2に移動したら、［Ctrl］＋［R］キーでA2セルの郵便番号をコピー
(3)［F2］キーで編集モードにして、［変換］キーで再変換して住所を選ぶ
(4)まだ入力確定せず、［Shit］＋［Alt］＋［↑］キーを2回押す。すると、住所のふりがなが表示されるはず。
(5)残りの番地等を追加して、［Enter］キーで入力確定

マクロではないですが、一度お試しください。

お礼

有難うございました。
（４）で確かにセルBの住所の下に振り仮名が出ますが、セルCにはどうしても入りません。
いろいろ工夫した結果、セルBに住所のほか郵便番号と振り仮名が出ているとき「↓」を押して振り仮名を選択して「エンター」を２回押すとPHONETIC関数を入れたセルCに振り仮名の出る事が確認できました。
尚、（１）と（２）は、マクロで処理をしてセルBに郵便番号が出るようにしていますので、セルBを選択してF2キイで編集モードにしました。

補足

下のお礼文中で、「エンター」を2回押すと記述しましたが、3回の誤りです。ともかく［Shit］＋［Alt］＋［↑］という重宝なキーを教えてくださりありがとうございました。今までの操作手順に比べ格段に便利になりました。別のシチュエーションでも使えそうです。
理想はWendyさんご提案のマクロの実現ですが、目下悪戦苦闘中です。

回答No.3

Wendy02

こんにちは。

まず、Excelのバージョンが問題になりますが、当面、こちらは、Excel 2003 上で動くことを前提として回答します。2007に関しては分かりません。IMEは、MS のものとし、辞書の郵便番号辞書にはチェックが入っているものとします。(少なくとも、Excel2000では、以下のコードは変わります）

A列に[郵便番号]を入れることを前提とします。
シートタブ（画面の下）のSheet1.. 等を右クリックしてください。
その中に、コードの表示がありますから、クリックして、VBEditor を開きます。

マクロは、二つになります。
理由は、フリガナ復活マクロを別にしました。Phonetic 関数で、都道府県を入れて、フリガナが出ない状態では、いずれにしても勝手が悪いと思います。

>２．セルB2に出た郵便番号を数式バー上で選択して「変換」キイを押してドロップダウンリストで住所を選択する。

これは、必要ありません。

マクロとしては、あまりスピードが期待できるものではありません。予めご了解願います。

'-----------------------------------------
'自動で、住所を出すマクロ
'-----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
　　'IMEは、MS-IME に限る
　　If Target.Column <> 1 Then Exit Sub 'A列でなかったら、取りやめ
　　If Target.Rows.Count > 1 Then Exit Sub '1行以上選んだ、取りやめ
　　If Len(Target.Value) <> 8 Then Exit Sub
　　If InStr(Target.Value, "-") = 0 Then Exit Sub
　　Application.OnKey "{DEL}"
　　Application.ScreenUpdating = False
　　With Target.Offset(0, 1).Validation '入力規則
　　　　.Delete
　　　　.Add Type:=xlValidateInputOnly
　　　　.IMEMode = xlIMEModeHiragana 'IME立ち上げ
　　End With
　　Application.EnableEvents = False
　　Target.Offset(0, 1).Select
　　SendKeys Target.Value '番号を入れる
　　SendKeys "{ }" '変換
　　SendKeys "{ENTER}"
　　With Target.Offset(0, 1).Validation '入力規則
　　　　.Delete　'入力規則を削除することで、IMEを戻す
　　End With
　　SendKeys "{Left}"
　　SendKeys "{Down}"
　　Application.EnableEvents = True
　　Application.ScreenUpdating = True
End Sub

'------------------------------------------------
'標準モジュールがベター
'フリガナ復活マクロ
'------------------------------------------------
'フリガナの復活であって、フリガナを出すものではありません。
'フリガナは、Phonetic 関数を使ってください。

Sub FuriganaRegain()
　　Dim c As Range
　　Application.ScreenUpdating = False
　　'マウスで選択をしてから、マクロを実行してください。
　　For Each c In Selection
　　　　If VarType(c.Value) = vbString Then
　　　　　　If InStr(c.Value, "県") = 4 Then
　　　　　　　　c.Value = Left(c.Value, 4) & "," & Mid(c.Value, 5)
　　　　　　Else
　　　　　　　　c.Value = Left(c.Value, 3) & "," & Mid(c.Value, 4)
　　　　　　End If
　　　　　　c.SetPhonetic
　　　　　　c.Replace ",", ""
　　　　End If
　　Next c
　　Application.ScreenUpdating = True
End Sub

'-------------------------------------------
なお、私は、いくつかアドイン等が出ているのは知っていますが、作者さんたちには失礼ですが、VB系のものは、どうにも使う気になれません。中には、恥ずかしいレベルのアドインもあるようです。といって、こんなマクロをここで掲示するのは矛盾していると思われる方もいるでしょうけれども、あくまでも、これは、VBAをちょっと知っている人なら誰でも使えるレベルのものだという認識で公開しています。

お礼

ご丁重なマクロのご提示ありがとうございました。これはQ&Aの検索でも見つけたのと同じ内容（同じWedyさんだったかも）で、試してみてうまくゆかなかったコマンドです。NUMロックが外されたりその他不可思議な現象が起こりました。(私のExcelは2003）
ところが知人のExcel2000で同じことをやったところ（後者のカナ表示マクロはセルを選択してダブルクリックで起動）全てうまく行きました。どうしてでしょうか。
また、うまくいったExcel2000でこの二つのマクロをなんとかつなぎ合わせようとしましたがうまくゆかず、カナ表示の繋ぎ目(後者のマクロのアタマ）でブレークポイントをかけましたところ、住所表示すらできませんでした。自己流の初心者にとってはなんとも不思議なマクロです。

回答No.2

imogasi

こういうのは操作を１歩１歩を説明してマクロを教えてもらおうとしても、マクロの記録をとっても、無駄です。
マクロの記録をとると
Sub Macro1()
ActiveCell.FormulaR1C1 = "xxx-xxxx"
Range("A1").Select
ActiveCell.FormulaR1C1 = "XXXXXXXXXXXXXXXX"
ActiveCell.Characters(1, 10).PhoneticCharacters = "XXXXXXXX"
Range("A2").Select
End Sub
になりますが、これを複数セルで（プログラムで、一部を変えて）繰り返えそうとしても無駄です。結果だけしか記録して無いからです。
＞２と３をマクロ化したいと思いますが
マクロはエクセルとアクセスなどにあり、エクセルならエクセルの操作・機能を使って処理をするものです。
そもそもエクセルには、郵便番号ー住所名を割り出す機能は備わっていません。各国で違うことですし。（ＭＳは世界企業）。
ーー
それと作業は１郵便番号を入れるとすぐＢ列に地名が出る方法と
まとめて１００行とか入れて、入力が終って全行Ｂ列に地名を出す
作業方式があるが、本件は後者が適当です。そんなこと考えたことありますか。質問の路線は前者でしょう。素人は前者で考えるが、
後者のほうが易しい。
ーー
もし郵便番号ー地名の表がエクセルシートにあれば、ＶＬＯＯＫＵＰ関数やＶＢＡのＦＩＮＤメソッドで簡単に、番号から地名が割り出せます（厳密には１番号ー１地名ではないが、どれは別にして）
ーー
そして郵便番号ー地名の表が12万行ぐらいになり、２００７以前では
収録不可能でした。また対応表の自作はまず不可能です。
ーー
そこで他人が作った郵便番号ー地名表とそれを検索する機能、セルに順に地名をセットする機能をしてくれるソフトを探すわけです。
処理に自信がある人用にはデータだけでも郵政公社提供のデータだけも手に入ります。
http://www.forest.impress.co.jp/article/2005/11/14/zipcsv.html
アクセスは１２万件からでもすぐ簡単に地名が引ける機能が備わっているが。
ーー
ですから、熟達者や勉強したい人は別として、質問にもあるアドインという特定の作業内容で
エクセルに適したソフト
の導入を考えるべきです。
導入するといつもすぐ使えるようになるしくみです。
導入はおっくうなものですが、１行ずつの
手作業を省くためにはやむをえない。
ーー
ＷＥＢ（Ｇｏｏｇｌｅなど）で「アドイン　郵便番号」などを照会すれば沢山記事が出る。どうしてそういう便利なものを利用しないのかな。
http://www.microsoft.com/downloads/details.aspx?familyid=6f6af8ef-b9dd-4e21-9e63-af4a0ff4e7ce&displaylang=ja
終るとＡ列を範囲指定して
ツール
ウイザード
郵便番号変換
元データセル　Ａ１：Ａ１００
変換後セル指定　Ｂ１：Ｂ１００
完了

回答No.1

berry_late

「かな」のくだりがよくわからないのですが、
別に参照用のシートを用意してはだめなのですか？

要するに、郵便番号とその対応する地名が入ったシート(不使用のセルでもかまいませんが)を用意し、
Ａ列に郵便番号を入力すると、Ｂ列はVLOOKでその参照用のシート(あるいはセル)から見てきた地名を返す。
続きの地名はＣ列に手入力。