Excelでリスト内で条件と一致しないセルを数えたい

Excelで複数のセルや条件と一致しないセルを数えたいです。

状況

データの中で、リストに含まれていないデータがどれくらい含まれているのかを調べたい。

f:id:doodle-on-web:20190903155336p:plain

関数例

=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))

説明

セルの数が多くない場合は、MATCH、ISNA、およびSUMPRODUCT関数に基づく数式を使用できます。示されている例では、セルF5の式は次のとおりです。

=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))

ここで、 "data"は名前付き範囲 B5：B16、 "exclude"は名前付き範囲D5：D7です。

この式のしくみ

まず、ちょっとした文脈です。通常、数えたくないものがいくつかある場合は、次のようにCOUNTIFSできます。

=COUNTIFS(range,"<>apple",range,"<>orange")

しかし、たくさんのもののリストがある場合、これはあまりうまく拡大できません。なぜなら、数えたくないものごとに追加の範囲/基準のペアを追加する必要があるからです。リストを作成し、このリストへの参照を基準の一部として渡すのがはるかに簡単です。それがまさにこのページの公式がしていることです。

基本的に、この式は次の式で "a"、 "b"、または "c"に等しくないセルを見つけるためにMATCH関数を使用します。

MATCH(data,exclude,0)

ルックアップ値とルックアップ配列は通常の設定から「反転」されていることに注意してください。名前付き範囲「data」からのすべての値をルックアップ値として指定し、名前付き範囲「exclude」で除外するすべての値を指定します。MATCHに複数のルックアップ値を指定しているので、次のような配列で複数の結果が得られます。

{1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1}

基本的に、MATCHは一致する値の位置を数値として返し、それ以外のすべての値については＃N / Aを返します。

＃N / Aの結果は、 "a"、 "b"、または "c"に等しくない値を表すため、私たちが関心を持っているものです。したがって、ISNAを使用してこれらの値をTRUEに、数値をFALSEに強制します。

ISNA(MATCH(data,exclude,0)

次に、TRUEを1に、FALSEを0に強制するために二重否定を使用します。SUMPRODUCT内の結果の配列は、次のようになります。

=SUMPRODUCT({0;0;0;1;1;1;0;0;0;1;0})

処理する配列が1つだけの場合、SUMPRODUCTは最終結果4を合計して返します。

注：SUMの代わりにSUMPRODUCTを使用すると、control + shift + enterを使用する必要がなくなります。

マイナスマッチ

いくつかのことのいずれにも該当しないセルを数える別の方法は、すべての値を数え、一致を差し引くことです。これを次のような式で実行できます。

=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))

ここでは、COUNTAはすべての空でないセルの数を返します。COUNTIF関数は、名前付き範囲 "exclude"を指定すると、リスト内の各項目に1つずつ、合計3つのカウントを返します。SUMPRODUCTは合計を合計し、この数はすべての空でないセルの数から差し引かれます。最終結果は、 "exclude"の値と等しくないセルの数です。