Excel関数で、セル内の文字列の中にリストの中のどれが含まれているかを出力する

セルA1に以下文字列が入力されている場合

　I like pop music very match
　
以下リストの中のどの文字列が含まれているかを検索し、その値の位置を返す方法を教えてください。

＜リスト＞

1 orange
2 some
3 music
4 thanks

結果、上記リストの中でA1の文章の中に含まれているのは、”music” なので、3 を返すのが
希望の動作です。
よい方法をご教授いただけますでしょうか。

よろしくお願いします。

質問者からの補足コメント

• 自己解決しました。
  原因はエラー#VALUE! によるもので、
  IFERRORとFIND関数を用いてうまくいきました。
  
  --------------
  A1　：I like pop music very match
  
  B1 　：orange
  B2 　：some
  B3 　：music
  B4 　：thanks
  
  IF(IFERROR(FIND(B1,A1),0)>0,1,IF(IFERROR(FIND(B2,A1),0)>0,2,IF(IFERROR(FIND(B3,A1),0)>0,3,IF(IFERROR(FIND(B4,A1),0)>0,4))))
  
  これで "3" が 返ってきました。
  
  ナレッジとして残しておきます。

    補足日時：2017/06/23 10:43

No.1 ベストアンサー 回答者： fujillin 回答日時： 2017/06/23 11:28

こんにちは

ユーザ定義関数などで処理した方が良さそうな気もしますが・・・

自己解決なさったようですが、少しだけ一般化して違う発想の例を。

リスト（補足の例の場合ではB1:B4)を「名前の定義」で「list」等としておきます。
（名前の定義を利用しなくても良いですが、式が簡単になりますので）

=SUMPRODUCT((MATCH(list,list,0))*NOT(LEN(A1)=LEN(SUBSTITUTE(A1,list,""))))

・リストの項目数は4項目限定ではありません。（いくつでも良いです）
・リスト内の文字が存在しない場合は0になります。
・2項目以上の内容が存在する場合は、各インデックス値の合計になります。

この回答へのお礼

名前の定義をリストで作成できるとは知りませんでした。

私の書式だと長ったらしくて納得はしておらず、リスト内の文字が存在しない場合はFALSEを
返したりして苦肉の策だったのですが、こちらを使用すると、シンプルで、いろいろと応用が効きそうです！
すごく勉強になりました！

ありがとうございました。

お礼日時：2017/06/23 11:55

No.3 回答者： WindFaller 回答日時： 2017/06/26 09:53

#2の訂正

He likes classical music very [mutch].　　←　much
元　　　　　　　　解析後
TreeTagger で、mutch を入れると、NN（名詞）として出てきてしまい、間違いになってしまいます。正しくは、RBの副詞です。

No.2 回答者： WindFaller 回答日時： 2017/06/25 10:53

こんにちは。

場違いな回答でしたらすみませんが、似たような質問が出るたびに思うことなのですが、その集計を本格的にするためには、英文の形態素解析が必要なのではないか、と思うのです。music は、変化がありませんが、名詞、動詞や形容詞の場合は、変化形アリで、そのままの単語検索では無理があると思うのです。

I like pop music very much.　(2箇所訂正)

http://language-and-engineering.hatenablog.jp/en …

英語の形態素解析をしてみました。
perl + TreeTagger
マニュアルは、http://funoisan.blog.fc2.com/blog-entry-4.html
で進めて試してみました。

He likes classical music very mutch.
元　　　　　　　　解析後
He　　　　PP　　　he
likes　　　VVZ　　like
classical　JJ　　　classical
music　　NN　　music
very　　　RB　　very
much　　　RB　　much

これを、VBAに組み入れるわけなのですが、そこまでは組んでいません。たぶん、可能だと思っています。

もちろん、元の質問にも答えないと回答にはなりませので、VBAのコードを置いておきます。実際は以下のようにはいかないことも多かろうと思います。ユーザー定義関数にも置き換えられますが、それはお望み次第です。

Sub Search_word_inLine()
Dim rng As Range
Dim wd As String
Dim Arwd As Variant
Dim i, n '(Variant)
'検索単語は B列に置く
Set rng = Range("B1", Cells(Rows.Count, 2).End(xlUp))
wd = Range("A1").Value
If wd = "" Then Exit Sub
wd = LCase(wd)
wd = Replace(wd, Space(2), Space(1), , , vbTextCompare)
wd = Replace(wd, ",", "", , , vbTextCompare)
wd = Replace(wd, ".", "", , , vbTextCompare)

Arwd = Split(wd, Space(1))
For Each n In Arwd
　i = Application.Match(n, rng, 0)
　If IsNumeric(i) Then
　　Cells(i, 3).Value = i 'C列に出力。複数ある時は、最後のひとつだけです。
　End If
Next
End Sub