EXCELで住所リストから適切な町名を参照したい

質問

EXCELの関数の使い方について、質問させていただきます。

リスト１には、郵便番号と住所のリストが入っていますが、
郵便番号が重複しています。（同じ郵便番号で地名違いのものがある）

---------------------------------------------------------------------------------
【リスト１】

A郵便番号　B県名 C住所１ D住所２
------------------------------------------------
0493521　　北海道　　山越郡長万部町　　曙町
0493521　　北海道　　山越郡長万部町　　大町
0493521　　北海道　　山越郡長万部町　　長万部
0493521　　北海道　　山越郡長万部町　　温泉町
0493521　　北海道　　山越郡長万部町　　新開町
0493521　　北海道　　山越郡長万部町　　陣屋町
0493521　　北海道　　山越郡長万部町　　住吉町
0493521　　北海道　　山越郡長万部町　　高砂町
0493521　　北海道　　山越郡長万部町　　本町
0493521　　北海道　　山越郡長万部町　　南栄町
0493521　　北海道　　山越郡長万部町　　元町

---------------------------------------------------------------------------------

リスト２には、ある住所録があるのですが、
住所が結合されているため、
リスト１と郵便番号を使って、
結合住所を、県名、住所１、住所２、住所３に分割したいと思っています。

県名、住所１は、vlookup関数で参照しても問題ないのですが、
住所２は、郵便番号が重複しているため、vlookup関数では、全て「曙町」になってしまいます。

住所２に、適切な地名を表示させたいのですが、どのようにすれば良いでしょうか？

---------------------------------------------------------------------------------
【リスト２】

A郵便番号　B結合住所　　　　　　　　　　 　　　　　　C住所１ 　　D住所２ E住所３
-----------------------------------------------------------------------------------
0493521　　山越郡長万部町長万部XXX-XXXX　　山越郡長万部町　長万部　 XXX-XXXX
0493521　　山越郡長万部町陣屋町XXX　　　　　　 山越郡長万部町　陣屋町　 XXX
0493521　　山越郡長万部町本町XXXX　　　　　　　山越郡長万部町　本町　　　XXXX

---------------------------------------------------------------------------------

住所２が正しく出ないため、
住所３も導けず、困っております。
（住所３は、SUBSTITUTE(B1,C1&D1,"") などとして導く予定です。）

何卒、ご教授をよろしくお願い致します。

回答No.3

bunjii · ベストアンサー

>郵便番号で絞り込みを考慮しないと難しいようなのですが、
>そういった事を式で実現することは可能でしょうか？
可能ですよ。
郵便番号でなくても住所1（市区町村名）と住所2（大字名）を連結した文字列を検索文字列とすれば単一の行Noがヒットすると思います。
=IF(G2="","",INDEX($D:$D,MAX(IFERROR((FIND(C$1:C$27&D$1:D$27,G2)>0)*1,0)*ROW(D$1:D$27)),1))
それでも複数がヒットするときは都道府県名も連結してみると良いでしょう。

お礼

bunjiiさん、ご返信いただき、ありがとうございました。

なるほど、連結したものを検索文字列に使うのですね…
そういった柔軟な発想が思いつかず、苦戦しておりました。
とても勉強になりました。
いただいた式を完全に理解するのには、まだ時間がかかりそうですが、
勉強したいと思います。

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

回答No.2

bunjii

住所2は次のような方法で抽出できます。
FIND関数は一般的に1つの文字列を1つの長い文字列から検出しますが、配列値を検索文字列として使うこともできます。
=(FIND(リスト1!D:D,B2)>0)*ROW(1:1000)
Ctrl+Shift+Enterで確定すると返り値が配列となりますので、MAX関数に渡せは該当の行番号が得られます。
この性質を応用すればINDEX関数と組み合わせて住所2の抽出が可能となります。

貼付画像は同一シートでの検証ですがあなたのデータに合わせて変形してください。
=IF(G2="","",INDEX($D:$D,MAX(IFERROR((FIND(D$1:D$12,G2)>0)*1,0)*ROW(D$1:D$12)),1))

補足

bunjiiさん

御回答いただき、ありがとうございました。

うわぁ、すごいですね、関数を複雑に組み合わせると、こんな事が出来るんですね。
添付していただいた画像のようにサンプルを作成し、確認したところ、
希望の通りに導くことが出来ました。

ただ、これをサンプルのように狭い地区データではなく、
全国規模の本番データに転用しようとすると難しいみたいで…

（考えられる理由）
・サンプルでＤ列にあたる部分の行数が、本番データでは１万件を超える。
・全国だと、Ｄ列の中でも重複する町名がある。
（例えば「新田」が含まれる町名は、日本全国で１千箇所を超える）

よって、郵便番号で絞り込みを考慮しないと難しいようなのですが、
そういった事を式で実現することは可能でしょうか？

例えば、こんなデータで…
---------------------------------------------------
リスト１（宮城県の新田が含まれる地域のみ）

9830038,宮城県,仙台市宮城野区,新田
9830039,宮城県,仙台市宮城野区,新田東
9860321,宮城県,石巻市,桃生町新田
9880828,宮城県,気仙沼市,下新田
9880829,宮城県,気仙沼市,新田
9850854,宮城県,多賀城市,新田
9870378,宮城県,登米市,豊里町新田鏡形
9870361,宮城県,登米市,豊里町新田町
9894601,宮城県,登米市,迫町新田
9870422,宮城県,登米市,南方町新田
9894511,宮城県,栗原市,瀬峰新田沢
9872031,宮城県,栗原市,高清水新田
9872263,宮城県,栗原市,築館新田
9810307,宮城県,東松島市,新田
9896711,宮城県,大崎市,鳴子温泉新田
9896226,宮城県,大崎市,古川新田
9896203,宮城県,大崎市,古川飯川
9812116,宮城県,伊具郡丸森町,新田向
9812116,宮城県,伊具郡丸森町,新田東
9812116,宮城県,伊具郡丸森町,新田西
9892351,宮城県,亘理郡亘理町,北新田
9814222,宮城県,加美郡加美町,下新田
9870144,宮城県,遠田郡涌谷町,沖新田
9870144,宮城県,遠田郡涌谷町,下新田
9870140,宮城県,遠田郡涌谷町,新沖新田
9870133,宮城県,遠田郡涌谷町,新田

---------------------------------------------------
リスト２
（県名,住所１,住所２,住所３は、自動検出できた場合の理想のデータ）

郵便番号,結合住所,県名,住所１,住所２,住所３
9894601,宮城県登米市迫町新田山居XXX,宮城県,登米市,迫町新田,山居XXX
9894601,宮城県登米市迫町新田畑中XXX,宮城県,登米市,迫町新田,畑中XXX
9896226,宮城県大崎市古川新田字中宿XXX,宮城県,大崎市,古川新田,字中宿XXX
9896203,宮城県大崎市古川飯川中新田XXX,宮城県,大崎市,古川飯川,中新田XXX
9814222,宮城県加美郡加美町下新田寺浦XXX,宮城県,加美郡加美町,下新田,寺浦XXX

---------------------------------------------------

教えていただいた式を、まだ半分も理解できていないので、頓珍漢な事を
申し上げているかも知れません。（その場合は、失礼いたしました。）

回答No.1

MackyNo1

エクセルアドインの郵便番号ウィザードを利用するのが簡単です。

Excelのバージョンが記載されていませんので一般論で説明しますが、郵便番号ウィザード(ご自分でバージョンにあった郵便番号ウィザードをインストールしてください）を起動して、リスト2の郵便番号部分を選択し、「郵便番号から住所を作成する」にチェックを入れ、出力先の先頭セルを指定します。
この操作で郵便番号の確定部分（北海道山越郡長万部町までの部分）が抽出できますのでSUBSTITUTE関数でそのあとの町名部分を取得してください。

なお、郵便番号確定部分以降の部分に番地などが入っている場合は、関数を使って番地部分を一括削除することができます。

ひとまず、文字列関数や郵便番号ウィザードは使用できるという前提で一般的な回答をしましたが、上記の説明でわからない部分があったら、その部分を具体的に補足説明してください。

補足

MackyNo1さん、御回答ありがとうございました。
Excelのバージョンの記入漏れ、大変失礼いたしました。
使用しているExcelは、2010（32ビット）です。OSはWindows7（64ビット）です。

郵便番号ウィザードをインストールしてみたのですが、
いざ使用しようとすると、「郵便番号変換エンジンが見つかりません。」エラーになります。
OSはWindows7（64ビット）ですが、Officeが32ビット版なので、
郵便番号ウィザードもちゃんと32ビット版を選んでいます。

Windows7にIME2010をインストールできないせいなのか、
全く起動してくれません。

よって、試せていない状態でのご返信となり、
大変失礼いたします。

郵便番号ウィザードが正しく起動した場合でも、
郵便番号が重複しているケースがありますので、
確定部分が出ないケースがあるのではないかと想像しております。

例えば、
北海道札幌市厚別区と、
北海道札幌市清田区は、共に、004-0000になります。

IMEの郵便番号辞書は起動したので、こちらで確認したのですが、
候補が２つでます。

郵便番号の確定部分（北海道山越郡長万部町までの部分）が出るとありましたが、
確定部分は、その次の「曙町」なども含まれているのではないでしょうか？

また、住所２の後ろに、地番以外の住所が付くケースもあります。
例えば、

「北海道 山越郡長万部町 曙町 字○○ １－１－１」といった感じです。

関数で番地を消せても、残りの住所が残って「曙町字○○」となってしまい、
理想的な住所２（曙町のみ）にならないのではないかと思うのですが…？？

いずれにしても、郵便番号ウィザードが起動してくれないので…しょぼぼんです…。
せっかく御回答いただいたのに、申し訳ありません。
ありがとうございました。