こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

---------------------------------------------------------------------------------
【リスト1】

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

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

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

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

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

---------------------------------------------------------------------------------
【リスト2】

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

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

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

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

投稿日時 - 2014-07-09 19:22:36

QNo.8671434

困ってます

質問者が選んだベストアンサー

>郵便番号で絞り込みを考慮しないと難しいようなのですが、
>そういった事を式で実現することは可能でしょうか?
可能ですよ。
郵便番号でなくても住所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))
それでも複数がヒットするときは都道府県名も連結してみると良いでしょう。

投稿日時 - 2014-07-11 17:59:08

お礼

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

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

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

投稿日時 - 2014-07-16 09:14:33

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

住所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))

投稿日時 - 2014-07-10 10:48:44

補足

bunjiiさん

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

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

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

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

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

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

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,宮城県,遠田郡涌谷町,新田

---------------------------------------------------
リスト2
(県名,住所1,住所2,住所3は、自動検出できた場合の理想のデータ)

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

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

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

投稿日時 - 2014-07-11 14:47:16

ANo.1

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

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

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

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

投稿日時 - 2014-07-10 10:21:01

補足

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

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

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


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


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

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

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

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


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

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

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

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

投稿日時 - 2014-07-11 16:23:52

あなたにオススメの質問