Die dynamischen Array-Funktionen in Excel aus Microsoft 365 erleichtern viele Dinge ganz ungemein. Was früher teilweise nur mit sehr komplexen Formeln lösbar war – oder wofür es vielleicht überhaupt keine Formellösung gab – ist jetzt häufig ein Kinderspiel.
Die EINDEUTIG-Funktion ist so ein Kandidat. Mit ihr lässt sich eine Liste, welche Duplikate enthält, ruckzuck auf eindeutige Datensätze eindampfen.
Manchmal liegen die Daten aber nicht als schön strukturierte Datensätze in einer Liste vor, wie das beispielsweise bei Adressen der Fall ist. Sondern man hat eine mehrspaltige Matrix, in der jeder Wert für sich alleine steht. Auch eine solche Matrix lässt sich von Duplikaten befreien, wenn man weiß, wie!
Und so geht’s:
Beispieldatei herunterladen
Szenario 1: Eine eindeutige Liste erstellen
Für dieses Beispiel habe ich ein 10 x 10 Matrix mit zufälligen Zahlen erstellt. Über eine bedingte Formatierungsregel werden mehrfach vorkommende Werte farbig gekennzeichnet:
Eine Matrix mit 10 x 10 zufälligen Zahlen
Aus dieser Matrix soll nun eine eindeutige, also duplikatfreie, Liste erstellt werden. Der naheliegende Weg führt über die in M365 enthaltene EINDEUTIG-Funktion.
=EINDEUTIG(A3:J12)
Die normale EINDEUTIG-Funktion scheitert zunächst
Das Ergebnis ist aber absolut enttäuschend. Es wird exakt die gleiche Matrix ausgegeben, einschließlich aller Duplikate.
Der Grund dafür ist, dass die Funktion zeilenweise vergleicht und dafür alle Werte in einer Zeile berücksichtigt. Käme in unserer Tabelle also beispielsweise die Zahlenreihe der ersten Zeile in der exakt gleichen Reihenfolge ein weiteres Mal vor, würde dies Zeile ausgefiltert werden.
In unserem Szenario soll aber jede Zahl einzeln und für sich betrachtet werden.
Für die Lösung benötigen wir eine zweite Funktion, nämlich ZUSPALTE. Diese Funktion überführt alle Werte einer Matrix in eine einzelne Spalte. Und darauf setzen wir die EINDEUTIG-Funktion auf:
=EINDEUTIG(ZUSPALTE(A3:J12))
EINDEUTIG und ZUSPALTE liefern das gewünschte Ergebnis
Wer eine sortierte Liste benötigt, umschließt das noch mit der SORTIEREN-Funktion:
=SORTIEREN(EINDEUTIG(ZUSPALTE(A3:J12)))
Szenario 2: Eine eindeutige Matrix erstellen
Die Liste ist ja schön und gut, aber vielleicht willst du ja bei der Matrix-Darstellung bleiben? Dann kommt eine weitere Array-Funktion zum Einsatz: ZEILENUMBRUCH
Diese Funktion nimmt eine Liste und bricht diese nach einer angegebenen Anzahl von Spalten in eine neue Zeile um. Für eine 10-spaltige Matrix sieht die Lösungsformel so aus:
=ZEILENUMBRUCH(EINDEUTIG(ZUSPALTE(A3:J12));10;"")
Ausgabe der bereinigten Werte in Matrixform
Die beiden doppelten Anführungszeichen am Ende der Formel bewirken, dass nicht benötigte Zellen auch leer angezeigt werden. Ohne diesen Parameter würden dort stattdessen #NV!-Fehler angezeigt werden.
Online-Kurs „Dynamische Array-Funktionen“
Szenario 3: Eine eindeutige Matrix aller einmaligen Werte erstellen
Vielleicht möchtest du aber in der Ausgabematrix nur solche Werte sehen, die von Haus aus nur ein einziges Mal in der Ursprungsmatrix vorhanden sind. Mehrfache Werte sollen also komplett ignoriert werden.
Das ist über einen weiteren Parameter in der EINDEUTIG-Funktion möglich:
=ZEILENUMBRUCH(EINDEUTIG(ZUSPALTE(A3:J12);;WAHR);10;"")
Nur einmalig vorkommende Werte
Dieser dritte Parameter WAHR sorgt eben dafür, dass nur einmalig vorkommende Werte ausgegeben werden.
Szenario 4: Nur die Duplikate ausgeben
Jetzt wird es etwas kniffeliger. Ich möchte jetzt nämlich nicht die duplikatfreie Matrix ausgeben, sondern wissen, welche Werte denn überhaupt mehrfach vorkommen. Hierfür werden wir uns in mehreren Schritten an die Lösung herantasten.
Schritt 1
Mit Hilfe von ZÄHLENWENN bestimmen wir die Häufigkeit für jeden einzelnen Wert in der Matrix. Damit sieht man, an welcher Stelle in der Matrix Werte mehrfach vorkommen:
=ZÄHLENWENN(A3:J12;A3:J12)
Schritt 1: Häufigkeiten berechnen
Schritt 2
Anstelle der Häufigkeiten sollen nun die mehrfach vorkommenden Zahlen ausgegeben werden. Für alle anderen Zellen soll ein #NV-Fehler geliefert werden. Dazu greife ich auf die Funktionen WENN und NV zurück. Letztere macht nichts anderes, als einfach einen #NV-Fehler zu liefern.
=WENN(ZÄHLENWENN(A3:J12;A3:J12)>1;A3:J12;NV())
Schritt 2: Nur mehrfache Werte anzeigen
Wozu das mit den #NV-Fehlern gut war, siehst du gleich im folgenden Schritt.
Schritt 3
Wir sind fast am Ziel. Genau wie im Beispiel oben mit der Liste nutzen wir jetzt die ZUSPALTE-Funktion. Nur dass wir jetzt am Ende noch einen weiteren Parameter übergeben, mit dem alle Fehlerwerte ignoriert werden:
=ZUSPALTE(WENN(ZÄHLENWENN(A3:J12;A3:J12)>1;A3:J12;NV());2)
Schritt 3: Aus der Matrix wird eine Spalte
Schritt 4
Im allerletzten Schritt legen wir noch die EINDEUTIG-Funktion drumherum und erhalten somit eine eindeutige Liste aller mehrfach vorkommenden Werte:
=EINDEUTIG(ZUSPALTE(WENN(ZÄHLENWENN(A3:J12;A3:J12)>1;A3:J12;NV());2))
Schritt 4: Eindeutige Liste der mehrfach vorkommenden Werte
Zugegeben, das letzte Szenario erfordert wieder eine etwas komplexere Lösung. Aber ohne die dynamischen Array-Funktionen aus Microsoft 365 wäre man hier ziemlich aufgeschmissen, oder?
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.
In Szenario 2 ist die Ergebnismatrix transponiert…
.. und ich glaube, es handelt sich mit SPALTENUMBRUCH um die falsche Funktion, um die Werte in 10 Spalten aufzuteilen. Tatsächlich macht er 10 Zeilen daraus (bei einer Matrix von 10×10) kann man das nicht gut erkennen. Die Funktion ZEILENUMBRUCH (gleiche Syntax) bricht tatsächlich nach 10 Spalten um und transponiert damit auch nicht die Daten.
Hallo Anke,
Asche auf mein Haupt, du hast völlig recht. Ich bin mit den Funktionen durcheinandergekommen und habe den Artikel jetzt korrigiert.
Vielen Dank für den Hinweis!
Schöne Grüße,
Martin
Stimmt, ich hatte die falsche Funktion verwendet. ZEILENUMBRUCH wäre richtig gewesen.
Schöne Grüße,
Martin
Hallo Martin,
generell möchte ich mich für Deine Leistungen auf diesem Gebiet danken.
Beispielsweise kannte ich die Excel-Formeln EINDEUTIG und ZUSPALTE noch gar nicht! Zwar nutze ich Excel sehr gern und häufig, doch bekomme ich nicht immer mit, was es für Neuerungen gibt (… man besucht ja nicht ständig Kurse!). Insofern wird Deine rechte Suchleiste mit Begriffen aus Excel immer länger.
Mach weiter so!
Es grüßt
Rudolf
Hallo Rudolf,
vielen Dank für dein Feedback. Das ist genau der Grund, warum ich hier schreibe, denn als normaler Anwender kann man einfach nicht alle Neuerungen im Blick behalten.
Schöne Grüße,
Martin