エクセルのカウントで重複分を回避する方法について

質問

複数のセルに入力されたそれぞれの文章の中で、該当するキーワード毎にカウントしたい。
但し、入力方法が統一されていないため、全角、半角、アルファベット大文字、小文字が混在しているため、以下のような数式を使ってカウントするが、１つのセル内に複数該当するキーワードがあってもトータル数は1としてカウントしたい。
=COUNTIFS(TSあり!S:S,"*配線*",TSあり!R:R,"*NET*")+COUNTIFS(TSあり!S:S,"*配線*",TSあり!R:R,"*ＮＥＴ*")+COUNTIFS(TSあり!S:S,"*配線*",TSあり!R:R,"*ネット*")+COUNTIFS(TSあり!S:S,"*配線*",TSあり!R:R,"*ﾈｯﾄ*")

→1つのセル内に、「配線」のキーワードが入っている場合は、その数に関係なく1とカウント。
　上記の条件分全体で、1か0のカウントをとりたい。といった内容になります。

お手数ですが、ご教示頂けると助かります。

回答No.5

SI299792

すみません、画像添付失敗しました。

回答No.4

SI299792

カウントでORを使う方法は以下があります。それぞれ利点・欠点があります

①DCOUNTA
利点・数式が簡単。
欠点・１行目に項目名が必要、条件テーブルを作る必要がある。
=DCOUNTA(TSあり!R:S,1,A1:B4)

②SUMPRODUCT
欠点・* が使えないのでFINDを使う必要がある。数式が複雑。配列関数なのでR:R 等行全体を指定するとレスポンスが相当悪くなる。
=SUMPRODUCT((ISERROR(FIND("NET",ASC(TSあり!R2:R99)))*ISERROR(FIND("ﾈｯﾄ",TSあり!R2:R99))+ISERROR(FIND("配線",TSあり!S2:S99))=0)+0)
範囲は実データに合わせて変更して下さい。

③Ｔ行等１行ワークエリアを使う
TSありシート
T2: =ISERROR(FIND("NET",ASC(R2)))*ISERROR(FIND("ﾈｯﾄ",R2))+ISERROR(FIND("配線",S2))
下へコピペ。
別シート
=COUNTIF(TSあり!T:T,0)

画像はややこしいですが、赤が①に必要、緑が③に必要です。

回答No.3

imogasi

(前置き）
＜エクセル関数＞
エクセル関数で出来ればよいのですが,
エクセルの関数で「セルに違う語句（文字列）が何種類入っているか」は
作業列に=IF(COUNTIF($A$2:A2,A2)=1,1,0)のような数値を一旦出し、
その中に「１」の数を数えるしかない。
複数列でも手間ですが可能です。
３６５なら「Microsoft365で使用できるUNIQUE関数」でも少しスマートです。
エクセルのバージョンも,質問に書いてないね。
ーーー
其れ以外で関数では、=SUMPRODUCT(1/COUNTIF(A2:B11,A2:B11)) (A,Bの2列での例）がある。
各グループの人数がn人ならば、各人に　1/n　点を持たせて、全員合計すれば、グループ数が
判るという理屈です。複数列でも可能のようです。
しかしこれも有名な問題の解き方ですが、誰でもすぐ思い付くアイデアではではないと思う。
ーー
これの範疇を越えた、セル単位の語句で全体を数えるという問題は、不可能的に難しいと思います。
エクセルではセル単位の語句（単語）を対象にしてカウントを考えるぐらいしかない。
　質問の表題だけをみたとき、「上記の、またあれか」と思いましたが、タイプとして、全く違う
複雑で難しい課題でした。
ーー
＜正規表現＞
語句検索などで、正規表現という仕組みのもありますが。文単位で個数を考えるのは難しいと思う。
ーー
＜VBA＞
其れなら、VBAでロジックを組んで質問のようなことができるかと言うと、実例などやセル・列・行の
データの文字列の現れ方が、質問文だけでは、よく判らないので、具体的には手が出ない。
質問者はプログラムの経験はないような質問の書き方なので、さあどうするか？だが。
検索のＦｉｎｄメソッドというのがあるが、ｴｸｾﾙとワードで少し処理が違い、難しい。
VBAで正規表現も使えますが、セル単位で数えるのは難しいと思う。
ーー
ただし、思い付きだが
Sub test01()
For Each cl In Range("A1:A12,B1:B12,H1:H3")
MsgBox cl.Address
Next
End Sub
Range("A1:A12,B1:B12,H1:H3")を、場合に応じて適当に指定すると、列単位（本質問ではS,R列？・行単位で指定した範囲のセル単位で、ある文字列があるかどうかは、Ｉｎｓｔｒ関数などで（存非と語内位置が）わかる。
この程度（２－３行のコード）で指定範囲の全部の各セルを捉まえることができるので、泥臭い方法だが、セル単位で判別ロジックが組めるならば、
（セル数が多くても、今はコンピュータ処理速度が速いので、）可能だろう。
気にしている全角、半角、別表現（事前にＲｅｐｌａｃｅで１つにサヤ寄せなど）などの統一処理を行えば）不統一は相当解消するだろう。
ーー
＜全般論＞
そもそもエクセルのシート（セル）に、文章的な情報を入れて、何かの処理をしようというのは、間違っていると思う。
本件もデータができるまでの事前に、やりやすいように・できるようにデータに手を打っておかないといけないのは鉄則だ。事後に、必要なデータや、正しいキーワードなどを人間の判別力を借りてでも、別列に都度作っておくとか対策が必要と思う。
ーー
やる前になって、無料のＷＥＢコーナーに相談するなんて、おかしいと思う。当初から、利用のことを考えた設計を行うべきで、システム設計の問題だと思う。会社にＳＥはいないのかな。
上記のVBAで言ったような本件のための・今回限りのプログラムは、SEは、雑プロとして嫌がると思う。

回答No.2

kon555

補足というか。orでやる方法を回答するのを忘れてました。
https://office-hack.com/excel/countifs-or/

回答No.1

kon555

関数に凝るより、一旦半角に統一してからカウントしましょう。
https://www.togu.co.jp/column/detail/159
　たしかカウントイフは大文字小文字を無視したように思いますが、必要ならこちら。
https://uxmilk.jp/19738

　あとは、各条件につき一つ作業列を作ってカウントし、最後にMAX関数で最大値を出せば「１つのセル内に複数該当するキーワードがあってもトータル数は1としてカウント」はできます。

補足

作業スペースで、=ASC(UPPER(xxxxx)とし、半角、大文字に統一すれば、検索の条件文が最低限で良いことが分かりました。ありがとうございます。

ただ、まだ分からないのが、、1つのセルに複数の条件（例：NET、ﾈｯﾄそれぞれで検索）でCOUNTIFS OR文でカウントする際に、該当するものが複数あっても1としてカウントしたい場合のやり方が分かりません。
恐れ入りますが、ご教示をお願いいたします。

エクセル2016を使用しています。