Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
住所の分割
投稿日時: 20/01/06 17:28:36
投稿者: ねね

こんにちは。
 
千葉県の住所から郵便番号を検索する作業をしているのですが、
元データは
千葉県千葉市中央区新宿1-2-3 田中ビル5F
のように県名から番地まですべて入っています。
 
このデータを関数を使って分割したいです。
=IF(MID(A2,3,1)="県",LEFT(A2,3),"")
で千葉県を返し
=MID(A2,LEN(F2)+1,LEN(A2)-LEN(F2))
で千葉市中央区新宿1-2-3 田中ビル3F
を返すところまではできました。
 
ここからさらに番地から後ろを分割したいです。
上記の方法ではかなり長い式を組む必要があるため断念しました。
数字から後ろで分割できたら...と考えたのですが、うまくできませんでした。
 
最終的には日本郵便で配布されているCSVを使って
住所を貼り付けるだけで郵便番号が返ってくるようにしたいです。
 
よろしくお願い致します。

回答
投稿日時: 20/01/06 18:52:24
投稿者: んなっと

「数字から後ろを分離」だけなら簡単です。
 
              A    B           C     D
1            住所                     
2     埼玉県白岡市白岡248 埼玉県      白岡市白岡    248
3     東京都品川区2-2-20 東京都        品川区   2-2-20
4   仙台市青葉区本町2-15-1       仙台市青葉区本町   2-15-1
5    札幌市北区北7条西4-12         札幌市北区北 7条西4-12
6    盛岡市盛岡駅前通15-20       盛岡市盛岡駅前通   15-20
7  東京都品川区東品川2-2-20 東京都     品川区東品川   2-2-20
8  群馬県高崎市あら町167番地 群馬県     高崎市あら町  167番地
9 新潟市中央区東大通り2-4-10     新潟市中央区東大通り   2-4-10
 
B2
=IF(OR(MID(A2,2,2)={"街道","宰府","諸県"}),"",LEFT(A2,IF(OR(MID(A2,3,1)={"都","道","府","県"}),3,IF(MID(A2,4,1)="県",4,))))
下方向・↓
C2
=REPLACE(LEFT(A2,MATCH(TRUE,INDEX(ISNUMBER(1*MID(A2,ROW($1:$20),1)),),0)-1),1,LEN(B2),)
下方向・↓
D2
=REPLACE(A2,1,SUMPRODUCT(LEN(B2:C2)),)
下方向・↓
 
「仙台市青葉区本町」を「仙台市青葉区」と「本町」に分けるところからが面倒。
日本郵便で配布されているCSVを見れば、一筋縄ではいかないことがわかると思います。

回答
投稿日時: 20/01/07 13:43:43
投稿者: んなっと

●Sheet2 に*.csv読み込み
 
    A   B    C    D    G       H            I
1 01101 060  0600000 ホッカイドウ 北海道 札幌市中央区  以下に掲載がない場合
2 01101 064  0640941 ホッカイドウ 北海道 札幌市中央区         旭ケ丘
3 01101 060  0600041 ホッカイドウ 北海道 札幌市中央区         大通東
4 01101 060  0600042 ホッカイドウ 北海道 札幌市中央区 大通西(1〜19丁目)
 
●Sheet1
                               A    B
 1                            住所    〒
 2                     埼玉県白岡市白岡248 3490218
 3                     東京都品川区2-2-20 1400000
 4                   仙台市青葉区本町2-15-1 9800014
 5                    札幌市北区北7条西4-12 0600807
 6                    盛岡市盛岡駅前通15-20 0200034
 7                  東京都品川区東品川2-2-20 1400002
 8                  群馬県高崎市あら町167番地 3700831
 9                 新潟市中央区東大通り2-4-10 9500087
10 東京都新宿区西新宿8-17-1住友不動産新宿グランドタワー34階 1606134
11                静岡県島田市細島字寺久保1700 4270012
12                   東京都豊島区池袋2-77-5 1710014
13                    大阪市北区堂島2-4-27 5300003
14                    広島市南区稲荷町2-16 7320827
15                 千葉県千葉市美浜区中瀬2-6-1 2610023
16                       高松市古新町8-1 7600025
17                 福岡市博多区博多駅前1-15-20 8120011
18                    鹿児島市与次郎2-4-35 8900062
19                  東京都品川区東品川2-2-20 1400002
20                  長野県中野市田上1311 3892371
21                     中野市田上2830 3830002
22             長野県茅野市北山渋御殿湯いう234 3910212
23       東京都品川区東五反田2-10-2東五反田スクエア15階 1410022
24 東京都新宿区西新宿8-17-1住友不動産新宿グランドタワー34階 1606134
25                   東京都目黒区上目黒3-9-1 1530051
 
     C       D      E     F
 1                      
 2 埼玉県    白岡市    白岡    ○
 3 東京都    品川区       (・ω・)
 4     仙台市青葉区    本町    ○
 5      札幌市北区     北 選択▼→
 6        盛岡市 盛岡駅前通    ○
 7 東京都    品川区   東品川    ○
 8 群馬県    高崎市   あら町    ○
 9     新潟市中央区   東大通    ○
10 東京都    新宿区   西新宿 選択▼→
11 静岡県    島田市    細島    ○
12 東京都    豊島区    池袋 選択▼→
13      大阪市北区    堂島    ○
14      広島市南区   稲荷町    ○
15 千葉県 千葉市美浜区    中瀬 選択▼→
16        高松市   古新町    ○
17     福岡市博多区  博多駅前    ○
18       鹿児島市   与次郎    ○
19 東京都    品川区   東品川    ○
20 長野県    中野市    田上 選択▼→
21        中野市    田上 選択▼→
22 長野県    茅野市    北山 選択▼→
23 東京都    品川区  東五反田    ○
24 東京都    新宿区   西新宿 選択▼→
25 東京都    目黒区   上目黒    ○
 
 
                        G
 1                       
 2                       
 3                       
 4                       
 5                   北七条西
 6                       
 7                       
 8                       
 9                       
10 西新宿住友不動産新宿グランドタワー(34階)
11                       
12              池袋(2〜4丁目)
13                       
14                       
15            中瀬(次のビルを除く)
16                       
17                       
18                       
19                       
20        田上(1300〜2511番地)
21                田上(その他)
22           北山(渋御殿湯・渋の湯)
23                       
24 西新宿住友不動産新宿グランドタワー(34階)
25                       
 
 
 
      H    I    J   K  L
 1                    
 2 3.0032943  32943  32963 2.012  1
 3 3.0039376  39376  39435      
 4 6.0012843  12843  13020 2.169  1
 5  5.000009   90   232 1.008  46
 6  3.001077  10770  10932 5.152  1
 7 3.0039376  39376  39435 3.051  1
 8 3.0029149  29149  29385 3.008  1
 9 6.0043355  43355  43566 3.143  1
10 3.0038512  38512  39207 3.068 600
11 3.0064168  64168  64286 2.098  1
12 3.0039718  39718  39800 2.002  3
13 5.0084854  84854  85065  2.15  1
14 5.0101827 101827 101895 3.004  1
15 6.0033646  33646  33697  2.01  37
16 3.0107091 107091 107282 3.161  1
17 6.0112011 112011 112088 4.055  1
18 4.0121924 121924 122133 3.207  1
19 3.0039376  39376  39435 3.051  1
20 3.0058581  58581  58630 2.028  2
21 3.0058581  58581  58630 2.028  2
22 3.0058669  58669  58684 2.004  2
23 3.0039376  39376  39435  4.05  1
24 3.0038512  38512  39207 3.068 600
25 3.0039436  39436  39463 3.006  1
 
 
 
 
 
 
B2
=IFERROR(TEXT(INDEX(Sheet2!$C:$C,IF(E2="",I2,I2+MOD(1000*K2,1000)-2+IF(L2=1,1,MATCH(G2,INDEX(Sheet2!$I:$I,I2+MOD(1000*K2,1000)-1):INDEX(Sheet2!$I:$I,I2+MOD(1000*K2,1000)+L2-2),0)))),"0000000"),"")
下方向・↓
C2
=IF(OR(MID(A2,2,2)={"街道","宰府","諸県"}),"",LEFT(A2,IF(OR(MID(A2,3,1)={"都","道","府","県"}),3,IF(MID(A2,4,1)="県",4,))))
下方向・↓
D2
=IFERROR(MID(A2,LEN(C2)+1,H2),"")
下方向・↓
E2
=IFERROR(LEFT(REPLACE(A2,1,SUMPRODUCT(LEN(C2:D2)),),INT(K2)),"")
下方向・↓
F2
=IF(A2="","",IF(K2="",HYPERLINK("#Sheet2!I"&I2&":I"&J2,"(・ω・)"),HYPERLINK("#Sheet2!I"&I2+MOD(1000*K2,1000)-1&":I"&I2+MOD(1000*K2,1000)+L2-2,IF(L2=1,"○","選択▼→"))))
下方向・↓
 
G2
入力規則:リスト▼:元の値:
=OFFSET(Sheet2!$I$1,I2+MOD(1000*K2,1000)-2,,L2)
 
H2
=IFERROR(AGGREGATE(14,6,{2;3;4;5;6;7;8;9;10}+MATCH(MID(A2,LEN(C2)+1,{2;3;4;5;6;7;8;9;10}/(MID(A2,LEN(C2)+{2;3;4;5;6;7;8;9;10},1)={"市","区","町","村"})),Sheet2!$H:$H,0)/10^7,1),"")
下方向・↓
I2
=IFERROR(MOD(H2*10^7,10^7),"")
下方向・↓
J2
=IFERROR(I2+MATCH(1,INDEX(0/(D2=INDEX(Sheet2!$H:$H,I2):INDEX(Sheet2!$H:$H,I2+1000)),))-1,"")
下方向・↓
K2
=IFERROR(AGGREGATE(14,6,MATCH(MID(A2,SUMPRODUCT(LEN(C2:D2))+1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})&"*",INDEX(Sheet2!$I:$I,I2):INDEX(Sheet2!$I:$I,J2),0)/1000+{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1),"")
下方向・↓
L2
=IFERROR(IF(ISNUMBER(MATCH(E2,INDEX(Sheet2!$I:$I,I2+MOD(1000*K2,1000)-1):INDEX(Sheet2!$I:$I,J2),0)),1,MATCH(1,INDEX(0/(E2=LEFT(INDEX(Sheet2!$I:$I,I2+MOD(1000*K2,1000)-1):INDEX(Sheet2!$I:$I,J2),LEN(E2))),))),"")
下方向・↓

投稿日時: 20/01/07 15:00:28
投稿者: ねね

んなっとさん
 
詳しい回答ありがとうございます!
手作業で行っていた部分まで分割できたので何とかなりそうです。
 
CSVのデータを=H2&I2でくっつけてINDEXとMATCHでいいかなーと思っていたのですが
この方法では戻り値が異なってしまうのでしょうか?

回答
投稿日時: 20/01/07 15:41:15
投稿者: んなっと

例えば下の4つは難しいかもしれませんね。
           A    B    C    D    E            G
1         住所    〒                       
2  千葉県旭市江ケ崎135 2892614 千葉県  旭市 江ケ崎 江ケ崎(12〜22、....
3  千葉県旭市江ケ崎158 2892503 千葉県  旭市 江ケ崎 江ケ崎(その他)
4 千葉県市原市草刈1820 2900156 千葉県 市原市  草刈 草刈(1656〜1999)
5 千葉県市原市草刈2001 2900001 千葉県 市原市  草刈 草刈(その他)
 
とりあえず解決したようなので、ここから先はどうぞご自分の方法で進めてください。

投稿日時: 20/01/07 17:45:06
投稿者: ねね

なるほど...確かに難しそうです。
 
データ量は多くないので
今は日本郵便のサイトで検索して対応しています。
結局ExcelにコピペするのでExcelで完結させたかったのですが...
 
時間のある時に挑戦してみようと思います。
ありがとうございました!