Excel -Sortierfunktion - automatische Sortierdaten mithilfe der Formel

Das Tutorial zeigt, wie die Sortierfunktion verwendet wird, um Datenarrays dynamisch zu sortieren. Sie lernen eine Formel, um alphabetisch in Excel zu sortieren, Zahlen in aufsteigender oder absteigender Reihenfolge zu ordnen, nach mehreren Spalten und mehr zu sortieren.

Die Sortierfunktionalität gibt es schon lange. Mit der Einführung dynamischer Arrays in Excel 365 schien jedoch eine erstaunlich einfache Möglichkeit zu sein, mit Formeln zu sortieren. Das Schöne an dieser Methode ist, dass die Ergebnisse automatisch aktualisieren, wenn sich die Quelldaten ändert.

• Excel -Sortierfunktion
• Grundlegende Sortierformel
• So sortieren Sie Daten in Excel mithilfe von Formel - Beispielen
  • Excel -Sortierung nach Spalte
  • Sortieren Sie in verschiedenen Reihenfolge nach mehreren Spalten
  • Sortieren und filtern Sie in Excel
  • Sortieren und nutzen Sie N obere oder untere Werte
• So erhalten Sie Sortierarray, um automatisch zu erweitern
• Excel -Sortierfunktion funktioniert nicht

Excel -Sortierfunktion

Die Sortierfunktion in Excel sortiert den Inhalt eines Arrays oder Bereichs nach Spalten oder Zeilen in aufsteigender oder absteigender Reihenfolge.

Die Sortierung gehört zur Gruppe der dynamischen Array -Funktionen. Das Ergebnis ist ein dynamisches Array, das je nach Form des Quellarrays automatisch in benachbarte Zellen oder horizontal verspricht.

Die Syntax der Sortierfunktion lautet wie folgt:

Sort (array, [sort_index], [sort_order], [by_col])

Wo:

Array (erforderlich) - ist ein Array von Werten oder ein Zellenbereich, um zu sortieren. Dies können Werte sein, einschließlich Text, Zahlen, Daten, Zeiten usw.

SORT_INDEX (optional) - eine Ganzzahl, die angibt, welche Spalte oder Zeile nach Sortieren sind. Wenn es ausgelassen wird, wird der Standardindex 1 verwendet.

Sort_order (optional) - definiert die Sortierreihenfolge:

• 1 oder ausgelassen (Standard) - aufsteigender Reihenfolge, dh vom kleinsten zum größten
• -1 - absteigende Ordnung, dh vom größten bis kleinsten

BY_COL (optional) - Ein logischer Wert, der die Sortierrichtung angibt:

• Falsch oder weggelassen (Standard) - sortieren nach Zeile. Sie werden diese Option die meiste Zeit verwenden.
• Richtig - Sortieren nach Spalte. Verwenden Sie diese Option, wenn Ihre Daten in Spalten wie in diesem Beispiel horizontal organisiert sind.

Excel -Sortierfunktion - Tipps und Notizen

Sort ist eine neue dynamische Array -Funktion und hat als solches einige Spezifitäten, die Sie kennen sollten:

• Derzeit ist die Sortierfunktion nur in Microsoft 365 und Excel 2021 erhältlich. Excel 2019, Excel 2016, unterstützt keine dynamischen Array -Formeln, sodass die Sortierfunktion in diesen Versionen nicht verfügbar ist.
• Wenn das von einer Sortierformel zurückgegebene Array das Endergebnis ist (dh nicht an eine andere Funktion übergeben), erzeugt Excel dynamisch einen Bereich für angemessene Größe und fülle ihn mit den sortierten Werten. Stellen Sie also sicher, dass Sie immer genügend leere Zellen oder/und rechts von der Zelle, in der Sie die Formel eingeben, immer ein #Spill -Fehler auftritt.
• Die Ergebnisse aktualisieren dynamisch, wenn sich die Quelldaten ändert. Das an die Formel gelieferte Array erstreckt sich jedoch nicht automatisch auf neue Einträge, die außerhalb des referenzierten Arrays hinzugefügt werden. Um solche Elemente einzuschließen, müssen Sie entweder die Array -Referenz in Ihrer Formel aktualisieren oder den Quellbereich in eine Tabelle konvertieren, wie in diesem Beispiel gezeigt, oder einen dynamischen benannten Bereich erstellen.

Grundlegende Excel -Sortierformel

Dieses Beispiel zeigt eine grundlegende Formel zum Sortieren von Daten in Excel in aufsteigender und absteigender Reihenfolge.

Angenommen, Ihre Daten sind alphabetisch angeordnet, wie im folgenden Screenshot gezeigt. Sie möchten Zahlen in Spalte B sortieren, ohne Daten zu brechen oder zu mischen.

Formel zur Sortierung in aufsteigender Reihenfolge

Um die Werte in Spalte B von kleinsten bis größtem zu sortieren, ist hier die zu verwendende Formel:

=SORT(A2:B8, 2, 1)

Wo:

• A2: B8 ist das Quellarray
• 2 ist die Spaltennummer, nach der sortiert wird
• 1 ist die aufsteigende Sortierreihenfolge

Da unsere Daten in Zeilen organisiert sind, kann das letzte Argument in die Standardeinstellung nach falschem Sortieren nach Zeilen weggelassen werden.

Geben Sie einfach die Formel in jeder leeren Zelle (D2 in unserem Fall) ein, drücken Sie die Eingabetaste und die Ergebnisse werden automatisch auf D2: e8 verschüttet.

Formel zur Sortierung in absteigender Reihenfolge

Setzen Sie das Argument Sort_order auf -1 wie folgt, um Daten abfällt, dh vom größten zu kleinsten zu sortieren:

=SORT(A2:B8, 2, -1)

Geben Sie die Formel in die obere linke Zelle des Zielbereichs ein und Sie erhalten dieses Ergebnis:

In ähnlicher Weise können Sie Textwerte in alphabetischer Reihenfolge von A bis Z oder Z nach A sortieren.

So sortieren Sie Daten in Excel mithilfe der Formel

Die folgenden Beispiele zeigen einige typische Verwendungen der Sortierfunktion in Excel und einige nicht triviale.

Excel -Sortierung nach Spalte

Wenn Sie Daten in Excel sortieren, ändern Sie zum größten Teil die Reihenfolge der Zeilen. Wenn Ihre Daten jedoch horizontal mit Zeilen mit Etiketten und Spalten mit Datensätzen organisiert sind, müssen Sie möglicherweise von links nach rechts sortieren und nicht von oben nach unten.

Um die Spalte in Excel zu sortieren, setzen Sie das By_col -Argument auf true. In diesem Fall wird Sort_index eine Zeile darstellen, keine Spalte.

Zum Beispiel, um die folgenden Daten nach qty zu sortieren. Verwenden Sie diese Formel vom höchsten bis niedrigsten:

=SORT(B1:H2, 2, 1, TRUE)

Wo:

• B1: H2 sind die Quelldaten, die sortieren sollen
• 2 ist der Sortierindex, da wir Zahlen in der zweiten Reihe sortieren
• -1 gibt die absteigende Sortierreihenfolge an
• Wahre Mittel, um Spalten zu sortieren, nicht Zeilen

Sortieren Sie nach mehreren Spalten in unterschiedlicher Reihenfolge (Multi-Level-Sortierung)

Wenn Sie mit komplexen Datenmodellen arbeiten, benötigen Sie möglicherweise häufig eine mehrstufige Art. Kann das mit einer Formel geschehen? Ja, leicht! Was Sie tun, ist, Array -Konstanten für die Argumente Sort_index und Sort_order zu liefern.

Zum Beispiel, um die folgenden Daten zuerst nach Region (Spalte A) von a bis z und dann nach qty zu sortieren. (Spalte C) Setzen Sie die folgenden Argumente vom kleinsten bis größten fest:

• Array sind die Daten in A2: C13.
• Sort_index ist die Array -Konstante {1,3}, da wir zuerst nach Region (1 ^ST -Spalte) und dann nach qty sortieren. (3 ^RD -Säule).
• Sort_order ist die Array-Konstante {1, -1}, da die erste Spalte in aufsteigender Reihenfolge und die 3- ^RD -Spalte in absteigender Reihenfolge sortiert werden soll.
• BY_COL wird weggelassen, weil wir Zeilen sortieren, was standardmäßig ist.

Wenn wir die Argumente zusammenfügen, erhalten wir diese Formel:

=SORT(A2:C13, {1,3}, {1,-1})

Und es funktioniert perfekt! Die Textwerte in der ersten Spalte sind alphabetisch sortiert und die Zahlen in der dritten Spalte von größten bis kleinsten:

Sortieren und filtern Sie in Excel

Wenn Sie Daten mit einigen Kriterien filtern und die Ausgabe in Ordnung bringen, verwenden Sie die Sortier- und Filterfunktionen zusammen:

Sort (filter (array, criteria_range = kriterien ), [sort_index], [sort_order], [by_col])

Die Filterfunktion erhält eine Reihe von Werten, die auf den von Ihnen definierten Kriterien basieren und dieses Array an das erste Argument der Sorte übergeben.

Das Beste an dieser Formel ist, dass sie auch die Ergebnisse als dynamischen Verschüttungsbereich ausgibt, ohne dass Sie die Strg -Eingabetaste drücken müssen, oder erraten Sie, wie viele Zellen sie kopieren können. Wie üblich geben Sie eine Formel in die oberste Zelle ein und drücken die Eingabetaste.

Beispielsweise extrahieren wir Elemente mit einer Menge, die gleich oder mehr als 30 (> = 30) aus den Quelldaten in A2: B9 entspricht, und ordnen die Ergebnisse in aufsteigender Reihenfolge an.

Dafür haben wir zuerst die Bedingung in Zelle E2 wie im Bild unten gezeigt eingerichtet. Und dann bauen Sie unsere Excel -Sortierformel auf diese Weise auf:

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

Abgesehen von Array , das durch die Filterfunktion erzeugt wird, geben wir nur das Argument SORT_INDEX an (Spalte 2). Die verbleibenden zwei Argumente werden weggelassen, da die Standardeinstellungen genau so funktionieren, wie wir es brauchen (sortieren Sie nach Zeile aufsteigend).

Holen Sie sich n größte oder kleinste Werte und sortieren Sie die Ergebnisse

Bei der Analyse riesiger Masse bei Informationen besteht häufig die Notwendigkeit, eine bestimmte Anzahl von Top -Werten zu extrahieren. Vielleicht nicht nur extrahieren, sondern sie auch in der gewünschten Reihenfolge anordnen. Und idealerweise wählen Sie, welche Spalten in die Ergebnisse einbezogen werden sollen. Klingt schwierig? Nicht mit den neuen dynamischen Array -Funktionen!

Hier ist eine generische Formel:

Index (sort (…), sequence ( n ), { column1_to_return , column2_to_return ,…})

Wobei n die Anzahl der Werte ist, die Sie zurückgeben möchten.

Nehmen wir im folgenden Datensatz an, dass Sie eine Top -3 -Liste basierend auf den Zahlen in Spalte C erhalten möchten.

Um es erledigt zu haben, sortieren Sie zuerst das Array A2: C13 nach der 3 ^-Rd -Spalte in absteigender Reihenfolge:

SORT(A2:C13, 3, -1)

Und dann die obige Formel im ersten ( Array- ) Argument der Indexfunktion nisten, damit das Array vom höchsten auf kleinste sortiert wird.

Für das zweite Argument ( row_num ), das angibt, wie viele Zeilen zurückgegeben werden sollen, generieren Sie die erforderlichen sequentiellen Zahlen mithilfe der Sequenzfunktion. Da wir 3 Top -Werte benötigen, verwenden wir Sequenz (3), die gleich in der Formel eine vertikale Arraykonstante {1; 2; 3} liefert.

Für das dritte Argument ( COL_NUM ), das definiert, wie viele Spalten zurückgeben werden sollen, liefern Sie die Spaltennummern in Form einer horizontalen Array -Konstante. Wir möchten die Spalten B und C zurückgeben, also verwenden wir das Array {2,3}.

Schließlich erhalten wir die folgende Formel:

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

Und es liefert genau die Ergebnisse, die wir wollen:

Um 3 Bottomwerte zurückzugeben, sortieren Sie einfach die Originaldaten vom kleinsten auf das größte. Ändern Sie dafür das Argument Sort_order von -1 auf 1:

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

Geben Sie einen sortierten Wert in einer bestimmten Position zurück

Wenn Sie aus einem anderen Blickwinkel nur eine bestimmte Sortierposition zurückgeben möchten? Sagen Sie, nur der 1., nur der 2. oder nur der 3. Datensatz aus der sortierten Liste? Verwenden Sie die oben diskutierte vereinfachte Version der oben diskutierten Index -Sortierformel: Um es erledigt zu haben:

Index (sort (…), n , { column1_to_return , column2_to_return ,…})

Wobei n die Position des Interesses ist.

Verwenden Sie beispielsweise eine bestimmte Position von oben (dh aus den Daten sortiert ab, die abfällt) diese Formel:

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

Verwenden Sie diese, um eine bestimmte Position von unten zu erhalten (dh aus den Daten sortiert aufsteigend):

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

Wenn A2: C13 Quelldaten ist, ist F1 die Position von oben, I1 ist die Position von unten und {2,3} sind die Spalten zurückgegeben.

Verwenden Sie die Excel -Tabelle, um das Sortierarray automatisch zu erweitern

Wie Sie bereits wissen, aktualisieren die sortierten Array -Updates automatisch, wenn Sie Änderungen an den Originaldaten vornehmen. Dies ist das Standardverhalten aller dynamischen Array -Funktionen, einschließlich Sortier. Wenn Sie jedoch neue Einträge außerhalb des referenzierten Arrays hinzufügen, sind sie nicht automatisch in einer Formel enthalten. Wenn Sie möchten, dass Ihre Formel auf solche Änderungen reagiert, konvertieren Sie den Quellbereich in eine voll funktionsfähige Excel-Tabelle und verwenden Sie strukturierte Referenzen in Ihrer Formel.

Um zu sehen, wie es in der Praxis funktioniert, betrachten Sie bitte das folgende Beispiel.

Angenommen, Sie verwenden die folgende Excel -Sortierformel, um Werte im Bereich A2: B8 in alphabetischer Reihenfolge zu ordnen:

=SORT(A2:B8, 1, 1)

Dann geben Sie einen neuen Eintrag in Zeile 9 ein… und sind enttäuscht, dass der neu hinzugefügte Eintrag außerhalb des Verschüttungsbereichs ausgelassen wird:

Konvertieren Sie nun den Quellbereich in eine Tabelle. Wählen Sie dazu einfach Ihren Bereich einschließlich der Spaltenüberschriften (A1: B8) aus und drücken Sie Strg t . Wählen Sie beim Erstellen Ihrer Formel den Quellbereich mit der Maus aus, und der Tabellenname wird automatisch in die Formel eingefügt (dies wird als strukturierte Referenz bezeichnet):

=SORT(Table1, 1, 1)

Wenn Sie einen neuen Eintrag direkt unter der letzten Zeile eingeben, erweitert die Tabelle automatisch und die neuen Daten werden in den Spill -Bereich der Sortierformel enthalten:

Excel -Sortierfunktion funktioniert nicht

Wenn Ihre Sortierformel zu einem Fehler führt, ist dies höchstwahrscheinlich aus den folgenden Gründen.

#NAME ERROR: ältere Excel -Version

Sort ist eine neue Funktion und funktioniert nur in Excel 365 und Excel 2021. In älteren Versionen, in denen diese Funktion nicht unterstützt wird, wird ein #-Name? Fehler treten auf.

#Spill -Fehler: Etwas blockiert Spill -Bereich

Wenn ein oder mehrere Zellen im Verschüttungsbereich nicht vollständig leer sind oder zusammengeführt werden, ist eine #Spill! Fehler wird angezeigt. Um es zu beheben, entfernen Sie einfach die Blockade. Weitere Informationen finden Sie in Excel #spill! Fehler - Was es bedeutet und wie man behebt.

#Value -Fehler: Ungültige Argumente

Immer wenn Sie auf einen #Value stoßen! Fehler, überprüfen Sie die Argumente SORT_INDEX und SORT_ORDER . Sort_index sollte die Anzahl der Spalten nicht überschreiten, und Sort_order sollte entweder 1 (aufsteigend) oder -1 (absteigend) sein.

#REF ERROR: Die Quell -Arbeitsmappe ist geschlossen

Da dynamische Arrays nur begrenzte Referenzen zwischen Arbeitsbüchern unterstützen, müssen beide Dateien geöffnet werden. Wenn die Quell -Arbeitsmappe geschlossen ist, werfen eine Formel einen #REF! Fehler. Um dies zu beheben, öffnen Sie einfach die referenzierte Datei.

So sortieren Sie Daten in Excel mithilfe der Formel. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Sortieren in Excel mit Formeln (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel -Sortierfunktion - automatische Sortierdaten mithilfe der Formel. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!