Duplikate mit UNIQUE entfernen

Die Funktion UNIQUE gibt eine Liste ohne Duplikate zurück und behält nur die eindeutigen Werte eines Bereichs bei. Das Ergebnis wird als Spill-Bereich ausgegeben, sodass eine einzige Formel eine übersichtliche, stets aktuelle Liste erzeugt.

Das ist wesentlich einfacher als der frühere Menübefehl „Duplikate entfernen“, der eine statische Kopie erstellte, die Sie bei jeder Datenänderung erneut erzeugen mussten.

UNIQUE akzeptiert ein erforderliches und zwei optionale Argumente:

=UNIQUE(array, [by_col], [exactly_once])

• array ist der Bereich, aus dem Duplikate entfernt werden sollen.
• Wenn by_col auf TRUE gesetzt ist, werden Spalten statt Zeilen verglichen (selten erforderlich).
• Wenn exactly_once auf TRUE gesetzt ist, werden nur Werte zurückgegeben, die genau einmal vorkommen.

In den meisten Fällen geben Sie nur das Array an.

Angenommen, A2:A12 enthält mehrfach vorkommende Regionsnamen: East, West, East, North, West, East. Um jede Region einmal zu erhalten:

=UNIQUE(A2:A12)

Die Formel gibt drei Werte aus: East, West, North – und zwar in der Reihenfolge ihres ersten Auftretens. Fügen Sie der Quelle eine neue Region hinzu, wird die Liste automatisch erweitert.

UNIQUE kann ganze Zeilen auf Duplikate prüfen. Wenn A2:B12 Region und Produkt enthält, ist eine Zeile nur dann ein Duplikat, wenn beide Spalten übereinstimmen.

=UNIQUE(A2:B12)

Damit wird jede eindeutige Kombination aus Region und Produkt als kleine zweispaltige Spill-Tabelle zurückgegeben. Das ist ideal, um die Zeilenbeschriftungen eines Übersichtsberichts zu erstellen.

Setzen Sie das dritte Argument auf TRUE, um nur Werte beizubehalten, die genau einmal vorkommen, und alle Werte zu entfernen, die mehrfach vorkommen.

=UNIQUE(A2:A12,,TRUE)

Beachten Sie die beiden Kommas: Sie überspringen das by_col-Argument und geben nur exactly_once an. Wenn East dreimal vorkommt, wird die Region vollständig entfernt, während eine nur einmal vorkommende Region beibehalten wird.

Verschachteln Sie UNIQUE in COUNTA oder ROWS, um die Anzahl eindeutiger Werte zu zählen. Um zu sehen, wie viele verschiedene Regionen vorhanden sind:

=ROWS(UNIQUE(A2:A12))

ROWS zählt die Zeilen in der Spill-Liste. Sobald neue Regionen hinzukommen, wird die Anzahl automatisch aktualisiert. So lässt sich einfach ermitteln, wie viele eindeutige Elemente Ihre Daten enthalten.

UNIQUE behält die Reihenfolge des ersten Auftretens bei. Für eine alphabetische Liste verschachteln Sie die Funktion in SORT:

=SORT(UNIQUE(A2:A12))

Nun werden die eindeutigen Regionen in dieser Reihenfolge ausgegeben: East, North, West. Diese Kombination wird sehr häufig verwendet, um übersichtliche Beschriftungen für Berichte zu erstellen.

UNIQUE lässt sich hervorragend mit FILTER kombinieren. Um nur die eindeutigen Produkte zu erhalten, die in der Region East verkauft wurden:

=UNIQUE(FILTER(B2:B12, A2:A12="East"))

FILTER schränkt zunächst auf die Zeilen aus East ein, anschließend entfernt UNIQUE doppelte Produkte. Durch das Verschachteln von Spill-Funktionen können Sie mit einer einzigen Formel komplexe Fragen beantworten.

Ein klassischer Kurzbericht kombiniert UNIQUE für die Beschriftungen mit SUMIF für die Summen. Platzieren Sie die eindeutigen Regionen mit =UNIQUE(A2:A12) in Spalte E und verwenden Sie neben der Spill-Liste:

=SUMIF(A2:A12, E2#, C2:C12)

Der Spill-Verweis E2# übergibt jede eindeutige Region an SUMIF, das eine Summe pro Region zurückgibt. Die gesamte Zusammenfassung wird aktualisiert, sobald die Daten wachsen.

UNIQUE funktioniert sowohl in Excel 365 als auch in Google Sheets, wobei es einige kleine Unterschiede gibt. In Google Sheets verglich die ältere Version von UNIQUE vollständige Zeilen und verfügte nicht über das exactly_once-Argument. Prüfen Sie daher das Verhalten, wenn die Ergebnisse abweichen.

Sheets bietet UNIQUE außerdem innerhalb von QUERY sowie die Funktion SORTN für fortgeschrittene Anwendungsfälle. Für alltägliche Listen eindeutiger Werte verhält sich das grundlegende =UNIQUE(range) auf beiden Plattformen gleich.

Eine Liste eindeutiger Werte eignet sich ideal für eine Dropdownliste zur Datenüberprüfung. Geben Sie =UNIQUE(A2:A12) in einem Hilfsbereich ein und verweisen Sie anschließend mit dem Operator # auf den Spill-Bereich.

Wenn die Liste in H1 beginnt, legen Sie die Quelle der Datenüberprüfung auf =H1# fest. Sobald neue Regionen in Ihren Daten erscheinen, wird die Auswahl der Dropdownliste automatisch erweitert, ohne dass manuelle Aktualisierungen erforderlich sind.

Testen Sie Ihr Verständnis von UNIQUE.

Sie haben gelernt, mit UNIQUE eindeutige Werte zu extrahieren:

• Die Syntax lautet =UNIQUE(array, [by_col], [exactly_once]).
• Die Funktion gibt eindeutige Werte in der Reihenfolge ihres ersten Auftretens als Spill-Bereich aus.
• Sie kann Duplikate aus vollständigen Zeilen mit mehreren Spalten entfernen.
• Setzen Sie exactly_once auf TRUE für Werte, die nur einmal vorkommen.
• Kombinieren Sie die Funktion mit SORT, FILTER, ROWS und SUMIF für übersichtliche, aktuelle Berichte.

Als Nächstes lernen Sie, den SPILL-Fehler zu erkennen und zu beheben.