So zählst du eindeutige Werte in Excel: Was du wissen musst

Wenn du mit Daten arbeitest, ist das Zählen eindeutiger Werte ein Muss, denn saubere Daten liefern bessere Erkenntnisse. In dieser Anleitung zeige ich dir, wie du eindeutige Werte in Excel zählst. Ich zeige dir auch Sonderfälle, z. B. wie du die Groß- und Kleinschreibung behandelst oder wie du eindeutige Werte basierend auf einer oder mehreren Bedingungen zählst.

Da die Begriffe manchmal durcheinander gebracht werden, nehme ich mir etwas Zeit, um dich über den Unterschied zwischen eindeutigen und unterscheidbaren Werten in Excel aufzuklären, und falls du stattdessen eine eindeutige Zählung durchführen möchtest, zeige ich dir am Ende dieses Artikels, wie das geht. 

Bevor wir loslegen, möchte ich darauf hinweisen, dass dies nur einige der Themen sind, die in unserem Lernpfad Excel-Grundlagen behandelt werden.

Ein Hinweis zu einzigartigen Werten vs. Eindeutige Werte in Excel

Manchmal werden die Begriffe eindeutige Werte und eindeutige Werte austauschbar verwendet, aber sie sind nicht dasselbe. Hier ist also ein kurzer Vergleich, damit du den Unterschied verstehst.

Unterscheidbare Werte sind die verschiedenen Werte in einem Datensatz, wobei Duplikate entfernt werden. In der Liste A, A, B, C, C, D sind die unterschiedlichen Werte zum BeispielA, B, C, D.

Eindeutige Werte sind dagegen Werte, die nur einmal im Datensatz vorkommen. Im gleichen Beispiel sind die eindeutigen Werte B und D (weil A und C mehr als einmal vorkommen).

In der ersten Hälfte des Artikels werde ich mich auf verschiedene Möglichkeiten konzentrieren, eindeutige Werte in Excel zu zählen. Wenn du aber zufällig hier gelandet bist und wirklich bestimmte Werte zählen willst, habe ich auch dafür eine Lösung, wenn du zu einem späteren Abschnitt scrollen möchtest.

Methoden zum Zählen eindeutiger Werte in Excel

Es gibt zwei gängige Methoden, um eindeutige Werte in Excel zu zählen.

Methode 1: Verwende die Funktion UNIQUE() mit dem richtigen Parameter

Der einfachste Weg, eindeutige Werte zu zählen, ist die Funktion UNIQUE() und die Funktion COUNTA(). Die Funktion UNIQUE() extrahiert alle eindeutigen Werte aus einem Bereich, und COUNTA() zählt, wie viele eindeutige Werte vorhanden sind. 

COUNTA(UNIQUE(range, false, true))

Ich habe zum Beispiel die folgende Formel auf einen kleinen Datensatz angewendet, und sie hat mir eindeutige Werte geliefert.

=COUNTA(UNIQUE(A2:A8, false, true))

Zähle eindeutige Werte mit den Funktionen UNIQUE() und COUNTA(). Bild vom Autor.

Wenn du stattdessen diese Syntax =COUNTA(UNIQUE(A2:A8)) verwenden würdest, die nicht den dritten Parameter hat, den wir als TRUE gesetzt haben, dann würden wir als Ergebnis eine eindeutige Zählung zurückbekommen, nicht eine eindeutige. Wenn du im dritten Argument TRUE angibst, gibt die Funktion eine eindeutige Anzahl zurück. Beide Dinge sind in der Funktion UNIQUE() enthalten, daher kann es am Anfang etwas verwirrend sein.

Methode 2: SUMME() mit IF() und COUNTIF() verwenden

Wenn du dich mit den Feinheiten der Funktion UNIQUE() nicht auskennst oder nicht über Excel 365 verfügst, kannst du SUM() auch mit IF() und COUNTIF() kombinieren, um die eindeutigen Werte zu zählen.

=SUM(IF(COUNTIF(range, range)=1,1,0))

Wenn ich zum Beispiel Daten im Bereich A2:A8 habe und die eindeutigen Werte zählen möchte, schreibe ich die folgende Formel:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))

Hier geht COUNTIF() die Liste durch und überprüft, wie oft jeder Name vorkommt. Wenn ein Name nur einmal auftaucht, wird er als 1 gezählt. Wenn es mehr als einmal vorkommt, bekommt es eine höhere Zahl. Dann filtert IF() diese Ergebnisse so, dass die 1en erhalten bleiben, aber alles andere zu 0 wird. Zum Schluss addiert SUM() alle 1en und gibt uns die Gesamtzahl der eindeutigen Werte.

Zähle eindeutige Werte mit SUM(), IF() und COUNTIF(). Bild vom Autor.

Zählen eindeutiger Text- und Zahlenwerte

Manchmal enthält unser Datensatz gemischte Datentypen und wir müssen sie unabhängig voneinander analysieren. Das mag auf den ersten Blick etwas abschreckend wirken, aber es ist in Excel möglich. Schauen wir uns zwei Methoden an - eine für das Zählen eindeutiger Textwerte und eine für eindeutige Zahlen.

Eindeutige Textwerte zählen

Wenn du eindeutige Textwerte zählen willst, kombinierst du die Funktionen ISTEXT(), COUNTIF() und SUM(). ISTEXT() prüft, ob ein Wert ein Text ist, während COUNTIF() zählt, wie oft jeder Wert vorkommt, und SUM() summiert die eindeutigen Texteinträge. 

Ich habe die folgende Formel auf eine Beispieldatei angewandt, und sie hat mir sofort gezeigt, wie viele eindeutige Textwerte vorhanden sind.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Zähle eindeutige Textwerte. Bild vom Autor.

Eindeutige numerische Werte zählen

Wenn du eindeutige Zahlen anstelle von Text zählen willst, verwende dieselbe Formel, aber ersetze ISTEXT() durch ISNUMBER(). ISNUMBER() berücksichtigt nur numerische Werte, während COUNTIF() und SUM() die Eindeutigkeit behandeln. Ich habe die obige Formel für dieselben Daten optimiert und sie zeigt mir jetzt nur noch die Anzahl der eindeutigen numerischen Werte an:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Zähle eindeutige numerische Werte. Bild vom Autor.

Zählen eindeutiger Werte mit Bedingungen und Kriterien

Nachdem wir nun die grundlegenden Methoden zum Zählen von eindeutigen Werten behandelt haben, wollen wir nun einige fortgeschrittene Techniken erkunden.

Zähle eindeutige Werte basierend auf Bedingungen

Um die eindeutigen Werte zu zählen, die auf einer bestimmten Bedingung basieren, verwende ich diese Syntax:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

In dieser Formel filtert FILTER() den Bereich anhand bestimmter Kriterien heraus, UNIQUE() entfernt Duplikate, ROWS() zählt die Ergebnisse und IFERROR() verhindert Fehler, indem es "Nicht gefunden" zurückgibt, wenn keine Treffer gefunden werden.

Nehmen wir an, ich habe eine Liste von Mitarbeitern und ihren Abteilungen und muss zählen, wie viele Mitarbeiter in einer bestimmten Abteilung arbeiten. Ich werde die folgende Formel verwenden:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

Hier steht A2:A10 für den Bereich der Mitarbeiternamen, den ich filtern möchte, und B2:B10=F1 prüft, welche Mitarbeiter zu der Abteilung gehören, die in F1 steht.

Zähle eindeutige Werte mit einer Bedingung. Bild vom Autor.

Zähle eindeutige Zeilen basierend auf mehreren Spalten

Wenn ich die einzelnen Mitarbeiter in einer bestimmten Abteilung zählen möchte, deren Gehalt unter 50.000 USD liegt, verwende ich diese Formel:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

Hier steht A2:A10 für den Bereich der Mitarbeiternamen, B2:B10=F1 prüft, welche Mitarbeiter zu der in F1 angegebenen Abteilung gehören, und C2:C10>F2 prüft, ob das Gehalt unter dem in F2 angegebenen Betrag liegt.

Zähle eindeutige Werte mit mehreren Kriterien. Bild vom Autor.

Groß- und Kleinschreibung wird berücksichtigt

In der Standardeinstellung unterscheidet Excel zwischen Groß- und Kleinschreibung. Zum Beispiel werden Apple und APPLE als unterschiedlich angesehen. Um solche Fälle zu erkennen, erstelle ich eine Hilfsspalte mit der folgenden Formel:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

Um die Anzahl der eindeutigen Werte zu ermitteln, verwende ich die Funktion COUNTIF() wie folgt:

=COUNTIF(B2:B11,"Unique")

Zähle eindeutige Werte unter Berücksichtigung der Groß- und Kleinschreibung. Bild vom Autor.

Best Practices für das Zählen eindeutiger Werte in Excel

Hier sind ein paar meiner Lieblingsmethoden, die dir helfen, alles genau und effizient zu halten:

• Achte darauf, dass die Daten keine zusätzlichen Leerzeichen oder inkonsistente Formatierungen enthalten. Andernfalls wird ein Fehler ausgegeben.
• Dein Datenbereich sollte keine leeren Zellen enthalten, da sie das Ergebnis verfälschen können.
• Wenn sich deine Daten häufig ändern, verwende PivotTables oder dynamische Array-Funktionen, damit alles in Echtzeit aktualisiert wird. 

Wie man stattdessen eindeutige Werte zählt

Als letzten Abschnitt, für den Fall, dass du wirklich zählen wolltest unterschiedliche Werte zählen willst, zeige ich dir verschiedene Methoden, um eindeutige Werte zu zählen.

Methode 1: Verwende die Funktionen COUNTIF() und SUM()

Um unterschiedliche Werte in Excel zu zählen, kannst du die Funktionen COUNTIF() und SUM() kombinieren. Die Funktion COUNTIF() prüft, wie oft jeder Wert in einem bestimmten Bereich vorkommt. Dann addiert SUM() die von COUNTIF() zurückgegebenen Werte, um die Gesamtzahl der eindeutigen Einträge zu ermitteln. 

=SUM(1/COUNTIF(range, range))

Nehmen wir an, ich möchte die unterschiedlichen Werte im Bereich A2:A8 finden. Ich gebe die folgende Formel ein:

=SUM(1/COUNTIF(A2:A8, A2:A8))

Hier prüft die Funktion COUNTIF(), wie oft jeder Wert in einer Liste vorkommt.

1/COUNTIF(A2:A8, A2:A8) dividiert 1 durch die Anzahl, wie oft jeder Wert vorkommt. Wenn eine Zahl zum Beispiel zweimal vorkommt, wird jede Instanz zu 0,5 (1/2). Wenn es dreimal erscheint, ist es 0,33 (1/3). 

Da wir diese Brüche nicht wollen, können wir die SUM() oder SUMPRODUCT() benutzen, um alle Werte zu addieren. Die Brüche aller Duplikate ergeben zusammen 1, und unterschiedliche Werte bleiben 1, da 1/1 = 1 ist:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))

Zähle unterschiedliche Werte mit den Funktionen COUNTIF() und SUM(). Bild vom Autor.

Methode 2: PivotTables für eindeutige Zählungen verwenden

Excel 2013 und Excel 2016 haben eine eingebaute Funktion zum Zählen unterschiedlicher Werte in einer PivotTable. Um dies zu nutzen, musst du zunächst eine PivotTable erstellen, indem du Daten auswählst: Gehe zu Einfügen und wähle PivotTable. Es erscheint ein Dialogfeld, in dem du " Vorhandenes Arbeitsblatt " wählst und das Feld " Diese Daten zum Datenmodell hinzufügen " markierst.

Erstelle eine PivotTable. Bild vom Autor.

Als Nächstes ziehst du die gewünschte Spalte in das Feld " Werte". In meinem Fall ziehe ich die Spalte " Früchte", weil ich ihre unterschiedlichen Werte zählen möchte. Dann klickst du im Dropdown-Menü auf Wertfeldeinstellungen. Es erscheint ein Pop-up-Fenster - wähle dort die Option Distinct Count aus. Du kannst dieser Spalte sogar einen eigenen Namen geben, aber das ist optional.

Zähle unterschiedliche Werte mit PivotTable. Bild vom Autor.

Methode 3: Erweiterte Filter anwenden

Wenn du nicht gerne mit Formeln arbeitest, verwende stattdessen die erweiterten Filter. 

In diesem Beispiel habe ich einen Datensatz und möchte mithilfe von erweiterten Filtern bestimmte Werte finden. Dazu wähle ich den Bereich aus, in dem ich einen bestimmten Wert finden möchte. Dann gehe ich auf die Registerkarte Daten und klicke auf Erweitert. 

Jetzt wähle ich im Dialogfeld Erweiterter Filter die Option An einen anderen Ort kopieren und gebe in das Feld Kopieren nach die Zielzelle ein, in der meine Liste erscheinen soll. Aktiviere dann das Kontrollkästchen Nur einmalige Datensätze und klicke auf OK.

Sobald ich eine Liste mit eindeutigen Werten habe, berechne ich die Anzahl dieser eindeutigen Werte mit der Funktion ROWS():

=ROWS(D2:D5)

Auch wenn die Option in der folgenden Abbildung nur eindeutige Datensätze liest, extrahiert sie tatsächlich unterschiedliche Werte. 

Zähle unterschiedliche Werte mit einer erweiterten Filteroption. Bild vom Autor.

Methode 4: VBA-Makros implementieren

Wenn du bestimmte Werte wiederholt zählen willst, verwende VBA (Visual Basic for Applications), bei dem wir den Code einmal schreiben und er den gesamten Prozess jedes Mal automatisiert, wenn wir die Funktion mit ihrem Namen aufrufen.

So kannst du das nutzen: Drücke Alt + F11 und gehe zur Option Modul unter dem Reiter Einfügen. Es wird ein neues Modul angezeigt, in das du deinen Code einfügen kannst. 

Das ist der Code, den ich erstellt habe, um die unterschiedlichen Werte zu zählen:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

Schreibe einen Code im VBA-Editor, um unterschiedliche Werte zu finden. Bild vom Autor.

Drücke dann Strg + S, um sie zu speichern und ALt + Q, um den VBA-Editor zu schließen. Jetzt kannst du diese benutzerdefinierte Funktion an einer beliebigen Stelle im Blatt aufrufen und den Bereich angeben, in dem die unterschiedlichen Werte gefunden werden sollen. In meinem Fall heißt sie CountUnique(). Jedes Mal, wenn ich diese Funktion mit einem bestimmten Bereich aufrufe, liefert sie mir einen Zählwert:

=CountUnique(A2:A8)

Zähle unterschiedliche Werte mit dem VBA-Editor. Bild vom Autor.

Referenztabelle

Als Referenz habe ich eine Tabelle erstellt, die dir hilft, den Unterschied zwischen eindeutigen und verschiedenen Zählungen zu erkennen. 

Häufige Probleme und Fehlerbehebung

Auch wenn du die besten Praktiken befolgst, kannst du manchmal vor Herausforderungen stehen. Aber das ist okay, denn das passiert bei den meisten Menschen.

Manchmal bekomme ich eine Fehlermeldung und bin verwirrt, bis ich merke, dass die versteckten Leerzeichen das Problem sind. Das passiert, wenn einige Werte zusätzliche Leerzeichen am Anfang oder Ende haben und Excel solche Fälle unterschiedlich behandelt, auch wenn sie gleich aussehen. Um dies zu beheben, schließe ich meine Formel in die Funktion TRIM() ein. Lies meinen anderen Lehrgang, Verwende die TRIM()-Funktion in Excel: Behebe jedes Abstandsproblem wenn du noch Fragen zu TRIM() hast.

Ein weiteres häufiges Problem sind Formelfehler in älteren Excel-Versionen. UNIQUE() funktioniert zum Beispiel nicht in Excel 2016 oder seinen Vorgängerversionen. Verwende stattdessen COUNTIF() oder SUMPRODUCT().

Schlussgedanken

Ich habe Schlüsselformeln, PivotTables und VBA-Makros zum Zählen eindeutiger und unterschiedlicher Werte behandelt. Jede Methode hat ihre Vorteile, aber du solltest die Methode wählen, die deinen Bedürfnissen entspricht und deine Excel-Version unterstützt.

Wenn du deine Excel-Kenntnisse vertiefen möchtest, schau dir unseren Lernpfad Excel-Grundlagen und den Kurs Datenaufbereitung in Excel an.