So sortieren Sie gemischte Zahlen und Text, mehrstufige Zahlen in Excel

Das Tutorial erklärt häufige Probleme bei der Bestellung von alphanumerischen Zeichenfolgen in Excel und bietet Arbeitslösungen für die Sortierung von Zahlen als Text und Text als Zahlen.

Das Sortieren von Text und Zahlen separat in Excel ist so einfach wie ABC oder 123 :) Aber die Wiederherstellung von Produkt-IDs, SKUs und anderen Werten, die sowohl Buchstaben als auch Ziffern enthalten, können Probleme verursachen. Die alphabetische Sortierung von Excel kann einen numerischen Teil einer Zeichenfolge nicht unterscheiden, um sie separat zu verarbeiten. Wenn Sie mehr Kontrolle darüber haben möchten, wie alphanumerische Stiche sortiert werden, schauen Sie sich die folgenden Beispiele an.

• Sortieren von Zahlen als Text
• Sortieren Sie gemischte Zahlen und Text
• Sortieren Sie Strings nach Text und Zahlen gleichzeitig
• Sortieren Sie Multilevel / Hierarchy -Nummern
• Sortieren Sie mehrstufige Zahlen, die mit Text vorangestellt sind

Sortieren Sie Zahlen als Text in Excel

Bei der Sortierung einer Zahlensäule vom kleinsten nach größtes in Excel, anstatt Folgendes zu haben:

1, 2, 3, 11, 12, 13, 110, 120, 130

Sie können manchmal so etwas bekommen:

1, 11, 110, 12, 120, 13, 130, 2, 3

Das passiert, wenn Zahlen tatsächlich Text sind und sie als Text sortieren - wie Wörter, die basierend auf ihren Buchstaben angeordnet sind, werden "Textneuchber" basierend auf ihren Ziffern anstelle ihrer Werte sortiert.

Wenn die Zielspalte als Text formatiert ist, dauert es nur, bis die Zahlen normalerweise sortiert werden, um den Text in die Nummer umzuwandeln. Das Ergebnis ist in Spalte C im Screenshot unten.

Im Gegenteil, wenn Sie Zahlen als Text sortieren möchten, konvertieren Sie Zahlen zuerst in Text und klicken Sie dann auf die Registerkarte Start >

Das Ergebnis ist wie in Spalte A im vorherigen Screenshot.

Wie man gemischte Zahlen und Text in Excel sortiert

Mit der integrierten Excel-Sortierfunktion werden alphanumerische Saiten (kombinierte Text und Zahlen) immer als Text sortiert, dh Buchstaben für Buchstaben, stellig. Führen Sie die folgenden Schritte aus, um eine Zahlenspalte mit einem Buchstabenpräfix oder Suffix als Zahlen zu sortieren. Und hier ist das Ergebnis, das wir erreichen wollen.

Um zu verhindern, dass Excel Zahlen in alphanumerischen Zeichenfolgen als Text bewirkt, werden wir diese Zahlen in einer Helferspalte extrahieren und nach dieser Spalte sortieren. Auf diese Weise können Sie die ursprünglichen Daten unverändert halten, sie jedoch so neu ordnen, wie Sie möchten.

1. Geben Sie in einer Spalte neben den ursprünglichen Werten eine Formel ein, die die Zahl aus der String extrahiert.

   In diesem Beispiel können wir einfach alle Zeichen nach einem Bindestrich ("-") extrahieren. In Excel 365 kann dies unter Verwendung der TextAfter -Funktion erfolgen. Wie bei jeder anderen Textfunktion ist die Ausgabe immer Text, unabhängig davon, ob Sie Buchstaben oder Ziffern abrufen. Daher multiplizieren wir das Ergebnis mit 1, um eine Textzeichenfolge in eine Zahl zu konvertieren.

   =TEXTAFTER(A2, "-")*1

   In älteren Excel -Versionen kann das gleiche Ergebnis unter Verwendung einer Kombination von drei verschiedenen Funktionen erzielt werden, die nach einem bestimmten Zeichen Text extrahieren. Zusätzlich wird die *1 -Operation durchgeführt, um die Textausgabe in die Nummer zu konvertieren:

   =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1

   

   Tipp. Um Zahlen aus jeder Position in einer Zelle schnell zu extrahieren, können Sie das in unserer ultimative Suite enthaltene Extrakt -Tool für Excel verwenden.

   

2. Sortieren Sie die Originaldaten nach den extrahierten Zahlen. Gehen Sie mit den ausgewählten Zahlen auf die Registerkarte Start > Sortieren und Filter > Sortieren Sie kleinste nach größtem . Wählen Sie im Dialogfeld, das auftaucht, die Option Auswahl aus und klicken Sie auf Sortieren . Weitere Details finden Sie in der Sortierung und Haltung von Zeilen zusammen.

   

   Jetzt werden die Zeichenfolgen, die Buchstaben und Ziffern enthalten, als Zahlen sortiert, und Sie können die Helfersäule bei Bedarf entfernen oder ausblenden.

   

Sortieren Sie Saiten nach Text und Zahlen gleichzeitig

Falls Ihre Quellzeichenfolgen einige verschiedene Elemente haben, können Sie jedes Element in einer separaten Spalte extrahieren und dann nach mehreren Spalten sortieren. Infolgedessen werden die Saiten wie in Spalte D im folgenden Screenshot angeordnet:

Da alle Zeichenfolgen in unserem Datensatz das gleiche Muster haben, ist der einfachste Weg, Text und Zahlen in verschiedene Spalten aufzuteilen, indem sie reguläre Ausdrücke verwenden. Dies kann mit Hilfe der benutzerdefinierten Funktion namens Regexpextract erfolgen. Zunächst fügen Sie Ihren Code zu Ihrem Excel hinzu, wie in diesem Tutorial erläutert. Verwenden Sie danach die folgenden Formeln.

Um das erste Auftreten von Text aus Zelle A2 zu extrahieren, lautet die Formel in B2:

=RegExpExtract(A2, "[^\d] ", 1)

Um die erste Zahl zu extrahieren, lautet die Formel in C2:

=RegExpExtract(A2, "\d ", 1)*1

Um die zweite Zahl zu extrahieren, lautet die Formel in D2:

=RegExpExtract(A2, "\d ", 2)*1

In den 2 ^ND- und 3 ^RD -Formeln multiplizieren wir den Regexpextraktionsausgang mit 1, um ein extrahiertes Substring in eine Zahl zu verwandeln.

Die Benutzer unserer Ultimate Suite können die Regex -Tools nutzen, um dasselbe Ergebnis zu erzielen. Sie müssen nur einen weiteren schnellen Betrieb ausführen, um die extrahierten numerischen Zeichenfolgen (Spalten C und D) in Zahlen umzuwandeln:

Sobald der Text und die Zahlen aufgeteilt sind, sortieren Sie Ihren Datensatz nach mehreren Spalten:

1. Wählen Sie alle Spalten aus (A2: D16 in unserem Fall).
2. Klicken Sie auf der Registerkarte "Daten" in der Gruppe Sortier & Filter auf die Schaltfläche Sortieren .
3. Fügen Sie so viele Sortierstufen hinzu, wie Sie benötigen, und wählen Sie die gewünschte Sortierreihenfolge.
4. Wenn Sie fertig sind, klicken Sie auf OK .

In unserem Fall wird der ausgewählte Bereich zunächst nach Text von a bis z sortiert, dann nach Nummer 1 und dann nach Nummer 2 von kleinsten bis größten:

Das Ergebnis ist genau das, wonach wir gesucht haben:

Tipp. Um die Saiten anders neu zu arrangieren, ändern Sie die Reihenfolge der Ebenen im Sortierdialogfeld .

So sortieren Sie mehrstufige / Hierarchie -Zahlen in Excel

In Microsoft Excel sind Multilevel -Zahlen wie 1.1, 1.1.1, 1.1.2 Zeichenfolgen, und sie sortieren als Text, keine Zahlen:

Um mehrstufige Zeichenfolgen als Zahlen zu sortieren, können Sie eine benutzerdefinierte benutzerdefinierte Funktion mit der folgenden Syntax verwenden:

Hierarchynumber (Bereich, del, max_level)

Wo:

• Reichweite ist die Reichweite, die sortiert werden muss.
• Del ist der Trennzeichen, der zur Trennung der Pegel verwendet wird, typischerweise ein Punkt (.).
• Max_level ist die maximale Anzahl von Ebenen in den Hierarchiezahlen.

Und hier ist der Code der Funktion:

Benutzerdefinierte Funktion zum Sortieren von Hierarchiezahlen

Funktion Hierarchynumber (Bereich als Bereich, del als String , max_level als ganzzahl ) als doppeltem dim als variante part = split (Bereich.Value, del) für Index = 0 bis Ubound (Teil) Hierarchynumber = Hierarchynumber -Teil (Index) * (10 ^ (max_level) * 2) Nächste Index Endfunktion

Die Funktion erzeugt Zahlen, die es ermöglichen, mehrstufige numerische Zeichenfolgen zu sortieren. Genauer gesagt teilt es eine Hierarchie -Zeichenfolge in einzelne Zahlen (Teile) auf, verarbeitet jeden Teil basierend auf dem folgenden Algorithmus und addiert die Ergebnisse:

part(Index) * (10 ^ ((level - Index) * 2))

Zum Beispiel erzeugt die Funktion für 1.1 dieses Ergebnis:

1*(10^((2-0)*2)) 1*(10^((2-1)*2)) = 10100

Für 1.2 ist es:

1*(10^((2-0)*2)) 2*(10^((2-1)*2)) = 10200

So sortieren Sie Hierarchie -Nummern mit der benutzerdefinierten Funktion:

1. Fügen Sie den Code der Hierarchynumberfunktion in ein Standard-Code-Modul Ihrer Arbeitsmappe ein und speichern Sie ihn als makro-fähige Arbeitsmappe (.xlsm). Die detaillierten Anweisungen finden Sie hier.
2. Geben Sie in einer leeren Zelle neben der ersten Hierarchie -Zeichenfolge eine Hierarchynumberformel ein und drücken Sie die Eingabetaste .
3. Ziehen Sie die Formel nach Bedarf über so viele Zeilen.
4. Sortieren Sie Ihren Datensatz basierend auf der Spalte der Formeln.

Angenommen, Sie haben in Spalte A mehrstufige numerische Zeichenfolgen mit maximal 3 Stufen. Die Formel für B2 lautet:

=HierarchyNumber(A2, ".", 3)

Nachdem Sie die Formel kopiert haben, erhalten Sie dieses Ergebnis:

Danach sortieren Sie den gesamten Datensatz (A2: B19) nach der Formel -Spalte von kleinsten bis größten:

Und holen Sie sich die multi-Level-Zahlen als Zahlen sortiert:

Sortieren Sie mehrstufige Zahlen, die mit Text vorangestellt sind

In diesem Beispiel wird ein etwas anderer Fall erörtert, in dem mehrstufige Nummern mit einem Text vorangestellt sind, beispielsweise "Stufe 1.1.1". Auf den ersten Blick klingt die Aufgabe komplizierter, aber in der Tat ist die Lösung einfacher :)

Zunächst extrahieren Sie mehrstufige numerische Substrings in eine separate Spalte. Zu diesem Zweck können Sie das Extrakt -Tool verwenden, das in unserer ultimativen Suite enthalten ist:

Oder Sie können eine Formel schreiben, um alle Zeichen nach einem Raum zu ziehen:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Und dann sortieren benutzerdefinierte. Fügen Sie im Dialogfeld Excel Sortier zwei Ebenen hinzu - zuerst nach der extrahierten Hierarchie -Nummer und dann nach der ursprünglichen Zeichenfolge - und klicken Sie auf OK:

Die folgende Warnung wird angezeigt, in der Sie " Zahlen und Zahlen, die als Text separat gespeichert sind " auswählen.

Infolgedessen werden die Saiten, die Multi-Level-Zahlen enthalten, nach Zahlen sortiert:

So sortieren Sie numerische Saiten und Multi-Level-Zahlen in Excel. Vielen Dank für das Lesen und sehen Sie Sie nächste Woche in unserem Blog!

Verfügbare Downloads

Sortieren Sie gemischte Zahlen und Text in Excel - Beispiele (.xlsm -Datei) Ultimate Suite Vollfunktionale Testversion (.exe -Datei)

Das obige ist der detaillierte Inhalt vonSo sortieren Sie gemischte Zahlen und Text, mehrstufige Zahlen in Excel. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!