Vorbemerkung
In diesem Tutorial wird das Sortieren von Listen mit Hilfe der in Excel eingebauten Funktionen besprochen - gemeint sind die Funktionen, die man innerhalb von Formeln in die Zellen eingibt. Die sortierte Liste wird also nach jeder Änderung sofort automatisch aktualisiert.
Die Benutzer von Office 365 brauchen dieses Tutorial über das Thema 'Sortieren' nicht. Sie können einfach die neue Funktion SORTIEREN verwenden. Diese Funktion steht auch in Office 2021 zur Verfügung.
NEU:
Ab Ende Januar 2022 brauchen auch Benutzer älterer Excelversionen (Excel 2007 - Excel 2019) dieses Tutorial nicht mehr!
Zumindest für diejenigen, die Microsoft Excel verwenden (nicht Open Office o.a.), gibt es jetzt eine mit VBA (Visual Basic for Applications) programmierte SORTIEREN-Funktion mit genau derselben Parameterliste wie die Excel-365-Version.
Auch die Funktionen SORTIERENNACH, FILTER, XVERWEIS, XVERGLEICH, EINDEUTIG, SEQUENZ und ZUFALLSMATRIX wurden implementiert.
Nähere Informationen und Download hier:
https://hermann-baum.de/excel/hbSort/de/
Wer nicht nur nach einer Lösung gesucht hat, sondern sich in der Anwendung von Excel-Formeln weiterbilden möchte, kann trotzdem gerne in dieses Tutorial hineinschauen.
Überblick
Im einfachsten Fall soll eine Liste von Zahlen sortiert werden. Damit beginnt diese Anleitung. Im zweiten Beispiel wird gezeigt, wie man eine Liste von Namen sortiert. Im dritten Schritt wird eine dreispaltige Liste bestehend aus Mitarbeitern, Abteilung und Überstunden nach Überstunden sortiert. Zum Schluss wird gezeigt, wie man diese Liste der Mitarbeiter nach Abteilung und nach Überstunden sortieren kann (zwei Sortierkriterien).
Download der Excel-Datei mit den Beispielen (Stand: 05.11.2021)
Beispiel 1: Sortieren einer Zahlenliste
Im Bereich B4:B12 haben wir eine Liste von Zahlen, die sortiert werden soll. Die sortierte Liste soll im Bereich D4:D12 erscheinen.
Dazu tragen wir in Zelle D4 die Formel
=KGRÖSSTE($B$4:$B$12;ZEILE(1:1))
ein und kopieren sie nach unten bis zur Zelle D12.
Die Formel =KGRÖSSTE($B$4:$B$12;1) würde aus dem Bereich $B$4:$B$12 die größte Zahl heraussuchen und in die Zelle eintragen. Die Formel =KGRÖSSTE($B$4:$B$12;2) würde die zweitgrößte Zahl ermitteln usw. Um die Zahlen 1, 2, 3, ... beim Nach-Unten-Kopieren der Formel automatisch zu erzeugen, kann man den Ausdruck Zeile(1:1) verwenden. Die Funktion Zeile ermittelt immer die Zeilennummer des in Klammern stehenden Bereichs, wobei hier 1:1 die erste Zeile ist. Der Ausdruck Zeile(1:1) gibt also eine 1 zurück. Beim Nach-Unten-Kopieren wird daraus Zeile(2:2), Zeile(3:3) usw., was den Zahlen 2, 3, ... entspricht. An Stelle von Zeile(1:1) könnte man auch Zeile(A1) schreiben. Das hätte denselben Effekt.
Im Ausdruck $B$4:$B$12 haben die Dollarzeichen vor den Zahlen den Effekt, dass beim Nach-Unten-Kopieren nicht B5:B13 entsteht. Die Dollarzeichen vor dem Buchstaben B bewirken, dass man die Formel auch nach rechts in eine andere Spalte verschieben könnte, ohne dass aus dem B ein C oder etwas Anderes wird.
Beispiel 2: Sortieren einer Namensliste
Wir betrachten zunächst den einfacheren Fall, dass die Liste eindeutig ist, also kein Name mehrfach vorkommt.
2.1 Eindeutige Namensliste
Im Bereich B4:B12 haben wir eine Liste von Namen, die sortiert werden soll. Die sortierte Liste soll im Bereich D4:D12 erscheinen.
Die einfache Lösung mit der Funktion KGRÖSSTE ist hier leider nicht möglich, weil die Funktion KGRÖSSTE nur mit Zahlen funktioniert. Um uns zunächst eine Reihenfolge zu schaffen, verwenden wir die links von der Namensliste liegende Spalte als Hilfsspalte. Sie kann später ausgeblendet werden.
In Zelle A4 tragen wir die Formel
=ZÄHLENWENN($B$4:$B$12;"<="&$B4)
ein und kopieren sie nach unten bis zur Zelle A12.
Die Funktion ZÄHLENWENN zählt im Bereich $B$4:$B$12, wie oft die Bedingung "<="&$B4 erfüllt ist. Das &-Zeichen ist in Excel-Formeln eine Verknüpfung von Texten (Zeichenketten, Strings). Aus "<="&$B4 wird dann "<=Waldecker". Da alle 9 Namen kleiner oder gleich "Waldecker" sind, erzeugt die Formel in Zelle A4 eine 9. In der Zelle A10 werden für den Namen Glatzer 3 Namen gezählt, die kleiner oder gleich "Glatzer" sind, nämlich "Baumann", "Brecht" und "Glatzer" selbst.
In Zelle D4 tragen wir die Formel
=SVERWEIS(ZEILE(1:1);$A$4:$B$12;2;0)
ein und kopieren sie nach unten bis zur Zelle D12.
Die Funktion SVERWEIS sucht im Bereich $A$4:$A$12 nach der Zahl 1 (bzw. 2, 3, 4, ...) und trägt dann den rechts neben der 1 (bzw. 2, 3, 4, ...) stehenden Namen (Bereich $B$4:$B$12) in die D-Spalte ein. Auf diese Weise entsteht eine sortierte Liste.
Einschränkung:
Diese Sortierung arbeitet nur fehlerfrei, wenn kein Name doppelt vorkommt. Sie eignet sich also z. B. für (eindeutige) Artikelnummern wie zum Beispiel "QXW50002-A23". Wenn ein Name zweimal vorkommt, dann entsteht in der Spalte A nicht die Zahlenfolge von 1 bis 9, sondern eine der Zahlen kommt doppelt vor, zum Beispiel die 7. In diesem Fall würde dann die Zahl 8 überhaupt nicht vorkommen und die SVERWEIS-Funktion schreibt bei der Suche nach der 8 den Fehlercode #NV (nicht validierbar) in die Zelle.
2.2 Nicht-eindeutige Namensliste
Wenn Namen mehrfach vorkommen, kann man eine zweite Hilfsspalte hinzuziehen.
In der Spalte A erzeugen wir wieder die Zahlen von 1 bis 9 für die Reihenfolge der Namen. Jedoch addieren wir zu jeder ganzen Zahl einen kleinen Bruchteil bestehend aus der Zeilennummer multipliziert mit 0,0001. Auf diese Weise kommt keine dieser Zahlen doppelt vor.
In Zelle A4 tragen wir die Formel
=ZÄHLENWENN($B$4:$B$12;"<="&$B4)+ZEILE()*0,0001
ein und kopieren sie nach unten bis zur Zelle A12.
Der Ausdruck ZEILE()*0,0001 erzeugt in der Zelle A4 die Zahl 0,0004, weil die Funktion ZEILE() ohne Parameter die Zeilennummer der Zelle, in der sie steht, zurück gibt, also in der Zelle A4 eine 4, in der Zelle A5 eine 5 usw.
In der zweiten Hilfsspalte, Spalte C, erzeugen wir nun wie in Beispiel 1 eine sortierte Liste dieser Zahlen. Da wir diesmal nicht die größte Zahl zuerst haben wollen, sondern die kleinste, verwenden wir nicht die Funktion KGRÖSSTE, sondern die Funktion KKLEINSTE.
In Zelle C4 tragen wir die Formel
=KKLEINSTE($A$4:$A$12;ZEILE(1:1))
ein und kopieren sie nach unten bis zur Zelle C12.
Jetzt sind wir in der Lage, mit Hilfe der SVERWEIS-Funktion aus dem Bereich A4:B12 nacheinander herauszusuchen, welcher Name zur Zahl 1,0005 gehört, welcher zur Zahl 2,0009 gehört usw.
In Zelle C4 tragen wir deshalb die Formel
=SVERWEIS($C4;$A$4:$B$12;2;0)
ein und kopieren sie nach unten bis zur Zelle C12.
Die Hilfsspalten A und C können danach ausgeblendet werden.
2.3 Namensliste mit Lücken
Manchmal hat man es mit einer Liste zu tun, in der es Lücken gibt. Löscht man beispielsweise die Namen "Brecht" und "Meininger" in den Zellen B9 und B12, so dass an diesen Stellen leere Zellen stehen, fängt die sortierte Liste mit zwei Nullen an. Diese Nullen könnte man zwar mit Hilfe der WENN-Funktion in leere Zeichenketten ("") verwandeln, jedoch sieht es besser aus, wenn die leeren Zellen in der sortierten Liste am Ende stehen.
Der Formel in Zelle A4 fügen wir deshalb den Summanden ($B4="")*10000 hinzu.
Wir tragen also in Zelle A4 die Formel
=ZÄHLENWENN($B$4:$B$12;"<="&$B4)+ZEILE()*0,0001+($B4="")*10000
ein und kopieren sie nach unten bis zur Zelle A12.
Beim Summanden ($B4="")*10000 machen wir uns zunutze, dass in Excel WAHR=1 und FALSCH=0 ist. Die Klammer ($B4="") nimmt den Wert 1 an, wenn die Zelle B4 leer ist. In diesem Fall wird die Zahl 10000 addiert. Auf diese Weise werden die Zahlen für leere Zellen sehr groß und rücken in der sortierten Liste (Spalte C) ans Ende.
Um zu vermeiden, dass am Ende der sortierten Namen nun zwei Nullen erscheinen, ergänzen wir die Formeln in Spalte D um eine WENN-Bedingung.
In Zelle D4 tragen wir die Formel
=WENN($C4>10000;"";SVERWEIS($C4;$A$4:$B$12;2;0))
ein und kopieren sie nach unten bis zur Zelle D12.
Die Formeln in Spalte C bleiben unverändert.
Beispiel 3: Sortieren einer Mitarbeiter-Liste nach Überstunden (1 Kriterium)
Wir gehen davon aus, dass eine Liste von Mitarbeitern einer Firma vorliegt. Für jeden Mitarbeiter sind seine Abteilung und seine Überstunden angegeben. Diese Liste soll nun nach dem Kriterium 'Überstunden' sortiert werden. Die Liste ist nicht vollständig gefüllt. Sie beinhaltet leere Zeilen.
In Spalte B erzeugen wir zunächst eine Liste der Überstunden, zu denen ein Bruchteil der Zeilennummer addiert wird, damit alle Zahlen nur einmal vorkommen - ähnlich wie im Beispiel 2. Allerdings addieren wir hier zu $E4 nicht den Ausdruck ZEILE()*0,0001. Denn dies hätte zur Folge, dass Mitarbeiter mit der gleichen Überstundenzahl in der sortierten Liste in umgekehrter Reihenfolge aufgelistet würden. Deshalb addieren wir besser den Ausdruck (1000-ZEILE())*0,0001. Die zusätzliche WENN-Funktion sorgt dafür, dass die Zelle bei nicht eingetragenen Überstunden leer bleibt.
In Zelle B4 tragen wir die Formel
=WENN($E4<>"";$E4+(1000-ZEILE())*0,0001;"")
ein und kopieren sie nach unten bis zur Zelle B20.
In der Spalte F erzeugen wir gemäß Beispiel 1 mit Hilfe der Funktion KGRÖSSTE eine sortierte Liste der Zahlen aus Spalte A.
In Zelle F4 tragen wir die Formel
=WENNFEHLER(KGRÖSSTE($B$4:$B$20;ZEILE(B1));"")
ein und kopieren sie nach unten bis zur Zelle F20.
Die vorgeschaltete Funktion WENNFEHLER verhindert den Fehlereintrag #ZAHL! in den Leerzeilen.
Die Funktion WENNFEHLER erwartet als ersten Parameter das, was eigentlich in die Zelle eingetragen werden soll, und als zweiten Parameter das, was im Falle eines Fehlers in die Zelle eingetragen werden soll (in unserem Fall die leere Zeichenkette "").
Den Rest (Spalten H, I und J) erledigen wir mit SVERWEIS-Funktionen. Damit in den leeren Zeilen keine Nullen erscheinen, wird die SVERWEIS-Funktion in eine WENN-Funktion eingebettet.
In Zelle H4 tragen wir die Formel
=WENN($F4<>"";SVERWEIS($F4;$B$4:$E$20;2;0);"")
ein und kopieren sie nach unten bis zur Zelle H20.
Die Formeln in den Spalten I und J sehen ähnlich aus:
Zelle I4: =WENN($F4<>"";SVERWEIS($F4;$B$4:$E$20;3;0);"")
Zelle J4: =WENN($F4<>"";SVERWEIS($F4;$B$4:$E$20;4;0);"")
Die Hilfsspalten B und F können später ausgeblendet werden.
Beispiel 4: Sortieren einer Mitarbeiter-Liste nach Abteilung und Überstunden (2 Kriterien)
Die Abteilung soll das erste Sortierkriterium sein und die Überstunden das zweite Sortierkriterium. Die Mitarbeiter sollen also zuerst nach Abteilung sortiert werden und innerhalb der Abteilung nach Überstunden.
Um nach der Abteilung zu sortieren, erstellen wir zunächst eine Art Rangzahl für die Abteilungen in Spalte B.
In Zelle B4 tragen wir die Formel
=ZÄHLENWENN($E$4:$E$20;">"&$E4)
ein und kopieren sie nach unten bis zur Zelle B20.
Zur Erklärung siehe Beispiel 2.2.
Die Idee ist, in Spalte A eine Rangzahl zu erzeugen, die über die endgültige Reihenfolge der sortierten Liste entscheidet. Um diese Rangzahl zu bilden, addieren wir wie im Beispiel 3 zur Überstundenzahl einen Bruchteil der Zeilennummer, damit keine Rangzahl doppelt vorkommt. Zusätzlich addieren wir die Rangzahl der Abteilung in Spalte B, diese jedoch mit dem Faktor 1000 multipliziert, weil die Sortierung nach der Abteilung Vorrang haben soll.
In Zelle A4 tragen wir die Formel
=WENN($F4<>"";$F4+$B4*1000+(1000-ZEILE())*0,0001;"")
ein und kopieren sie nach unten bis zur Zelle A20.
Zur Erklärung siehe Beispiel 2.2.
Die vorgeschaltete WENN-Funktion sorgt wieder dafür, dass bei einer leeren Zelle in der Spalte 'Überstunden' (Spalte F) für die Rangzahl nichts eingetragen wird.
Genau wie im Beispiel 3 sortieren wir nun in der Spalte G die Rangzahlen von Spalte A.
In Zelle G4 tragen wir die Formel
=WENNFEHLER(KGRÖSSTE($A$4:$A$20;ZEILE(C1));"")
ein und kopieren sie nach unten bis zur Zelle G20.
Der Rest wird durch die SVERWEIS-Funktion erledigt. In den Spalten I, J und K tragen wir die folgenden Formeln ein:
Zelle I4: =WENN($G4<>"";SVERWEIS($G4;$A$4:$E$20;4;0);"")
Zelle J4: =WENN($G4<>"";SVERWEIS($G4;$A$4:$E$20;5;0);"")
Zelle K4: =WENN($G4<>"";SVERWEIS($G4;$A$4:$F$20;6;0);"")
Alle drei werden nach unten bis zur Zeile 20 kopiert.
Die Hilfsspalten A, B und G können später ausgeblendet werden.
