【Excel】 住所の県、建物でセルを分けたい。

こんにちは

A1 に、東京都千代田区○○1-1○○荘203
という住所があります。

これを、
B1 に 東京都
C1 に 千代田区○○1-1
D1 に ○○荘203

と、分けたいのですが、B1、C1、D1は
それぞれどのような式になるでしょうか。

なお、住所は都道府県表示が無い場合があります。
番地部分には、「丁目」「番地」などの文字はありません。
建物表示も無い場合があります。

Excel2007です。

よろしくお願いします。

No.8 回答者： kagakusuki 回答日時： 2013/12/31 17:41

　回答No.4、6です。

>「これまでのデータの修正が大変でして・・・

という事は、B列～C列のセルにどの様なデータが入っているのかという事を判定の基準として、何か別の情報処理を行うとか、B列～C列のセルに表示されている結果をそのまま印刷して使うといった使い方ではなく、単に、情報の修正作業の手間を減らす事が出来れば良いという事なのですね？
　そうしますと、完全な自動化は無理でも、住所と建物名を機械的に分離する事が可能なものだけを分離しておき、住所と建物名の区別が機械には判断出来ないものに関しては、例えば「分離不能」等と表示させておいて、分離不能なものに関しては後から人間が確認して分離する、といった方法でもある程度は手間を減らす事が出来ると思うのですが如何でしょうか？
　それと、確認したいのですが、質問者様が実際に行おうとしている事は、
A列にはどこか別の所にあった3つの住所データを繋げた文字列データを貼り付けておき、
そのデータを利用して、B列～C列に都道府県名と（都道府県を除いた）住所、建物名の3つに分けて表示させ、
そのB列～C列に表示されているデータをコピーして、実際に使用する住所1、住所2、住所3の入力欄に貼り付ける
といった流れになると思うのですが、この様な理解で合っていますでしょうか？
　もし、上記の理解であっているとしますと、D列やE列といった他の列は使用していないと思われますから、それらの列に「分離不能」とか、「確認済み」といった内容を表示させたり、入力したりするために使用しても問題が無い様に思えるのですが、それでも宜しいでしょうか？
　もし、その様な方法でも宜しければ、関数と作業列を使用した方法と、VBAを使用する方法が考えられます。
　今、私が思いついている方法では、関数と作業列を使用した方法よりも、VBAを使用する方法の方が、若干、人間の手作業が少なくなると予想されるので、出来ればVBAによる方法をお勧めしたいのですが、VBAを使う上で何か問題になる事は特にないでしょうか？

No.7 回答者： bunjii 回答日時： 2013/12/31 12:39

>作業用シートのAC列の都道府県検索用データとAD列の都道府県名の文字列は、同じ内容を2列(48都道府県)を書けばよいのでしょうか。

ＡＣ列は逆検索用でＡＤ列はその返り値です。
通常はＡＣ列のみで良いのですが、京都府の場合は元の文字列の先頭から"都"文字で有無を確かめると東京都の"都"と誤認します。
それをVLOOKUP関数で逆検索した結果を返す値がAD列になります。
従って、京都府の場合はAC列に"京都"として返り値のAD列は"京都府"としなければなりません。
他の都道府県で市町村名に都道府県の内の文字が含まれた時に逆検索の返り値が異なるときに都道府県名が省略しているものと見做すように処理しています。

従って、現時点では京都府のみはAC列とAD列が異なります。
他に誤検出が出た時は修正が必要でしょう。

No.6 回答者： kagakusuki 回答日時： 2013/12/27 08:35

　回答No.4です。

>「空知郡南幌町南十一線西」として登録してもらうとか…

等の様に、番地よりも前にはアラビア数字が来ない様にするという条件が必ず守られていて、尚且つ、

>番地部分には、「丁目」「番地」などの文字はありません。

という条件も必ず守られている場合には、次の様な方法で、「都道府県」、「都道府県を除いた住所」、「建物名」のそれぞれを分離して表示させる事が出来ます。

　まず、B1セルに都道府県名を表示させる方法に関しては、回答No.4の方法をそのまま使います。
　次に、「建物名」を表示させるセルであるD1セルには、次の様な関数を入力して下さい。

=IF(OR(ISNUMBER(FIND(1,ASC($A1))),ISNUMBER(FIND(2,ASC($A1))),ISNUMBER(FIND(3,ASC($A1))),ISNUMBER(FIND(4,ASC($A1))),ISNUMBER(FIND(5,ASC($A1))),ISNUMBER(FIND(6,ASC($A1))),ISNUMBER(FIND(7,ASC($A1))),ISNUMBER(FIND(8,ASC($A1))),ISNUMBER(FIND(9,ASC($A1)))),REPLACE($A1,1,FIND(0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($A1),1,0),2,0),3,0),4,0),5,0),6,0),7,0),8,0),9,0))-1+SUMPRODUCT(ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(ASC($A1),"‐",0),"-",0),FIND(0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($A1),1,0),2,0),3,0),4,0),5,0),6,0),7,0),8,0),9,0)),ROW(INDIRECT("Z1:Z"&LEN($A1)-FIND(0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($A1),1,0),2,0),3,0),4,0),5,0),6,0),7,0),8,0),9,0))+1)))+0)*1),),"")

　そして、「都道府県を除いた住所」を表示させるセルであるC1セルには、次の様な関数を入力して下さい。

=IF($A1="","",MID($A1,LEN($B1)+1,LEN($A1)-LEN($B1)-LEN($D1)))

　以上です。



　尚、住所の記述形式を「東京都千代田区○○1-1○○荘203」等の様に番地と建物名を密着させるのではなく、「東京都千代田区○○1-1 ○○荘203」等の様に、番地と建物名との間に必ず空白（或いは空白の代わりに「#」とか「◆」等の「漢字や仮名文字ではない記号」でも可）を挟む様にして頂けるのであれば、（例え、番地よりも前にアラビア数字が来ていたとしても）それを基準に住所と建物名を分離する事が出来ます。
　その際、番地よりも前の部分には、空白または基準となる特定の記号が含まれていない方が、分離しやすくなるので望ましいのですが、例え、番地よりも前の部分に空白または基準となる特定の記号が含まれていたとしましても、(上記の関数よりも更に複雑な関数となってしまいますが)分離する事は不可能では御座いません。
　ですから、番地と建物名との間に空白等を挟む様にする事が可能か否かを御教え願います。

この回答への補足

ご回答ありがとうございます。

番地と建物の間に、空白や＃などの
識別文字が入れられるかという問題ですが、
実は、元の入力用フォームが住所1、住所2、住所3と
なっているのですが、
複数のスタッフが、その都度入力しており、
住所1には都道府県名を含む地名、住所2に番地、
住所3に建物名および部屋番号・・・といったルールを
明確にしていなかったため、住所1にすべて書いてあったり、
住所1に市町村名まで、住所2に町名と番地・・・
などというようになっており、3つの住所データを繋げたので、
識別文字が入れられません。

今後は入力時に分けるつもりですが、
これまでのデータの修正が大変でして・・・

補足日時：2013/12/31 11:10

No.5 回答者： bunjii 回答日時： 2013/12/26 20:57

回答No.3の追加情報としてご確認ください。

元データのシート名を「住所録」としました。
作業用シート名を「作業用」としました。

作用用シートのA列～Z列へ元データの住所を1文字ずつ切り出します。
AA列へは都道府県が検出された文字数を算出します。
AB列へは住所の番地に当たる半角数字と他店の名の全角文字の境界を算出します。
AC列は都道府県名の検索用データをセットします。
AD列は都道府県名の返り値とする正しい文字列をセットします。
この2つの列は京都府の時に東京都の「都」と京都府の「都」が混同して検出されるため都道府県名のマスターリストでも逆検索で確認できないことを補うためです。
AC列の「京都」対してAD列の返り値を「京都府」としてあります。

作業用の各セルの数式は次のようにしました。
AA1=LEN(IFERROR(VLOOKUP(LEFT(住所録!A1,MIN(IF((A1:Z1="府")+(A1:Z1="県")+(A1:Z1="道")+(A1:Z1="都"),COLUMN(A1:Z1),""))),AC$2:AD$48,2,FALSE),""))
AB1=MIN(IF(IFERROR((CODE(A1:Y1)<58)*(CODE(B1:Z1)>256),0)=0,LEN(住所録!A1),COLUMN(A1:Y1)))
この二つの式は配列の返り値を必要とするためCtrl+Shift+Enterで確定します。

住所録の各セルの数式は次のようにしました。
B1=LEFT(A1,作業用!AA1)
C1=MID(A1,LEN(B1)+1,作業用!AB1-LEN(B1))
D1=MID(A1,作業用!AB1+1,LEN(A1)-作業用!AB1)

元データの地名に算用数字が含まれるときは全角の数字（漢数字ではない）で入力されていることが条件になります。
番地には必ず半角の数字半角のマイナス（-）を使うことも条件になります。
関数のみで処理するには無理がありそうです。

回答No.4で提示された数式を試しました。
この式は良くできていますので採用の価値ありと判断します。
私には動作の論理が理解できませんでしたが結果は確実のようです。
私の代案は誤って検出されることを予測して検出された"都"、"道"、"府"、"県"の切り出し文字列でマスターとなるデータにVLOOKUP関数で検索を掛けています。
検出できないときは市区町村名や字などの地名に含まれるものとしてブランクが返ります。
実際のデータでテストし、誤検出がありましたら式を修正したり、マスター（都道府県名の検索列のデータ）を補正してください。

この回答への補足

ご回答ありがとうございます。

同じように作ってみました。
見事に思っていたように分けられました。
ありがとうございました。

ただ、1点わからないところがあります。
作業用シートの
AC列の都道府県検索用データと
AD列の都道府県名の文字列は、
同じ内容を2列(48都道府県)を書けばよいのでしょうか。

補足いただけましたら幸いです。

補足日時：2013/12/31 10:48

No.4 回答者： kagakusuki 回答日時： 2013/12/26 13:36

>都道府県名の無い住所から、正しい住所を判断する必要はございません。

>その行の入力者が都道府県を入力したか否かということです。

という事は、都道府県が入力されていないものに関しては、都道府県の表示欄であるB列のセルを空欄とすれば良いという事でしょうか？
　もし、そうであるならば、都道府県を表示させる際には次の様にされると良いと思います。
　まず、適当な使用していないセル範囲に47都道府県名を全て入力して下さい。
　ここでは仮にSheet2のA2～A48の範囲に、北海道から沖縄までの都道府県名を入力するものとします。
　次に、A列に住所が入力されているシートのB1セルに次の様な関数を入力して下さい。

=IF(SUMPRODUCT(COUNTIF($A1,Sheet2!$A$2:$A$48&"*")*1),INDEX(Sheet2!$A:$A,SUMPRODUCT(ROW(Sheet2!$A$2:$A$48)*COUNTIF($A1,Sheet2!$A$2:$A$48&"*"))),"")

　そして、A1セルをコピーして、A2以下に貼り付けて下さい。

　因みに、都道府県を切り出すために、何故、47都道府県の名称のリストが必要になるのかと言いますと、例えば「小県郡青木村」、「長野市県町」、「岐阜市山県岩」、「宮城郡利府町」、等々の様に、「都」、「道」、「府」、「県」の文字を含んでいる地名は47都道府県に限っている訳ではないのですから、単に「都」、「道」、「府」、「県」の文字を基準に切り出しただけでは、A列に入力されている住所に都道府県表示が無い場合において、誤った都道府県名が表示されてしまう恐れがあるからです。
　又、例えば、「府」の文字の有無を判定する前に、「道」の文字の有無を判定して、「道」の文字の所までを切り出す様な関数とした場合には、「京都府京都市北区衣笠街道町」等の様にな場合には、B列には「京都府」ではなく、「京都府京都市北区衣笠街道」と表示されてしまいます。
　だからと言って、「道」の文字の有無を判定する前に、「府」の文字の有無を判定して、「府」の文字の所までを切り出す様な関数とした場合には、「北海道帯広市別府町」等の様にな場合には、B列には「北海道」ではなく、「北海道帯広市別府」と表示されてしまいます。
　だからと言って、「都」、「道」、「府」、「県」の何れかの文字の中で、最も先に現れた文字の所までを切り出す関数とした場合には、「京都府」の場合には「府」ではなく「都」が先になっていますので、「京都府」ではなく、「京都」と表示されてしまいます。
　この様な例は他にも多数あると思われますので、都道府県名を求める際には、単純に「都」、「道」、「府」、「県」の文字の所までを切り出す訳には行かず、47都道府県の全ての名称と比較する必要がある訳です。


　尚、都道府県以下の住所と建物名の分離方法に関してはただいま検討中ですが、「空知郡南幌町南１１線西」等の様に、番地よりも前の部分にアラビア数字が含まれているパターンがあるため難航しております。

この回答への補足

ご回答、ありがとうございます。

都道府県のリストが必要な理由がよくわかりました。
近隣府県の2万件程度の名簿だったので、たまたま京都府の住所が
無かったに過ぎません。
リストを用いるようにいたします。

また、番地の前にアラビア数字が使われる住所も見受けられませんでした。
確かに今後はあり得るのかもしれません。
今後そのような住所を登録する場合、
「空知郡南幌町南十一線西」として登録してもらうとか…

気軽に「教えて」と言ってみたものの、
大変なことになってしまいました。
申し訳ございません。

補足日時：2013/12/26 14:45

No.3 回答者： bunjii 回答日時： 2013/12/25 17:18

>住所と建物名の間に半角英数字が入るので、それで分けられないかと考えていましたが、やはり式だけでは無理でしょうか。

半角数字+全角の組み合わせが文字列中の何処にあるかを検出する関数が見当たりません。
作業用のシートを用意して位置を検出することは可能ですがVBAで処理された方がベターと思います。

No.2 回答者： kagakusuki 回答日時： 2013/12/25 15:12

　一つ確認したい事があります。

>なお、住所は都道府県表示が無い場合があります。

との事ですが、全国には伊達市が2箇所、府中市が2箇所あるのですが、都道府県名無しで府中市と書かれているものがあった場合などには、どの府中市の事なのかを、現行ではどうやって判断しておられるのでしょうか？
　同様の問題は、郡名に関しても存在するかも知れません。（こちらは郡名＋町村名で判断する事が出来るか否かまだ確認してはおりません）
　それと、市ではなく、町村の場合においては、必ず郡名が付いた形式で書かれているのでしょうか？
　もし、都道府県名と郡名の両方が省略されていて、いきなり町村名から始まっている様なものがある場合には、どの町村の事であるのかを判断する事は難しいと思います。

この回答への補足

ご回答ありがとうございます。

欄外に郵便番号がござます。
また、A列のデータはすでに入力済みですので、
都道府県名の無い住所から、正しい住所を判断する必要はございません。
その行の入力者が都道府県を入力したか否かということです。

今回の場合、住所であることでややこしくなってしまいましたが、

A1の内容が、
【都道府県部分】テキスト+(都道府県)+
【市町村番地部分】テキスト+(前の半角英数字+ハイフン)+
【建物名部分】テキスト+(後の半角英数字+ハイフン)

と、考えて3セルに分けられないかと言うことです。

よろしくお願いいたします。

補足日時：2013/12/25 16:01

No.1 回答者： bunjii 回答日時： 2013/12/25 14:23

都道府県を切り出すことは可能です。

=LEFT(A1,IFERROR(FIND("県",A1),IFERROR(FIND("都",A1),IFERROR(FIND("府",A1),IFERROR(FIND("道",A1),0)))))

番地と建物表示の区切りが検出できないので建物表示を区別するのは無理かと思われます。
マクロを使えば可能と思いますが不得手なので割愛します。

この回答への補足

ご回答ありがとうございます。

都道府県は、分けられました。
ありがとうございます。
住所と建物名の間に半角英数字が入るので、
それで分けられないかと考えていましたが、
やはり式だけでは無理でしょうか。

補足日時：2013/12/25 16:05