Excel 数式：セルが複数の項目すべてを含むかどうかを確認

Excel で列 E に値のリストがあり、列 B の各セルが列 E のすべての値を含んでいるかどうかを確認し、下のスクリーンショットのように TRUE または FALSE を返したいとします。このチュートリアルでは、このタスクを実現するための数式をご紹介します。

汎用的な数式：

引数

返す値：

この数式は論理値を返します。FALSE はセルがすべての項目を含んでいないことを示し、TRUE はセルがすべての項目を含んでいることを示します。

この数式の動作方法

たとえば、列 B にテキスト文字列のリストがあり、そのリストが範囲 E3:E5 内のすべての値を含んでいるかどうかを確認したい場合は、以下の数式をご利用ください。

Enterキーを押した後、確認したいセルにフィルハンドルをドラッグします。FALSE はセルが範囲 E3:E5 内のすべての値を含んでいないことを示し、TRUE は対応するセルがすべての値を含んでいることを示します。

解説

SEARCH 関数：SEARCH 関数は、あるテキスト文字列内で別のテキスト文字列が最初に現れる位置を返します。テキストが見つかった場合はその位置（文字数）を返し、見つからない場合は#VALUE!エラーを返します。たとえば、数式 SEARCH($E$3:$E$5,B4)は、セル B4 内で範囲 E3:E5 の各値を検索し、それぞれのテキスト文字列がセル B4 内に現れる位置を返します。結果は次のような配列になります：{1;7;12}

ISNUMBER 関数：ISNUMBER 関数は、値が数値かどうかを判定し、TRUE または FALSE を返します。ここでISNUMBER(SEARCH($E$3:$E$5,B4))は、SEARCH 関数が3 つの数値を検出したため、次のような配列結果を返します：{true;true;true}

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

SUMPRODUCT 関数：SUMPRODUCT 関数は、複数の範囲または配列の対応する要素をそれぞれ乗算し、その積の合計を返します。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4)))は、1＋1＋1＝3 を返します。

COUNTA 関数：COUNTA 関数は、空白でないセルの数を返します。COUNTA($E$3:$E$5)は3 を返すため、SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4)))の結果が3 と等しい場合、数式全体は TRUE を返し、そうでない場合は FALSE を返します。

備考：

数式 =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things)は厳密なチェックを行っていません。詳しくは以下のスクリーンショットをご確認ください：

サンプルファイル

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

関連する数式

• 指定した値と等しいセルをカウント
  COUNTIF 関数を使えば、指定した値と一致するセルや、その値を含まないセルの個数を簡単にカウントできます。
• x または y と等しいセルをカウント
  場合によっては、2 つの条件のいずれかを満たすセルの数をカウントしたいことがあります。そのような場合は、COUNTIF 関数が使えます。
• x および y と等しいセルをカウント
  ここでは、2 つの条件を同時に満たすセルをカウントする数式をご紹介します。
• 指定した値と等しくないセルをカウント
  この記事では、COUNTIF 関数を使って、特定の値と等しくないセルの数を簡単にカウントする方法をご紹介します！