Beitrag aus SmartTools Excel Weekly
Nachtrag: Alle Einträge aus einem Bereich mit mehreren Zeilen und Spalten sortieren
Excel 365 2024 2021 2019 2016 2013
FRAGE Sie hatten kürzlich eine Leserfrage beantwortet, in der es um die Sortierung eines zweidimensionalen Tabellenbereichs ging. Die Einträge sollten nicht zeilenweise nach irgendwelchen Spalten sortiert werden, sondern alle Einträge sollten einzeln betrachtet werden und in einer sortierten, einspaltigen Liste ausgegeben werden. Ich habe versucht, Ihre Sortierformel einzusetzen. Leider funktioniert sie in meiner Excel-Version aber nicht. Gibt es auch eine Alternative für ältere Excel-Versionen?
O. Blankenburg
ANTWORT Die Formel, die wir in dem damaligen Helpline-Beitrag vorgestellt hatten, funktioniert tatsächlich nur in Excel 365 und Excel 2024. In älteren Excel-Versionen ist es ein wenig komplizierter.
Da es keine Funktion gibt, mit der Sie einen zweidimensionalen Bereich in eine einspaltige Liste umwandeln können, müssen Sie für diesen Zweck mit einer Hilfsspalte arbeiten. Anschließend können Sie auf Basis dieser Hilfsspalte die Sortierung erreichen. Aber auch dafür gibt es keine simple Funktion. In älteren Excel-Versionen müssen Sie zum Sortieren eine komplexe Arrayformel erstellen. Aber Schritt für Schritt.
Angenommen, die Einträge, die Sie sortieren wollen, stehen im Bereich A4:C9 (drei Spalten, sechs Zeilen). Als erstes müssen Sie diese Einträge in eine einspaltige Liste umwandeln. Dafür soll Spalte E als Hilfsspalte dienen. Beginnen Sie mit der Ausgabe in Zelle E4 und geben Sie darin folgende Formel ein:
=INDEX($A$4:$C$9;REST(ZEILE(A1)-1;
ZEILEN($A$4:$C$9))+1;QUOTIENT(ZEILE(A1)-1;
ZEILEN($A$4:$C$9))+1)
Wenn Sie die Formel an Ihr eigenes Tabellenmodell anpassen wollen, ersetzen Sie die drei Vorkommen von "$A$4:$C$9" durch die absolute Adresse des Bereichs, in dem Ihre zu sortierenden Werte stehen. Sollten Ihre Werte zum Beispiel im Bereich A3:F27 stehen, ersetzen Sie in der Formel "$A$4:$C$9" durch "$A$3:$F$27". Die Verweise auf Zelle A1 dürfen Sie jedoch nicht ändern.
Kopieren Sie die Formel dann nach unten, und zwar in so viele Zellen, wie der zu sortierende Bereich Zellen enthält. Der Beispielbereich A4:C9 besteht aus drei Spalten und sechs Zeilen, also aus 3 * 6 = 18 Zellen. Somit müssten Sie die Formel in Zelle E4 hinunter bis in Zelle E21 kopieren.
Sie erhalten auf diese Weise eine einspaltige Liste aller Einträge aus dem Ursprungsbereich. Diese Liste lässt sich nun in einer weiteren Spalte in alphabetischer Reihenfolge ausgeben.
In unserem Beispiel geben Sie dazu in Zelle F4 eine Arrayformel ein, das heißt, eine Formel, die Sie nicht einfach nur mit Return, sondern mit Strg + Umschalt + Return abschließen. Die Arrayformel lautet:
{=WENNFEHLER(INDEX($E$4:$E$21;VERGLEICH(ZEILEN(A$1:A1);
ZÄHLENWENNS($E$4:$E$21;"<="&$E$4:$E$21;$E$4:$E$21;
"<>0");0));"")}
Die geschweiften Klammern müssen Sie nicht selbst eingeben. Die fügt Excel automatisch hinzu, wenn Sie die Formeleingabe mit Strg + Umschalt + Return beenden.
Um die Formel an Ihr eigenes Tabellenmodell anzupassen, ersetzen Sie die Adresse "$E$4:$E$21" durch die absolute Adresse des Bereichs, in dem Sie die weiter oben vorgestellte INDEX-Formel eingegeben haben. Die Verweise auf Zelle A1 lassen Sie wie oben unverändert.
Schließlich kopieren Sie die Formel nach unten, und zwar in genau soviele Zeilen wie die erste INDEX-Formel. Im Beispiel kopieren Sie die Formel nach unten bis in Zelle F21.
Das Ergebnis ist eine einspaltige Liste, in der die Einträge aus dem Ursprungsbereich in alphabetischer Reihenfolge aufgeführt werden.
Der Weg ist zwar deutlich komplizierter als in Excel 365 oder Excel 2024. Aber möglich ist die Sortierung auch in älteren Excel-Versionen. Und Sie müssen die Formeln auch nur einmal eingeben. Sobald sich Werte im Ursprungsbereich ändern, passt sich automatisch die sortierte Liste an.