Excel で複数の値と等しくないセルの個数をカウントする

Excel では、COUNTIF 関数を使えば特定の値と等しくないセルの個数を簡単にカウントできますが、複数の値と一致しないセルを数えたことはありますか？ たとえば、下のスクリーンショットのように列 A にある製品の総数を取得したいものの、C4～C6 に記載された特定のアイテムを除外したい場合です。 本記事では、こうしたケースに対応するためのExcel 数式をいくつかご紹介します。

• 数式1 で複数の値と等しくないセルの個数をカウントする
• 数式2 で複数の値と等しくないセルの個数をカウントする

数式1 で複数の値と等しくないセルの個数をカウントする

複数の指定値と等しくないセルをカウントするには、MATCH 関数、ISNA 関数、および SUMPRODUCT 関数を組み合わせて使うことができます。一般的な構文は次のとおりです。

• range：カウント対象のセル範囲を指定します。
• exclude_values：範囲リストから除外する値

1。以下の数式を、結果を表示したい空白セルにコピー＆ペーストするか、直接入力してください。

2。次に、正しい結果を得るにはEnterキーを押してください（スクリーンショットを参照）。

数式の説明：

• MATCH(A2:A13,C2:C4,0)：この MATCH 関数は、一致する各値の位置を数値で返し、見つからない場合は#N/A エラーを返します。つまり、A2:A13 の値が C2:C4 のいずれかと一致する場合にその位置（数値）を返し、一致しない場合は#N/A エラーを返します。今回の結果は、{1；#N/A；#N/A;1;3;2；#N/A；#N/A;3；#N/A；#N/A；#N/A}です。
• ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A;#N/A;3;#N/A;#N/A;#N/A})：この ISNA 関数により、エラー値は TRUE に、数値は FALSE になります。その結果、次のような配列が得られます：{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}。
• --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE})：二重マイナス演算子（--）によって、すべてのTRUEが1に、FALSEが0に変換されます。そのため、SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))はSUMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1})と等しくなります。
• その後、SUMPRODUCT 関数を使用して配列内の数値を合計し、結果7 を得ます。

数式2 で複数の値と等しくないセルの個数をカウントする

Excel でこの作業を解決するには、COUNTA 関数、SUMPRODUCT 関数、および COUNTIF 関数を組み合わせることもできます。一般的な構文は次のとおりです。

• range：カウントしたいセルのリスト。
• exclude_values：範囲リストから除外する値。

1。結果を表示したい空白セルに、以下の数式を入力またはコピーしてください。

2。次に、Enterキーを押すと、次のスクリーンショットのような結果が表示されます。

数式の説明：

• COUNTA(A2:A13)：この COUNTA 関数は、範囲 A2:A13 内で空でないセルの個数を返します。結果は12 になります。
• COUNTIF(A2:A13,C2:C4)：この COUNTIF 関数は、範囲 A2:A13 内で C2:C4 の各条件に一致するセルをカウントします。その結果、{2;1;2}が返され、これは「Apple」が2 回、「Grape」が1 回、「Lemon」が2 回出現することを意味します。
• SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2})：この SUMPRODUCT 関数は、配列内のすべての要素を合計し、最終的な結果として5 を返します。
• COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5：すべての空白でないセルの数（12）から、SUMPRODUCT 関数の結果（5）を減算して、最終的な求めたい値を得ます。

注：上記の両方の数式は大文字と小文字を区別しません。

使用される関連関数：

その他の記事：