Excel関数

質問

Excel2010を使用しております。

A列に住所が1万件ほど入力されており、
この住所データを、ある住所ごとに6分類に仕分けを行いたいのです。

例として、

世田谷区・目黒区・八王子市は「01」
渋谷区・港区・品川区・埼玉県・神奈川県は「02」
狛江市・町田市は「03」
調布市・府中市は「04」
新宿区は「05」
その他の県は「06」

本当はもっと細かく分類しているのですが、
「01」～「06」までを住所の隣にB列セルに返したいのです。

A列の住所内に「渋谷区」が入っている住所があれば、「01」と返し、
「愛知県」とあれば「06」と返すような数式はございますでしょうか？

適した数式をご教示頂きたく、何卒宜しくお願い致します。

回答No.7

kagakusuki · ベストアンサー

東京都内であっても、千代田区や武蔵野市の様に、御質問文中の例の中には分類が明記されていない地域もありますが、その様な地域の場合には何と表示させれば良いのか判りませんので、取り敢えずは仮に「該当無し」と表示させるものとします。
　それから、単純に区や市の名称の有無だけで判定を行ったのでは、例えば「大阪府大阪市港区」や「名古屋市港区」の様に、他県でも同名の区や市が存在する場合もあり得ますので、分類が06となる県であるのか否かを先に判定しておき、その上で、住所が06の県では無いものに対してのみ、再度、どの分類になるのかを判定する必要があります。
　又、「東京都府中市」という住所を表す文字列の中には、「東"京都府"中市」という具合に「京都府」という文字列が含まれています。
　この様な場合においても、「東京都府中市」を「京都府」と誤認しない様に、関数を組む必要がありますし、若しかしますと、「東京都府中市」以外にも、他の地域の名称を含んでいる住所があるかも知れませんので、注意する必要があります。

　一例としては、以下の様な方法があります。
　今仮に、Sheet1のA列に住所が並んでいて、その隣のB列に分類を自動的に表示させるものとします。
　又、Sheet2のA列～C列に、どの分類番号とするのかを決定する際に基準となるデータを、表形式で入力しておくものとします。

　まず、Sheet2のA列とB列に

　　　　　A列　　　　　　B列
1行目　　住所　　　　　分類
2行目　世田谷区　　　　01
3行目　目黒区　　　　　01
4行目　八王子市　　　　01
5行目　渋谷区　　　　　02
6行目　港区　　　　　　02
　・　　　　・　　　　　　　・
　・　　　　・　　　　　　　・
　・　　　　・　　　　　　　・
　・　　　　・　　　　　　　・
13行目　府中市　　　04
14行目　新宿区　　　05

という具合に、住所と分類の関係（06の住所は除く）を表した表を作成して下さい。
　次に、Sheet2のC2以下に、東京都、神奈川県、埼玉県を除く、北海道から沖縄県までの、分類が06となる全ての県を入力して下さい。
　次に、Sheet2のD2以下に、東京都、神奈川県、埼玉県等の、分類が06とはならない全ての県を入力して下さい。

　次に、Sheet1のB2セルに次の数式を入力して下さい。

=IF(AND(SUMPRODUCT(ISNUMBER(FIND(Sheet2!$C$2:$C$45,INDEX($A:$A,ROW())))*1),SUMPRODUCT(ISNUMBER(FIND(Sheet2!$D$2:$D$4,INDEX($A:$A,ROW())))*1)=0),"06",IF(SUMPRODUCT(ISNUMBER(FIND(Sheet2!$A$2:$A$14,INDEX($A:$A,ROW())))*1)=1,INDEX(Sheet2!$B:$B,SUMPRODUCT(ISNUMBER(FIND(Sheet2!$A$2:$A$14,INDEX($A:$A,ROW())))*ROW(Sheet2!$A$2:$A$14))),IF(SUMPRODUCT(ISNUMBER(FIND(Sheet2!$A$2:$A$14,INDEX($A:$A,ROW())))*1),"判定不能",IF(INDEX($A:$A,ROW())="","","該当無し"))))

　次に、Sheet1のB2セルをコピーして、Sheet1のB3以下（Sheet1のB列において、分類を表示させる可能性のある全てのセル）に貼り付けて下さい。

　これで、Sheet1のB列に、分類が自動的に表示される様になります。
　尚、A列に何も入力されていない場合には、B列の同じ行のセルには何も表示されません。
　又、A列に入力されている住所が、分類番号が決まっていない地域である場合には「該当無し」と表示されます。
　又、もしも、「東京都調布市○○町田市場」といった具合に、1つの住所の中に、「06」以外の分類となるキーワードが複数含まれていた場合には「判定不能」と表示されます。

　それから、SUMPRODUCT関数は、計算の繰り返し回数が数千回以上になりますと、計算に負荷が過大になって、処理時間が長くなってしまう事で有名ですが、上記の関数の場合は、繰り返し回数が多い個所でも、Sheet2!$C$2:$C$45のセル範囲に対する計算の44回しか繰り返しがありませんので、SUMPRODUCT関数としては計算負荷が比較的少なくて済みます。（とは言え、1万行も処理を行うとなりますと、どの様な関数であっても、結構時間を要する事になります）

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.9

MarcoRossiItaly

No.6です。すみません、No.6の式は次のようにワイルドカード文字をまとめて書くと、もうちょいスッキリしますね。計算結果は変わりません。書式もNo.6で説明したとおり「00」とかを設定してください。

=6-sumproduct({5,5,5,4,4,4,4,4,3,3,2,2,1}*countif(a1,"*"&{"世田谷区","目黒区","八王子市","渋谷区","港区","品川区","埼玉県","神奈川県","狛江市","町田市","調布市","府中市","新宿区"}&"*"))

回答No.8

kagakusuki

ANo.7です。
　先程の投稿において、添付した画像が何故か表示されなかった（サイトのシステムの不調か何かが原因かも知れません）ため、再度添付画像を送信致します。

回答No.6

MarcoRossiItaly

=6-sumproduct({5,5,5,4,4,4,4,4,3,3,2,2,1}*countif(a1,{"*世田谷区*","*目黒区*","*八王子市*","*渋谷区*","*港区*","*品川区*","*埼玉県*","*神奈川県*","*狛江市*","*町田市*","*調布市*","*府中市*","*新宿区*"}))

と入力し、そのセルに対して「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」で「00」を入力するとか。

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.5

tom04

こんばんは！
VBAになりますが・・・一例です。

↓の画像のようにSheet2に表を作成しておきます。
そしてSheet1は1行目がタイトル行でデータはA列の2行目以降にあるとします。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
i = Cells(Rows.Count, 2).End(xlUp).Row
If i > 1 Then
Range(Cells(2, 2), Cells(i, 2)).ClearContents
End If
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 5
For k = 2 To ws.Cells(Rows.Count, j).End(xlUp).Row
If InStr(Cells(i, 1), ws.Cells(k, j)) Then
With Cells(i, 2)
.Value = ws.Cells(1, j)
.NumberFormatLocal = "00"
End With
End If
Next k
Next j
Next i
Columns("A:B").AutoFilter field:=2, Criteria1:="="
i = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(2, 2), Cells(i, 2))
.Value = 6
.NumberFormatLocal = "00"
End With
Worksheets("Sheet1").Select
Selection.AutoFilter
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないのでデータ変更があっても反映されません。
データ変更があるたびにマクロを実行する必要があります。

ご希望の方法でなかったらごめんなさいね。m（_ _）m

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.4

MackyNo1

A1セルに住所が入力されている場合、地域名と数字の関係のリストを作成しておき、以下のような数式を入力して下方向にオートフィルすれば対応する数字を表示することができます。

=IF(COUNT(INDEX(FIND($D$1:$D$12,A1),)),INDEX(E:E,MAX(INDEX(ISNUMBER(FIND($D$1:$D$12,A1))*ROW($D$1:$D$12),))),"06")

ただし、上記の数式は配列数式ですので、まとめて多数のセルに入力するとシートの再計算に時間がかかるなどの問題がありますので、例えば100件ごとに処理するなどの対応をされるのが良いと思います。
またその計算結果を使ったシートをその後も入力用のシートとして使用したい場合は、必要に応じて数式で表示済みの数字部分については、一番下の行の数式を残し、それ以外の数式範囲を選択し右クリック「コピー」、そのままもう一度右クリック「形式を選択して貼り付け」で「値」を選択するなどして計算負荷を少なくしてしてください。

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.3

maron--5

◆Sheet2の式
B1=IF(A1="","",IFERROR(LOOKUP(1,0/(MMULT(ISNUMBER(FIND(Sheet1!$B$1:$F$5,Sheet2!A1))*(Sheet1!$B$1:$F$5<>""),{1;1;1;1;1})),Sheet1!$A$1:$A$5),"06"))
★下にコピー

Sheet1
______[A]_______[B]__________[C]__________[D]__________[E]___________[F]
[1]___01___世田谷区___目黒区___八王子市
[2]___02___渋谷区______港区______品川区______埼玉県___神奈川県
[3]___03___狛江市______町田市
[4]___04___調布市______府中市
[5]___05___新宿区

Sheet2
_________________[A]_______________________[B]
[1]___東京都品川区あいう123______02
[2]___東京都新宿区あいう124______05
[3]___東京都港区あいう125_________02
[4]___府中市1452______________________04
[5]___福岡県博多市258______________06

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.2

excelist

FIND関数またはSEARCH関数で実現できると思います。
（２つの関数の使い方は同じと思っておいてもＯＫです）

例えばＢ１セルに

=FIND("世田谷区",A1,1)

と入力し、Ａ１セルに世田谷区を含む文字列があった場合、その文字の位置が返ってきます。

世田谷区○○・・・の場合→1が返ってくる
東京都世田谷区○○・・・の場合→4が返ってくる
世田谷区という文字がない場合→エラー値（#VALUE!）が返ってくる

エラーが返ってくる場合があるのでIF関数とISERROR関数（またはIFERROR関数）を駆使すればなんとかなりそうです。が・・・

>本当はもっと細かく分類しているのですが、

とあるので、細かさ次第ではもうちょっと工夫しないといけないかも知れません。Ａ列の文字列から条件となる文字列（世田谷区や新宿区など）【だけを】取り出せるのであれば、それをＢ列に出力させておいて、別のところに

世田谷区　　01
目黒区　　　01
八王子市　　　01
渋谷区　　　02
・・・

という定義を作って、Vlookup関数を使えばスマートに書けそうなんですけども・・・

お礼

ご回答参考になりました。

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。

回答No.1

akina_line

こんにちは。

　複雑な場合分けをする場合、VBAで「ユーザファンクション」を作成してB列に関数式として、埋め込んだほうが、後々機能を拡張したりメンテナンスが楽になります。

　また、データの精度はどうですか。確実に「市」「区」「県」が入力されているものなのでしょうか。これらの文字が確実に入力されていれば、これらの文字を区切り文字として利用できますが、入っていたりいなかったりすると、場合分けが複雑になります。

では。

お礼

おかげさまで無事解決いたしました。

お忙しい中ご対応頂き有難うございました。