geek.conf.2

あるエンジニアの備忘録

ある文字列が含まれていたら、これに対応して、分類する@Excel

 

ちす。

今日は、Excelで、ある文字列が、含まれていたら、その文字列に応じて、分類を行うセルを実現する関数を紹介します。

 

つまり、こちらの画像で説明するとですね。

・シート[Sheet1]

f:id:tsubauaaa:20151104184036p:plain

シート[Sheet1]に検索ワードと色の対応表を作成します。

・シート[string_category]

f:id:tsubauaaa:20151104183727p:plain

シート[string_category]のB列(分類対象)にシート[Sheet1]のA列(検索対象)内の文字列

が含まれていた場合に、その隣のB列(色)の値をシート[string_category]のA列に返す。

といったことを実現します。

シート[string_category]の2行目を例にすると、分類対象列に、"リンゴが美味しい"とあり、シート[Sheet1]の2行めは"リンゴ"と"赤"を対応表として定義しています。

この場合、シート[string_category]の2行目のC列には、色分類結果として、"赤"が自動判別され、シート[string_category]の2行目A列は、[=C2]としている、という訳です。なので、シート[string_category]のC列は、非表示にしても良いかもです。

さて、シート[string_category]のC列に、以上を実現する関数が入力されています。

その関数は、これ↓です。

If that contain the string , classified into each ...

 

分解して、説明すると、まず、FIND関数内で、分類対象(B2)に、検索ワード(Sheet1!$A$2:$A$8)が含まれている場合の、左端から数えた検索ワード位置を取得します。さらにLOOKUP(0,0/FIND(Sheet1!$A$2:$A$8,B2),Sheet1!$A$2:$A$8)とあります。これは、検索ワード位置がある場合は、0/[数字] = 0となり、LOOKUP関数は、TRUEを返し、対応範囲である、検索ワードを返します。

※ちなみに検索ワード位置がない場合は、0/0 = #DIV/0!となり、エラーとなります。

そして、VLOOKUP(LOOKUP(0,0/FIND(Sheet1!$A$2:$A$8,B2),Sheet1!$A$2:$A$8),Sheet1!$A$2:$B$100,2,0)は、VLOOKUP("検索ワード","対応表",2,0)

※2は列番号、0は完全一致検索型

となり、検索ワードに対応した、色を返す、という動きになります。

 最後に、IFERRORで、エラー時には、"N/A"を返すようにします。

※LOOKUP関数内で定義するシート[Sheet1]の対応表は、値が入っている行番号までをきちんと指定しないと期待通りに、動かないので、注意です。

という感じで終わります。