Wie zählt man eindeutige Werte basierend auf mehreren Kriterien in Excel?
In vielen praktischen Szenarien ist es oft nicht nur notwendig, Werte zu zählen, sondern auch festzustellen, wie viele eindeutige Elemente bestimmte Bedingungen in Ihren Daten erfüllen. Zum Beispiel möchten Sie vielleicht herausfinden, wie viele verschiedene Produkte ein bestimmter Verkäufer verkauft hat oder wie viele einzigartige Bestellungen innerhalb eines bestimmten Zeitraums aufgegeben wurden. Um solche Aufgaben effizient in Excel zu bewältigen, müssen Sie sich mit geeigneten Formeln, erweiterten Funktionen wie Pivot-Tabellen oder sogar benutzerdefinierten VBA-Lösungen vertraut machen. In diesem Artikel werden wir mehrere praktische Methoden untersuchen, um eindeutige Werte basierend auf einem oder mehreren Kriterien zu zählen, mit Schritt-für-Schritt-Anleitungen und Tipps.
Einzigartige Werte basierend auf einem Kriterium zählen
Einzigartige Werte basierend auf zwei gegebenen Daten zählen
Einzigartige Werte basierend auf zwei Kriterien zählen
Einzigartige Werte basierend auf drei Kriterien zählen
Einzigartige Werte mit Pivot-Tabelle zählen (Distinct Count, Excel 2013+)
Einzigartige Werte mit VBA-Code zählen (für komplexe/automatisierte Fälle)
Einzigartige Werte basierend auf einem Kriterium zählen
Betrachten wir einen häufigen Fall: Sie möchten zählen, wie viele verschiedene Produkte von Tom verkauft wurden. Diese Methode ist geeignet, wenn Sie über einen einfachen Datensatz verfügen und die Einzigartigkeit anhand einer einzigen Bedingung, wie den Verkaufsdaten einer einzelnen Person, bewerten möchten. Es ist unkompliziert, erfordert aber eine sorgfältige Verwendung von Array-Formeln.
Für dieses Szenario geben Sie die folgende Formel in eine leere Zelle ein (z. B. Zelle G2):
=SUMME(WENN("Tom"=$C$2:$C$20;1/(ZÄHLENWENNS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Nachdem Sie die Formel eingegeben haben, drücken Sie Strg + Umschalt + Enter (nicht nur Enter), um sie als Array-Formel zu bestätigen. Die geschweiften Klammern werden um die Formel in der Formelleiste erscheinen, und Sie sehen das Ergebnis sofort, wie unten gezeigt:
Hinweis:
- „Tom“ ist das Kriterium, das Sie verwenden möchten, um die Ergebnisse zu filtern. Sie können „Tom“ durch einen Verweis auf eine andere Zelle ersetzen (z. B. $F$2), wenn Sie mehr Flexibilität wünschen.
- $C$2:$C$20 enthält die Namen der Verkäufer, die ausgewertet werden sollen.
- $A$2:$A$20 ist die Produktspalte, für die Sie eindeutige Zählungen wünschen.
- Wenn sich Ihr Datenbereich ändert, passen Sie die Referenzen entsprechend an.
Tipp: Wenn Sie Excel 365 oder Excel 2019 und höher verwenden, können Sie die Funktionen UNIQUE und FILTER für einfachere Formeln ausprobieren.
Wenn Sie auf Fehler #DIV/0! stoßen, überprüfen Sie die Kriterien erneut und stellen Sie sicher, dass Ihre Bereiche gleich lang sind.
Einzigartige Werte basierend auf zwei gegebenen Daten zählen
Wenn Sie die Anzahl der eindeutigen Elemente innerhalb eines bestimmten Datumsbereichs ermitteln müssen, zum Beispiel alle einzigartigen Produkte, die zwischen dem 01.09.2016 und dem 30.09.2016 verkauft wurden, können Sie diesen Ansatz anwenden. Dies ist besonders nützlich, wenn Sie Daten Trends zwischen bestimmten Zeiträumen analysieren, wie monatlich, quartalsweise oder benutzerdefinierte Datumsbereiche. Seien Sie jedoch vorsichtig bei der Datumsformatierung; sie muss mit den Datumswerten in Ihrem Arbeitsblatt übereinstimmen.
Platzieren Sie die folgende Formel in eine leere Zelle, wo Sie das Ergebnis anzeigen möchten:
=SUMME(WENN($D$2:$D$20<=DATUM(2016;9;30)*($D$2:$D$20>=DATUM(2016;9;1));1/ZÄHLENWENNS( $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016;9;30);$D$2:$D$20; ">="&DATUM(2016;9;1)));0)
Drücken Sie nach Eingabe der Formel Strg + Umschalt + Enter, um sie als Array-Formel auszuführen. Der folgende Screenshot zeigt das Ergebnis:
Hinweis:
- 2016,9,1 und 2016,9,30 sind die Start- und Enddatum-Kriterien. Sie können diese bei Bedarf ändern oder sogar Zellbezüge für dynamische Datumsfilter verwenden.
- $D$2:$D$20 enthält die zu überprüfenden Datumsangaben.
- $A$2:$A$20 ist wieder die Artikel- oder Produktspalte, die Sie eindeutig zählen möchten.
- Stellen Sie sicher, dass Ihre Daten als gültige Excel-Daten und nicht als Text gespeichert sind. Wenn Ihr Ergebnis nicht wie erwartet erscheint, überprüfen Sie Ihre Datumsformatierung und Bereiche.
Tipp: Verwenden Sie DATUM(Jahr, Monat, Tag), um Probleme mit regionalen Datumsformaten zu vermeiden. Bei der Verwendung dynamischer Bereiche sollten Sie benannte Bereiche für mehr Klarheit in Betracht ziehen.
Einzigartige Werte basierend auf zwei Kriterien zählen
Angenommen, Sie möchten nur die Produkte analysieren, die Tom im September verkauft hat, indem Sie Name und einen Datumsbereich in Ihre eindeutige Zählung integrieren. Dieses Szenario ist üblich für zeitraumbezogene Leistungsüberprüfungen oder segmentierte Analysen. Wenn sich Ihre Kriterien erweitern, wird die Formel komplexer, und die Genauigkeit der Daten wird noch wichtiger.
Geben Sie die folgende Formel in eine beliebige leere Zelle, z. B. H2, ein:
=SUMME(WENN(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATUM(2016;9;30))*($D$2:$D$20>=DATUM(2016;9;1));1/ZÄHLENWENNS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016;9;30);$D$2:$D$20; ">="&DATUM(2016;9;1)));0)
Bestätigen Sie die Formel nach der Eingabe mit Strg + Umschalt + Enter. Sie sollten die eindeutige Zählung sofort sehen; überprüfen Sie die folgende Abbildung:
Hinweise:
- „Tom“ ist das Namenskriterium, während „2016,9,1“ und „2016,9,30“ die Grenzen Ihres Datumsbereichs sind. Passen Sie diese bei Bedarf an oder machen Sie sie dynamisch mit Zellbezügen.
- $C$2:$C$20 ist die Mitarbeiter- (oder ein anderes erstes Kriterium) Spalte; $D$2:$D$20 ist die Datums-Spalte; $A$2:$A$20 enthält die eindeutigen zu zählenden Elemente.
- Die Bereiche müssen alle gleich lang sein, um Fehler zu vermeiden.
Wenn Sie „oder“-Bedingungen verwenden möchten, wie z. B. eindeutige Produkte, die von Tom verkauft wurden oder in der Südregion, können Sie die folgende Formel verwenden. Dies ermöglicht breitere Suchbedingungen, obwohl sich Ergebnisse überschneiden können, wenn Daten beiden Kriterien entsprechen:
=SUMME(--(HÄUFIGKEIT(WENN(("Tom"=$C$2:$C$20)+("Süd"=$B$2:$B$20); ZÄHLENWENN($A$2:$A$20; "<"&$A$2:$A$20); ""); ZÄHLENWENN($A$2:$A$20; "<"&$A$2:$A$20))>0))
Vergessen Sie nicht, Strg + Umschalt + Enter zu drücken. Sie werden die Ergebnisse wie unten gezeigt sehen:
Tipp: Wenn Sie OR-Bedingungen anwenden, achten Sie auf mögliche Doppelzählungen, wenn derselbe Datensatz beide Bedingungen erfüllt. Bei großen Datensätzen kann die Leistung beeinträchtigt werden.
Einzigartige Werte basierend auf drei Kriterien zählen
Manchmal erfordert Ihre Analyse möglicherweise drei oder mehr Bedingungen, wie zum Beispiel die Bestimmung eindeutiger Produkte, die von Tom im September nur in der Nordregion verkauft wurden. Dies ist üblich bei der mehrdimensionalen Datenanalyse für Berichte oder gezielte Geschäftseinblicke. Eine sorgfältige Verwaltung der Bezüge ist bei der Handhabung solcher zusammengesetzter Logik unerlässlich.
Platzieren Sie diese Array-Formel in eine leere Zelle (z. B. I2):
=SUMME(WENN(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATUM(2016;9;30))*($D$2:$D$20>=DATUM(2016;9;1))*("Nord"=$B$2:$B$20);1/ZÄHLENWENNS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATUM(2016;9;30); $D$2:$D$20; ">="&DATUM(2016;9;1); $B$2:$B$20; "Nord"));0)
Drücken Sie Strg + Umschalt + Enter, um abzuschließen. Hier ist ein Beispielergebnis zur Referenz:
Für fortgeschrittene Bedingungen überprüfen Sie, dass alle Bereiche konsistent sind und dass die Datentypen (z. B. Datum und Text) korrekt sind. Fehlausrichtungen können Fehler oder irreführende Ergebnisse verursachen.
Tipps:
- Wenn Sie auf großen Datensätzen Leistungsprobleme feststellen, sollten Sie die Formel aufteilen oder die PivotTable-Lösung von Excel verwenden.
- Benannte Bereiche oder Zellbezüge für alle Kriterien verbessern die Lesbarkeit und reduzieren Formelfehler.
- Für häufige Nutzung sollten Sie diese Formeln in benannten Zellbezügen oder benutzerdefinierten Funktionen speichern.
Einzigartige Werte mit Pivot-Tabelle zählen (Distinct Count, Excel 2013+)
Für Benutzer von Excel 2013 oder neuer bieten Pivot-Tabellen eine interaktivere, formelfreie Alternative zum Zählen eindeutiger Werte über ein oder mehrere Kriterien. Die Distinct Count-Funktion hilft Ihnen, große Datensätze effizient zusammenzufassen und zu filtern, was diese Methode besonders für dynamische, berichtsbasierte Umgebungen geeignet macht. Beachten Sie jedoch, dass frühere Versionen von Excel die Distinct Count-Funktion innerhalb von Pivot-Tabellen nicht unterstützen.
So verwenden Sie diese Methode:
- Wählen Sie Ihr Dataset aus und gehen Sie zu Einfügen > PivotTable.
- Aktivieren Sie im Dialogfeld „PivotTable erstellen“ das Kontrollkästchen „Diese Daten dem Datenmodell hinzufügen“, wählen Sie den Ort für die Pivot-Tabelle aus und klicken Sie auf OK.
- Ziehen Sie das Feld, das Sie eindeutig zählen möchten (z. B. Produkt) in den Werte-Bereich. Standardmäßig wird es als „Anzahl von...“ angezeigt.
- Klicken Sie auf das Feld im Werte-Bereich und wählen Sie „Wertfeldeinstellungen“.
- Scrollen Sie im Pop-up-Dialogfeld nach unten und wählen Sie „Distinct Count“ (Diese Option ist nur in Excel 2013 oder höher verfügbar und erscheint, wenn die Pivot-Tabelle mit der Option „Diese Daten dem Datenmodell hinzufügen“ erstellt wurde).
- Fügen Sie Ihre Kriterienfelder (z. B. Verkäufer, Region, Datum) in den Filter- oder Zeilen-/Spaltenbereich ein, um einzelne oder mehrere Bedingungen anzuwenden.
- Ihre Pivot-Tabelle zeigt nun die eindeutige Anzahl der Werte gefiltert nach Ihren gewählten Kriterien an.
Vorteile: Sehr visuell, einfaches Anpassen von Filtern ohne Bearbeitung von Formeln und geeignet für interaktive Berichterstattung.
Einschränkungen: Nicht verfügbar in Excel 2010 oder früher; das Hinzufügen neuer Daten erfordert das manuelle Aktualisieren der Pivot-Tabelle.
Praktischer Tipp: Stellen Sie immer sicher, dass die Quelldaten keine Duplikate innerhalb desselben Datensatzes enthalten, sofern dies nicht beabsichtigt ist. Wenn die Option „Distinct Count“ fehlt, erstellen Sie die Pivot-Tabelle neu und aktivieren Sie das Kontrollkästchen „Diese Daten dem Datenmodell hinzufügen“.
Einzigartige Werte mit VBA-Code zählen (für komplexe/automatisierte Fälle)
Manchmal müssen Sie möglicherweise automatisch eindeutige Werte basierend auf verschiedenen Kriterien zählen, insbesondere bei der Bearbeitung sehr großer Datensätze oder bei häufig wiederholten Analysen. Ein VBA-Makro ist ideal für solche Situationen, da es unterschiedliche Logiken – einschließlich Mehrfachfiltern – schnell verarbeiten kann, ohne manuelle Eingriffe nach der Einrichtung. VBA ist jedoch fortgeschrittener als reguläre Excel-Funktionen, daher sollte es am besten von Benutzern verwendet werden, die mit Makros vertraut sind oder laufende Analyseanforderungen haben.
Schritte zur Durchführung:
- Drücken Sie Alt + F11, um den VBA-Editor zu öffnen. Wählen Sie im Editor Einfügen > Modul, um ein neues Modul zu erstellen.
- Kopieren und fügen Sie den folgenden VBA-Code in das Modul ein:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Schließen Sie den VBA-Editor und kehren Sie zu Ihrem Arbeitsblatt zurück. Drücken Sie Alt + F8, wählen Sie CountUniqueWithCriteria und führen Sie das Makro aus.
- Folgen Sie den Eingabeaufforderungen, um die Bereiche und Kriterien gemäß Ihren Daten anzugeben. Das Ergebnis wird in der von Ihnen gewählten Zelle erscheinen und auch als Meldungsfenster.
Parametererklärung und Hinweise:
- Dieses Makro ist derzeit für ein Kriterium eingerichtet. Um es für mehrere Kriterien zu erweitern, ändern Sie die
If ... Then-Logik innerhalb der Schleife. - Speichern Sie Ihre Arbeitsmappe immer vor dem Ausführen von Makros, da Änderungen nicht rückgängig gemacht werden können.
- Aktivieren Sie Makros in Ihren Excel-Einstellungen, wenn Sie auf Ausführungsfehler stoßen.
- Diese Methode funktioniert gut für größere oder häufig aktualisierte Daten, bei denen manuelle Formeln umständlich wären.
Vorteile: Sehr anpassbar und automatisierbar, verarbeitet große und sich ändernde Datensätze effizient. Geeignet für fortgeschrittene oder wiederholte Workflow-Anforderungen.
Nachteile: Erfordert Makro-Berechtigungen und Anfänger benötigen möglicherweise Zeit, um sich mit VBA-Operationen vertraut zu machen.
Beim Arbeiten mit eindeutigen Wertezählungen basierend auf Kriterien bestätigen Sie immer Ihre Bereichsbezüge und stellen Sie sicher, dass alle Kriterienspalten in der Größe übereinstimmen. Nicht übereinstimmende Bereiche sind eine häufige Fehlerquelle oder führen zu falschen Ergebnissen. Wenn Formeln unerwartete Ergebnisse liefern, überprüfen Sie auf versteckte Formatierungsprobleme oder leere Zellen. Für leistungsentscheidende Szenarien bieten Pivot-Tabellen und VBA robuste Alternativen zu Array-Formeln. Wählen Sie die Lösung, die am besten zu Ihrem Komfortlevel und der Komplexität Ihres Datensatzes passt. Denken Sie daran, dass Kutools für Excel zusätzliche Dienstprogramme und Abkürzungen bietet, die viele dieser Aufgaben für noch größere Effizienz in komplexen Arbeitsmappen optimieren können.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins