Liste nur mit eindeutigen Werten erstellen 125

Artikelbild-222
Mit Hilfe einer Matrixformel können aus einer Liste nur die eindeutigen Werte extrahiert werden (ohne Duplikate)
 

(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

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

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

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

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)

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

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

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

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

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

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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Oliver Harder Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

125 Gedanken zu “Liste nur mit eindeutigen Werten erstellen

  • Avatar-Foto
    Léon

    Hallo
    Ich habe das Problem wenn der letzte Wert der Liste nicht doppelt vorkommt, er mir diesen bis an die Ende der Liste wiederholt?
    Ich ziehe die Formel in die 39 Felder darunter.

    =WENNFEHLER(INDEX($K$4:$K$43;VERGLEICH(1;ZÄHLENWENN($K$3:K3;$K$4:$K$43)+($K$4:$K$43″“)*1;0));““)

    Gleiches Resultat ob ich jetzt als Matrix-Formel eingebe oder nicht?

    Ich habe eine Liste mit 40 Namen (am liebsten würde ich aus 2 Listen mit 2×20 Namen auswählen, aber das funktioniert gar nicht)

    In beiden Listen können die gleichen oder verschiedene Namen sein. Die Listen sind 2 Ranglisten wo die Punkte zusammengezählt werden müssen (Spalte neben-dran). Das heißt jeder Name mit Punkten soll nur einmal vorkommen. (am besten den Punkten nach sortiert, aber das mache ich in einem 2. Schritt, da ich das so nicht hinbekomme)

    Maximal wird die Liste also 40 Namen groß. Darum ziehe Ich die Liste auch nach unten um zu kopieren.

    Beispiel:
    Liste 1 Liste 2
    Spieler A 25 Spieler D 25
    Spieler B 22 Spieler C 22
    Spieler C 20 Spieler A 20
    Spieler D 18 Spieler F 18
    Spieler E 16 Spieler G 16

    Komme nicht darauf was ich falsch mache?

    Vielleicht habt ihr einen Tipp?

    Danke und schönes Wochenende

    • Avatar-Foto
      Léon

      Die Liste aus der ich die Daten sortiere ist so:
      Spieler A 25
      Spieler B 22
      Spieler C 20
      Spieler D 18
      Spieler E 16
      Spieler D 25
      Spieler C 22
      Spieler A 20
      Spieler F 18
      Spieler G 16

      Entschuldigung, dass ich das vergessen hatte. Die andere Darstellung versuche ich in einem nächsten Schritt hinzubekommen. Ich werde versuchen dass die Daten in 2 Tabellen stehen können.

      • Avatar-Foto
        Léon

        Formel für das Beispiel:

        =WENNFEHLER(INDEX($H$15:$H$24;VERGLEICH(1;ZÄHLENWENN($H$14:H14;$H$15:$H$24)+($H$15:$H$24″“)*1;0));““)

        • Avatar-Foto
          Léon

          Sorry. Dummer Fehler von mir:

          =WENNFEHLER(INDEX($H$15:$H$24;VERGLEICH(1;ZÄHLENWENN($H$14:H14;$H$15:$H$24)+($H$15:$H$24″“)*1;0));““)

          Hatte falsch gelesen und einen Wert falsch in dir Formel eingesetzt:

          Richtig ist:
          =WENNFEHLER(INDEX($H$15:$H$24;VERGLEICH(1;ZÄHLENWENN($N$14:N14;$H$15:$H$24)+($H$15:$H$24″“)*1;0));““)

          So jetzt suche ich weiter nach einer Lösung, die Werte aus 2 oder mehreren Tabellen auszulesen und sortiert nach den Punkten wiederzugeben.

          Schönes Wochenende und elegante Lösung wenn man sie dann richtig anwendet. 😉

          • Avatar-Foto
            Martin Weiß

            Hallo Leon,

            wenn du mehrere Tabellen auslesen musst, rate ich dir zu Power Query. Darüber lassen sich die Daten aus allen Tabellen zu einer „Mastertabelle“ zusammenfassen, die du dann auswerten kannst. Tipp: Mit einer Pivot-Tabelle geht diese Auswertung vermutlich noch deutlich einfacher als mit verschachtelten Formeln 😉

            Schöne Grüße,
            Martin