エクセルの関数でできますか？

質問

エクセルに北海道から沖縄まで、顧客の所在地一覧を管理してます。
Ａの列に事業者名、Ｂに郵便番号Ｃに住所が入っています。
ですが、入力した人によって、住所が県から入力されてたり
市から入力されてたりバラバラなので、県名が入っていないところは
市の前に県名を挿入したいのですが、一括で県毎に挿入できるように
関数などで出来るのでしょうか？
過去の質問など調べましたが、分かるものが無かったのでご質問させて
いただきました、詳しい方ご教授お願いいたします。

回答No.2

web2525 · ベストアンサー

とりあえず【郵便番号変換ウイザード】のアドインをMSのHPからDLして下さい。
http://www.microsoft.com/downloads/details.aspx?familyid=6F6AF8EF-B9DD-4E21-9E63-AF4A0FF4E7CE&displaylang=ja
作業セルを用意して(仮に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

Nouble

お待たせしました
出来ました

ちょっと前置きですが
"市","区"以外に"郡"も検索対象にしないといけないことに気付いたので追加しています

後、
配列数式の方はもっと早くから出来ていたのですが
配列数式を使わないほうがちょっと手間取りました

どちらも凄く長いですが
やっていることは至極単純です
根気よく見て頂ければそうは難しく無いと思います

では本題ですが
まず下処理として
ご用意頂く対応表の郵便番号の列と現行の顧客の所在地一覧中の郵便番号の列のそれぞれに対し
近隣に列を挿入して
=TEXT(MID(「郵便番号の列《例えばA:A》」,1,3),"000")&"-"&TEXT(MID(「郵便番号の列《例えばA:A》」,5,4),"0000")
と入れてデータ範囲の挿入行全体にコピーしてください

具体的な式の例を挙げると
仮に
郵便番号がＢ列、列挿入で出来た列がＤ列
データが3行目から500行目まで書かれていたとすると
Ｄ３に　=TEXT(MID(B:B,1,3),"000")&"-"&TEXT(MID(B:B,5,4),"0000")
と入力し
このＤ３をＤ４:Ｄ５００にコピーしてください

その後再度Ｄ３:Ｄ５００を選択しＢ３:Ｂ５００にあたい値貼付けするという作業《あくまで例ですが》を双方(対応表と現行の所在地一覧)に行ってください
その後挿入した列は一旦削除しておいてください

次に現行の所在地一覧に郵便番号がSheet1のＢ列、Sheet1のＣ列に住所が
データが3行目から500行目まで
対応表の郵便番号がSheet2のＢ列、Sheet2のＣ列に住所が
記載されているとして
Sheet1のＣ列の右に列を挿入して新たにＤ列を作ってください

では行きます
パターン1です
そこのSheet1のＤ3に
=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のＤ3に
=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

Wendy02

こんにちは。

ご質問の内容でしたら、可能だと思いますが、空きの補助列が必要です。
まず、郵便番号ウィザードで、一旦、郵便番号から、住所を出していただく必要があります。その後で、その列と住所を比較して、都道府県が入っていないものに対して、加えるように作ります。

以下のようなマクロで、変更が可能です。以下では、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

Yeti21

No.1ですが、「下記サイト」と書いてURLが抜けていましたので
補足しておきます。

http://www.post.japanpost.jp/zipcode/download.html

無条件に都道府県名を付加するだけなら簡単ですが、バラバラというのが厄介ですね。
しかも、手入力なら郵便番号が正しいのかも、正しい形式で入っているのかも
わかりませんよね。
それに、住所も。
先日扱った似たようなデータでは、誤字脱字もあり、市区郡も抜けていたりで
更に郵便番号も入っていないデータがたくさん有り、随分苦労しました。

回答No.4

Nouble

郵便番号⇔住所対応表さえあれば
２通りの方法が考えられます

１つ目は
現在記載されている内容とその対応表とマッチングし
重複部分を削除すれば完成すると思うのです

具体的に言うと
同一地区を除いて
住所には必ず区か市の名が記載されていなければ用をなさないはずですから
これらはほぼあると見なせると思います
ただ全部調べてないので何ともいえないのですが
区の名を冠する住所の中には市の名を冠しないものがあるようですね

なので
区が双方にあれば今記載されているほうの区以降と対応表の区の名までを繋いでやればいいし
双方に区がなかったとしても
市の名で同様に検索を掛け繋げばいいと思います

ただ問題は現在の記載が
・区の名すら省略している場合
・同一地区ということを前提として大幅に(市町村名すら)記載を省略している場合
が挙げられます
厄介ですね

もう一つの方法として
記載漏れが県名だけで区など以下は書かれているとするならば
県名の入っていないセルにだけ県名を追加すれば完成すると思うのですが

ただ２番目のやり方はmshr1962さんが既にやられているようなので割愛します

申し訳ありませんがただ今考え中です　<(_　_)>
しばしご猶予をお願いします

回答No.3

mshr1962

関数だと難しいのではないかと思いますが、やるとするなら
１．郵便番号（頭3桁）と都道府県の表を作成
２．郵便番号の頭3桁で都道府県を呼び出して住所の先頭に付ける関数を作成
１の例
郵便番号 都道府県
001 北海道
010 秋田県
020 岩手県
030 青森県
040 北海道
100 東京都
210 神奈川
260 千葉県
300 茨城県
320 栃木県
330 埼玉県
370 群馬県
400 山梨県
500 岐阜県
600 京都府
700 岡山県
800 福岡県
900 沖縄県
950 新潟県
960 福島県
980 宮城県
990 山形県
※上記はすべての都道府県は見てません。使うつもりならすべての都道府県を確認してください。
※北海道のように複数の設定範囲を持つ場合があるので注意してください。
※表は郵便番号を昇順で並べ替える必要があります。

２の関数
=VLOOKUP(LEFT(C2,3),表の範囲,2,TRUE)&SUBSTITUTE(D2,VLOOKUP(LEFT(C2,3),表の範囲,2,TRUE),"")
初めのVLOOKUPで都道府県を頭にセット、次のSUBSTITUTEで現在の住所から都道府県があった場合、重複するので削除してます。

回答No.1

Yeti21

関数では無理ですね。

都道府県名が入っていないデータの都道府県名は何から持って来ようとしているのでしょうか？
都道府県の次に来るのは、市区郡ですが、全国となると膨大な量になります。
その市区郡を見て、都道府県を判断するんですか？
それとも郵便番号から？郵便番号は必ず正しく入っているんでしょうか？
また、市区郡が抜けてその次から入っているようなデータは無いですか？

下記サイトから郵便番号データはダウンロードできますので、シートに入っている
郵便番号からダウンロードした郵便番号データを検索して都道府県名が一致しなかったら
付加するようなロジックをVBAで作成すれば可能かもしれません。
もし、VBAでロジックを思うように作成できるスキルをお持ちでなければ、勉強して
挑戦してみるか、手で入力するかの選択になると思います。