• ベストアンサー
  • 暇なときにでも

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

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

共感・応援の気持ちを伝えよう!

  • 回答数13
  • 閲覧数1706
  • ありがとう数14

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.4
  • keithin
  • ベストアンサー率66% (5278/7939)

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

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • 文字列AかBを含むセル数をSUMPRODUCT関数で求める場合

    文字列AかBを含むセル数をSUMPRODUCT関数で求める場合 エクセル2000です。 A1:A10のセル範囲だとして =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1) とすると、同一セルにA、Bの両方があった場合、重複してカウントされてしまいます。 重複を除外するために、 =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1)-SUMPRODUCT(ISNUMBER(FIND("A",A1:A10))*ISNUMBER(FIND("B",A1:A10))) と、重複してカウントされた数から両方が存在するセルの数を減じて求めましたが、以下のようなより簡素な式でも求められことがわかりました。 =SUMPRODUCT((ISNUMBER(FIND("A",A1:A10))+ISNUMBER(FIND("B",A1:A10))>=1)*1) この式でなぜAB両方があるセルが重複カウントされないのかどうもわかりません。 ご教示いただければ幸いです。 なお、SUMPRODUCTではなく =SUM(COUNTIF(A1:A10,{"*A*","*B*"}))-SUM(COUNTIF(A1:A10,{"*A*B*","*B*A*"})) でもできることは存じておりますが、今回はSUMPRODUCTの疑問として質問いたしました。

  • EXCEL関数について

    質問があります。 エクセルのA行に 定期点検 (月度検査) ラジオ修理 ボリューム つまみ 校正 視力検査 ボールペン 発注 替芯 購入 コピー用紙 経費 ポケットティッシュ配布用    ・    ・       ・  とあるのを、B行に下記の用に変換したいのですが、 検査 修理 検査 検査 購入 購入 購入 購入 その他 その他 その他    ・    ・       ・  下記のように関数をつくったのですが、うまく動きません =IF(A1="","",IF(ISNUMBER(FIND("点検",A1)),"検査",IF(ISNUMBER(FIND("修理",A1)),"修理",IF(ISNUMBER(FIND("校正",A1)),"検査",IF(ISNUMBER(FIND("検査",A1)),"検査",IF(ISNUMBER(FIND("発注",A1)),"購入",IF(ISNUMBER(FIND("購入",A1)),"購入",IF(ISNUMBER(FIND("経費",A1)),"購入,"IF(ISNUMBER(FIND("配布用",A1)),"購入","その他"))))) わかる方がいらっしゃいましたら、どうぞ教えて下さい。

  • Excelの関数に別セル文字列を含める方法について

    Excelの関数に別セル文字列を含める方法について Excelについて質問させていただきます。 あるセルの文字列を用いて別のセルの関数に利用することはできますでしょうか? 例) ・目的  シート1のA2に、シート<AAAA>のA1セルにある"test"を表示させたい ・方法 ○シート1 A1 "<AAAA>" A2 <AAAA>!A1 (ここの<AAAA>を何らかの方法にてA1を参照にさせ、表示させたい) ○シート<AAAA> A1 "test"

その他の回答 (12)

  • 回答No.13

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

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

こんにちは。#6、7、8、cjです。 #7お礼欄へのレスです。 > =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 は 1、FALSE は 0、一旦数値の配列に直してから渡す必要があります。 その手段として"1*"や"--"を付けることで数値化しているものです。 以前、"1*"と"--"を比較して、どちらが優れているかという議論に参加したことがあります。 その時、沢山の数式をベンチテストしたデータを根拠に、私としては"--"の方が優るという結論を出したので 私は"--"派(?)なのです。 でも、"1*"の方が概念として理解されやすいので、使う時もありますけど。 配列数式を回答として書ける方なら、たぶんどちらもご存じだと思われますので、 どちらを使うかは好みでいいと思いますけれど。 /// "A","B","C","CD"、どれも含まい文字列に対しては、 本来FALSEを返すべきところ、 "--"がないとTRUEを返してしまいます。 例えば"EEEE"という値の場合、FALSEが正解なのに、、、。 まあ、でも、考えてみれば、SUM関数をわざわざ使うまでも無かったってオチなんですけどね。 「迷ったらSUMで書ける数式優先。失敗が少ないから。」という心掛けは 外れること、少ないとは思うのですけれど。 ところで、 色々昔の経緯を思い出したのですが、、、回答の方も頑張ってくださいね。 回答で鍛えられるものって大きくて、ありがたいものです。 今回も、とても勉強になりました。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

  • 回答No.10

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

=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

くどいかな?でもやっぱあげます。 =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

すみません。ちょっと余計な括弧消し忘れてました。 ついでに、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

こんにちは。お久しぶりです。 #あぁ、、、でも、せっかく書いていたので、、、 > 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
  • ベストアンサー率49% (2537/5117)

こんばんは! 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

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • エクセルで"=AND(ISNUMBER(B2),ISNUMBER(C3),ISNUMBER(D4))"の簡略化?

    エクセル2000です。ワークシート関数での質問です。 特定の複数のセルのすべてに数値が入力されているかどうかの判定は、たとえば以下のような関数で調べられますね? =AND(ISNUMBER(B2),ISNUMBER(C3),ISNUMBER(D4)) このように少ないセルならこれでもいいのですが、多くなってくるといちいち、セルの数だけISNUMBER()を書いていくのが大変になります。 '=ISNUMBER(B2,C3,D4)←これでは当然エラーになりますが、こんな感じで簡単に判定するワークシート関数はないのでしょうか?

  • セル内の文章から文字を抜出したいです

    セル内の文章から文字を抜出したいです。 文字の書き方は、本当にランダムな並び方をしています。 [例] A1セル あいうえお12345 ↓ B1セル ○○ A2セル かきくけこ456789 ↓ B2セル ■■ いま、下の関数式を入れていますが、5つしか文字を抜き出せません。 せめて倍の10個は抜出したいのですが、どうすればいいでしょうか? =IF(ISNUMBER(FIND("あいうえお",A2)),"OO",IF(ISNUMBER(FIND("さしすせそ",A2)),"■■",IF(ISNUMBER(FIND("かきくけこ",A2)),"□□",IF(ISNUMBER(FIND("たちつてと",A2)),"◇◇",IF(ISNUMBER(FIND("なにぬねの",A2)),"△△","その他"))))) よろしくお願いいたします。

  • excelで文字列を検索する関数を教えてください。

    いつもお世話になります。 エクセルの関数で、シートAのセルA1に入っている文字列をシートBのC列から探し出し、同じ文字列があればTrueをなければFalseを返す関数を教えてください。 わからないながらも色々調べてみたのですが、検索にはVLOOKUPがよく使われているようですが、シートBのC列は、データの並べ替えができないので、VLOOKUPは、使えないのです。 よろしくお願いします。

  • Excelの文字列検索関数について

    Excel2007で、あるセルに入っている英単語が、別のセル範囲(複数セル)に入っている英文の中にあるかどうか、を調べたいのですが、関数を使って実現できるでしょうか? たとえば、 A1に"This is a pen." A2に"That is a desk" という英文があり、 B1に"bat", B2に"pen"を入れたとします。 この場合、A1~A2に"bat"は存在しないのでC1には"FALSE"が、 A1~A2に"pen"は存在するのでC2には"TRUE"が自動的に入るようにしたいのです。 (TRUE - FALSE でなくても、数字などでもかまいません) FIND関数は単一セルの中の文字列しか検索できないし、 MATCH関数はセル内容が同一でないと結果が出ないのでそのままでは使えません。 何かいい方法はないでしょうか。

  • 文字列検索の参照文字列の指定について

    Excelでセルに任意の文字列をペーストします。そのセルは別のブックにリンクしているのでペーストする度にリンク先のセルでは文字列が更新されます。リンク先のブックにはワークシートが10あっていづれかのシートのA列にその文字列がある事になっています。更新された文字列を検索する作業を繰り返すのでマクロで組み込もうと思ったのですがFIND関数にしても、VBでCell.Find(What:=....と書くにも参照する文字列を直接指定してやらないと動きません。参照する文字列が相対的に変化するので「特定のセルの中に入力された文字列」を参照してブック内を検索したいということなのですが、実現するにはどうしたらようでしょうか?

  • エクセル ある文字列を入れるとある文字列を返す

    エクセルでの関数、ど素人です。一応自分で調べたのですが、無理なので質問させていただきます。よろしくお願いいたします。 エクセルで、時刻ですが文字列として1745&#65374;2345までを手入力します(1745,とか2000とか2330とか。。。。。) 入れた文字列によって、すぐ横のセルに1745、1800、1815であれば8.00、1830からは0.25刻みで8.25、1845であれば8.50、1900であれば8.75。。。。2330であれば13.25、2345であれば13.50と言ったように、入力した文字列に対しある特定の文字列を自動的に入れたいのですが、どのような関数を使えばよろしいのでしょうか。 私が行おうとしたのは、別シートに文字列一覧を作り、if(特定のセル(手入力するセル)=別シートの1745&#65374;2345まで、横のセル=別シートの8.00、8.00、8.00&#65374;13.50まで)や、sumif、lookupを使ってみましたが撃沈です。 どなたか教えていただきたく、お願いいたします。

  • EXCEL関数の配列の考え方について

    EXCEL関数の配列について、ご教授願います。 具体的には、集計作業で条件を絞り込む時、例えば、列Aでは文字列「あ」又は「い」を含み、列Bでは文字列「う」、「え」、または「お」を含むデータ数を集計する場合、 =SUMPRODUCT((A1:A100={"あ","い"})*(B1:B100={"う","え","お"}))・・・(1) 私はいつもこの方法を使用しています。この場合、列Aでの条件数(「あ」「い」の2つ)と列Bでの条件数(同3つ)が異なっている場合でも、特に問題は起こりません。 但し、これを下記のように書き換えます。 =SUMPRODUCT(ISNUMBER(FIND({"あ","い"},A1:A100))*ISNUMBER(FIND({"う","え","お"},B1:B100)))・・・(2) (2)式は(1)式と同じ意味を示すはずですが、こちらは#N/Aでエラーとなります。調べてみたところ、列Aの条件数(2つ)と列Bの条件数(3つ)が異なる場合(どちらかの条件数が1の場合を除く)、エラーになるという事実は判明しましたが、計算式内部でどのような処理が行われた結果、そうなったのか、全く分かりませんでした。恐らく、EXCELの配列や行列式の考え方が両者で異なっているからだと思うのですが、ネットで調べてもその答えらしきものを見つけることができませんでした。  これについて、ご存知の方、どうぞご教授下さいます様、お願い致します。

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

    エクセルの関数ですが、指定したある特定のセルの中に「任意」または「フリー」という文字列が無かった場合のみ「TRUE」を返してほしいのです。 やってみましたがいまいち自信がもてないので質問しました。 =IF(AND(LEFT(OFFSET(D48,B49,0),2)<>"任意",LEFT(OFFSET(D48,B49,0),3)<>"フリー"),TRUE) と =AND(ISERROR(FIND("フリー",OFFSET(D48,B49,0))),ISERROR(FIND("任意",OFFSET(D48,B49,0)))) はまったく同じ結果となりますか? 異なる結果が返る場合はありませんか? また、両方とも同じ式の繰り返しを使っていますが、もっと簡単に記述できませんでしょうか?

  • エクセル2002でIF関数を使用して特定の文字列を検索しそれを数字で別のセルに表記する方法

    名に「女子」の文字があれば1の女子。 「短期大学」の文字があれば2の短期大学。「短期」としか書いてないところは3の短期、それら以外は、4のその他。また、女子と短期大学の両方書いてある場合は、最初に出てくる方を採用(例えば○○女子短期大学とあれば1の女子、○○短期大学女子というのであれば、2の短期大学)。このような場合最初の3つの条件であれば=IF(ISNUMBER(FIND("女子",A1)),"1",IF(ISNUMBER(FIND("短期大学",A1)),"2",IF(ISNUMBER(FIND("短期",A1)),3,4))) と考えたんですがその先がわからず。(これもあってるかどうか・・・)もしもわかる方がいましたらぜひぜひ教えてください。

  • エクセル関数で文字列の中のスペースの有無を調べたい。

    エクセル関数について教えてください。 全てが半角のはずの文字列・A1セルについて、下記について調べたいです。 1.同列(A列)の他のセルに入力されている文字列とのダブリチェック(同じ文字列がないかの確認)     *ダブリがある場合"ダブリ"表示 2.A1の文字列中のスペースの有無        *スペースがある場合"スペース"表示 3.A1の文字列中に全角が混ざっていないか      *全角がある場合"全角"表示 1.については、 =IF(COUNTIF(A:A,A1)>=2,"ダブリ","") の関数を使用してチェックが出来ると思うのですが、 2.については、関数TRIM 及び 置換での削除方法、3.については、 関数ASC での半角変換 しか手段が見つかりませんでした。 上記のものをマクロを組んでいる中に組み込みたいので、出来れば、上記3つの結果をひとつのセル(C1)に表示させたいのですが、可能でしょうか? *A1セルに上記3つが当てはまる場合、C1セルに "ダブリスペース全角" と表示させたい。 どれについて引っかかっているのかがわかればいいので、記号などに置き換えてもかまいません。 可能な限り調べては見たのですが、私の知識ではどうにもなりませんでした。 もし、いい案などがあればご紹介してください。 宜しくお願いいたします。