Benutzerdefinierte Sortierliste mit Formel – Excel & Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie Daten in Excel und Google Sheets nach einer benutzerdefinierten Sortierreihenfolge dynamisch sortieren können.
Die Funktion SORTIERENNACH in Excel 365
Die SORTIENNACH-Funktion erzeugt ein dynamisches Array mit sortierten Daten.
In diesem Beispiel verwenden wir die SORTIERENNACH-Funktion, um aus einer Datentabelle ein dynamisches Array zu erstellen, das nach einer benutzerdefinierten Sortierliste nach Team sortiert ist:
=SORTIERENNACH(B3:D8;VERGLEICH(B3:B8;F3:F5;0))
Hinweis: Die Funktion SORTIERENNACH wird nur in Zelle H3 eingetragen. Der Rest der Tabelle wird automatisch ausgefüllt.
Die sortierte Tabelle ist dynamisch, d. h., wenn sich die Werte für Team oder Spieler ändern oder Datensätze hinzugefügt oder gelöscht werden, wird die Ausgabetabelle automatisch neu sortiert und in der Größe angepasst.
Hinweis: Die Funktion SORTIERENNACH ist in Excel 2019 oder früheren Versionen nicht verfügbar. Lesen Sie unten, welche Sortieroptionen für frühere Excel-Versionen verfügbar sind.
Um zu erklären, wie diese Formel funktioniert, müssen wir sie in einzelne Schritte unterteilen:
Unsere endgültige Formel lautet:
=SORTIERENNACH(B3:D8;VERGLEICH(B3:B8;F3:F5;0))Zuerst definieren wir die Datentabelle, die sortiert werden muss. Dies wird B3:D8 sein.
Als nächstes müssen wir die Sortierreihenfolge festlegen. Wir werden die VERGLEICH-Funktion verwenden, um die Reihenfolge zu finden, in der jeder Team in der benutzerdefinierten Sortierliste erscheint. Dieser Teil der Formel lautet:
=VERGLEICH(B3:B8;F3:F5;0)Wenn wir die Werte aus den Zellenbereichen hinzufügen, erhalten wir:
=VERGLEICH({"Blau"; "Rot"; "Gelb"; "Rot"; "Blau"; "Gelb"}; {"Gelb"; "Blau"; "Rot"}; 0)Die VERGLEICH-Funktion ersetzt alle Teams durch die Reihenfolge, in der sie in der benutzerdefinierten Sortierliste erscheinen, wobei eine exakte Textübereinstimmung verwendet wird:
={2; 3; 1; 3; 2; 1}Diese Reihe von Werten wird dann von der Hauptfunktion SORTIERENNACH als Sortierreihenfolge verwendet:
=SORTIERENNACH(B3:D8, {2; 3; 1; 3; 2; 1} )Die sortierte Tabelle wird dann erstellt.
Benutzerdefinierte Sortierliste – Vor Excel 365
Da die SORTIERENNACH-Funktion für Excel-Benutzer vor der 365-Version nicht zur Verfügung steht, zeigen wir nun, wie man die gleiche Aufgabe mit einem manuellen Verfahren durchführen kann.
Das integrierte Excel-Sortierwerkzeug kann verwendet werden, um nach einer benutzerdefinierten Sortierliste zu sortieren, indem die Option Benutzerdefinierte Liste aus den Sortieroptionen im Sortiertool ausgewählt wird:
Eine neue benutzerdefinierte Liste sollte als kommagetrennte Liste hinzugefügt werden. In diesem Beispiel fügen wir „Gelb, Blau, Rot“ hinzu.
Die benutzerdefinierte Liste wird dann in der Auswahl der Sortierreihenfolge angezeigt.
Die Datentabelle wird dann nach den Werten in der benutzerdefinierten Liste sortiert.
Beachten Sie, dass diese benutzerdefinierte Sortierreihenfolge in der Arbeitsmappe gespeichert wird und wieder verwendet werden kann. Wenn Sie keine benutzerdefinierte Liste zu Excel hinzufügen möchten, können Sie stattdessen eine Methode mit einer Hilfsspalte ausprobieren.
Benutzerdefinierte Sortierliste mit Hilfsspalte – Vor Excel 365
Im folgenden Beispiel wird die VERGLEICH-Funktion in einer Hilfsspalte verwendet, um eine benutzerdefinierte Sortierreihenfolge zu definieren, indem auf eine benutzerdefinierte Sortierliste verwiesen wird:
=VERGLEICH(B3;$G$3:$G$5;0)
Achten Sie darauf, dass Sie das $-Symbol verwenden, wenn Sie auf die benutzerdefinierte Sortierliste verweisen. Bitte lesen Sie unseren Artikel über das Sperren von Zellbezügen, um mehr über dieses Thema zu erfahren.
Nachdem die Hilfsspalte hinzugefügt wurde, kann ein manueller Sortiervorgang mit Hilfe des Sortierassistenten durchgeführt werden:
Dadurch wird die Tabelle nach der benutzerdefinierten Sortierreihenfolge sortiert:
Wenn Änderungen an den Werten von Team oder der benutzerdefinierten Sortierliste vorgenommen werden, muss der Sortierassistent erneut ausgeführt werden, um die Sortierreihenfolge der Datentabelle zu aktualisieren.
Benutzerdefinierte Sortierliste mit Formel in Google Sheets
Die SORTIERENNACH-Funktion ist in Google Sheets nicht verfügbar, aber die SORT-Funktion kann stattdessen verwendet werden und ist leistungsfähiger als die SORT-Funktion in Excel 365. Sie ermöglicht es uns, benutzerdefinierte Sortierlisten zu verwenden, um Datenbereiche dynamisch zu sortieren.
In diesem Beispiel verwenden wir die SORT-Funktion von Google Sheets, um aus einer Datentabelle ein dynamisches Array zu erstellen, das die Team-Werte nach einer benutzerdefinierten Sortierliste sortiert:
=SORT(B3:D8;VERGLEICH(B3:B8;F3:F5;0);WAHR)
Die Google Sheets-Funktion SORT sortiert Daten nach den angegebenen Spaltennummern oder benutzerdefinierten Sortierreihenfolgen in den Eingabedaten und ermöglicht es uns, festzulegen, ob die Sortierreihenfolge aufsteigend ist oder nicht.
