Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Daten in Excel und Google Sheets dynamisch nach einer benutzerdefinierten Sortierreihenfolge sortieren.
SORTBY-Funktion in Excel 365
Die SORTBY-Funktion erzeugt ein dynamisches Array sortierter Daten.
In diesem Beispiel verwenden wir die SORTBY-Funktion, um eine Datentabelle zu nehmen und ein dynamisches Array zu erzeugen, das sie sortiert nach Mannschaft nach a Benutzerdefinierte Sortierliste:
| 1 | =SORTIEREN NACH(B3:D8,VERGLEICH(B3:B8,F3:F5,0)) |
Notiz: Die SORTBY-Funktion wird nur in Zelle H3 geschrieben. Der Rest der Tabelle wird automatisch ausgefüllt.
Die sortierte Tabelle ist dynamisch, was bedeutet, dass, wenn einer der Mannschaft oder Spielername Werte ändern oder aufzeichnen oder hinzugefügt / gelöscht werden, dann wird die Ausgabetabelle automatisch in der Größe angepasst und neu sortiert.
Notiz: Die SORTBY-Funktion ist in Excel 2022 oder früher nicht verfügbar. Lesen Sie unten die Sortieroptionen, die für frühere Versionen von Excel verfügbar sind.
Um zu erklären, wie diese Formel funktioniert, teilen wir sie in Schritte auf:
Unsere endgültige Formel lautet:
| 1 | =SORTIEREN NACH(B3:D8,VERGLEICH(B3:B8,F3:F5,0)) |
Zuerst definieren wir die Datentabelle, die sortiert werden soll. Dies wird B3:D8 sein.
Als nächstes müssen wir die Sortierreihenfolge definieren. Wir werden die MATCH-Funktion verwenden, um die Reihenfolge zu finden, in der jeder Mannschaft Name erscheint in der Benutzerdefinierte Sortierliste. Dieser Teil der Formel lautet:
| 1 | =VERGLEICH(B3:B8,F3:F5,0) |
Wenn wir die Werte aus den Zellbereichen hinzufügen, erhalten wir:
| 1 | =MATCH({"Blau"; "Rot"; "Gelb"; "Rot"; "Blau"; "Gelb"}, {"Gelb"; "Blau"; "Rot"}, 0) |
Die MATCH-Funktion ersetzt alle Mannschaft Namen mit der Reihenfolge, in der sie in der Benutzerdefinierte Sortierliste, mit einer genauen Textübereinstimmung:
| 1 | ={2; 3; 1; 3; 2; 1} |
Dieses Array von Werten wird dann von der Hauptfunktion SORTBY als Sortierreihenfolge verwendet:
| 1 | =SORTIEREN (B3:D8, {2; 3; 1; 3; 2; 1} ) |
Die sortierte Tabelle wird dann erzeugt
Benutzerdefinierte Sortierliste - Pre-Excel 365
Da die SORTBY-Funktion Excel-Benutzern vor der Excel 365-Version nicht zur Verfügung steht, zeigen wir nun, wie Sie dieselbe Aufgabe mit einem manuelleren Prozess ausführen.
Das integrierte Excel-Sortierwerkzeug kann verwendet werden, um nach a zu sortieren Benutzerdefinierte Sortierliste durch Auswahl der 'Benutzerdefinierte Liste… ' aus den Sortierreihenfolgeoptionen im Sortierwerkzeug:
Eine neue Benutzerdefinierte Liste sollte als kommagetrennte Liste hinzugefügt werden. In diesem Beispiel fügen wir hinzu: ‘Gelb, Blau, Rot’
Die Benutzerdefinierte Liste wird dann in der Sortierreihenfolge-Auswahl angezeigt
Die Datentabelle wird dann sortiert nach Benutzerdefinierte Liste Werte
Beachten Sie, dass diese benutzerdefinierte Sortierreihenfolge in der Arbeitsmappe gespeichert wird und erneut verwendet werden kann. Wenn Sie kein hinzufügen möchten Benutzerdefinierte Liste zu Excel, dann kann stattdessen eine Methode mit einer Hilfsspalte ausprobiert werden.
Benutzerdefinierte Sortierliste mit Hilfsspalte - Pre-Excel 365
In diesem nächsten Beispiel verwenden wir die MATCH-Funktion in einer Hilfsspalte, um a Benutzerdefinierte Sortierreihenfolge unter Bezugnahme auf a Benutzerdefinierte Sortierliste:
| 1 | =SPIEL(B3,$G$3:$G$5,0) |
Beachten Sie die Verwendung des $-Symbols, wenn Sie auf die Benutzerdefinierte Sortierliste. Bitte lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu diesem Thema zu erfahren.
Nachdem die Hilfsspalte hinzugefügt wurde, kann ein manueller Sortierprozess mit dem Sortierassistent:
Dies erzeugt dann die Tabelle sortiert nach dem Benutzerdefinierte Sortierreihenfolge:
Wenn Änderungen an den Mannschaft oder Benutzerdefinierte Sortierliste Werte, dann muss der Sortierassistent erneut ausgeführt werden, um die Sortierreihenfolge der Datentabelle zu aktualisieren.
Benutzerdefinierte Sortierliste mit Formel in Google Tabellen
Die SORTBY-Funktion ist in Google Sheets nicht verfügbar, aber ihre 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 eine Datentabelle zu verwenden und ein dynamisches Array zu erstellen, das sortiert Mannschaft Werte durch a Benutzerdefinierte Sortierliste:
| 1 | =SORTIEREN(B3:D8,VERGLEICH(B3:B8,F3:F5,0),WAHR) |
Die SORT-Funktion von Google Sheets 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
