エクセル：一覧表に存在する文字が含まれているセルを抽出したいです

Sheet1のA列のセルに、一覧表にある花が含まれている場合は1を立てるという数式をご教示いただきたいです。
COUNTIFSやワイルドカード検索など試しましたがなかなかうまく行かず、知恵をお借りしたいです。
※Sheet1の文字の並びはバラバラです。花の名前が最初にあったり途中にあったりします。
※Sheet2の一覧は実際には40個ほどあります。

以下の場合だと、B列に数式を入れ、①②④⑤に1が立つというイメージです。

【Sheet1】
A列
①さくら回廊_千葉
②ひまわり畑散策
③いるかショー_品川
④さくら周遊
⑤青いネモフィラの絨毯
⑥くじら海峡


【Sheet2】一覧表
A列
さくら
ひまわり
ネモフィラ
コスモス
バラ

よろしくお願いいたします。

No.5 回答者： mike_g 回答日時： 2024/05/14 21:10

[No.4]への補足、

老婆心ながら、
G/標準;;
に書式設定した後で、提示式を入力したセル B1 を下方にズズーッとオートフィルするのがお奨めです。セル B3、B6 以降が空白を呈しているのは当該書式を設定しているから、為念。

この回答へのお礼

お礼日時：2024/05/15 09:22

No.4 回答者： mike_g 回答日時： 2024/05/14 18:26

Sheet1!B1: =SUMPRODUCT(ISNUMBER(FIND(Sheet2!A$1:A$9,A1))*1)

添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

No.3 回答者： fujillin 回答日時： 2024/05/14 16:38

No2です。

連投失礼。
少しだけ短くできるのを見落としました。

=IF(SUMPRODUCT(COUNTIF(A1,"*"&Sheet2!A$1:A$50&"*")*(Sheet2!A$1:A$50<>"")),1,"")

でも、同様の結果が得られます。

この回答へのお礼

ありがとうございます！！いただいた式で無事抽出できました。
大変助かりました！！

お礼日時：2024/05/14 17:54

No.2 回答者： fujillin 回答日時： 2024/05/14 15:45

No1です。

＞Sheet2の一覧表にあるものが、Sheet1のA列に含まれていたら、
＞Sheet1のB列に1が立つという数式を探しております。
あぁ、検索方向を逆に解釈してしまったようですね。
申し訳ありませんでした。

であるなら、Sheet1のB1セルに
=IF(SUMPRODUCT((COUNTIF(A1,"*"&Sheet2!A$1:A$50&"*")>0)*(Sheet2!A$1:A$50<>"")),1,"")
とかではいかがでしょうか？

※ 一覧の項目数を、仮に50までと設定してあります。

この回答へのお礼

お礼日時：2024/05/14 17:52

No.1 回答者： fujillin 回答日時： 2024/05/14 12:50

こんにちは

＞COUNTIFSやワイルドカード検索など試しましたがなかなかうまく行かず
普通にその通りに行えばよさそうに思います。

B1セルに
　=IF(COUNTIF(Sheet1!A:A,"*"&A1&"*")*(A1<>""),1,"")
を入力して、下方にフィルコピーではダメなのでしょうか？

この回答へのお礼

早速ご回答をいただきありがとうございます！
私の説明が足りず申し訳ありません。
数式を反映させたいのはSheet1のB列になります。
（いただいた数式だとSheet2の一覧表での抽出となっていると思われます）
Sheet2の一覧表にあるものが、Sheet1のA列に含まれていたら、Sheet1のB列に1が立つという数式を探しております。
説明が分かりにくく申し訳ありません。
もしお分かりになりましたらご回答いただけますと幸いです。
よろしくお願いいたします。

お礼日時：2024/05/14 14:47