Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
除外したいワードが含まれるかどうかを判定するための数式について
投稿日時: 21/08/13 12:14:50
投稿者: あや88

皆様、よろしくお願いいたします。
除外リストがあり、その言葉が含まれている文章の場合は「×」が表示されるように数式を組んだのですが、除外ワードが増えるたび、数式に変更を加えなければならず大変困っています。
 
内容としてはD列に下記の式を入れ、除外ワードが入っているか判定するように設定しています。この方法ですと除外ワードが増えるたび、数式に「COUNTIFS(C2,"*"&A$4&"*")」を追記しなければならず大変手間がかかってしまいます。A列に除外ワードを追記しても、数式に変更を加えなくてよいような方法について、何かございましたらご教示いただけないでしょうか。できればVBAを使わない方法があればと思っています。
 
下記に数式を記載いたします。
 
◆D列の数式:=IF(OR(COUNTIFS(C2,"*"&A$4&"*"),COUNTIFS(C2,"*"&A$2&"*"),COUNTIFS(C2,"*"&A$3&"*")),"×","")
 
◆除外(A2〜4)    項目(C2〜7)    判定(D2〜7)
リンゴ        私はスイカが好きです。    
バナナ        私はメロンが好きです。    
キリン        私はリンゴが好きです。    ×
キリン        私はミカンが好きです。    
キリン        私はキリンが好きです。    ×
キリン        私はバナナが好きです。    ×
 
◆エクセルバージョン : 2019
 
お手数をおかけしますが、よろしくお願いいたします。

回答
投稿日時: 21/08/13 12:24:01
投稿者: んなっと

    A B            C  D
2 リンゴ   私はスイカが好きです。  
3 バナナ   私はメロンが好きです。  
4 キリン   私はリンゴが好きです。 ×
5       私はミカンが好きです。  
6       私はキリンが好きです。 ×
7       私はバナナが好きです。 ×
 
D2
=IF(SUM(COUNTIF(C2,"*"&A$2:A$4&"*")),"×","")
下方向・↓

投稿日時: 21/08/13 12:47:41
投稿者: あや88

んなっと様
 
ご返答頂きありがとうございます。
早速試させていただいたところ、全て空欄になってしまい×印がつきませんでした。
エクセルのバージョンが2019のためでしょうか?
 

回答
投稿日時: 21/08/13 12:54:07
投稿者: んなっと

    A B            C  D
2 リンゴ   私はスイカが好きです。  
3 バナナ   私はメロンが好きです。  
4 キリン   私はリンゴが好きです。 ×
5       私はミカンが好きです。  
6       私はキリンが好きです。 ×
7       私はバナナが好きです。 ×
  
D2
=IF(SUM(COUNTIF(C2,"*"&A$2:A$4&"*")),"×","")
下方向・↓

回答
投稿日時: 21/08/13 13:02:30
投稿者: メジロ2

横から失礼します。
「んなっと」さんの式は配列数式にする必要があるように思います。
「んなっと」さんは書き忘れたのでしょう。
 
「Ctrl + Shift + Enter」キーで式を確定(入力)します。

回答
投稿日時: 21/08/13 13:04:27
投稿者: んなっと

また同じ間違いでした。
メジロ2さんありがとうございます。
あや88さん、こちらに変更してください。
 
=IF(SUMPRODUCT(COUNTIF(C2,"*"&A$2:A$4&"*")),"×","")

回答
投稿日時: 21/08/13 13:12:42
投稿者: WinArrow
投稿者のウェブサイトに移動

作業セルと配列関数を使用した例
 
 
作業セルとして、H1を使います、
="$A$1:$A$"&counta(A:A)
 
D列セル
=IF(MAX(IFERROR(FIND(INDIRECT($H$1),C1),0))>0,"×","")
と入力し、[Shft]+[Ctrl]+[Enter]
下へフィルコピー
 
 

投稿日時: 21/08/13 13:16:21
投稿者: あや88

んなっと様、メジロ2様
ご返答ありがとうございます!
 
=IF(SUMPRODUCT(COUNTIF(C2,"*"&A$2:A$4&"*")),"×","")
こちらで正しく表示されました!
 
除外ワードの範囲を7行目まで広げた場合は、どの様にすればよろしいでしょうか?
現時点では除外ワードは3つで、A5〜A7が空欄です。
範囲を広げ下記の数式を入れてみたのですが、全て空欄になってしまいます。
 
 
=IF(SUMPRODUCT(COUNTIF(C4,"*"&A$2:A$7&"*")),"×","")
 
 
大変お手数をおかけしますが、よろしくお願いいたします。

回答
投稿日時: 21/08/13 13:19:09
投稿者: んなっと

D2
=IF(SUMPRODUCT(COUNTIF(C2,"*"&A$2:A$7&"*")),"×","")

投稿日時: 21/08/13 13:29:18
投稿者: あや88

んなっと様、ありがとうございます。
試してみたところ、全て「×」になってしまいます。
申し訳ございません。

回答
投稿日時: 21/08/13 13:47:12
投稿者: んなっと

ちょっと待ってくださいね。今直します。

回答
投稿日時: 21/08/13 13:51:01
投稿者: んなっと

    A B            C  D
2 リンゴ   私はスイカが好きです。  
3 バナナ   私はメロンが好きです。  
4 キリン   私はリンゴが好きです。 ×
5       私はミカンが好きです。  
6       私はキリンが好きです。 ×
7       私はバナナが好きです。 ×
 
D2
=IF(SUMPRODUCT(COUNTIF(C2,REPT("*"&A$2:A$7&"*",A$2:A$7<>""))),"×","")
下方向・↓

投稿日時: 21/08/13 14:40:27
投稿者: あや88

んなっと様
 
本当にありがとうございました!
解決いたしました。
また、よろしくお願いいたします。