• 締切済

    質問Excel関数

    • 2012/6/25
    • sakuichi

    Excel2010を使用しております。

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

    例として、

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

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

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

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

    続きを読む

  • 回答

    • 2012/6/26
    • 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万行も処理を行うとなりますと、どの様な関数であっても、結構時間を要する事になります)

    続きを読む