Das Tutorial erklärt die Syntax und die typischen Verwendungszwecke der neuen SORTBY-Funktion für dynamische Arrays. Erfahren Sie, wie Sie in Excel mit einer Formel benutzerdefiniert sortieren, eine Liste zufällig sortieren, Zellen nach Textlänge anordnen und vieles mehr.
Microsoft Excel bietet eine Reihe von Möglichkeiten zum Anordnen Textdaten alphabetisch, datiert chronologischund Zahlen vom kleinsten zum größten oder vom höchsten zum niedrigsten. Es gibt auch eine Möglichkeit, nach Ihren eigenen zu sortieren benutzerdefinierte Listen. Zusätzlich zur herkömmlichen Sortierfunktion bietet Excel 365 eine brandneue Methode zum Sortieren von Daten mit Formeln – sehr bequem und unglaublich einfach zu bedienen!
Excel SORTBY-Funktion
Die SORTBY-Funktion in Excel dient zum Sortieren eines Bereichs oder Arrays basierend auf den Werten in einem anderen Bereich oder Array. Die Sortierung kann nach einer oder mehreren Spalten erfolgen.
SORTBY ist einer von sechs neuen dynamische Array-Funktionen verfügbar in Microsoft Excel 365. Das Ergebnis ist ein dynamisches Array, das in benachbarte Zellen übertragen wird und automatisch aktualisiert wird, wenn sich die Quelldaten ändern.
Die SORTBY-Funktion hat eine variable Anzahl von Argumenten – die ersten beiden sind erforderlich und die anderen sind optional:
SORTBY (Array, by_array1, [sort_order1], [by_array2, sort_order2],…)
Array (erforderlich) – Der Bereich der zu sortierenden Zellen oder Wertefelder.
By_array1 (erforderlich) – der Bereich oder das Array, nach dem sortiert werden soll.
Sort_order1 (optional) – die Sortierreihenfolge:
- 1 oder weggelassen (Standard) – aufsteigend
- -1 – absteigend
By_array2 /. Sort_order2,… (Optional) – zusätzliche Array / Order-Paare zum Sortieren.
Wichtige Notiz! Derzeit ist die SORTBY-Funktion nur für den monatlichen Kanal von Microsoft 365-Abonnements verfügbar. Ab Juli 2020 ist die Aufnahme in den halbjährlichen Kanal geplant. In Excel 2019, Excel 2016 und früheren Versionen ist die SORTBY-Funktion nicht verfügbar.
SORTBY-Funktion – 4 Dinge, an die Sie sich erinnern sollten
Damit eine Excel SORTBY-Formel ordnungsgemäß funktioniert, sind einige wichtige Punkte zu beachten:
- By_array Argumente sollten entweder eine Zeile hoch oder eine Spalte breit sein.
- Das Array und alles by_array Argumente müssen kompatible Dimensionen haben. Wenn Sie beispielsweise nach zwei Spalten sortieren, Array, by_array1 und by_array2 sollte die gleiche Anzahl von Zeilen haben; sonst a #WERT Fehler wird passieren.
- Wenn das von SORTBY zurückgegebene Array das Endergebnis ist (Ausgabe in einer Zelle und nicht an eine andere Funktion übergeben), erstellt Excel eine Dynamik Überlaufbereich und füllt es mit den Ergebnissen. Stellen Sie also sicher, dass Sie unten und / oder rechts von der Zelle, in die Sie die Formel eingeben, genügend leere Zellen haben, andernfalls a # SPILL-Fehler wird passieren.
- Die Ergebnisse der SORTBY-Formeln werden automatisch aktualisiert, wenn sich die Quelldaten ändern. Neue Einträge, die außerhalb des Arrays hinzugefügt werden, auf das in der Formel verwiesen wird, sind jedoch nicht in den Ergebnissen enthalten, es sei denn, Sie aktualisieren das Array Referenz. Damit das referenzierte Array automatisch erweitert wird, konvertieren Sie den Quellbereich in einen Excel-Tabelle oder erstellen Sie eine dynamischer benannter Bereich.
Grundlegende SORTBY-Formel in Excel
Hier ist ein typisches Szenario für die Verwendung einer SORTBY-Formel in Excel:
Angenommen, Sie haben eine Liste von Projekten mit dem Wert Feld. Sie möchten die Projekte nach ihrem Wert auf einem separaten Blatt sortieren. Da andere Benutzer die Zahlen nicht sehen müssen, möchten Sie die Zahlen lieber nicht einschließen Wert Spalte in den Ergebnissen.
Die Aufgabe kann einfach mit der SORTBY-Funktion ausgeführt werden, für die Sie die folgenden Argumente angeben:
- Array ist A2: A10 – da Sie das nicht wünschen Wert Spalte, die in den Ergebnissen angezeigt werden soll, lassen Sie sie aus dem Array heraus.
- By_array1 ist B2: B10 – sortieren nach Wert.
- Sort_order1 ist -1 – absteigend, d. h. vom höchsten zum niedrigsten.
Wenn wir die Argumente zusammenfassen, erhalten wir diese Formel:
=SORTBY(A2:B10, B2:B10, -1)
Der Einfachheit halber verwenden wir die Formel auf demselben Blatt – geben Sie sie in D2 ein und drücken Sie die Taste Eingeben Schlüssel. Die Ergebnisse „verschütten“ automatisch auf so viele Zellen wie nötig (in unserem Fall D2: D10). Technisch gesehen befindet sich die Formel jedoch nur in der ersten Zelle. Wenn Sie sie aus D2 löschen, werden alle Ergebnisse gelöscht.
Bei Verwendung auf einem anderen Blatt nimmt die Formel die folgende Form an:
=SORTBY(Sheet1!A2:A10, Sheet1!B2:B10, -1)
Wo Blatt1 ist das Arbeitsblatt mit den Originaldaten.
Verwenden der SORTBY-Funktion in Excel – Formelbeispiele
Im Folgenden finden Sie einige weitere Beispiele für die Verwendung von SORTBY in Excel 365, die sich hoffentlich als nützlich und aufschlussreich erweisen werden.
Nach mehreren Spalten sortieren
Die oben diskutierte Grundformel sortiert Daten nach einer Spalte. Aber was ist, wenn Sie eine weitere Sortierstufe hinzufügen müssen?
Angenommen, unsere Beispieltabelle enthält zwei Felder: Status (Spalte B) und Wert (Spalte C)möchten wir zuerst sortieren nach Status alphabetisch und dann von Wert absteigend.
Um nach zwei Spalten zu sortieren, fügen wir einfach ein weiteres Paar der hinzu by_array /. Sortierung Argumente:
- Array ist A2: C10 – dieses Mal möchten wir alle drei Spalten in die Ergebnisse aufnehmen.
- By_array1 ist B2: B10 – sortieren Sie zuerst nach Status.
- Sort_order1 ist 1 – alphabetisch von A bis Z sortieren.
- By_array2 ist C2: C10 – dann sortieren nach Wert.
- Sort_order2 ist -1 – sortiere vom größten zum kleinsten.
Als Ergebnis erhalten wir die folgende Formel:
=SORTBY(A2:B10, B2:B10, 1, C2:C10, -1)
Was unsere Daten genau so umordnet, wie wir es angewiesen haben:
Benutzerdefinierte Sortierung in Excel mit einer Formel
Um Daten in einer benutzerdefinierten Reihenfolge zu sortieren, können Sie entweder Excel verwenden Benutzerdefinierte Sortierung Erstellen oder erstellen Sie eine SORTBY MATCH-Formel auf folgende Weise:
SORTBY (Array, MATCH (range_to_sort, benutzerdefinierte Liste, 0))
Wenn Sie sich unseren Datensatz genauer ansehen, werden Sie es wahrscheinlich bequemer finden, die Projekte nach ihrem Status „logisch“ zu sortieren, z. nach Wichtigkeit und nicht alphabetisch.
Dazu erstellen wir zunächst eine benutzerdefinierte Liste in der gewünschten Sortierreihenfolge (In Bearbeitung, Abgeschlossen, In Wartestellung) Geben Sie jeden Wert in eine separate Zelle im Bereich E2: E4 ein.
Und dann liefern wir unter Verwendung der obigen generischen Formel den Quellbereich für Array (A2: C10), die Status Spalte für range_to_sort (B2: B10) und die benutzerdefinierte Liste, für die wir erstellt haben benutzerdefinierte Liste (E2: E4).
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))
Als Ergebnis haben wir die Projekte genau nach Bedarf nach ihrem Status sortiert:
Um nach benutzerdefinierter Liste in umgekehrter Reihenfolge zu sortieren, geben Sie -1 für die ein sort_order1 Streit:
=SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0), -1)
Und Sie haben die Projekte in die entgegengesetzte Richtung sortiert:
Möchten Sie zusätzlich Datensätze in jedem Status sortieren? Kein Problem. Fügen Sie der Formel einfach eine weitere Sortierstufe hinzu, z. B. bis Wert (C2: C10) und definieren Sie die gewünschte Sortierreihenfolge, in unserem Fall aufsteigend:
=SORTBY(A2:C10, MATCH(B2:B10, E2:E5, 0), 1, C2:C10, 1)
Ein großer Vorteil der SORTBY-Formel gegenüber der benutzerdefinierten Sortierfunktion von Excel besteht darin, dass die Formel automatisch aktualisiert wird, wenn sich die Originaldaten ändern, während die Funktion bei jeder Änderung bereinigt und neu sortiert werden muss.
Wie diese Formel funktioniert:
Wie bereits erwähnt, kann die SORTBY-Funktion von Excel nur „Sortieren nach“ -Arrays verarbeiten, deren Abmessungen mit dem Quellarray kompatibel sind. Da unser Quellarray (C2: C10) 9 Zeilen enthält und die benutzerdefinierte Liste (E2: E4) nur 3 Zeilen enthält, können wir es nicht direkt an das liefern by_array Streit. Stattdessen verwenden wir die SPIEL Funktion zum Erstellen eines 9-zeiligen Arrays:
MATCH(B2:B10, E2:E5, 0)
Hier verwenden wir die Status Spalte (B2: B10) als Suchwerte und unsere benutzerdefinierte Liste (E2: E5) als Sucharray. Das letzte Argument wird auf 0 gesetzt, um nach genauen Übereinstimmungen zu suchen. Als Ergebnis erhalten wir ein Array von 9 Zahlen, die jeweils die relative Position einer bestimmten Zahl darstellen Status Wert in der benutzerdefinierten Liste:
{1;3;2;1;3;2;2;1;2}
Dieses Array geht direkt an die by_array Argument der SORTBY-Funktion und zwingt sie, die Daten in der Reihenfolge zu platzieren, die den Elementen des Arrays entspricht, d. h. zuerst Einträge, die durch Einsen dargestellt werden, dann Einträge, die durch Einsen dargestellt werden, und so weiter.
Zufällige Sortierung in Excel mit einer Formel
In früheren Versionen von Excel können Sie mit dem eine zufällige Sortierung durchführen RAND Funktion wie in diesem Tutorial erklärt: So sortieren Sie eine Liste in Excel nach dem Zufallsprinzip.
In Excel 365 können Sie eine leistungsstärkere verwenden RANDARRAY Funktion zusammen mit SORTBY:
SORTIERE NACH(Array, RANDARRAY (REIHEN (Array)))
Wo Array sind die Quelldaten, die Sie mischen möchten.
Diese generische Formel funktioniert für eine Liste, die aus einer einzelnen Spalte besteht, sowie für einen mehrspaltigen Bereich.
Verwenden Sie beispielsweise diese Formel, um eine Liste in A2: A10 zufällig zu sortieren:
=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))
Verwenden Sie Folgendes, um Daten in A2: C10 zu mischen und die Zeilen zusammenzuhalten:
=SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10)))
Wie diese Formel funktioniert:
Die RANDARRAY-Funktion erzeugt ein Array von Zufallszahlen, die zum Sortieren verwendet werden sollen, und Sie übergeben es in der by_array Argument von SORTBY. Um anzugeben, wie viele Zufallszahlen generiert werden sollen, zählen Sie die Anzahl der Zeilen im Quellbereich mithilfe der ROWS-Funktion und „geben“ diese Zahl an die Reihen Argument von RANDARRAY. Das ist es!
Sortieren Sie die Zellen nach Zeichenfolgenlänge
Verwenden Sie die Taste, um Zellen nach der Länge der darin enthaltenen Textzeichenfolgen zu sortieren LEN-Funktion um die Anzahl der Zeichen in jeder Zelle zu zählen und die berechneten Längen an die zu liefern by_array Argument von SORTBY. Das Sortierung Das Argument kann abhängig von der bevorzugten Sortierreihenfolge entweder auf 1 oder -1 gesetzt werden.
So sortieren Sie nach Textzeichenfolge vom kleinsten zum größten:
SORTBY (Array, LEN (Array), 1)
So sortieren Sie nach Textzeichenfolge vom größten zum kleinsten:
SORTBY (Array, LEN (Array), -1)
Und hier ist eine Formel, die diesen Ansatz für reale Daten demonstriert:
=SORTBY(A2:A7, LEN(A2:A7), 1)
Wobei A2: A7 die ursprünglichen Zellen sind, die Sie nach Textlänge in aufsteigender Reihenfolge sortieren möchten:
SORTBY vs. SORT
In der Gruppe der neuen Funktionen für dynamische Excel-Arraysgibt es zwei zum Sortieren. Nachfolgend listen wir die wichtigsten Unterschiede und Ähnlichkeiten sowie den Zeitpunkt auf, zu dem sie am besten verwendet werden können.
- Im Gegensatz zur SORT-Funktion muss bei SORTBY das Array „Sortieren nach“ weder Teil des Quell-Arrays sein, noch muss es in den Ergebnissen angezeigt werden. Wenn Sie also einen Bereich anhand eines anderen unabhängigen Arrays oder einer benutzerdefinierten Liste sortieren möchten, ist SORTBY die richtige Funktion. Wenn Sie einen Bereich nach seinen eigenen Werten sortieren möchten, ist SORTIEREN besser geeignet.
- Beide Funktionen unterstützen mehrere Sortierebenen und können mit anderen dynamischen Arrays und herkömmlichen Funktionen verkettet werden.
- Beide Funktionen stehen nur Excel 365-Benutzern zur Verfügung.
Excel SORTBY-Funktion funktioniert nicht
Falls Ihre SORTBY-Formel einen Fehler zurückgibt, liegt dies höchstwahrscheinlich an einem der folgenden Gründe.
Ungültige by_array-Argumente
Das by_array Argumente müssen eine einzelne Zeile oder eine einzelne Spalte sein und in der Größe mit dem kompatibel sein Array Streit. Zum Beispiel, wenn Array hat 10 Zeilen, by_array sollte auch 10 Zeilen enthalten. Ansonsten ein #WERT! Fehler tritt auf.
Ungültige sort_order-Argumente
Das Sortierung Argumente können nur 1 (aufsteigend) oder -1 (absteigend) sein. Wenn kein Wert festgelegt ist, wird SORTBY standardmäßig in aufsteigender Reihenfolge verwendet. Wenn ein anderer Wert festgelegt ist, wird ein #WERT! Fehler wird zurückgegeben.
Es ist nicht genügend Platz für Ergebnisse vorhanden
Wie jede andere dynamische Array-Funktion verschüttet SORTBY die Ergebnisse in einen automatisch anpassbaren und aktualisierbaren Bereich. Wenn nicht genügend leere Zellen vorhanden sind, um die vollen Zellen aufzunehmen Überlaufbereich, eine #SPILL! Fehler wird angezeigt.
Quellarbeitsmappe ist geschlossen
Wenn eine SORTBY-Formel auf eine andere Excel-Datei verweist, müssen beide Arbeitsmappen geöffnet sein. Wenn die Quellarbeitsmappe geschlossen ist, wird ein #REF! Fehler tritt auf.
Ihre Excel-Version unterstützt keine dynamischen Arrays
Bei Verwendung in einer vordynamischen Version von Excel (außer Excel 365) gibt die SORT-Funktion ein #NAME? Error.
So verwenden Sie die SORTBY-Funktion in Excel, um benutzerdefinierte Sortierungen und andere Aufgaben auszuführen. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!
Übungsarbeitsbuch zum Download
Excel SORTBY-Formeln (XLSX-Datei)
Sie könnten auch interessiert sein an:
Der Beitrag Excel SORTBY-Funktion – benutzerdefinierte Sortierung mit Formel erschien zuerst auf Anfänger Kurse.
from
https://fuer-anfaenger.info/excel-sortby-funktion-benutzerdefinierte-sortierung-mit-formel/
No comments:
Post a Comment