多くの値を含まないセルの数をカウントする

通常、COUNTIF 関数を使えば、特定の値を含まないセルをカウントするのは簡単です。本記事では、Excel の制限された範囲内で複数の値を含まないセルの数をステップごとにカウントする方法をご紹介します。

値が少ないセルをカウントするには？

以下のスクリーンショットのように、B3:B11 の範囲で D3:D4 にリストされた値を含まないセルをカウントするには、次のように操作してください。

汎用的な数式

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

引数

注：この数式は配列数式として入力する必要があります。数式を適用した後、数式の両端に中かっこが表示されていれば、配列数式が正しく入力されています。

この数式の使用方法は？

1．結果を表示する空白セルを選択してください。

2．次の数式を入力し、Ctrl+Shift+Enterキーを同時に押して、結果を取得します。

=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 の方向を転置し、{“count”,”blank”}を返します。
• 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}。
• この場合、セルをカウントする際に除外したい値が2 つあるため、B3:B11 の各セルが2 回検索され、配列には18 個の値が得られます。この配列の各数値は、B3:B11 の各セル内で「count」または「blank」のいずれかの最初の文字が出現する位置を示しています。
• 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})：先頭のマイナス記号2 つにより、「TRUE」は1 に、「FALSE」は0 に変換されます。これにより、新しい配列が得られます。{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 関数は、この2 つの配列の行列積を計算し、結果として{0;0;1;1;1;0;0;1;2}という配列を返します。この結果を元のデータと照合すると、ゼロ以外の値はいずれかの除外対象文字列が該当したことを示し、ゼロは除外対象文字列が見つからなかったことを意味します。

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 を返します。

関連関数

関連数式