Eindeutige Werte aus einem Bereich ermitteln

Ab und zu fragt Bill Jelen – oder auch bekannt als MrExcel – die Excel Fangemeide z.B. via Twitter und YouTube nach Lösungswegen zu dem einen oder anderen Excel-Problem. In Excel führen viele Wege nach Rom und es ist immer wieder spannend zu sehen, auf welche Lösungen die Community kommt.

So wie auch vor einigen Tagen geschehen. Diesmal ging es darum, wie sich aus einem Bereich alle eindeutigen Werte sortiert als Liste anzeigen lassen. Primär ging es hierbei darum, die Excel-Funktion EINDEUTIG zu verwenden, die aus einer Spalte oder Zeile alle eindeutigen Werte extrahiert, jedoch nicht bereichsbezogen arbeiten kann.

In diesem Artikel werde ich meine Lösung vorstellen, wie dennoch mit der Excel-Funktion EINDEUTIG alle eindeutigen Werte aus einem Bereich mit mehreren Zeilen und Spalten extrahiert und sortiert werden können.

Kurzer Ausflug in die Welt dynamischer Arrays in Excel

Seit einiger Zeit wurden in Excel 365 sogenannte dynamische Arrays eingeführt – eine kleine Revolution in Excel. Denn Formeln, die mehrere Ergebnisse zurückliefern, können in nun neueren Excel-Versionen in benachbarte Zellen überlaufen. Dynamische Arrays und mit diesen in Excel eingeführten neuen Funktionen stehen allerdings nur neueren Excel-Versionen (Excel 365 in den Versionen ab Ende des letzten Jahres) zur Verfügung. Ältere Excel Versionen, wie Excel 2010 oder auch die Kaufversionen zu Excel 2016 und Excel 2019 kennen diese Features nicht.

Zunächst aber ein erläuterndes Beispiel zu dynamischen Arrays. Angenommen, wir haben eine Liste von Werten in 100-er Schritten im Bereich B2:B10. Soll nun der fünfte Wert aus der Liste extrahiert werden, liesse sich dazu die Formel INDEX(B2:B10;5;1) verwenden. In nachfolgender Abbildung ist dies in Zelle D2 dargestellt.

Möchten wir nun zwei Werte, z.B. den dritten und sechsten Wert, aus der Liste extrahieren, gab es in früheren Excel Versionen zwei Möglichkeiten.

Die erste Möglichkeit wäre, ganz simpel die INDEX-Funktion zweimal anzuwenden und in der Formel für den dritten Wert als zweiten Parameter die 3 und für die Formel für den sechsten Wert als zweiten Parameter die 6 anzugeben.

Die zweite Möglichkeit wäre, eine Matrixformel zu verwenden. Dazu markieren wir zunächst z.B. die beiden Zellen D3 und D4. Anschließend geben wir in Zelle D3 die Formel =INDEX(B2:B10;{3;6};1) in die Eingabezeile ein und beenden die Eingabe mit Strg + Umschalt + Enter, wodurch um die Formel geschweifte Klammern gesetzt werden, die die Formel als Matrixformel kennzeichnen.

Irgendwie schon kompliziert, oder? Und, das Beispiel ist jetzt recht einfach gehalten, aber es gibt durchaus sinnvollere, komplexere Matrixformeln, die übrigens auch sehr mächtig sein können.

Mit den dynamischen Arrays ändert sich das. Matrixformeln lassen sich genauso komfortabel eingeben wie normale Formeln. Die Ausgabe ändert sich allerdings: solche Formeln laufen automatisch über, das heißt sie expandieren auf den zurückgegebenen Bereich.

In nachfolgender Abbildung wurde in einer Tabelle in Excel 365 nur in Zelle D3 die Formel INDEX(B2:B10;{3;6};1) angegeben. Sobald die Eingabe mit Enter abgeschlossen wird, erscheint der zweite Wert in D4. Das heißt, die Formel hat sich von D3 auf die Zelle D4 ohne mein Zutun expandiert, im Excel-Jargon übergelaufen. Dieser Bereich wird, wenn eine Zelle im Bereich angeklickt wird, durch eine blaue Umrandung hervorgehoben. Und in Zelle D4 wird die Formel in der Eingabezeile in grau dargestellt.

Mit der Einführung dynamischer Arrays in Excel wurden auch neue Excel-Funktionen eingeführt, die von diesem Verhalten profitieren. Dies sind EINDEUTIG, FILTER, SORTIEREN, SORTIERENNACH, SEQUENZ und ZUFALLSMATRIX. Seit kurzen sind zusätzlich XVERWEIS und XVERGLEICH verfügbar, deutlich verbesserte Versionen von SVERWEIS, WVERWEIS und VERGLEICH.

Dynamische Arrays sind kompatibel zu älteren Excel-Versionen, d.h. sie werden automatisch in Matrixformeln umgewandelt. Die neuen Funktionen stehen älteren Excel-Versionen jedoch nicht zur Verfügung.

Die Knobelaufgabe

Die Originaltabelle aus Bill Jelens Video habe ich insoweit modifiziert, als dass ich eine eigene Liste von Städtnamen per Zufall in den Datenbereich verteilt habe. Die Datentabelle, aus der alle eindeutigen Werte gezogen werden sollen, befindet sich im Bereich A2:D13. Das zu erwartende Ergebnis ist in Spalte F aufgeführt.

Schauen wir uns zunächst die Definition der Excel-Funktion EINDEUTIG an. Die Funktion erwartet eine Bereichsagabe sowie zwei optionale Argumente. Das zweite Argument legt fest, ob die eindeutigen Werte bezogen auf die Werte in Spalten oder in Zeilen zurückgegeben werden sollen. Das dritte Argument legt fest, ob nur einmalig vorkommende Werte oder mehrfach vorkommende Werte berücksichtigt werden sollen oder nicht. Standardmäßig wird in Zeilen nach den eindeutigen Werte gesucht und es ist egal, ob die Werte einmal oder mehrmals in der Liste vorkommen.

Schauen wir uns nun an, was passiert, wenn wir die eindeutigen Werte nur aus der Spalte A extrahieren möchten. Funktioniert.

Verwenden wir allerdings nun den kompletten Datenbereich, erhalten wir folgend abgebildetes Ergebnis. Das klappt somit nicht. Zwar expandiert der Rückgabebereich auf die Bereichsgröße der Datenquelle, aber es werden keine eindeutigen Werte ermittelt.

Somit kann EINDEUTIG nur auf eine eindimensionale Liste wirken. Wir müssten also die Spalten A, B, C und D zu einer kombinieren – und das am besten dynamisch.

Die Größe des Datenbereich ist 12 x 4 Zellen – 12 Zeilen, 4 Spalten und somit insgesamt 48 Zellen. Die Excel-Funktion INDEX extrahiert aus einem Bereich die Werte, basierend auf dessen Zeile und Spalte. Erstellen wir uns zunächst manuell eine Liste der jeweiligen Werte für die Zeilen- und Spaltennummer, die in INDEX verwendet werden soll. In nachfolgender Abbildung sind einige Zeilen ausgeblendet und die einzelnen Blöcke hervorgehoben.

Anstatt jedoch die Werte manuell in Hilfsspalten einzutragen, wäre es sicherlich sinnvoller, diese automatisch berechnen zu lassen. Es sind insgesamt 48 Zellen im Quelldatenbereich, was wiederum 48 Zeilen ergibt, wenn die Spalten aus dem Quellbereich in eine Spalte gelegt würden. Um die Spaltennummer 1 bis 4 zu berechnen, müsste nach jedem Vielfachen von 12 eine Zahl um 1 erhöht werden. Das erreichen wir, indem wir zunächst die laufende Nummer durch 12 teilen und anschließend aufrunden. Wie in folgender Abbildung zu sehen, erhöht sich der gesuchte Wert immer um 1, wenn die Nummer bzw. Zähler das Vielfache von 12 überschreitet.

In ähnlicher Form müssten wir die Zeilennummer berechnen. Hier ist es jedoch so, dass sobald der Zähler 1 … 48 das Vielfache von 12 überspringt, die Zeilenummer wieder bei 1 anfangen müsste. Das kann mit Hilfe der Funktion REST bewerkstelligt werden. Der Rest von 1/12 ist 1, der Rest von 2/12 ist 2 usw. Bis 12 erreicht wird, dann ist der Rest Null. Der Rest von 13/12 ist wieder 1, der Rest von 14/12 wiederum 2 usw. Somit ergibt folgende Pseudo-Formel, wenn man den Rest Null berücksichtigt, der dann auftritt, wenn der Zähler genau ein Vielfaches von 12 ist.

=WENN(REST(Zähler;12)<>0;REST(Zähler;12);12)

Damit hätten wir nun, basierend auf einem Zähler 1 bis 48 die Zeilen- und Spaltennummer berechnet. Aber wie dynamisieren wir den Zähler? Recht simpel, denn hier hilft uns die Excel-Funktion SEQUENZ, ebenfalls eine neue Funktion. Diese Funktion liefert als dynamisches Array eine Zahlenfolge zurück. Die Definition sieht wie folgt abgebildet aus.

Als Argumente können die Anzahl der Zeilen, der Anzahl Spalten, ein Startwert und eine Schrittweite angegeben werden. Das erste Argument ist ein Pflichtargument, die weiteren sind optional. Für unsere Zwecke reicht somit SEQUENZ(48;1) völlig aus. Insgesamt ergeben sich dadurch folgende Formeln zur Berechnung der Zeilen- und Spaltennummern:

Zeilennummer  =WENN(REST(SEQUENZ(48;1);12)<>0;REST(SEQUENZ(48;1);12);12)
Spaltennummer =AUFRUNDEN(SEQUENZ(48;1)/12;0)

Und dadurch können wir nun unsere INDEX-Formel vervollständigen.

INDEX($A$2:$D$13;WENN(REST(SEQUENZ(48;1);12)<>0;REST(SEQUENZ(48;1);12);12);
AUFRUNDEN(SEQUENZ(48;1)/12;0))

Unsere Liste, die die einzelnen Spalten aus dem Bereich A2:D13 dynamisch untereinander darstellt, sieht dann wie folgt aus.

Es ist fast geschafft, denn nun brauchen wir nur noch die Formel innerhalb der Funktion EINDEUTIG zu kapseln. Soll noch eine Sortierung erfolgen, verwenden wir die Formel SORTIEREN. Das Endergebnis ist somit:

SORTIEREN(EINDEUTIG(INDEX(A2:D13;WENN(REST(SEQUENZ(48;1);12)<>0;
REST(SEQUENZ(48;1);12);12);AUFRUNDEN(SEQUENZ(48;1)/12;0))))

Die Formel liesse sich noch weiter dynamisieren, um eine beliebige Größe eines Datenbereichs zu verwenden. Beispielweise können wir dem Datenbereich einen Namen vergeben und dessen Höhe und Breite anhand der Excel-Funktionen ZEILEN und SPALTEN berechnen. Vergeben wir dem Datenbereich den Namen Daten, ergibt sich folgende Formel:

SORTIEREN(EINDEUTIG(INDEX(A2:D13;WENN(REST(SEQUENZ(ZEILEN(Daten)*SPALTEN(Daten);1);ZEILEN(Daten))<>0;
REST(SEQUENZ(ZEILEN(Daten)*SPALTEN(Daten);1);ZEILEN(Daten));ZEILEN(Daten));
AUFRUNDEN(SEQUENZ(ZEILEN(Daten)*SPALTEN(Daten);1)/ZEILEN(Daten);0))))

Fazit

Dynamische Arrays erweisen sich in Excel als sehr komfortabel. Diese Knobelaufgabe ist nur ein Beispiel unter vielen. Um diese Knobelaufgabe mit konventionnellen Methoden zu lösen, wäre wahrscheinlich – ich habe es nicht probiert – ein Formelmonster notwendig.

Ein Nebeneffekt der vorgestellten Formel und sehr interessante Erkenntnis ist, dass durch dynamische Arrays, z.B. Spalten aus einem Bereich untereinander ausgegeben werden können.

Leider sind dynamische Arrays nur modernen Excel-Versionen vorbehalten. Eine Abwärtskompatibilität der neuen Funktionen ist in älteren Excel-Versionen nicht gegeben. Aber das war ja auch der Fall, als z.B. in Excel 2010 neue Funktionen eingeführt wurden.

Happy Exceling :-)

• Tweet von Bill Jelen mit der Knobelaufgabe, Englisch
• Video bei YouTube von Bill Jelen mit der Knobelaufgabe, Englisch
• Dynamische Arrays in Excel, MS Support, Deutsch