Zählen Sie eindeutige numerische Werte basierend auf Kriterien in Excel
Im Excel-Arbeitsblatt kann ein Problem auftreten, bei dem die Anzahl eindeutiger numerischer Werte basierend auf einer bestimmten Bedingung gezählt wird. Wie kann ich beispielsweise die eindeutigen Qty-Werte des Produkts „T-Shirt“ aus dem Bericht zählen, wie unten im Screenshot gezeigt? In diesem Artikel zeige ich einige Formeln, um diese Aufgabe in Excel zu lösen.
- Zählen Sie eindeutige numerische Werte basierend auf Kriterien in Excel 2019, 2016 und früher
- Zählen Sie eindeutige numerische Werte basierend auf Kriterien in Excel 365
Zählen Sie eindeutige numerische Werte basierend auf Kriterien in Excel 2019, 2016 und früher
In Excel 2019 und früheren Versionen können Sie die Funktionen SUMME, FREQUENZ und WENN kombinieren, um eine Formel zum Zählen eindeutiger Werte basierend auf Kriterien zu erstellen. Die generische Syntax lautet:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Der Zellbereich, der den angegebenen Kriterien entspricht;
- criteria: Die Bedingung, auf deren Grundlage Sie eindeutige Werte zählen möchten;
- range: Der Bereich der Zellen mit eindeutigen zu zählenden Werten.
Bitte wenden Sie die folgende Formel in eine leere Zelle an und drücken Sie Strg + Umschalt + Enter Tasten, um das richtige Ergebnis zu erhalten, siehe Screenshot:
Erklärung der Formel:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Diese IF-Funktion gibt den Wert in Spalte C zurück, wenn das Produkt in Spalte A „T-Shirt“ ist, das Ergebnis ist ein Array wie folgt: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE; FALSCH;350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Die FREQUENCE-Funktion wird verwendet, um jeden der numerischen Werte in der Array-Liste zu zählen und das Ergebnis wie folgt zurückzugeben: {0;2;1;1;1;0;0;0;0;0;0;0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Testen Sie, ob jeder Wert im Array größer als 0 ist, und erhalten Sie das Ergebnis wie folgt: {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. Und dann wandelt das doppelte negative Vorzeichen die WAHR und FALSCH in 1 und 0 um und gibt ein Array wie folgt zurück: {0;1;1;1;1;0;0;0;0;0;0;0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Verwenden Sie schließlich die SUM-Funktion, um diese Werte zu addieren und die Gesamtzahl zu erhalten: 4.
Tipps:
Wenn Sie die eindeutigen Werte basierend auf mehr als einer Bedingung zählen möchten, müssen Sie der Formel nur weitere Kriterien mit dem Zeichen * hinzufügen:
Zählen Sie eindeutige numerische Werte basierend auf Kriterien in Excel 365
In Excel 365 kann die Kombination der Funktionen ROWS, UNIQUE und FILTER helfen, eindeutige numerische Werte basierend auf Kriterien zu zählen. Die generische Syntax lautet:
- range: Der Bereich der Zellen mit eindeutigen zu zählenden Werten.
- criteria_range: Der Zellbereich, der den angegebenen Kriterien entspricht;
- criteria: Die Bedingung, auf deren Grundlage Sie eindeutige Werte zählen möchten;
Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine Zelle ein und drücken Sie Enter Taste, um das Ergebnis zurückzugeben, siehe Screenshot:
Erklärung der Formel:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2: A12=E2: Dieser Ausdruck prüft, ob der Wert in Zelle E2 im Bereich A2:A12 liegt, und erhält dieses Ergebnis: {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}.
- FILTER(C2:C12,A2:A12=E2): Die FREQUENCE-Funktion wird verwendet, um jeden der numerischen Werte in der Array-Liste zu zählen und das Ergebnis wie folgt zurückzugeben: {0;2;1;1;1;0;0;0;0;0;0;0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Hier wird die UNIQUE-Funktion verwendet, um eindeutige Werte aus dem Listen-Array zu extrahieren, um dieses Ergebnis zu erhalten: {300;500;400;350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Die ROWS-Funktion gibt die Anzahl der Zeilen basierend auf einem Zellbereich oder Array zurück, das Ergebnis ist also: 4.
Tipps:
1. Wenn der übereinstimmende Wert nicht im Datenbereich vorhanden ist, erhalten Sie einen Fehlerwert. Um den Fehlerwert durch 0 zu ersetzen, wenden Sie bitte die folgende Formel an:
2. Um die eindeutigen Werte basierend auf mehr als einer Bedingung zu zählen, müssen Sie der Formel nur andere Kriterien mit dem Zeichen * hinzufügen:
Verwendete relative Funktion:
- SUMME:
- Die Excel-SUM-Funktion gibt die Summe der gelieferten Werte zurück.
- FREQUENZ:
- Die Funktion FREQUENCY berechnet, wie oft Werte innerhalb eines Wertebereichs vorkommen, und gibt dann ein vertikales Zahlenarray zurück.
- REIHEN:
- Die Funktion ROWS gibt die Anzahl der Zeilen in einer gegebenen Referenz oder einem Array zurück.
- EINZIGARTIG:
- Die Funktion UNIQUE gibt eine Liste eindeutiger Werte in einer Liste oder einem Bereich zurück.
- FILTER:
- Die FILTER-Funktion hilft beim Filtern einer Reihe von Daten basierend auf von Ihnen definierten Kriterien.
Weitere Artikel:
- Zählen Sie eindeutige numerische Werte oder Datumsangaben in einer Spalte
- Angenommen, Sie haben eine Liste von Zahlen, die einige Duplikate enthalten, möchten nun die Anzahl der eindeutigen Werte zählen oder die Werte erscheinen nur einmal in der Liste, wie unten im Screenshot gezeigt. In diesem Artikel werden wir über einige nützliche Formeln sprechen, um diese Aufgabe in Excel schnell und einfach zu lösen.
- Alle Übereinstimmungen / Duplikate zwischen zwei Spalten zählen
- Das Vergleichen von zwei Datenspalten und das Zählen aller Übereinstimmungen oder Duplikate in den beiden Spalten kann für die meisten von uns eine häufige Aufgabe sein. Zum Beispiel haben Sie zwei Namensspalten, einige Namen erscheinen sowohl in der ersten als auch in der zweiten Spalte. Jetzt möchten Sie alle übereinstimmenden Namen (die Übereinstimmungen, die sich irgendwo innerhalb der beiden Spalten befinden) zwischen zwei Spalten zählen, wie im folgenden Screenshot gezeigt, dieses Tutorial werden einige Formeln zum Erreichen dieses Ziels in Excel vorstellen.
- Zählen Sie die Anzahl der Zellen gleich einem von vielen Werten
- 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.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
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.
