Wie ermittelt man die Anzahl eindeutiger Werte in einem Bereich basierend auf mehreren Kriterien in Excel?
In vielen praktischen Szenarien genügt es nicht, einfach nur Werte zu zählen – entscheidend ist vielmehr, wie viele **eindeutige** Elemente bestimmte Bedingungen in Ihren Daten erfüllen. Vielleicht möchten Sie beispielsweise wissen, wie viele verschiedene Produkte ein bestimmter Verkäufer verkauft hat oder wie viele eindeutige Bestellungen innerhalb eines bestimmten Zeitraums eingegangen sind. Um solche Aufgaben effizient in Excel zu meistern, sollten Sie sich mit leistungsstarken Formeln, erweiterten Funktionen wie PivotTable oder sogar maßgeschneiderten VBA-Lösungen vertraut machen. In diesem Artikel stellen wir Ihnen mehrere praxiserprobte Methoden vor, um die Anzahl eindeutiger Werte in einem Bereich basierend auf einem oder mehreren Kriterien zu ermitteln – jeweils mit Schritt-für-Schritt-Anleitungen und wertvollen Tipps.
Anzahl der eindeutigen Werte in einem Bereich basierend auf einem Kriterium
Anzahl der eindeutigen Werte in einem Bereich basierend auf zwei vorgegebenen Daten
Anzahl der eindeutigen Werte in einem Bereich basierend auf zwei Kriterien
Anzahl der eindeutigen Werte in einem Bereich basierend auf drei Kriterien
Anzahl der eindeutigen Werte in einem Bereich mit PivotTable (Einmalige Anzahl, Excel 2013+)
Anzahl der eindeutigen Werte in einem Bereich mit VBA-Code (für komplexe/automatisierte Fälle)
Anzahl der eindeutigen Werte in einem Bereich basierend auf einem Kriterium
Betrachten wir einen häufigen Anwendungsfall: Sie möchten herausfinden, wie viele verschiedene Produkte Tom verkauft hat. Diese Methode eignet sich ideal für einfache Datensätze, bei denen Sie die Eindeutigkeit anhand einer einzigen Bedingung – etwa der Verkaufsdaten einer einzelnen Person – ermitteln möchten. Sie ist unkompliziert in der Anwendung, erfordert jedoch eine sorgfältige Handhabung von Matrixformeln.
Geben Sie für dieses Szenario die folgende Formel in eine leere Zelle ein (z. B. Zelle G2):
=SUM(IF(„Tom"=$C$2:$C$20,1/(COUNTIFS($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 Matrixformel zu bestätigen. Daraufhin erscheinen geschweifte Klammern um die Formel in der Bearbeitungsleiste, und das Ergebnis wird sofort wie unten dargestellt angezeigt:
Hinweis:
- „Tom“ ist die Bedingung, mit der Sie Ihre Ergebnisse filtern möchten. Für mehr Flexibilität können Sie „Tom“ durch einen Bezug auf eine andere Zelle (z. B. $F$2) ersetzen.
- $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 die Anzahl der eindeutigen Werte ermitteln möchten.
- Sollte sich Ihr Datenbereich ändern, passen Sie die Bezüge einfach entsprechend an.
Tipp: Wenn Sie Excel 365 oder Excel 2019 (oder neuer) verwenden, können Sie zur Vereinfachung Ihrer Formeln die Funktionen EINDEUTIG und FILTER nutzen.
Sollten #DIV/0!-Fehler auftreten, überprüfen Sie Ihre Kriterien und stellen Sie sicher, dass alle Bereiche dieselbe Länge haben.
Anzahl der eindeutigen Werte in einem Bereich basierend auf zwei vorgegebenen Daten
Möchten Sie die Anzahl eindeutiger Elemente innerhalb eines bestimmten Datumsbereichs ermitteln – beispielsweise alle eindeutigen Produkte, die zwischen dem 01,09.2016 und dem 30,09.2016 verkauft wurden –, ist dieser Ansatz ideal. Er eignet sich besonders gut für die Analyse von Datentrends in festgelegten Zeiträumen, etwa monatlich, vierteljährlich oder nach eigenen Vorgaben. Achten Sie jedoch darauf, dass das verwendete Datumsformat exakt mit dem Ihrer Arbeitsblattdaten übereinstimmt.
Platzieren Sie die folgende Formel in einer leeren Zelle, in der das Ergebnis angezeigt werden soll:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1))),0)
Drücken Sie Strg + Umschalt + Enter, nachdem Sie die Formel eingegeben haben, um sie als Matrixformel auszuführen. Die folgende Abbildung zeigt das Ergebnis:
Hinweis:
- 2016,9,1 und 2016,9,30 sind die Start- und Enddatum-Kriterien. Sie können diese jederzeit anpassen oder sogar Zellbezüge für dynamische Datumsfilter nutzen.
- $D$2:$D$20 enthält die Datumsangaben, die überprüft werden sollen.
- $A$2:$A$20 ist erneut die Spalte mit den Artikeln oder Produkten, deren eindeutige Anzahl Sie ermitteln möchten.
- Stellen Sie sicher, dass Ihre Daten als gültige Excel-Daten und nicht als reine Textzeichenfolgen gespeichert sind. Sollte das Ergebnis nicht wie erwartet angezeigt werden, prüfen Sie die Datumsformatierung sowie die betreffenden Bereiche.
Tipp: Verwenden Sie DATUM(Jahr;Monat;Tag), um Probleme mit regionalen Datumsformaten zu vermeiden. Bei dynamischen Bereichen empfehlen wir die Verwendung benannter Bereiche für mehr Übersichtlichkeit.
Anzahl der eindeutigen Werte in einem Bereich basierend auf zwei Kriterien
Angenommen, Sie möchten ausschließlich die Produkte analysieren, die Tom im September verkauft hat, indem Sie sowohl den Namen als auch einen Datumsbereich in Ihre eindeutige Zählung einbeziehen. Ein solches Szenario kommt häufig bei periodenbasierten Leistungsbewertungen oder segmentierten Analysen vor. Mit steigender Anzahl an Kriterien wird die Formel komplexer – und die Genauigkeit der Daten gewinnt noch stärker an Bedeutung.
Geben Sie die folgende Formel in eine beliebige leere Zelle ein – zum Beispiel in H2:
=SUM(IF((„Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, „Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1))),0)
Bestätigen Sie die Formel nach der Eingabe mit Strg + Umschalt + Enter. Die eindeutige Anzahl erscheint sofort – sehen Sie in der folgenden Abbildung nach:
Hinweise:
- „Tom“ ist Ihr Namenskriterium, während „2016,9,1“ und „2016,9,30“ die Grenzen Ihres Datumsbereichs festlegen. Passen Sie diese Werte nach Bedarf an oder gestalten Sie sie dynamisch mithilfe von Zellbezügen.
- $C$2:$C$20 ist die Spalte mit dem Personal (oder einem anderen ersten Kriterium); $D$2:$D$20 enthält die zugehörigen Daten; $A$2:$A$20 listet die eindeutigen Artikel auf, die gezählt werden sollen.
- Alle Bereiche müssen dieselbe Länge aufweisen, um Fehler zu vermeiden.
Möchten Sie „ODER“-Bedingungen verwenden – beispielsweise um die eindeutigen Produkte zu zählen, die entweder von Tom verkauft wurden oder in der Region Süd –, können Sie die folgende Formel nutzen. Dadurch erweitern sich die Suchkriterien, wobei sich Ergebnisse überschneiden können, falls Daten beide Bedingungen erfüllen.
=SUMME(--(HÄUFIGKEIT(WENN((„Tom"=$C$2:$C$20)+(„South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Vergessen Sie nicht, Strg + Umschalt + Enter zu drücken. Sie sehen das Ergebnis wie unten dargestellt:
Tipp: Achten Sie bei der Anwendung von ODER-Kriterien auf mögliche Doppelzählungen, falls derselbe Datensatz beide Bedingungen erfüllt – bei großen Datensätzen kann dies die Leistung beeinträchtigen.
Anzahl der eindeutigen Werte in einem Bereich basierend auf drei Kriterien
Manchmal erfordert Ihre Analyse drei oder mehr Bedingungen, beispielsweise die Ermittlung eindeutiger Produkte, die von Tom im September ausschließlich in der Region Nord verkauft wurden. Dies ist in mehrdimensionalen Datenanalyse zur Erstellung von Berichten oder gezielten Geschäftseinblicken üblich. Eine sorgfältige Referenzverwaltung ist bei solchen zusammengesetzten Logiken unerlässlich.
Platzieren Sie diese Matrixformel in einer leeren Zelle (z. B. I2):
=SUM(IF((„Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(„North"=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, „Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Drücken Sie Strg + Umschalt + Enter, um den Vorgang abzuschließen. Hier sehen Sie ein Beispielergebnis zur Orientierung:
Achten Sie bei fortgeschrittenen Bedingungen stets darauf, dass alle Bereiche konsistent sind und die Datentypen – wie etwa Datum oder Text – korrekt angegeben wurden. Inkonsistenzen können zu Fehlern oder irreführenden Ergebnissen führen.
Tipps:
- Sollten bei großen Datensätzen Leistungsprobleme auftreten, erwägen Sie, die Formel zu vereinfachen oder stattdessen die PivotTable-Lösung von Excel zu nutzen.
- Benannte Bereiche oder Zellbezüge für alle Kriterien erhöhen die Lesbarkeit und minimieren Formelfehler.
- Für häufige Nutzung empfiehlt es sich, diese Formeln in benannten Zellbezügen oder benutzerdefinierten Funktionen zu speichern.
Anzahl der eindeutigen Werte in einem Bereich mit PivotTable (Eindeutige Anzahl, Excel 2013+)
Für Benutzer von Excel 2013 oder neuer bieten PivotTables eine interaktivere und formellose Alternative, um die Anzahl eindeutiger Werte in einem Bereich basierend auf einem oder mehreren Kriterien zu ermitteln. Die Funktion „Eindeutige Anzahl“ ermöglicht es Ihnen, große Datenmengen effizient zu aggregieren und zu filtern – ideal für dynamische, berichtsbasierte Umgebungen. Beachten Sie jedoch, dass frühere Excel-Versionen die Funktion „Eindeutige Anzahl“ in PivotTables nicht unterstützen.
So verwenden Sie diese Methode:
- Wählen Sie Ihren Datensatz aus und klicken Sie auf Einfügen > PivotTable.
- Aktivieren Sie im Dialogfeld „PivotTable erstellen“ das Kontrollkästchen „Diese Daten dem Datenmodell hinzufügen“, wählen Sie den gewünschten Speicherort für die PivotTable aus und klicken Sie anschließend auf OK.
- Ziehen Sie das Feld, dessen eindeutige Werte Sie zählen möchten (z. B. Produkt), in den Bereich „Werte“. Standardmäßig wird es als „Anzahl von …“ angezeigt.
- Klicken Sie auf das Feld im Bereich „Werte“ und wählen Sie Wert-Feld-Einstellungen.
- Wählen Sie im erscheinenden Dialogfeld weiter unten die Option Einmalige Anzahl aus. (Diese Option ist nur in Excel 2013 oder höher verfügbar und wird angezeigt, wenn die PivotTable mit der Option „Diese Daten dem Datenmodell hinzufügen“ erstellt wurde.)
- Fügen Sie Ihre Kriterienfelder (z. B. Verkäufer, Region, Datum) dem Filter- oder Zeilen-/Spaltenbereich hinzu, um einfache oder mehrere Bedingungen anzuwenden.
- Ihre PivotTable zeigt nun die eindeutige Anzahl der Werte an – gefiltert exakt nach Ihren gewählten Kriterien.
Vorteile: Sehr anschaulich, Filter lassen sich leicht anpassen, ohne Formeln bearbeiten zu müssen, und eignet sich hervorragend für interaktive Berichterstattung.
Einschränkungen: Nicht verfügbar in Excel 2010 oder früher; beim Hinzufügen neuer Daten muss die PivotTable manuell aktualisiert werden.
Praktischer Tipp: Stellen Sie stets sicher, dass die Quelldaten innerhalb desselben Datensatzes keine unbeabsichtigten Duplikate enthalten. Falls die Option „Eindeutige Anzahl“ fehlt, erstellen Sie die PivotTable neu und aktivieren Sie die Option „Diese Daten dem Datenmodell hinzufügen“.
Anzahl der eindeutigen Werte in einem Bereich mit VBA-Code (für komplexe/automatisierte Fälle)
Gelegentlich müssen Sie die Anzahl der eindeutigen Werte in einem Bereich automatisch anhand verschiedener Kriterien ermitteln – besonders bei sehr großen Datensätzen oder häufig wiederkehrenden Analysen. Ein VBA-Makro ist für solche Szenarien ideal, da es nach der Einrichtung unterschiedliche Logiken, einschließlich Mehrfachbedingungsfilterung, ohne manuelles Eingreifen schnell verarbeiten kann. Allerdings ist VBA anspruchsvoller als herkömmliche Excel-Funktionen und daher am besten für Benutzer geeignet, die bereits mit Makros vertraut sind oder einen kontinuierlichen Analysebedarf haben.
Vorgehensweise:
- 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 Sie den folgenden VBA-Code in das Modul:
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 aus und führen Sie das Makro aus.
- Folgen Sie den Anweisungen, um die Bereiche und Kriterien gemäß Ihren Daten festzulegen. Das Ergebnis wird sowohl in der von Ihnen ausgewählten Zelle als auch in einem Meldungsfenster angezeigt.
Parametererklärung und Hinweise:
- Dieses Makro ist derzeit für ein einzelnes Kriterium eingerichtet. Um es für mehrere Kriterien zu erweitern, passen Sie die
If ... Then-Logik innerhalb der Schleife an. - Speichern Sie Ihre Arbeitsmappe immer vor dem Ausführen von Makros – denn vorgenommene Änderungen lassen sich nicht rückgängig machen.
- Aktivieren Sie Makros in Ihren Excel-Einstellungen, falls Ausführungsfehler auftreten.
- Diese Methode eignet sich besonders gut für größere oder häufig aktualisierte Daten, bei denen manuelle Formeln unpraktisch wären.
Vorteile: Sehr anpassbar und automatisierbar – verarbeitet große und sich ständig ändernde Datensätze effizient. Ideal für fortgeschrittene oder wiederkehrende Arbeitsabläufe.
Nachteile: Erfordert Makroberechtigungen; Einsteiger benötigen möglicherweise etwas Zeit, um sich mit VBA-Vorgängen vertraut zu machen.
Wenn Sie eindeutige Werte anhand von Kriterien zählen, überprüfen Sie stets Ihre Bereichsbezüge und stellen Sie sicher, dass alle Kriterienspalten exakt denselben Umfang haben. Nicht übereinstimmende Bereiche sind eine häufige Fehlerquelle und führen oft zu falschen Ergebnissen. Sollten Formeln unerwartete Resultate liefern, prüfen Sie auf versteckte Formatierungsprobleme oder leere Zellen. Für leistungskritische Szenarien bieten PivotTables und VBA robuste Alternativen zu Matrixformeln. Wählen Sie die Lösung, die am besten zu Ihrem Kenntnisstand und der Komplexität Ihres Datensatzes passt. Denken Sie daran: Kutools für Excel liefert zusätzliche Werkzeuge und Verknüpfungen, die viele dieser Aufgaben in komplexen Arbeitsmappen noch effizienter machen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins