Liste nur mit eindeutigen Werten erstellen 5

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

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 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 Datei 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

Und das ganze mit Hilfe einer Formel-Lösung.

Für Ungeduldige: die 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:$F4;$B$5:$B$24);0))}

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));"")}

Und das war wieder einmal ein Beispiel dafür, wie vielseitig Matrix-Formeln sein können.

Ü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

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.



Schreibe einen Kommentar

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

5 Gedanken zu “Liste nur mit eindeutigen Werten erstellen

  • Jörg Böhmichen

    Hallo Martin,
    danke für Deinen neuen Excel-Tipp! Ich finde die Aufgabe realistisch, wobei es häufig Listen gibt, die zwar in der ersten Spalte denselben Namen gibt, in den Folgespalten aber verschiedene Daten stehen, z.B. Arbeitsstunden — diese müssten dann in der eindeutigen Liste mit einer passenden Aggregatfunktion zusammengefasst werden (SUM, MIN, MAX, AVG, …).
    Mir fällt auf, dass es nur die WENNFEHLER-Funktion erst ab Vers. 2007 und höher gibt, INDEX, VERGLEICH und ZÄHLENWENN habe ich in Excel 2002 auch schon. Kannst Du die entsprechenden Dateien in beiden Versionen zum Download anbieten? Also „??? bis 2003“ und „2007 und höher“? Vielen Dank!

    Freundliche Grüße
    Jörg

    • Martin Weiß Autor des Beitrags

      Hallo Jörg,

      das stimmt natürlich, dass manche Funktionen vor Excel 2007 noch nicht verfügbar sind. Allerdings ziehe ich hier auch die Grenze. Auch auf die Gefahr hin, dass ich jetzt vielleicht manche Leser verprelle, aber die ganz alten Versionen kann und möchte ich in meinen Artikeln nicht mehr berücksichtigen (und selbst Excel 2007 hat ja auch schon 11 Jahre auf dem Buckel…).

      Schöne Grüße,
      Martin

  • Sven Uhlig

    Hallo Martin,

    Deine Lösung hat mich daran erinnert, dass ich ja noch auf der Suche nach einer Lösung für folgendes Problem bin:

    Ich habe eine Tabelle mit Artikeln. In Spalte A steht eine Anzahl, in Spalte B die Artikelnummer, in Spalte C die Artikelbezeichnung, in Spalte D der Einzelpreis, in Spalte E der Gesamtpreis pro Artikel und in Spalte F muss eine Materialnummer händisch eingetragen werden. Die Materialnummer muss aus einer externen nicht per Excel abfragbaren Datenbank herausgesucht werden. Als Suchkriterium kann man die Artikelnummer oder die Artikelbezeichnung verwenden, da nicht jeder Artikel eine Artikelnummer hat. In der Tabelle kommen, manche Artikel mehrfach (doppelt, dreifach, usw.) vor.
    Damit ich weiß, welche Artikel mehrfach vorkommen habe ich mit „ZÄHLENWENN“ eine Bedingte Formatierung erstellt, wie Du es im Artikel „Das doppelte Lottchen …“ (https://www.tabellenexperte.de/dublettensuche-mit-excel/) beschrieben hast. Das hilft mir zwar schon mal weiter ist aber noch nicht perfekt und effektiv genug. Weil ja Artikel mit der gleichen Anzahl auch die gleiche Farbe haben. Das macht es z.B. bei 10 Artikeln, welche es jeweils 3x gibt, trotzdem noch nicht wesentlich übersichtlicher und einfacher bei der Erfassung der Materialnummern.

    Wenn ich jetzt für einen mehrfach vorkommenden Artikel die Materialnummer aus der Datenbank rausgesucht habe und diese in meiner Artikelliste eintrage möchte, dann wäre es doch sehr hilfreich, wenn hinter dem 1. Mehrfach-Artikel in meiner Liste, sagen wir mal in Spalte G, ein Hyperlink auf die Zelle F… des 2. gleichen Mehrfach-Artikels, stehen würde. Damit dieser 2. gleiche Mehrfach-Artikel einfach und schnell mit einem Mausklick angesteuert werden kann. Wenn es noch einen 3. gleichen Mehrfach-Artikel geben sollte, dann soll natürlich hinter dem 2. gleichen Mehrfach-Artikel ebenfalls ein Hyperlink zum 3. gleichen Mehrfach-Artikel stehen usw.

    Es müsste also in der Spalte G eine Formel stehen, die zum einen prüft ob es sich um einen Mehrfach-Artikel handelt (vermutlich mit „ZÄHLENWENN“, 1. Prüfung auf die Artikelnummer, wenn nicht vorhanden, dann auf die Artikelbezeichnung) und wenn ja, in welcher „ZEILE“ sich der nächste gleiche Mehrfach-Artikel befindet und dessen „ADDRESSE“ in einen „HYPERLINK“ schreiben. Gibt es keinen weiteren gleichen Mehrfach-Artikel, wird kein weiterer Hyperlink angezeigt/ausgegeben. Für jeden Mehrfach-Artikel muss natürlich der richtige Hyperlink zu seines gleichen hinterlegt werden.

    Wäre super wenn Du mir hier helfen könntest.

    Viele Grüße
    Sven

    • Sven Uhlig

      Hallo Martin,

      ich habe mittlerweile eine Lösung für mein Problem gefunden und beantworte meine Frage mal selbst, für den Fall dass noch jemand so ein Thema hat.
      Die benötigte Formel um einen Hyperlink zu den weiteren Mehrfach-Datensätzen mit Prüfung auf die Spalte B (Artikelnummer) zu erzeugen, lautet wie folgt (bei mir steht diese in Zelle G2):
      =WENN(ZÄHLENWENN($B3:$B$100;$B2);HYPERLINK(„#F“&VERGLEICH($B2;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)
      Ich habe die Formel auch noch in der Spalte H2 zur Prüfung auf die Spalte C (Artikelbezeichnung) eingetragen. Danach habe ich diese runter kopiert. Ich erhalte also je einen Hyperlink auf den nächsten Mehrfach-Datensatz, welcher in Spalte F (Materialnummer) landet.

      Eine Frage habe ich jedoch noch:
      Wenn ich eine Zeile ausschneide und an einer anderen Stelle wieder einfüge, dann wird bei ZÄHLENWENN und auch bei VERGLEICH die benannte Zelle in dem der definierte Bereich beginnt nicht automatisch angepasst. Um beim obigen Beispiel zu bleiben, ich schneide Zeile 2 aus
      =WENN(ZÄHLENWENN($B3:$B$100;$B2);HYPERLINK(„#F“&VERGLEICH($B2;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)
      und füge diese als Zeile 8 wieder ein, dann sieht die Formel so aus:
      =WENN(ZÄHLENWENN($B3:$B$100;$B8);HYPERLINK(„#F“&VERGLEICH($B8;$B3:$B$100;0)+ZEILE();“▼NÄCHSTER MEHRFACH-DATENSATZ▼“); „“)

      Wieso wird B3 nicht automatisch von Excel auf B9 angepasst?

      Viele Grüße
      Sven

      • Martin Weiß Autor des Beitrags

        Hallo Sven,

        ich muss schon sagen: Das ist eine sehr coole Lösung, die du da gefunden hast. Hut ab!
        Das Problem beim Ausschneiden ist ein generelles Excel-Problem bzw. eine Eigenheit von Excel. Das kannst du nur umgehen, indem du stattdessen die Zeile kopierst, einfügst und die Ursprungszeile anschließend wieder löscht. Beim Kopiervorgang werden die Bezüge angepasst, beim Ausschneiden leider nicht.

        Schöne Grüße,
        Martin