エクセルで文字列検索の関数

質問

エクセルのワークシート関数で質問です。
「A1セルに、文字列、A、B、Cの何れかを含み、かつCDを含まない」ことを調べる関数です。
素直に、
=AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1)),NOT(ISNUMBER(FIND("CD",A1))))
と長ったらしく書けば取得できることはわかるのですが、もっと簡潔なやり方がありそうな気がします。
どうか教えてください。

回答No.4

keithin · ベストアンサー

=AND(ISERROR(FIND("CD",A1)),OR(ISNUMBER(FIND({"A","B","C"},A1))))
とかで十分です。

お礼

実に簡潔なすばらしいご回答、ありがとうございます！
とても勉強になりました。

回答No.13

MarcoRossiItaly

No.12です。一点、言い忘れました。度々すみません。

=sum(true,true,true)
=sum(true,true,true)<3
=sum({true,true,true})
=sum({true,true,true})<3
=sum(--{true,true,true})
=sum(--{true,true,true})<3

上の6式はそれぞれ、上から順に、「3」、「FALSE」、「0」、「TRUE」、「3」、「FALSE」を返します。つまり、上から1番目の数式の書き方はオッケーだが、3番目のように「{ }」のみを混ぜるのには（「3」にならないので）問題があって、5番目のように「--」が付くと問題が解消されるということです。

お礼

ご親切になんどもありがとうございました。
これからもご指導ください。

回答No.12

MarcoRossiItaly

SUM関数は、「TRUE」あるいは「FALSE」という論理値を合計することができます。そういう仕様です。例えば、

=sum(true,1,false,0,-3,,,)

という数式は、「-1」を返します。つまり、「TRUE」を「1」として、「FALSE」を「0」として合計しています。

ただし、A1セルに「TRUE」が、B1に「100」が入力されているとき、次式は「101」ではなく、「100」を返します。

=sum(a1:b1)

このように、セル参照の参照先に、論理値あるいは文字列が入力されていても、それは合計されずに無視されます。

=true<3
=0+true<3

※「0+」の代わりに、先頭に「1*」や「--」を付けたり、true の直後に「-0」、「/1」、「^1」を付けても、同じ結果になります。

どこかのセルに、上の2式を入力してみてください。「0+」を付けないときは「FALSE」を返します。「0+」を付けると、「TRUE」を返します。これは、「true<3」とだけ書いたのでは論理式として成り立っていないため、「TRUEにはなり得ないのでFALSE」ということを意味しています。「0+」を付ければ「1<3」という論理式になるので、「TRUE」です。

=sum(true<3,1,false,0,-3,,,)
=sum(0+true<3,1,false,0,-3,,,)

という2式においても同様で、「true<3」は「FALSE」、「0+true<3」は「TRUE」として扱われるので、全体の数式も異なる値を返します。そうなる過程は、リボンの「数式＞数式の検証」などで確認することができます。

お礼

とても勉強になりました。
ありがとうございます。

回答No.11

cj_mover

こんにちは。＃６、７、８、ｃｊです。
＃７お礼欄へのレスです。

> =AND(SUM(--ISERR(FIND({"A","B","C"},A1)))<3,ISERR(FIND("CD",A1)))　ですが、--は何ででしょう？
> これがなくとも検査値が空白以外は同じ答えを返すようですがなぜかわかりませんでした。

=AND(SUM(1*ISERR(FIND({"A","B","C"},A1)))<3,ISERR(FIND("CD",A1)))
この方が解りやすいでしょうか。
論理値の配列（セル範囲ではなく値の配列）をそのままSUM関数に渡しても正しい結果は得られません。
そこで、TRUE は １、FALSE は ０、一旦数値の配列に直してから渡す必要があります。
その手段として"1*"や"--"を付けることで数値化しているものです。
以前、"1*"と"--"を比較して、どちらが優れているかという議論に参加したことがあります。
その時、沢山の数式をベンチテストしたデータを根拠に、私としては"--"の方が優るという結論を出したので
私は"--"派（？）なのです。
でも、"1*"の方が概念として理解されやすいので、使う時もありますけど。
配列数式を回答として書ける方なら、たぶんどちらもご存じだと思われますので、
どちらを使うかは好みでいいと思いますけれど。

///
"A","B","C","CD"、どれも含まい文字列に対しては、
本来FALSEを返すべきところ、
"--"がないとTRUEを返してしまいます。
例えば"EEEE"という値の場合、FALSEが正解なのに、、、。

まあ、でも、考えてみれば、SUM関数をわざわざ使うまでも無かったってオチなんですけどね。
「迷ったらSUMで書ける数式優先。失敗が少ないから。」という心掛けは
外れること、少ないとは思うのですけれど。

ところで、
色々昔の経緯を思い出したのですが、、、回答の方も頑張ってくださいね。
回答で鍛えられるものって大きくて、ありがたいものです。
今回も、とても勉強になりました。

お礼

よくわかりました。
ありがとうございます。

回答No.10

MarcoRossiItaly

No.9です。度々すみません。No.9の数式は、「>0」を削除しても構いません。そのほうが若干、式が短かったですね。

=or(countif(a1,"*"&{"A","B","C"}&"*"))*(countif(a1,"*CD*")=0)=1

なお、「ISNUMBER(FIND)」の場合、「A」などの文字列について、大文字と小文字を区別する必要があります。「COUNTIF」の場合は、区別がありません（区別できません）。

また、「NOT(ISNUMBER(FIND))」という部分は、「ISERROR(FIND)」と書き換えても構いません。

=AND(or(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1))),iserror(FIND("CD",A1)))

お礼

> 「COUNTIF」の場合は、区別がありません

使い分けできますね、ありがとうございます。

回答No.9

MarcoRossiItaly

=or(countif(a1,"*"&{"A","B","C"}&"*")>0)*(countif(a1,"*CD*")=0)=1

いろいろなご回答が出ているとおり、様々な流儀で書けます。あいまい検索にしておきましたが、完全一致に限定したい場合は「*」を削除してください。

No.5さんのご意見（何れか／全て）に一票。修正は次みたいな感じで。

=AND(or(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1))),NOT(ISNUMBER(FIND("CD",A1))))

お礼

ありがとうございます。
勉強になります。

回答No.8

cj_mover

くどいかな？でもやっぱあげます。

=ISERR(FIND("CD",A1))-AND(ISERR(FIND({"A","B","C"},A1)))=1
=ISERR(FIND("CD",A1))*OR(COUNTIF(A1,"*"&{"A","B","C"}&"*"))=1

（"=1"を取れば[1 | 0]）

結構軽く＋わかりやすくなったみたいです。
keithinさんのご回答は私も勉強になりました。

お礼

ありがとうございます。
くどいなんてとんでもない。
いろんな方法がありますねえ。

回答No.7

cj_mover

すみません。ちょっと余計な括弧消し忘れてました。
ついでに、COUNTIF版も上げます。
（COUNTIFの方が若干、計算が重いと思いますけれど）
論理値
=AND(SUM(COUNTIF(A1,"*"&{"A","B","C"}&"*")),ISERR(FIND("CD",A1)))
=AND(SUM(--ISERR(FIND({"A","B","C"},A1)))<3,ISERR(FIND("CD",A1)))
[1 | 0]値
=(SUM(--ISERR(FIND({"A","B","C"},A1)))<3)*ISERR(FIND("CD",A1))
=(SUM(COUNTIF(A1,"*"&{"A","B","C"}&"*"))>0)*ISERR(FIND("CD",A1))

お礼

cj_moverさん、以前は大変お世話になりました。
いろいろなやり方がありますね。
ありがとうございます。
=AND(SUM(--ISERR(FIND({"A","B","C"},A1)))<3,ISERR(FIND("CD",A1)))　ですが、--は何ででしょう？
これがなくとも検査値が空白以外は同じ答えを返すようですがなぜかわかりませんでした。

回答No.6

cj_mover

こんにちは。お久しぶりです。
＃あぁ、、、でも、せっかく書いていたので、、、

> A、B、Cの何れかを含み
ってことは、OR()ですよね＞

論理値
=AND((SUM(--ISERR(FIND({"A","B","C"},A1)))<3),ISERR(FIND("CD",A1)))
[1 | 0]値
=(SUM(--ISERR(FIND({"A","B","C"},A1)))<3)*ISERR(FIND("CD",A1))
とか、ですかね。
もう少し軽くなりそうな気もしますが、まあ。

お礼

> ってことは、OR()ですよね

はい、ご指摘の通りです。
すみませんでした。

回答No.5

noname#204879

貴方が「素直に」書いた式の意味は、
「A1セルに、文字列、A、B、Cの何れかを含み、かつCDを含まない」
ではなく、
「A1セルに、文字列、A、B、Cの全てを含み、かつCDを含まない」
になっていませんか？

お礼

おっしゃるとおりでした。
ありがとうございます。

回答No.3

tom04

こんばんは！

CDがなく、A・B・Cのいずれかがある場合は「TRUE」
それ以外の場合は「FALSE」としてみました。

=IF(NOT(ISNUMBER(FIND("CD",A1))),IF(OR(ISNUMBER(FIND({"A","B","C"},A1))),TRUE,FALSE),FALSE)

あまり短くなっていませんが
こんなんではどうでしょうか？m(_ _)m

お礼

ありがとうございます。
勉強になりました。