No.2ベストアンサー
- 回答日時:
とりあえず【郵便番号変換ウイザード】のアドインをMSのHPからDLして下さい。
http://www.microsoft.com/downloads/details.aspx? …
作業セルを用意して(仮にDセルとします)、郵便番号変換ウイザードを利用してBセルの郵便番号から住所データを作成します。
次に作業セル(仮にEセルとします)に
=IF(LEFT(D2,4)=LEFT(C2,4),C2,IF(MID(D2,4,2)=LEFT(C2,2),LEFT(D2,3)&C2,LEFT(D2,4)&C2))
これで都道府県つきの住所へ変換できます。
値のみを貼り付けた後に作業セルを削除。
自動では郵便番号変換ウイザードを行えないので手動での操作が必要ですが、一応一括変換は可能。
次からは都道府県付での登録を他の方に義務付ける。
で終了。
この回答への補足
ありがとうございます。
この関数を入れたら一発で出来ました。
マクロはちょっとネットで調べただけだと
私には難しすぎたので、この関数を教えていただけて
助かりました。
郵便番号変換ウィザードも便利ですね。
勉強になりました。ありがとうございました。
No.7
- 回答日時:
お待たせしました
出来ました
ちょっと前置きですが
"市","区"以外に"郡"も検索対象にしないといけないことに気付いたので追加しています
後、
配列数式の方はもっと早くから出来ていたのですが
配列数式を使わないほうがちょっと手間取りました
どちらも凄く長いですが
やっていることは至極単純です
根気よく見て頂ければそうは難しく無いと思います
では本題ですが
まず下処理として
ご用意頂く対応表の郵便番号の列と現行の顧客の所在地一覧中の郵便番号の列のそれぞれに対し
近隣に列を挿入して
=TEXT(MID(「郵便番号の列《例えばA:A》」,1,3),"000")&"-"&TEXT(MID(「郵便番号の列《例えばA:A》」,5,4),"0000")
と入れてデータ範囲の挿入行全体にコピーしてください
具体的な式の例を挙げると
仮に
郵便番号がB列、列挿入で出来た列がD列
データが3行目から500行目まで書かれていたとすると
D3に =TEXT(MID(B:B,1,3),"000")&"-"&TEXT(MID(B:B,5,4),"0000")
と入力し
このD3をD4:D500にコピーしてください
その後再度D3:D500を選択しB3:B500にあたい値貼付けするという作業《あくまで例ですが》を双方(対応表と現行の所在地一覧)に行ってください
その後挿入した列は一旦削除しておいてください
次に現行の所在地一覧に郵便番号がSheet1のB列、Sheet1のC列に住所が
データが3行目から500行目まで
対応表の郵便番号がSheet2のB列、Sheet2のC列に住所が
記載されているとして
Sheet1のC列の右に列を挿入して新たにD列を作ってください
では行きます
パターン1です
そこのSheet1のD3に
=IF(COUNTIF(Sheet2!B:B,B3)>0,LEFT(VLOOKUP(B3,Sheet2!B:C,2,0),MAX(IF(ISERROR(FIND({"市","区","郡"},VLOOKUP(B3,Sheet2!B:C,2,0))),0,FIND({"市","区","郡"},VLOOKUP(B3,Sheet2!B:C,2,0)))))&RIGHT(C3,LEN(C3)-MAX(IF(ISERROR(FIND({"市","区","郡"},C8)),0,FIND({"市","区","郡"},C8)),0)),"照合不能")
と入力しD4:D500にペーストしてください
次にパターン2です
同様にSheet1のD3に
=IF(COUNTIF(Sheet2!B:B,B3)>0,IF(ISERROR(FIND("市",C3)),IF(ISERROR(FIND("区",C3)),IF(ISERROR(FIND("郡",C3)),"照合不能",LEFT(VLOOKUP(B3,Sheet2!B:C,2,0),FIND("郡",VLOOKUP(B3,Sheet2!B:C,2,0)))&RIGHT(C3,LEN(C3)-FIND("郡",C3))),LEFT(VLOOKUP(B3,Sheet2!B:C,2,0),FIND("区",VLOOKUP(B3,Sheet2!B:C,2,0)))&RIGHT(C3,LEN(C3)-FIND("区",C3))),LEFT(VLOOKUP(B3,Sheet2!B:C,2,0),FIND("市",VLOOKUP(B3,Sheet2!B:C,2,0)))&RIGHT(C3,LEN(C3)-FIND("市",C3))),"照合不能")
と入力しD4:D500にペーストしてください
パターン1は配列数式とIF構文によるエラー補完を一部で使っています
おかげで"市","区","郡"の記載すら現行表になくても
それ以降を記載漏れがないように書いていさえすれば
ちゃんと復元してくれます
その点パターン2は"市","区","郡"のいずれかが現行表の住所欄に書かれていないと住所を復元できません
どちらも想定範囲内でエラーが出た場合は「照合不能」と表示しますので
そういう時は現行表の郵便番号が怪しいのですが
人海戦術で修正する必要が出てきます
今回ご説明は割愛しますが
不明点について仰って頂ければ説明しますので
忌憚無く仰って下さい
最後に
この構文の問題点として
"市","区","郡"以降の記載がちゃんとされているかを照合していないので
IF(COUNTIF(Sheet2!B:B,B3)>0,…
のところに条件を追加して精査できるようにしないと
エラーが出てしまう可能性があります
皆さんもお忙しいのにありがとうございました。
エクセルって奥が深いですよね。
もっと勉強して有効活用しないとと思いました。
関数もじっくり勉強していきたいと思います。
ありがとうございました。
No.6
- 回答日時:
こんにちは。
ご質問の内容でしたら、可能だと思いますが、空きの補助列が必要です。
まず、郵便番号ウィザードで、一旦、郵便番号から、住所を出していただく必要があります。その後で、その列と住所を比較して、都道府県が入っていないものに対して、加えるように作ります。
以下のようなマクロで、変更が可能です。以下では、D列を補助列にしています。
D列に対して、C列を変更しますので、Offset(,-1) となります。
それと、頭の3桁で都道府県を抽出するのは大変です。今調べてみましたら、952個もありました。
今は、郵便番号ウィザードの二次的利用が出来ないようになっていますので、ユーザー定義関数による変換が出来ません。
'D1 が先頭でないのでしたら、適当の場所に書き換えてください。
Sub CheckAddress()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("D1", Range("D65536").End(xlUp))
If InStr(c.Offset(, -1), c.Value) = 0 Then
If InStr(c.Value, "県") = 4 Then
c.Offset(, -1).Value = Left(c.Value, 4) & c.Offset(, -1).Value
Else
c.Offset(, -1).Value = Left(c.Value, 3) & c.Offset(, -1).Value
End If
End If
c.ClearContents
Next c
Application.ScreenUpdating = True
End Sub
No.5
- 回答日時:
No.1ですが、「下記サイト」と書いてURLが抜けていましたので
補足しておきます。
http://www.post.japanpost.jp/zipcode/download.html
無条件に都道府県名を付加するだけなら簡単ですが、バラバラというのが厄介ですね。
しかも、手入力なら郵便番号が正しいのかも、正しい形式で入っているのかも
わかりませんよね。
それに、住所も。
先日扱った似たようなデータでは、誤字脱字もあり、市区郡も抜けていたりで
更に郵便番号も入っていないデータがたくさん有り、随分苦労しました。
No.4
- 回答日時:
郵便番号⇔住所対応表さえあれば
2通りの方法が考えられます
1つ目は
現在記載されている内容とその対応表とマッチングし
重複部分を削除すれば完成すると思うのです
具体的に言うと
同一地区を除いて
住所には必ず区か市の名が記載されていなければ用をなさないはずですから
これらはほぼあると見なせると思います
ただ全部調べてないので何ともいえないのですが
区の名を冠する住所の中には市の名を冠しないものがあるようですね
なので
区が双方にあれば今記載されているほうの区以降と対応表の区の名までを繋いでやればいいし
双方に区がなかったとしても
市の名で同様に検索を掛け繋げばいいと思います
ただ問題は現在の記載が
・区の名すら省略している場合
・同一地区ということを前提として大幅に(市町村名すら)記載を省略している場合
が挙げられます
厄介ですね
もう一つの方法として
記載漏れが県名だけで区など以下は書かれているとするならば
県名の入っていないセルにだけ県名を追加すれば完成すると思うのですが
ただ2番目のやり方はmshr1962さんが既にやられているようなので割愛します
申し訳ありませんがただ今考え中です <(_ _)>
しばしご猶予をお願いします
No.3
- 回答日時:
関数だと難しいのではないかと思いますが、やるとするなら
1.郵便番号(頭3桁)と都道府県の表を作成
2.郵便番号の頭3桁で都道府県を呼び出して住所の先頭に付ける関数を作成
1の例
郵便番号都道府県
001北海道
010秋田県
020岩手県
030青森県
040北海道
100東京都
210神奈川
260千葉県
300茨城県
320栃木県
330埼玉県
370群馬県
400山梨県
500岐阜県
600京都府
700岡山県
800福岡県
900沖縄県
950新潟県
960福島県
980宮城県
990山形県
※上記はすべての都道府県は見てません。使うつもりならすべての都道府県を確認してください。
※北海道のように複数の設定範囲を持つ場合があるので注意してください。
※表は郵便番号を昇順で並べ替える必要があります。
2の関数
=VLOOKUP(LEFT(C2,3),表の範囲,2,TRUE)&SUBSTITUTE(D2,VLOOKUP(LEFT(C2,3),表の範囲,2,TRUE),"")
初めのVLOOKUPで都道府県を頭にセット、次のSUBSTITUTEで現在の住所から都道府県があった場合、重複するので削除してます。
No.1
- 回答日時:
関数では無理ですね。
都道府県名が入っていないデータの都道府県名は何から持って来ようとしているのでしょうか?
都道府県の次に来るのは、市区郡ですが、全国となると膨大な量になります。
その市区郡を見て、都道府県を判断するんですか?
それとも郵便番号から?郵便番号は必ず正しく入っているんでしょうか?
また、市区郡が抜けてその次から入っているようなデータは無いですか?
下記サイトから郵便番号データはダウンロードできますので、シートに入っている
郵便番号からダウンロードした郵便番号データを検索して都道府県名が一致しなかったら
付加するようなロジックをVBAで作成すれば可能かもしれません。
もし、VBAでロジックを思うように作成できるスキルをお持ちでなければ、勉強して
挑戦してみるか、手で入力するかの選択になると思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
誕生日にもらった意外なもの
みなさんがもらった誕生日プレゼントで面白いものがあったらぜひ教えてください!
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excel、市から登録している住所録に県を加える
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
お店に入るために行列に並んで...
-
「B列が日曜の場合」C列に/...
-
ワードの表で離れた列の幅を一...
-
Excel、市から登録している住所...
-
【VBA】特定列に文字が入ってい...
-
エクセル 文字数 多い順 並...
-
エクセル(勝手に太字になる)
-
エクセルの表から正の数、負の...
-
VBAで文字列を数値に変換したい
-
基準となる列を参照して、別の...
-
エクセルの関数(日数の平均の...
-
Excel VBAでデータ比較
-
エクセルかグーグルスプレッド...
-
エクセルで2列のセルを連続して...
-
文字列に数字を含むセルを調べたい
-
エクセル 時間帯の重複の有無
-
エクセルの項目軸を左寄せにしたい
-
複素数を描けるソフトについて
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
エクセル 文字数 多い順 並...
-
Excelで半角の文字を含むセルを...
-
エクセル(勝手に太字になる)
-
2つのエクセルのデータを同じよ...
-
エクセルで文字が混じった数字...
-
エクセルの項目軸を左寄せにしたい
-
エクセルの並び変えで、空白セ...
-
「B列が日曜の場合」C列に/...
-
Excel、市から登録している住所...
-
VBAで文字列を数値に変換したい
-
エクセルの表から正の数、負の...
-
エクセル 同じ値を探して隣の...
-
エクセル 時間帯の重複の有無
-
エクセルで、列の空欄に隣の列...
-
文字列に数字を含むセルを調べたい
-
EXCELで 一桁の数値を二桁に
-
エクセルで一行毎、一枚づつ自...
おすすめ情報