[[20190603190751]]　『文字列のOR検索にNOT検索を追加する方法』（業務中）　ページの最後に飛ぶ

『文字列のOR検索にNOT検索を追加する方法』（業務中）

あるセルにある文章に対して以下の式を用いて有無の確認をしておりました。
このOR検索だけの式にNOT検索追加したいと思い、*NOT(COUNTIF(a1,"*蜜柑*")などを追加してみましたが上手く行かず質問するものです。
対象セルに「りんご」または「林檎」または「リンゴ」があるときは○をつけますが、「蜜柑」などの対象としたくない複数の文字列があるときは○を表示させない方法となります。

=IF(OR(COUNTIF(a1,"*りんご*"),COUNTIF(a1,"*林檎*"),COUNTIF(a1,"*リンゴ*")),"○","　")

どうぞ宜しく御願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

A1セルに蜜柑が含まれる

 COUNTIF(a1,"*蜜柑*")=1
 (1なのでFalseではない＝True扱い）

A1セルに蜜柑が含まれない

 COUNTIF(a1,"*蜜柑*")=0
 (0なのでFalse扱い）

A1セルにリンゴ、林檎、りんごのいずれかが含まれている場合

 SUM(COUNTIF(A17,{"*りんご*","*林檎*","*リンゴ*"}))>0
 （※1～3が返り、0でないためTrue扱い）

A1セルにリンゴ、林檎、りんごのいずれも含まれていない場合

 SUM(COUNTIF(A17,{"*りんご*","*林檎*","*リンゴ*"}))=0
 (0なのでFalse扱い）

ということだから、

 =IF(COUNTIF(A1,"*蜜柑*"),"○を表示させない",IF(SUM(COUNTIF(A1,{"*りんご*","*林檎*","*リンゴ*"})),"○","蜜柑も林檎も無い"))

ではどうでしょうか？

（もこな２） 2019/06/03(月) 20:18

ありがとうございます。大変助かります。

蜜柑の他に「バナナ」「葡萄」を追加したいと思い、思うままに以下のような式を作ってしまいました。
上手く動くと思いましたが、使ってみると「蜜柑」しか反応しませんでした。
リンゴと同じロジックで{}にて複数化するものと思いましたが甘かったようです。
　
こういう場合は、蜜柑・バナナ・葡萄の何れかが含まれていた場合に「○を表示させない」にするには如何様に繋げればよいのでしょうか。

=IF(COUNTIF(A1,{"*蜜柑*","*バナナ*","*葡萄*"}),"○を表示させない",IF(SUM(COUNTIF(A1,{"*りんご*","*林檎*","*リンゴ*"})),"○","蜜柑も林檎も無い"))
（業務中） 2019/06/03(月) 21:12

{}で囲まれている部分は、配列数式というものになっています。
私も説明できるほど詳しくないので、仕組み的なお話は別途お調べいただくか、別の回答者さんをおまちください。

修正案は、配列を扱えるSUMを追加するのはどうでしょうか

 =IF(sum(COUNTIF(A1,{"*蜜柑*","*バナナ*","*葡萄*"})),"○を表示させない",IF(SUM(COUNTIF(A1,{"*りんご*","*林檎*","*リンゴ*"})),"○","蜜柑も林檎も無い"))

※スマホからなので、テストしてません。

（もこな２） 2019/06/03(月) 22:28

有難う御座います。無事に動作しました。
ただ、どうして動作しているかは今一つ分かってないので勉強します。
助かりました。
（業務中） 2019/06/03(月) 23:41

＞ただ、どうして動作しているかは今一つ分かってないので勉強します。
わかっている（つもりの）範囲で説明すると

【配列数式以外の部分】

 ・COUNTIF関数は、本来条件にあうものを数える関数だから0以上の整数が返ってくる（得られる）仕組みである。
　 したがって、条件にあうものが無い場合は0個みつかるため、結果は0になる。

 ・IF関数は、条件式の結果（TrueかFalse)に応じて第一引数、第二引数を返す仕組みである。

 ・Excelの仕組みとして、数値をTrueかFalseいずれかで分類使用とする場合、0はFalseとして扱い、
　 それ以外はTrueとして扱うルールになっている。
　　　 例：=IF(0,"真","偽")・・・偽   =IF(1,"真","偽")・・・真   =IF(-1,"真","偽")・・・真

【配列数式の部分】

 =IF(SUM(COUNTIF(A1,{"*蜜柑*","*バナナ*","*葡萄*"})),"真","偽")
　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ↑のIF関数の条件式にあたる部分を配列数式を使わずに記載するとこうなる
  SUN(
   COUNTIF(A1,"*蜜柑*"),
   COUNTIF(A1,"*バナナ*"),
   COUNTIF(A1,"*葡萄*"))

 したがって、A1セルの値に蜜柑、バナナ、葡萄のいずれも含まれていない場合は「0+0+0=0」となり、
 いずれかでも含まれていれば、1以上になる

【結論】

 条件式の結果が「0」or「1以上」となれば、Excelの仕組みに基づいてIF関数が「1以上=True」「0=False」
 として判定するため、
 蜜柑、バナナ、葡萄のすくなくともいずれかが含まれている・・・真
 蜜柑、バナナ、葡萄のいずれも含まれていない・・・・・・・・・偽
 という結果になります。

【留意点】
わかってる"つもり"なので説明に間違いがあるかもしれません。
その場合、他の回答者さんから適宜ツッコミがあるとおもいますので、時々本トピックをチェックされるとよいとおもいます。

（もこな２） 2019/06/05(水) 19:52

 ＞【配列数式以外の部分】 
 ＞【配列数式の部分】
 ＞配列数式を使わずに記載するとこうなる

 配列数式と言ってるのは
 おそらく「配列定数」のことだと思われます。

 ＞SUN(

 おそらく SUM 関数のことだと思われます。

 以上
（笑） 2019/06/05(水) 21:06

SUM関数はタイプミスですね。ご指摘ありがとうございます。

配列数式はおもいっきり勘違いしてました。
http://mt-soft.sakura.ne.jp/kyozai/excel_high/230_special/02_hairetu_kiso/index.htm
↑によると

【配列定数】

 配列的なデータを数式内で表現するための方法
 （列を[,]、行を[;]で区切り、（配列部分）全体を{}で囲む）

【配列数式】

 配列（複数セル）を対象にして作成した1つの数式
 数式を設定後、Shift＋Ctrl＋Enterで式を確定

とのことのようです。

そのほか、前投稿について、体裁を修正しました。

（もこな２） 2019/06/05(水) 21:39

コメント返信：

[ 一覧(最新更新順) ]

[[20190603190751]] 『文字列のOR検索にNOT検索を追加する方法』（業務中） ページの最後に飛ぶ

[[20190603190751]]　『文字列のOR検索にNOT検索を追加する方法』（業務中）　ページの最後に飛ぶ