Anzahl der Zellen zählen, die viele Werte nicht enthalten

Normalerweise ist es einfach, Zellen zu zählen, die einen bestimmten Wert nicht enthalten, indem man die COUNTIF-Funktion verwendet. Dieser Beitrag führt Sie durch die Vorgehensweise, die Anzahl der Zellen zu zählen, die viele Werte in einem bestimmten Bereich in Excel nicht enthalten.

Wie zählt man Zellen, die viele Werte nicht enthalten?

Wie im folgenden Screenshot gezeigt, um die Zellen in B3:B11 zu zählen, die die in D3:D4 aufgelisteten Werte nicht enthalten, können Sie wie folgt vorgehen.

Allgemeine Formel

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

Argumente

Hinweis: Diese Formel muss als Array-Formel eingegeben werden. Nachdem Sie die Formel angewendet haben, wenn geschweifte Klammern um die Formel herum erscheinen, wurde eine Array-Formel erfolgreich erstellt.

Wie verwendet man diese Formel?

1. Wählen Sie eine leere Zelle aus, um das Ergebnis auszugeben.

2. Geben Sie die folgende Formel ein und drücken Sie dann gleichzeitig die Tasten Strg + Umschalt + Eingabe, um das Ergebnis zu erhalten.

=SUMME(1-(MMULT(--(ISTZAHL(SUCHEN(TRANSPOSE(D3:D4);B3:B11)));ZEILE(D3:D4)^0)>0))

Wie funktionieren diese Formeln?

=SUMME(1-(MMULT(--(ISTZAHL(SUCHEN(TRANSPOSE(D3:D4);B3:B11)));ZEILE(D3:D4)^0)>0))

1) --(ISTZAHL(SUCHEN(TRANSPOSE(D3:D4);B3:B11))):

• TRANSPOSE(D3:D4): Die TRANSPOSE-Funktion dreht die Ausrichtung von D3:D4 und gibt {"count","blank"} zurück;
• SUCHEN({"count","blank"};B3:B11): Die SUCHEN-Funktion findet hier die Position des Teilstrings "count" und "blank" im Bereich B3:B11 und gibt ein Array zurück als {#WERT!,#WERT!;#WERT!,#WERT!;1,#WERT!;#WERT!,8;1,#WERT!;#WERT!,#WERT!;#WERT!,#WERT!;1,
  #WERT!;1,7}.
• In diesem Fall wird jede Zelle in B3:B11 zweimal durchsucht, da es zwei Werte gibt, die Sie beim Zählen von Zellen ausschließen möchten, sodass Sie 18 Werte im Array erhalten. Jede Zahl im Array zeigt die Position des ersten Zeichens von "count" oder "blank" in jeder Zelle von B3:B11 an.
• ISTZAHL{#WERT!,#WERT!;#WERT!,#WERT!;1,#WERT!;#WERT!,8;1,#WERT!;#WERT!,#WERT!;#WERT!,
  #WERT!;1,#WERT!;1,7}: Die ISTZAHL-Funktion gibt WAHR zurück, wenn sie auf Zahlen im Array trifft, und gibt FALSCH zurück, wenn sie auf Fehler trifft. Hier wird das Ergebnis zurückgegeben als {FALSCH,FALSCH;FALSCH,FALSCH;WAHR,FALSCH;FALSCH,WAHR;WAHR,FALSCH;FALSCH,FALSCH;FALSCH,FALSCH;WAHR,FALSCH;
  WAHR,WAHR}.
• --({FALSCH,FALSCH;FALSCH,FALSCH;WAHR,FALSCH;FALSCH,WAHR;WAHR,FALSCH;FALSCH,FALSCH;FALSCH,FALSCH;WAHR,
  FALSCH;WAHR,WAHR}): Diese beiden Minuszeichen wandeln "WAHR" in 1 und "FALSCH" in 0 um. Hier erhalten Sie ein neues Array als {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) ZEILE(D3:D4)^0: Die ZEILE-Funktion gibt die Zeilennummern der Zellreferenz zurück: {3;4}, und dann gibt der Potenzierungsoperator (^) das Ergebnis der Zahl 3 und 4 hoch 0 zurück und liefert schließlich das Ergebnis als {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): Die MMULT-Funktion gibt das Matrixprodukt dieser beiden Arrays zurück: {0;0;1;1;1;0;0;1;2}, um den Originaldaten zu entsprechen. Jede Zahl ungleich Null im Array stellt einen Wert dar, wo mindestens einer der ausgeschlossenen Strings gefunden wurde, und Null zeigt an, dass keine ausgeschlossenen Strings gefunden wurden.

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

• {0;0;1;1;1;0;0;1;2}>0: Hier wird überprüft, ob jede Zahl im Array größer als 0 ist. Wenn eine Zahl größer als 0 ist, wird WAHR zurückgegeben, andernfalls FALSCH. Dann erhalten Sie ein neues Array als {FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH,FALSCH,WAHR;WAHR}.
• 1-{FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH,FALSCH,WAHR;WAHR}: Da wir nur die Zellen zählen müssen, die die angegebenen Werte nicht enthalten, müssen wir diese Werte im Array umkehren, indem wir sie von 1 subtrahieren. Hier wandelt der mathematische Operator automatisch WAHR- und FALSCH-Werte in Einsen und Nullen um und gibt schließlich das Ergebnis als {1;1;0;0;0;1;1;0;0} zurück.
• SUMME{1;1;0;0;0;1;1;0;0}: Die SUMME-Funktion summiert alle Zahlen im Array und gibt das Endergebnis als 4 zurück.

Verwandte Funktionen

Verwandte Formeln