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

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

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

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

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

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

Arguments

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

この式の使い方は？

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

関連機能

関連式

---

最高のオフィス生産性ツール

Kutools forExcel-群衆から目立つのに役立ちます

🤖	Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行   |  コードを生成  |  カスタム数式の作成  |  データを分析してグラフを生成する  |  Kutools関数を呼び出す...
	人気の機能: 重複を検索、強調表示、または識別する  |  空白行を削除する  |  データを失わずに列またはセルを結合する  |  数式なしのラウンド ...
	スーパーVルックアップ: 複数の基準  |  複数の値  |  複数のシートにわたって  |  ファジールックアップ...
	上級ドロップダウンリスト: 簡単なドロップダウンリスト  |  依存関係のドロップダウン リスト  |  複数選択のドロップダウンリスト...
	列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  列を比較する 同じセルと異なるセルを選択する ...
	注目の機能: グリッドフォーカス  |  デザインビュー  |  ビッグフォーミュラバー  |  ワークブックとシートマネージャー | リソースライブラリ (自動テキスト)  |  日付ピッカー  |  ワークシートを組み合わせる  |  セルの暗号化/復号化  |  リストごとにメールを送信する  |  スーパーフィルター  |  特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
	上位 15 のツールセット:  12 テキスト ツール (テキストを追加, 文字を削除する ...）  |  50+ チャート 種類 (ガントチャート ...）  |  40+ 実用的 式 (誕生日に基づいて年齢を計算する ...）  |  19 挿入 ツール (QRコードを挿入, パスから画像を挿入 ...）  |  12 変換 ツール (数字から言葉へ, 通貨の換算 ...）  |  7 マージ＆スプリット ツール (高度な結合行, Excelのセルを分割する ...）  |  ... もっと

Kutools for Excelは300以上の機能を誇り、 必要なものをワンクリックで手に入れることができます...

 続きを読む無料ダウンロード

---

Officeタブ-MicrosoftOffice（Excelを含む）でタブ付きの読み取りと編集を有効にする

• 数十の開いているドキュメントを切り替えるのにXNUMX秒！
• マウスの手に別れを告げて、毎日何百ものマウスクリックを減らしてください。
• 複数のドキュメントを表示および編集する際の生産性が 50% 向上します。
• Chrome、Edge、Firefox と同様に、効率的なタブを Office (Excel を含む) にもたらします。

 続きを読む 無料ダウンロード