Liste nur mit eindeutigen Werten erstellen 125

(Artikel überarbeitet am 26.10.2021)

Vor einiger Zeit hatte ich in einem Artikel beschrieben, wie man in einer Liste mit Duplikaten die Anzahl der eindeutigen Werte bestimmt (Wie zählt man eindeutige Werte in Excel?).

Der heutige Artikel geht einen Schritt weiter:
Ich möchte nicht nur wissen, wieviele eindeutigen Werte es gibt, sondern ich möchte diese gleich in einer eigenen Liste ausgeben. Also dublettenfrei. Und zwar nur mit Hilfe von Excel-Formeln.

Und so geht’s:

Ausgangslage und gewünschtes Ergebnis

Für mein Beispiel verwende ich eine einfache Namensliste, welche manche Einträge mehrfach enthält (die Beispieldatei kannst du dir hier herunterladen). Als Ergebnis soll die eindeutige Liste herauskommen, die im Bild in Spalte D gezeigt wird. Jeder Name also genau einmal.

Ausgangslage und gewünschtes Ergebnis

Ich werde dazu zwei Lösungsvarianten vorstellen:
Eine „universelle“ Variante, die zugegebenermaßen etwas komplizierter ist, aber dafür mit jeder Excel-Version funktioniert. Und für diejenigen, die mit Microsoft 365/Office 365 arbeiten und die somit die neuen dynamischen Arrayfunktionen nutzen können, gibt es am Ende noch eine deutlich einfachere Lösung.

Beginnen wir aber mit der universellen Variante, die wir nun in mehreren Schritten erarbeiten.

Für Ungeduldige: die (fast) fertige Lösungsformel

Um dieses Ergebnis zu erreichen, verwende ich eine Matrix-Formel, die aus drei Funktionen besteht:

• ZÄHLENWENN
• VERGLEICH
• INDEX

{=INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN(D$4:$D4;$B$5:$B$24);0))}
Ich empfehle dir aber trotzdem, hier noch nicht mit dem Lesen aufzuhören, denn es gibt weiter unten noch ein paar interessante Ergänzungen.

Wie bei jeder Matrix-Formel gilt auch hier:
Die geschweiften Klammern dürfen nicht von Hand eingegeben werden. Stattdessen muss die Formel zwingend mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen werden!

Die fertige Matrix-Formel

Aus kosmetischen Gründen wird die finale Formel am Ende noch mit der WENNFEHLER-Funktion umschlossen. Zur besseren Übersicht lasse ich das in den nachfolgenden Erläuterungen aber weg.

Schritt 1: ZÄHLENWENN

Matrix-Formeln sind immer etwas schwer durchschaubar. Daher empfiehlt es sich, die Formel zu zerlegen und mit Hilfsspalten zu arbeiten. Da man verschachtelte Formeln am besten von innen nach außen bearbeitet, beginnen wir mit der ZÄHLENWENN-Funktion.

Erste Hilfsspalte mit der ZÄHLENWENN-Funktion

Damit wird für jede einzelne Zeile geprüft, ob der enthaltene Name oberhalb der jeweiligen Zeile schon einmal vorgekommen ist. Wenn nein, liefert die Funktion den Wert 0 (Null), ansonsten eben 1, 2 oder wie oft auch immer der Name weitere oben schon aufgetaucht ist.

Wichtig: Das erste Argument der ZÄHLENWENN-Funktion, also der zu durchsuchende Bereich, beginnt bereits in der Überschriftenzeile.
Ebenfalls wichtig sind die gemischten Bezüge, also die korrekte Setzung der Dollarzeichen:
D$4:$D4

Wenn die Formel dann nach unten kopiert wird, beginnt damit der Bezug immer absolut in Zelle D4 und verlängert sich dann mit jeder Zeile nach unten.

Wie man im Screenshot oben sieht, interessieren uns am Ende nur die Zeilen mit einem Null-Wert.

Schritt 2: VERGLEICH

Die nächste Schicht in der verschachtelten Formel ist die VERGLEICH-Funktion, welche die ZÄHLENWENN-Funktion umschließt:
{=VERGLEICH(0;ZÄHLENWENN($B$4:B4;$B$5:$B$24);0)}

Diese Formel wird in die erste Zeile als Matrix-Formel eingegeben und dann nach unten kopiert.

Zweite Hilfsspalte mit der VERGLEICH-Funktion

Wir suchen damit nach dem Wert 0 (= 1. Argument) innerhalb der Ergebnisse der ZÄHLENWENN-Funktion (= 2. Argument). Und dabei soll ein exakter Vergleich verwendet werden, daher im dritten Argument wieder der Wert 0.

Als Ergebnis erhalten wir die relative Position aller 0-Werte in der Liste.

Schritt 3: INDEX

Die alles umfassende INDEX-Funktion nimmt am Ende die Positionen, die VERGLEICH ermittelt hat und liefert die entsprechenden Namen zurück:

Das fertige Liste (inklusive WENNFEHLER)

Die in Zelle D5 eingegebene Matrix-Formel wird dann bis ans Ende der Tabelle nach unten kopiert. Da die eindeutige Liste erwartungsgemäß weniger Einträge umfasst, gibt es einige #NV-Fehler. Diese lassen sich aber mit einer zusätzlichen WENNFEHLER-Funktion elegant unterdrücken:
{=WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(0;ZÄHLENWENN($F$4:F4;$B$5:$B$24);0));"")}

Übrigens:
Mein geschätzter Excel-Kollege Andreas Thehos, den ich auf den ExcellentDays 2018 kennenlernen durfte, stellt mit der AGGREGAT-Funktion eine andere interessante Lösung für dieses Problem vor:
Guckst du hier: Excel – Einträge einer Tabelle ohne Doppelte auflisten

Sonderfall: Leere Zellen

Meine oben vorgestellte Lösung hat allerdings noch einen Schönheitsfehler. Wenn die Ausgangsliste nämlich leere Einträge enthält, wird dafür in der Zielliste eine Null angezeigt:

Leere Zellen ergeben einen Nullwert

Der Grund dafür ist, dass auch ein leerer Eintrag erst einmal gezählt wird. Und wenn man mit einer Formel auf eine leere Zelle verweist, wird eben die Ziffer Null dargestellt. Um dieses Problem zu lösen, muss unsere Formel nochmal ein wenig angepasst und erweitert werden.

Ich prüfe jetzt zusätzlich, ob die jeweilige Zelle in der Ausgangsliste leer ist:
$B$5:$B$24<>""

Als Ergebnis erhält man ein WAHR für jeden Eintrag, der nicht leer ist und ein FALSCH für jede leere Zelle. Um aus WAHR eine 1 und aus FALSCH eine 0 zu machen, setze ich die Prüfung in Klammen und multipliziere das Ganze mit 1:
($B$5:$B$24<>"")*1

Das heißt, für einen Eintrag, der nur einmal in der Liste vorkommt, ergibt die Zählung jetzt den Wert 1, für einen Namen, der zweimal vorkommt, 2 und so weiter. Leere Zellen hingegen ergeben den Wert 0. Und damit müssen wir jetzt in der VERGLEICH-Funktion nicht mehr nach dem Wert 0 suchen, sondern nach 1.

Die gesamte neue Formel sieht für Zelle F5 also folgendermaßen aus:
=WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(1;ZÄHLENWENN($F$4:F4;$B$5:$B$24)+($B$5:$B$24<>„“)*1;0));““)

Angepasste Formel filtert Nullwerte aus

Bitte auch hier unbedingt darauf achten, dass die Formel mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen wird!

Ich weiß, die Lösung ist nicht ganz trivial. Aber es funktioniert 😉

Deutlich bequemer mit Excel 365

Wenn du das Glück hast, Excel aus Microsoft 365/Office 365 zu nutzen, dann habe ich für dich eine wesentlich komfortablere Lösung, als die oben beschriebenen Matrixformeln.

Denn dann stehen dir auch die neuen dynamischen Array-Funktionen zur Verfügung, die vieles von dem auf den Kopf stellen, wie man in der Vergangenheit bestimmte Probleme in Excel lösen musste.

Für unsere Zwecke sind das die Funktionen EINDEUTIG und FILTER.

Wie der Name schon vermuten lässt, liefert EINDEUTIG eine eindeutige Liste ohne Duplikate. Man gibt dabei lediglich die Ausgangsliste an und erhält sofort das Ergebnis. Da es sich um eine dynamische Funktion handelt, muss die Formel nur in die erste Zelle eingetippt werden. Sie läuft dann automatisch in soviele Zeile über, wie eben gerade notwendig.
=EINDEUTIG(B5:B24)

Nur in Microsoft 365: Die EINDEUTIG-Funktion

Da in dieser einfachen Form leere Zellen ebenfalls als Nullwert angezeigt werden, brauchen wir noch eine weitere Zutat, nämlich die ebenfalls neue FILTER-Funktion. Für sich genommen filtert sie eine Liste nach den angegebenen Kriterien. Um beispielsweise alle leeren Zellen auszufiltern, sieht die Funktion so aus:
=FILTER(B5:B24;B5:B24<>"")

Nur in Microsoft 365: Die FILTER-Funktion

Und wenn man jetzt die beiden Funktionen kombiniert, erhält man eine extrem einfache und höchst komfortable Lösung:
=EINDEUTIG(FILTER(B5:B24;B5:B24<>""))

EINDEUTIG kombiniert mit FILTER

Wie gesagt, die Voraussetzung für diese Variante ist Microsoft 365/Office 365. Wenn du Excel 2019 oder älter einsetzt oder Dateien mit anderen Excel-Anwendern austauschen musst, die solche Versionen im Einsatz haben, bleibt dir leider nur die kompliziertere erste Formellösung.

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…