In meinem Blogbeitrag zur Funktion ZEILENUMBRUCH vom 22.10.2022 waren die umzuwandelnden Daten in Spalte B nach Datum sortiert. Was aber, wenn die Ausgangsdaten nicht chronologisch dastehen? Wie lassen sich die Einträge in der dreispaltigen Liste, die mit ZEILENUMBRUCH entsteht, nach Datum sortieren? Dafür kombiniere ich ZEILENUMBRUCH mit SORTIEREN. Ich zeige hier zwei Lösungsvarianten.
Die gestapelten Daten in Spalte B sind nicht nach Datum sortiert
Variante A: Die dreispaltige Liste mit zwei Formeln zusammensetzen
Bei der ersten Variante baue ich die gewünschte dreispaltige Liste in zwei Schritten auf:
- Zuerst erzeuge ich per Formel die drei Spaltenüberschriften,
- anschließend die nach Datum sortierten Einträge in drei Spalten.
Schritt 1: Die Spaltenüberschriften mit MTRANS erzeugen
Die drei Einträge für die Spaltenüberschriften, die im Bereich B2:B4 untereinander stehen, lasse ich wie folgt in einer Zeile anzeigen.
- Ich klicke auf Zelle D2, die der Ausgangspunkt der dreispaltigen Liste werden soll.
In dieser Zelle gebe ich folgende Formel ein: =MTRANS(B2:B4). - Mit MTRANS wandele ich hier den vertikalen Zellbereich B2:B4 in den horizontalen Bereich von D2 bis F2 um.
Die Spaltenüberschriften nicht vertikal, sondern horizontal anordnen mit MTRANS
Schritt 2: Das Ergebnis von ZEILENUMBRUCH nach Datum sortieren
Unterhalb der Spaltenüberschriften erzeuge ich nun ab Zelle D3 die dreispaltige Liste, die automatisch nach Datum sortiert wird. Dazu baue ich die ZEILENUMBRUCH-Funktion wie folgt in eine SORTIREN-Funktion ein:
- ZEILENUMBRUCH(B5:B58;3) ist das erste Argument in der SORTIEREN-Funktion. Es gibt an, welche Matrix sortiert werden soll.
- Das zweite Argument der SORTIEREN-Funktion ist der Sortierindex, also die Information, nach welcher Spalte die Einträge zu sortieren sind. Hier trage ich einen 1 ein, denn das Datum steht in Spalte 1 der zu erzeugenden Matrix.
- Im dritten Argument der SORTIEREN-Funktion lege ich mit 1 fest, dass die Einträge aufsteigend sortiert werden sollen.
Die komplette Formel in D3 lautet: =SORTIEREN(ZEILENUMBRUCH(B5:B58;3);1;1).
Die dreispaltige Liste nach Datum sortieren: ZEILENUMBRUCH in SORTIEREN einbauen
Die entstandene dreispaltige Liste formatiere ich anschließend wie im Blogbeitrag vom 22.10.2022 beschrieben.
Variante B: Mit nur einer Formel die dreispaltige Liste aufbauen
Lassen sich die Daten aus Spalte B mit nur einer Formel in eine dreispaltige, nach Datum sortierte Liste umwandeln? Die Antwort lautet JA. Möglich wird dies, wenn ich die Funktionen ZEILENUMBRUCH und SORTIEREN noch mit VSTAPELN kombiniere.
Profi-Technik: Überschriften und Daten mit einer einzigen Formel sortiert anordnen
Aus den untereinander gestapelten Daten in Spalte B erzeuge ich ab Zelle D2 die dreispaltige sortierte Liste wie folgt:
- Mit der Anweisung ZEILENUMBRUCH(B2:B4;3) schreibe ich die Spaltenüberschriften des Zellbereichs B2:B4 in eine Zeile mit drei Spalten.
Hinweis: Die Spaltenüberschriften muss ich separat erzeugen, weil sie sich nicht nach Datum sortieren lassen. - Danach ordne ich mit SORTIEREN(ZEILENUMBRUCH(B5:B58;3);1;1) die verbleibenden Daten aus Spalte B in Zeilen mit je drei Spalten sortiert nach Datum an.
- Beide Bereiche – die Spaltenüberschriften und die darunterliegenden sortierten Daten – liste ich dann mit VSTAPELN untereinander auf.
Die komplette Formel in Zelle D2 lautet bei dieser Variante:
=VSTAPELN(ZEILENUMBRUCH(B2:B4;3);SORTIEREN(ZEILENUMBRUCH(B5:B58;3);1;1)).
VSTAPELN, ZEILENUMBRUCH und SORTIEREN kombinieren, um mit einer Formel die sortierte Liste zu erzeugen
Interesse an weiteren dynamischen Arrayfunktionen?
Hier im Blog wird es ganz sicher weitere Beiträge zu den neuen Matrixfunktionen in Excel 365 geben. Wer sich mit allen Neuerungen von Excel 365 vertraut machen möchte, kann sich schon mal den Termin der Excel-Kompetenztage vormerken: 22. bis 24. Mai 2023.
