【Excel】複数の文字列を含む行を別sheetに抽出する方法を教えて下さい

エクセル勉強中の者です。

少々複雑な業務をすることになり、
一通り調べてみたのですが似たようなものがなく、ご相談です。

sheet1にベースとしている表があります。
sheet2でB1,C1,D1に入力した単語を1つでも含むものを除外し、
残った該当の行を、すべて一覧で表示させたいです。

sheet1のJ列にはE～Iを足した作業列を作りました。(画像参照ください)

＝＝＝＝＝＝＝
sheet1のJ列を、sheet2のB1で検索し行を抽出する方法はあったのですが、
『複数の単語』および『その該当する単語を含まないもの』を検索する方法がいまいちわからず…

・検索の単語は最高3つまでで、それ以上検索することはありません。
・sheet2の検索セルは、たとえば縦(B1、B2、B3)にしても構いません。

他に情報が必要でしたら追記いたします。

お手数で申し訳ないのですが是非お力をお貸しいただきたく、何卒宜しくお願い致します。

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

• ご回答ありがとうございます！
  こちらの説明不足で大変申し訳ございませんでした。
  この後教えて頂いた式でやってみますが、
  下記、念のため補足です。
  
  >>◇例1
  >>セルに、　英語、数学、国語・・・とあるときに、「語数」は含むのか？
  >>含まないものと解釈。
  
  →実際にはカタカナも含まれます
  (検索例「コミュニケーション/フランス語/ドイツ文学」という感じです)ので
  「含まないもの」で合っております。
  
  >>◇例２
  >>セルに、　英文学、数学、国語・・・とあるときに、「文学」は含むのか？
  >>ご質問文からは不明。
  
  →上記のような場合もあるのでできれば「ドイツ文学」と「英文学」は区別したいです。
  Sheet2のB1:D1は全て空白ではないです。検索のたびに毎回必ず3つ入ります。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/02/10 23:50

No.1 回答者： ママチャリ 回答日時： 2021/02/10 22:48

>『その該当する単語を含まないもの』を検索する方法がいまいちわからず…

ってことは、含むものを処理することなら出来るってことですよね？
であれば、元の表をまるまるコピーして、含む行を削除するようにしてはいかがでしょうか？

No.2 ベストアンサー 回答者： fujillin 回答日時： 2021/02/10 23:00

こんばんは

＞sheet2でB1,C1,D1に入力した単語を1つでも含むものを除外し
「含む」の意味がはっきりとしません。

◇例1
セルに、　英語、数学、国語・・・とあるときに、「語数」は含むのか？
含まないものと解釈。
◇例２
セルに、　英文学、数学、国語・・・とあるときに、「文学」は含むのか？
ご質問文からは不明。

＞sheet1のJ列にはE～Iを足した作業列を作りました
作業列を使用しても良いお考えのようなので・・・
「Sheet2のB1:D1は全て空白ではない」（*①）と仮定しても良いものとして、Sheet1のJ2セルに
（例2）を「含まない」とする場合は、（*②）
=IF(SUMPRODUCT(COUNTIF(E3:I3,Sheet2!B$1:D$1))=0,COUNT(J$1:J2)+1,"")
（例2）を「含む」とする場合は、（*③）
=IF(SUMPRODUCT(ISNUMBER(FIND(Sheet2!B$1:D$1,TEXTJOIN(CHAR(27),,E2:I2)))*1)=0,COUNT(J$1:J1)+1,"")
を入力し、下方にフィルコピーします。
この結果、抽出に該当する行にだけ順に連番が振られるはずです。

＞sheet2のB1で検索し行を抽出する方法はあったのですが
いろいろ調べていらっしゃるようですので、あとはJ列の結果を参照して抽出すれば良いので、ご自身でできるものと推測します。
J列から順に1、2、3…と検索して表示すればよいだけですので、同様の方法をとっている説明を既にご覧になっているはずと思いますので、以降の処理は省略いたします。

◆ご注意ほか
※ *①の仮定を外す（空白セルもあり得る）ことは可能ですが、*②、*③の式が無駄に長くなるので、この仮定を入れさせていただいています。
抽出条件が、『「含まない」行を抽出』となっているので、空白にしたい場合は、「含まれないであろう」文字列あるいは「他と同じ文字列」をダミーで入れておけば足りるであろうと想定しました。
※ ダミーの値を入れるのではわかりにくいので、空白も許容したいというような場合は、上記の式にその条件を加えれば算出可能ですので、挑戦してみて下さい。
※ もしも、*③の式中のTEXTJOIN関数が使えない環境である場合は、その部分を「E2&CHAR(27)&F2&CHAR(27)&G2&CHAR(27)&H2&CHAR(27)&I2」に置き換えれば、同じ内容の計算になります。