Direkt zum Inhalt

Zählen Sie die Anzahl der Zellen, die nicht vielen Werten in Excel entsprechen

In Excel können Sie mit der ZÄHLENWENN-Funktion leicht die Anzahl der Zellen ermitteln, die nicht einem bestimmten Wert entsprechen, aber haben Sie jemals versucht, die Zellen zu zählen, die nicht vielen Werten entsprechen? Zum Beispiel möchte ich die Gesamtzahl der Produkte in Spalte A abrufen, aber die spezifischen Artikel in C4:C6 ausschließen, wie unten im Screenshot gezeigt. In diesem Artikel werde ich einige Formeln zur Lösung dieser Aufgabe in Excel vorstellen.


Zählen Sie die Anzahl der Zellen, die nicht vielen Werten entsprechen, mit Formel1

Um Zellen zu zählen, die nicht vielen angegebenen Werten entsprechen, können Sie die Kombination der Funktionen MATCH, ISNA und SUMPRODUCT verwenden. Die generische Syntax lautet:

= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
  • range: Die Liste der Zellen, die Sie zählen möchten;
  • exclude_values: Die zu ignorierenden Werte aus der Bereichsliste.

1. Geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie das Ergebnis ausgeben möchten:

=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))

2. Dann drücken Enter Schlüssel, um das richtige Ergebnis zu erhalten, siehe Screenshot:


Erklärung der Formel:
  • ÜBEREINSTIMMUNG(A2:A13,C2:C4,0): Diese MATCH-Funktion gibt die Position jedes übereinstimmenden Wertes als Zahl zurück und gibt #N/A Fehlerwert zurück, wenn er nicht gefunden wurde. Das bedeutet, wenn die Werte in A2:A13 den Werten in C2:C4 entsprechen, wird eine Zahl zurückgegeben, andernfalls wird ein #NA-Fehler zurückgegeben. Hier ist das Ergebnis: {1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N / A; #N / A;}.
  • ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Diese ISNA-Funktion erzwingt, dass die Fehlerwerte WAHR und die Zahlen FASLE werden, also erhalten Sie Folgendes: {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE} .
  • --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR}): Der doppelte negative Operator -- konvertiert alle TRUEs in 1s und FALSEs in 0s wie folgt: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1 ;0;0;0;1;1;0;1;1;1;}).
  • Und dann wird die SUMPRODUCT-Funktion verwendet, um die Zahlen im Array zu summieren und das Ergebnis zu erhalten:7.

Zählen Sie die Anzahl der Zellen, die nicht vielen Werten entsprechen, mit Formel2

Um diese Aufgabe in Excel zu lösen, können Sie auch die Kombination der Funktionen COUNTA, SUMPRODUCT und COUNTIF anwenden, die generische Syntax lautet:

=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
  • range: Die Liste der Zellen, die Sie zählen möchten;
  • exclude_values: Die zu ignorierenden Werte aus der Bereichsliste.

1. Bitte geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie, um das Ergebnis einzufügen:

=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))

2. Dann drücken Enter Taste, um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten:


Erklärung der Formel:
  • ANZAHLA(A2:A13): Diese COUNTA-Funktion gibt die Anzahl aller nicht leeren Zellen im Bereich A2:A13 zurück. Und es wird das Ergebnis erhalten:12.
  • ZÄHLENWENN(A2:A13,C2:C4): Diese ZÄHLENWENN-Funktion zählt die Zellen im Bereich A2:A13, die dem Kriterium in C2:C4 entsprechen. Sie erhalten also dieses Ergebnis: {2;1;2} was bedeutet, dass Apple zweimal erscheint, Traube einmal erscheint und Zitrone erscheint zweimal.
  • SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): Diese SUMPRODUCT-Funktion summiert alle Elemente im Array und gibt ein Endergebnis zurück:5.
  • COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 :Das Ergebnis (5) der SUMMENPRODUKT-Funktion wird von der Anzahl aller nicht leeren Zellen (12) subtrahiert, um das gewünschte Endergebnis zu erhalten.

Hinweis: Bei beiden obigen Formeln wird die Groß-/Kleinschreibung nicht beachtet.


Verwendete relative Funktion:

  • PASSEN ZU:
  • Die MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position dieses Werts zurück.
  • ISNA:
  • Die ISNA-Funktion wird verwendet, um zu überprüfen, ob eine Formel einen #N/A-Fehler zurückgibt.
  • SUMMENPRODUKT:
  • Die SUMPRODUCT-Funktion kann verwendet werden, um zwei oder mehr Spalten oder Arrays miteinander zu multiplizieren und dann die Summe der Produkte zu erhalten.
  • COUNTIF:
  • Die COUNTIF-Funktion ist eine statistische Funktion in Excel, mit der die Anzahl der Zellen gezählt wird, die ein Kriterium erfüllen.
  • Leutnant:
  • Die Funktion ANZAHLA gibt die Anzahl der Zellen mit Ausnahme von leeren Zellen zurück.

Weitere Artikel:

  • Zählen Sie die Anzahl der Zellen gleich einem von vielen Werten in Excel
  • Angenommen, ich habe eine Liste von Produkten in Spalte A, jetzt möchte ich die Gesamtzahl der spezifischen Produkte Apple, Grape und Lemon abrufen, die im Bereich C4:C6 aus Spalte A aufgeführt sind, wie unten im Screenshot gezeigt. Normalerweise funktionieren in Excel die einfachen Funktionen ZÄHLENWENN und ZÄHLENWENN in diesem Szenario nicht. In diesem Artikel werde ich darüber sprechen, wie Sie diese Aufgabe schnell und einfach mit der Kombination der SUMMENPRODUKT- und ZÄHLENWENN-Funktionen lösen können.
  • Zählen Sie die Anzahl der Zellen, die nicht zwischen zwei gegebenen Zahlen in Excel liegen
  • Die Anzahl der Zellen zwischen zwei Zahlen zu zählen, ist eine häufige Aufgabe für uns in Excel, aber in bestimmten Fällen möchten Sie möglicherweise die Zellen nicht zwischen zwei gegebenen Zahlen zählen. Zum Beispiel habe ich eine Produktliste mit den Verkäufen von Montag bis Sonntag, jetzt muss ich die Anzahl der Zellen abrufen, die nicht zwischen den spezifischen niedrigen und hohen Zahlen liegen, wie unten gezeigt. In diesem Artikel werde ich einige Formeln für den Umgang mit dieser Aufgabe in Excel vorstellen.

Die besten Tools für die Office-Produktivität

Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Beschreibung


Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).

  • Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
  • Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
  • Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
  • Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations