Excelの数式: セルが特定の値を含むかどうかを確認し、他の値を除外する

2つの値リストがあると仮定して、セルB3が範囲E3:E5内のいずれかの値を含むかどうかを確認したいが、同時に範囲F3:F4内のいずれの値も含まないことを確認したい場合（以下のスクリーンショット参照）。このチュートリアルでは、Excelでこのタスクを迅速に処理するための数式を提供し、その数式の引数について説明します。

汎用数式：

引数

戻り値：

数式は1または0を返します。セルが含まれるべき値のいずれかを含み、除外すべき値を含まない場合、1を返します。それ以外の場合は0を返します。この数式では、1と0は論理値TRUEおよびFALSEとして扱われます。

この数式の仕組み

セルB3が範囲E3:E5内のいずれかの値を含むかどうかを確認したいが、同時に範囲F3:F4の値を除外したい場合、次の数式を使用してください。

押す Enter キーを押してチェック結果を得る。

説明

パート1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) は、セルがE3:E5の値を含むかどうかを確認します。

SEARCH関数: SEARCH関数は、あるテキスト文字列が別の文字列内に存在する場合、最初の文字の位置を返します。SEARCH関数が一致するテキストを見つけると、相対的な位置を返し、見つからない場合は#VALUE!エラーを返します。例えば、SEARCH($E$3:$E$5,B3)という数式は、範囲E3:E5の各値をセルB3内で検索し、セルB3内の各テキスト文字列の位置を返します。結果として次のような配列を返します：{1;7;12}。

ISNUMBER関数: ISNUMBER関数は、セルが数値の場合にTRUEを返します。したがって、ISNUMBER(SEARCH($E$3:$E$5,B3))は、SEARCH関数が3つの数値を見つけた場合、{true,true,true}という配列結果を返します。

--ISNUMBER(SEARCH($E$3:$E$5,B3))は、TRUE値を1に変換し、FALSE値を0に変換します。したがって、この数式は配列結果を{1;1;1}に変更します。

SUMPRODUCT関数: 複数の範囲を掛け合わせたり、配列を合計したりして、積の合計を返します。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))は、1+1+1=3を返します。

最後に、左側の数式を比較します。 SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) そして0を比較します。左側の数式の結果が0より大きい限り、結果はTRUEになり、そうでなければFALSEになります。ここではTRUEを返します。

パート2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) は、セルがF3:F4の値を含まないかどうかを確認します。

数式SEARCH($F$3:$F$4,B3)は、範囲E3:E5の各値をセルB3内で検索し、セルB3内の各テキスト文字列の位置を返します。結果として次のような配列を返します：{#VALUE!;#VALUE!}。

ISNUMBER(SEARCH($F$3:$F$4,B3))は、SEARCH関数が0個の数値を見つけた場合、{false;false}という配列結果を返します。

--ISNUMBER(SEARCH($F$3:$F$4,B3))は、TRUE値を1に変換し、FALSE値を0に変換します。したがって、この数式は配列結果を{0;0}に変更します。

SUMPRODUCT関数: 複数の範囲を掛け合わせたり、配列を合計したりして、積の合計を返します。 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))は、0+0=0を返します。

最後に、左側の数式を比較します。 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) そして0を比較します。左側の数式の結果が0に等しい限り、結果はTRUEを返し、そうでなければFALSEを返します。ここではTRUEを返します。

パート3: 二つの数式を掛ける

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

この数式では、1と0は論理値TRUEおよびFALSEとして扱われます。

サンプルファイル

クリックしてサンプルファイルをダウンロード

関連数式

• セルが特定のテキストを含むかどうかを確認する
  セルが範囲A内のいくつかのテキストを含むが、範囲B内のテキストを含まないかどうかを確認したい場合、COUNT、SEARCH、AND関数を組み合わせた配列数式を使用できます。
• セルが複数のもののいずれかを含むかどうかを確認する
  このチュートリアルでは、セルがExcelで複数の値のいずれかを含むかどうかを確認するための数式を提供し、その数式の引数と数式の動作について説明します。
• セルが複数のもののいずれかを含むかどうかを確認する
  Excelで、列Eに値のリストがあり、列Bのセルが列Eのすべての値を含むかどうかを確認し、TRUEまたはFALSEを返したいと仮定します。
• セルが数値を含むかどうかを確認する
  場合によっては、セルが数値文字を含むかどうかを確認したい場合があります。このチュートリアルでは、セルが数値を含む場合にTRUEを返し、数値を含まない場合にFALSEを返す数式を提供します。