Excel 数式：セルに複数の値のいずれかが含まれているが、他の値は含まれていないかを確認する

著者Sun変更日2024-10-22

2 つの値のリストがあると仮定します。セル B3 に範囲 E3:E5 のいずれかの値が含まれており、かつ範囲 F3:F4 のどの値も含まれていないことを確認したいとします（以下のスクリーンショット参照）。このチュートリアルでは、このタスクをExcel で素早く処理するための数式を紹介し、その引数を詳しく説明します。
ドキュメントがいずれかの項目を含むかどうかをチェック（ただし除外1）

汎用数式：

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0)

引数

Text: the text string you want to check.

Include: the values you want to check if argument text contains.

Exclude: the values you want to check if argument text does not contain.

返す値：

この数式は、セルに含める必要のある値のいずれかが含まれており、かつ除外する必要のある値が一切含まれていない場合に1 を返し、それ以外の場合は0 を返します。ここで、1 および0 はそれぞれ論理値 TRUE および FALSE として扱われます。

この数式の動作方法

セル B3 に範囲 E3:E5 のいずれかの値が含まれていて、かつ範囲 F3:F4 の値を除外したい場合は、次の数式をご利用ください。

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

キーを押してEnterを入力し、チェック結果を取得します。
ドキュメントがいずれかの項目を含むかどうかをチェック（ただし除外2）

解説

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

SEARCH 関数は、指定したテキスト文字列内で別の文字列が最初に現れる位置を返します。一致する文字列が見つかった場合はその相対位置を、見つからなかった場合は#VALUE!エラーを返します。たとえば、数式 SEARCH($E$3:$E$5,B3)は、セル B3 内から範囲 E3:E5 の各値を検索し、それぞれの文字列がセル 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 が返されています。
ドキュメントがいずれかの項目を含むかどうかをチェック（ただし除外3）

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

数式 SEARCH($F$3:$F$4,B3)は、範囲 $F$3:$F$4の各値をセルB3 内から検索し、見つかった各テキスト文字列の位置を返します。このとき、次のような配列結果が得られます：{#VALUE!;#VALUE!}。

ISNUMBER(SEARCH($F$3:$F$4,B3))は、SEARCH 関数が数値を1 つも検出しなかったため、{FALSE;FALSE}という配列結果を返します。{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 が返されています。
ドキュメントがいずれかの項目を含むかどうかをチェック（ただし除外4）

パート3：2 つの数式を掛け合わせる

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

=TRUE*TRUE

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

サンプルファイル

サンプルファイルをダウンロードするにはクリックしてください

最高の Office 生産性ツール

Kutools for Excel - あなたを群衆の中から際立たせます

🤖	KUTOOLS AI アシスト：次に基づいてデータ分析を革新します：インテリジェント実行｜コード生成｜カスタム数式作成｜データ分析とチャート生成｜拡張機能呼び出し…
	人気機能：検索・ハイライト・重複をマーキング｜空白行を削除する｜データを失うことなく列の結合またはセルを｜数式を使用しない四捨五入...
	スーパー VLookup：複数条件｜複数値｜複数シート間｜ファジーマッチ...
	高度なドロップダウンリスト：簡単なドロップダウンリスト｜連動型ドロップダウンリスト｜複数選択可能なドロップダウンリスト...
	列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜列を比較して同じ/異なるセルを選択...
	注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブックとシートマネージャー｜リソースライブラリ（オートテキスト）｜日付ピッカー｜ワークシートの統合｜暗号化／セルの復号化｜リストからメール送信｜スーパーフィルター｜特殊フィルタ（太字のフォントを持つセルをフィルタリング／イタリック／取り消し線。。。）。。。
	トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除...）｜ 50+チャートタイプ（ガントチャート...）｜ 40+実用的な数式（誕生日に基づいて年齢を計算します...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入...）｜ 12 変換ツール（単語に変換する、為替レートの変換...）｜ 7 結合と分割ツール（高度な行のマージ、Excel セルを分割...）｜。。。他にも多数