欠損値のカウント

2 つのリストがある場合、一方のリストにのみ存在し、もう一方のリストには存在しない値の個数をカウントするには、SUMPRODUCT 関数をMATCH 関数および ISNA 関数と組み合わせて使うか、COUNTIF 関数を使うことができます。

SUMPRODUCT、MATCH、ISNA による欠損値のカウント
SUMPRODUCT と COUNTIF による欠損値のカウント

SUMPRODUCT、MATCH、ISNA による欠損値のカウント

上記のように、リスト B にありリスト A に存在しない値の総数をカウントするには、まず MATCH 関数を使って、リスト B の各値がリスト A 内でどの相対位置にあるかを示す配列を取得します。リスト A に該当する値がない場合は#N/A エラーが返されるため、次に ISNA 関数でそのエラーを判定し、SUMPRODUCT 関数でエラーの総数をカウントします。

一般的な構文

=SUMPRODUCT(--ISNA(MATCH()))range_to_count,lookup_range,0)))

リスト B にありリスト A に存在しない値の総数をカウントするには、下記の数式をセル H6 にコピーまたは入力し、Enterキーを押して結果を取得してください：

=SUMPRODUCT(--ISNA(MATCH()))F6:F8,B6:B10,0)))

数式の説明

=SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0)))

• MATCH(F6:F8,B6:B10,0):このmatch_type 0により、MATCH 関数はセルF6からF8の各値が範囲 B6:B10 内でどの相対位置にあるかを示す数値を返します。リスト A に該当値が存在しない場合は#N/A エラーが返されます。その結果、次のような配列が得られます：{2;3;#N/A}。
• ISNA()MATCH(F6:F8,B6:B10,0))=ISNA(){2;3;#N/A}):ISNA 関数は、「#N/A」エラーかどうかを判定します。エラーであれば TRUE、そうでなければ FALSE を返します。したがって、この ISNA 数式は次の配列を返します：{FALSE;FALSE;TRUE}。
• SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSE;FALSE;TRUE}): 二重マイナス記号（--）によって、TRUE は1 に、FALSE は0 に変換されます：{0;0;1}。その後、SUMPRODUCT 関数がその合計を返します：1。

SUMPRODUCT と COUNTIF による欠損値のカウント

リスト B にありリスト A に存在しない値の総数をカウントするには、COUNTIF 関数を使い、「=0」という条件でリスト A に値が存在するかを判定することも可能です。値が欠損している場合は0 が返され、その後、SUMPRODUCT 関数がその欠損値の総数をカウントします。

一般的な構文

=SUMPRODUCT(--(COUNTIF()))lookup_range,range_to_count)=0))

リスト B にありリスト A に存在しない値の総数をカウントするには、下記の数式をセル H6 にコピーまたは入力し、Enterキーを押して結果を取得してください：

=SUMPRODUCT(--(COUNTIF()))B6:B10,F6:F8)=0))

数式の説明

=SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0))

• COUNTIF(B6:B10,F6:F8):COUNTIF 関数は、セルF6からF8の各値が範囲 B6:B10 内にそれぞれ何回出現するかをカウントします。その結果は、次のような配列になります：{1;1;0}。
• --()COUNTIF(B6:B10,F6:F8)=0)=--(){1;1;0}=0):この式 {1;1;0}=0 は、TRUE および FALSE からなる配列 {FALSE;FALSE;TRUE}を生成します。二重マイナス（--）により、TRUE は1 に、FALSE は0 に変換されます。最終的な配列は次のようになります：{0;0;1}。
• SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}):SUMPRODUCT 関数はその後、合計を返します：1。

関連関数

関連数式