Excel Funktion
SORTIEREN
für Excel 2007 bis 2019

Download der Exceldatei mit dem VBA-Code der Funktionen
SORTIEREN, SORTIERENNACH, XVERWEIS, XVERGLEICH, FILTER, EINDEUTIG, SEQUENZ, ZUFALLSMATRIX
und allen auf diesen Seiten dargestellten Beispielen:
▸Download der Exceldatei
(Version 3.8 - Letzte Änderung: 24.10.2024 - XVerweis und XVergleich nun fit für den Einsatz in Matrixformeln ▸mehr dazu)

Worum geht es?

In Excel 365 und ab Excel 2021 hat Microsoft die lange vermissten Zellfunktionen SORTIEREN, SORTIERENNACH, FILTER, XVERWEIS und weitere implementiert. Allerdings wurden sie für ältere Excel-Versionen nicht nachgerüstet, so dass alle Benutzer von Excel 2007 bis einschließlich Excel 2019 darauf verzichten mussten.

Mehr lesen...

Die Funktion SORTIEREN

Die Funktion SORTIEREN ermöglicht das Sortieren von bestimmten Bereichen auf dem Tabellenbaltt. Durch ein oder mehrere Spaltennummern/Zeilennummern gibt man an, nach welchen Spalten/Zeilen sortiert werden soll.

Syntax

= Sortieren ( Matrix; [Index]; [Reihenfolge]; [nach_Spalte] )

Parameter	Erläuterung
Matrix	Matrix oder Bereich, der/die sortiert werden soll
Index (optional)	Nummer der Zeile oder der Spalte, nach der sortiert werden soll (Standard: Index = 1)
Reihenfolge (optional)	Sortierreihenfolge  1 für aufsteigend (Standard) -1 für absteigend
Nach_Spalte (optional)	FALSCH  für senkrechtes Sortieren in der Spalte (Standard) WAHR   für waagerechtes Sortieren in der Zeile

Die Funktion Sortieren hat das gleiche Verhalten wie die Excel 365-Funktion SORTIEREN.
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
▸https://support.microsoft.com/de-de/office/sortieren-funktion-22f63bd0-ccc8-492f-953d-c20e8e44b86c

Beispiel:  = Sortieren ($B$3:$F$85; 3; -1)

Der letzte Parameter fehlt. Somit wird der Standardwert FALSCH genommen und es wird senkrecht (in der Spalte) sortiert. Der Bereich B3:F85 wird nach der 3. Spalte (Spalte D) in absteigender Reihenfolge sortiert.

Was Microsoft nicht dokumentiert, ist die Möglichkeit, auch nach mehreren Kriterien zu sortieren. An Stelle einer einzelnen Spaltennummer kann man nämlich auch ein Array von Spaltennummern angeben.

Beispiel:  = Sortieren ($B$3:$F$85; {4;3;1}; {-1;1;1})

Hier wird nach den Spalten 4 (absteigend), 3 (aufsteigend) und 1 (aufsteigend) sortiert.

Die Funktion SORTIERENNACH

Die Funktion SORTIERENNACH ermöglicht ebenfalls das Sortieren von bestimmten Bereichen. An Stelle von Spaltennummern/Zeilennummern gibt man eindimensionale Spalten-/Zeilenbereiche als Sortierkriterien an - je nachdem, ob senkrecht oder waagerecht sortiert werden soll.

Diese Funktion ist flexibler, weil die Sortierkriterien nicht innerhalb des zu sortierenden Bereichs liegen müssen, sondern auch außerhalb liegen können, zum Beispiel auf anderen Tabellenblättern.

Syntax

= Sortierennach ( Matrix; [Nach_Matrix1]; [Reihenfolge1]; [Nach_Matrix2]; [Reihenfolge2]; ... )

Parameter	Erläuterung
Matrix	Matrix oder Bereich, der/die sortiert werden soll
Nach_Matrix1	Matrix oder Bereich, nach der/dem sortiert werden soll
Reihenfolge1 (optional)	Sortierreihenfolge  1 für aufsteigend (Standard) -1 für absteigend
Nach_Matrix2 (optional)	Matrix oder Bereich, nach der/dem sortiert werden soll
Reihenfolge2 (optional)	Sortierreihenfolge  1 für aufsteigend (Standard) -1 für absteigend
. . .

Die Funktion Sortierennach hat das gleiche Verhalten wie die Excel 365-Funktion SORTIERENNACH.
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
▸https://support.microsoft.com/de-de/office/sortierennach-funktion-cd2d7a62-1b93-435c-b561-d6a35134f28f

Beispiel:  = Sortierennach ($B$3:$F$85; $C3:$C85; -1; $E3:$E85; 1; $B3:$B85; -1)

Der Bereich B3:F85 wird zuerst nach Spalte C in absteigender Reihenfolge sortiert (erstes Sortierkriterium). Das zweite und das dritte Sortierkriterium sind die Spalten E (aufsteigend) und B (absteigend).

Die Anzahl der Sortierkriterien (bestehend aus je einem Paar Nach_Matrix und Reihenfolge) ist nicht begrenzt.

Sortieren Beispiel 1 - Sortieren nach einem einzelnen Sortierkriterium

Das folgende Beispiel zeigt die Sortierung einer Mitarbeiterliste nach Umsatz in absteigender Reihenfolge. Die verwendete Formel lautet:
= Sortieren ($B$6:$F$25; 5; -1)

Sortieren Beispiel 2 - Sortieren nach mehreren Sortierkriterien

Das folgende Beispiel zeigt die Sortierung einer Mitarbeiterliste nach Jahr (absteigende Reihenfolge), innerhalb eines Jahres nach Produkt (aufsteigende Reihenfolge) und innerhalb eines Produkts nach Mitarbeiter (aufsteigende Reihenfolge). Die verwendete Formel lautet:
= Sortierennach ($B$6:$F$29; $E$6:$E$29; -1; $D$6:$D$29; 1; $B$6:$B$29)

Spezifikation

Wie oben bereits am Beispiel gezeigt, ist es möglich, bei der Funktion Sortieren für den Parameter 'Index' auch ein Array von Zahlen anzugeben. Diese Zahlen müssen Zeilen- oder Spaltennummern sein, die auf Zeilen bzw. Spalten innerhalb des zu sortierenden Bereichs verweisen. Ansonsten wird der Fehlercode #WERT zurückgegeben.

Bei der Funktion Sortierennach müssen die Parameter Nach_Matrix1, Nach_Matrix2, ... einspaltige oder einzeilige Bereiche sein, deren Länge mit der entsprechenden Dimension des ersten Parameters übereinstimmt. Ist die Länge zu kurz, wird der Fehler #Wert ausgegeben. Ist sie zu lang, werden die überflüssigen Zellen ignoriert.

Optionale Parameter am Ende der Parameterliste können ganz weggelassen werden. In der Mitte der Parameterliste können optionale Parameter dadurch weggelassen werden, dass man nur das Semikolon des Parameters setzt.

Die folgenden beiden Beispiele sortieren den Bereich A1:A9 absteigend:
= Sortieren (A1:A9; ; -1)
= Sortierennach (A1:A9; A1:A9; -1)

Alle Parameter, die eine Bereichsadresse erwarten, akzeptieren auch eine entsprechende Matrix (Array) oder einen Ausdruck, der eine solche Matrix (Array) zurückgibt. Zum Beispiel ist auch folgender Aufruf möglich:
= Sortierennach ($B$6:$B$11; {4;2;1;3;5;6}).
Der zweite Parameter ist dann das erste Sortierkriterium und legt eine feste (statische) Reihenfolge fest. Der erste Name in der Spalte B (der Name in Zelle B6) erscheint an vierter Position, der zweite an zweiter Position, der dritte an erster Position usw.

Das dynamische Überfließen der Werte (engl. spilling) in die benachbarten Zellen je nach Platzbedarf, so wie es in Excel 365 stattfindet, lässt sich leider mit VBA prinzipiell nicht nachbilden. Eine mit VBA programmierte benutzerdefinierte Funktion (UDF) muss als letzte Aktion eine Matrix von Werten zurückgeben. Wohin die zurückgegebenen Werte dann verteilt werden, liegt nicht mehr in der Hand des Programmierers, sondern wird von Excel intern gesteuert.

Ein Hauch von Dynamik lässt sich trotzdem erreichen, wenn man seine zu sortierenden Daten ▸in eine Tabelle verwandelt, so dass man in der Parameterliste der Funktion Sortieren die sog. strukturierten Verweise verwenden kann. Ein kleines Beispiel wird im folgenden Abschnitt vorgestellt.

Verwendung von strukturierten Verweisen

Eine gewisse dynamische Anpassung des sortierten Bereichs an eine Vergrößerung oder Verkleinerung der Ausgangsdaten kann man erreichen, wenn man die Ausgangsdaten in eine Tabelle verwandelt und in der Parameterliste der Funktion Sortieren strukturierte Verweise verwendet.

Im folgenden Beispiel wurden die Ausgangsdaten im Bereich B4:C10 in eine Tabelle namens 'Fond' verwandelt. Der Bereich E5:F14 wurde selektiert, in der Bearbeitungszeile die Formel =Sortieren (Fond) eingegeben und die Eingabe mit STRG-UMSCHALT-EINGABE abgeschlossen.

Der Bereich E5:F14 wurde absichtlich zu groß gewählt. In den Zellen des Bereichs E11:F14 erscheinen nun lauter Fehlercodes #NV. Mit Hilfe der bedingten Formatierung =istNV (E5) und Schriftfarbe Weiß werden diese Fehlercodes ausgeblendet.

Wenn jetzt die Tabelle der Ausgangsdaten nach unten erweitert oder nach oben gekürzt wird, erweitert bzw. verkürzt sich automatisch auch die Tabelle der sortierten Daten.

Zugabe:
Die Funktion 'Sortierennach2'

Die Funktion Sortierennach2 gibt es nicht in Excel 365. Sie ist eine echte Zugabe. Sie hat dieselbe Parameterliste wie Sortierennach.

Sie hat im Unterschied zu Sortierennach einen Zusatzeffekt. Wenn man zum Beispiel senkrecht sortiert und mehrere Sortierkriterien verwendet, wird die Spalte mit dem ersten Sortierkriterium als erste (ganz links) angezeigt, darauf folgt die Spalte mit dem zweiten Sortierkriterium usw. und danach folgen die restlichen Spalten.

Sortierennach2 - Beispiel 1

Das Beispiel 1 zeigt die Sortierung nach drei Sortierkriterien: Jahr, Produkt und Mitarbeiter. Bei normaler Sortierung (mit der Funktion Sortierennach) würde im Ergebnisbereich dieselbe Reihenfolge der Spalten erscheinen wie im Bereich der Originaldaten. Die erste Spalte wäre also 'Mitarbeiter'. Beim Einsatz der Funktion Sortierennach2 dagegen steht in der ersten Spalte das Jahr, in der zweiten das Produkt usw.

Die Funktion Sortierennach2 zeigt also die Spalten in der Reihenfolge der Sortierkriterien an. Entsprechendes gilt für das waagerechte Sortieren.

Sortierennach2 - Beispiel 2

Mit Hilfe der Funktion Sortierennach2 ist ohne größeren Aufwand das folgende Szenario möglich:

1. Die Originaldaten können nach jeder beliebigen Spalte sortiert werden, das heißt in diesem Beispiel nach fünf Sortierkriterien.
2. Jede beliebige Reihenfolge der Sortierkriterien ist mit Hilfe von fünf Auswahlfeldern (Drop-Down-Listen) möglich.
3. Die einzelnen Spalten werden immer in der Reihenfolge der Sortierkriterien angezeigt.

Um nach den fünf Kriterien Jahr, Region, Produkt, Mitarbeiter und Umsatz zu sortieren, kann man die folgende Formel verwenden:
= Sortierennach2 ($B$6:$F$105; $E$6:$E$105; -1; $C$6:$C$105; 1; $D$6:$D$105; 1; $B$6:$B$105; 1; $F$6:$F$105; -1)
Doch damit hätte man sich auf die Reihenfolge der Sortierkriterien festgelegt.

Am Beispiel des ersten Sortierkriteriums soll nun gezeigt werden, wie man diese Reihenfolge variabel macht.

Wenn im Auswahlfeld I2 die Spaltenüberschrift 'Jahr' ausgewählt ist, soll die vierte Spalte der Originaldaten als erstes Sortierkriterium verwendet werden. An Stelle des feststehenden Ausdrucks $E$6:$E$105 brauchen wir einen variablen Ausdruck, der genau diesen Bereich zurückgibt.

Die Funktion BEREICH.VERSCHIEBEN ist da die richtige Wahl. Der Ausdruck BEREICH.VERSCHIEBEN ($B$6; 0; 3; 100; 1) würde uns genau den richtigen Bereich zurückgeben, wäre allerdings noch nicht variabel.

Die ersten drei Parameter bestimmen die linke obere Ecke des zurückgegebenen Bereichs. Von $B$6 geht man 0 Zeilen nach unten und 3 Spalten nach rechts. Die obere linke Ecke wäre also die Zelle $H$6. Die letzten beiden Parameter geben die Höhe und die Breite des zurückgegebenen Bereichs an. Mit der Höhe 100 und der Breite 1 würde also genau die Spalte 'Jahr' der Originaldaten zurückgegeben.

Offenbar haben wir unser Ziel erreicht, wenn wir den dritten Parameter (die Verschiebung der linken oberen Ecke nach rechts) in Abhängigkeit von dem Text in Zelle I2 berechnen. Genau dies macht der Ausdruck VERGLEICH ($I$2; $B$5:$F$5; 0) -1. Die Funktion VERGLEICH sucht im Bereich $B$5:$F$5 (unsere Spaltenüberschriften) nach dem Wort 'Jahr' und gibt uns den Spaltenindex 4 zurück. Wir subtrahieren 1 und sind fertig.

Wir setzen die VERGLEICH-Funktion als dritten Parameter in die Funktion BEREICH.VERSCHIEBEN ein und erhalten den Ausdruck
BEREICH.VERSCHIEBEN ($B$6; 0; VERGLEICH($I$2; $B$5:$F$5; 0) -1; 100; 1).

Im letzten Schritt setzen wir diesen Ausdruck als zweiten Parameter in die Funktion Sortierennach2 ein. Die anderen Sortierkriterien werden ebenfalls durch diesen Ausdruck ersetzt, nur passen wir jeweils die Adresse des Auswahlfeldes entsprechend an. Statt $I$2 steht im zweiten Sortierkriterium $H$2 usw.

Die Parameter, welche die Sortierrichtung (aufsteigend, absteigend) bestimmen, werden noch durch WENN-Funktionen ersetzt:
WENN($I$3="absteigend"; -1; 1).
Aus $I$3 wird beim nächsten Parameter $H$3 usw.

Somit erhalten wir die folgende imposante Formel:

= Sortierennach2 ($B$6:$F$105; BEREICH.VERSCHIEBEN($B$6; 0; VERGLEICH($I$2; $B$5:$F$5; 0) -1; 100; 1); WENN($I$3="absteigend"; -1; 1); BEREICH.VERSCHIEBEN($B$6; 0; VERGLEICH($J$2; $B$5:$F$5; 0) -1; 100; 1); WENN($J$3="absteigend"; -1; 1); BEREICH.VERSCHIEBEN($B$6; 0; VERGLEICH($K$2; $B$5:$F$5; 0) -1; 100; 1); WENN($K$3="absteigend"; -1; 1); BEREICH.VERSCHIEBEN($B$6; 0; VERGLEICH($L$2; $B$5:$F$5; 0) -1; 100; 1); WENN($L$3="absteigend"; -1; 1); BEREICH.VERSCHIEBEN($B$6; 0; VERGLEICH($M$2; $B$5:$F$5; 0) -1; 100; 1); WENN($M$3="absteigend"; -1; 1))

Geschafft!

Zur Entspannung noch eine nette kleine Problemstellung: die Geburtstagsliste.

Geburtstagsliste

Man hat eine Liste mit den Spalten Name und Geburtsdatum. Aus dieser Liste möchte man nun eine Geburtstagsliste erstellen, die vom 1. Januar bis zum 31. Dezember geht.

Die Formel lautet:
= Sortieren (WAHL({1.2}; DATUM(; MONAT(C4:C10); TAG(C4:C10)); $B$4:$B$10))

Die Funktion Sortieren wird mit einem einzigen Parameter aufgerufen. Es ist die Matrix, die von der Funktion WAHL zurückgegeben wird. Die Funktion WAHL erzeugt eine Matrix, die aus zwei Spalten besteht.

Die erste Spalte ist das, was der Ausdruck DATUM(; MONAT(C4:C10); TAG(C4:C10)) zurückgibt. Der erste Parameter der Funktion DATUM fehlt. Dadurch bekommen alle Datumswerte das Jahr 1900. Alle Geburtstage haben also das Jahr 1900 und werden deshalb in der gewünschten Reihenfolge sortiert. Damit das Jahr 1900 in der Geburtstagsliste nicht angezeigt wird, wählt man eine Formatierung, die nur aus Tag und Monat besteht.

Die zweite Spalte ist einfach die Liste der Namen in Spalte B.

Da in der Parameterliste der Funktion Sortieren kein zweiter Parameter angegeben ist, wird der Standardwert 1 verwendet. Das heißt, es wird nach der 1. Spalte sortiert. Und das sind die Geburtstage.

Für Benutzer von Excel 365

Wer Excel 365 verwendet und an den beiden Sonderfunktionen SORTIERENNACH2 und XVERWEIS2 interessiert ist, kann hier eine Exceldatei herunterladen, die nur diese beiden UDFs enthält:
▸Download der Exceldatei
(Version 3.5 - Letzte Änderung: 24.10.2024)