多くの値を含まないセルの数を数える

通常、COUNTIF関数を使用すると、特定の値をXNUMXつ含まないセルを簡単にカウントできます。この投稿では、Excelで指定された範囲内の多くの値を含まないセルの数をカウントする方法について説明します。

多くの値を含まないセルを数える方法は？

以下のスクリーンショットに示すように、D3：D11にリストされている値を含まないB3：B4のセルをカウントするには、次のようにします。

ジェネリックフォーミュラ

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Arguments

レンジ （必須）：多くの値を含まないセルをカウントする範囲。

Criteria_range （必須）：範囲には、セルをカウントするときに除外する値が含まれます。

注意： この数式は、配列数式として入力する必要があります。数式を適用した後、数式を中括弧で囲んでいる場合は、配列数式が正常に作成されます。

この式の使い方は？

1.。結果を出力するには、空白のセルを選択します。

2.以下の式を入力して、を押します。 Ctrlキー + シフト + 入力します 同時にキーを押して結果を取得します。

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

これらの式はどのように機能しますか？

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

TRANSPOSE(D3:D4)：TRANSPOSE関数は、D3：D4の方向を回転し、戻ります。 {「カウント」、「空白」};
SEARCH({“count”,”blank”},B3:B11)：ここでのSEARCH関数は、範囲B3：B11から部分文字列「count」と「blank」の位置を検索し、次のように配列を返します。 {#VALUE！、＃VALUE！;＃VALUE！、＃VALUE ！; 1、＃VALUE！;＃VALUE！、8; 1、＃VALUE！;＃VALUE！、＃VALUE！;＃VALUE！、＃VALUE！ ; 1、
#VALUE ！; 1,7}.
この場合、セルをカウントするときに除外する値が3つあるため、B11：B18の各セルが3回検索されるため、配列に11個の値が取得されます。配列内の各数字は、BXNUMX：BXNUMXの各セルの「カウント」または「空白」の最初の文字の位置を示します。
ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
#VALUE!;1,#VALUE!;1,7}：ISNUMBER関数は、配列内の数値に一致する場合はTRUEを返し、エラーに一致する場合はFALSEを返します。ここで結果を次のように返します {FALSE、FALSE; FALSE、FALSE; TRUE、FALSE; FALSE、TRUE; TRUE、FALSE; FALSE、FALSE; FALSE、FALSE; TRUE、FALSE;
TRUE、TRUE}.
--({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
FALSE;TRUE,TRUE})：これらの1つのマイナス記号は、「TRUE」を0に変換し、「FALSE」をXNUMXに変換します。ここでは、次のように新しい配列を取得します。 {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) ROW(D3:D4)^0：ROW関数は、セル参照の行番号{3; 4}を返します。次に、べき乗演算子（^）は、3と4の0乗の結果を返し、最後に次のように結果を返します。 {1; 1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):MMULT関数は、元のデータを満たすために、次の0つの配列の行列積{0; 1; 1; 1; 0; 0; 1; 2; XNUMX}を返します。配列内のゼロ以外の数値は、除外された文字列の少なくともXNUMXつが見つかった値を表し、ゼロは除外された文字列が見つからなかったことを示します。

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

{0;0;1;1;1;0;0;1;2}>0：ここで、配列内の各数値が0より大きいかどうかを確認します。数値が0より大きい場合はTRUEを返し、そうでない場合はFALSEを返します。次に、次のように新しい配列を取得します {FALSE; FALSE; TRUE; TRUE; TRUE; FALSE、FALSE、TRUE; TRUE}.
1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}：指定された値を含まないセルのみをカウントする必要があるため、配列内のこれらの値を1から減算して逆にする必要があります。ここで、数学演算子は自動的にTRUEとFALSEの値を1と0に変換し、最終的に結果として {1;1;0;0;0;1;1;0;0}.
SUM{1;1;0;0;0;1;1;0;0}：SUM関数は、配列内のすべての数値を合計し、最終結果を4として返します。