So sortieren Sie Daten in Excel mit VBA (eine Schritt-für-Schritt-Anleitung)

Excel bietet bereits einige Möglichkeiten, Daten schnell zu sortieren.

Sie können einen Datensatz ganz einfach sortieren, indem Sie die Sortiersymbole in der Multifunktionsleiste oder im Sortierdialogfeld verwenden.

Warum müssen Sie dann wissen, wie dies mit VBA geht?

Zu wissen, wie Daten mit VBA sortiert werden, kann hilfreich sein, wenn sie Teil Ihres Codes sind. Angenommen, Sie erhalten täglich/wöchentlich einen Datensatz, den Sie in einer bestimmten Reihenfolge formatieren und sortieren müssen.

Sie können ein Makro erstellen, das all dies mit einem einzigen Klick für Sie erledigt. Das spart Ihnen jedes Mal viel Zeit und Mühe.

Wenn Sie Excel-Dashboards erstellen, können Sie die Sortierfunktion von Excel auf eine neue Ebene heben, indem Sie dem Benutzer ermöglichen, die Daten einfach durch Doppelklicken auf die Kopfzeile zu sortieren (wie unten gezeigt).

Wie Sie dies erstellen, werde ich später in diesem Tutorial behandeln. Lassen Sie uns zunächst schnell die Grundlagen klären.

Verstehen der Range.Sort-Methode in Excel VBA

Beim Sortieren mit VBA müssen Sie die Range.Sort-Methode in Ihrem Code verwenden.

Der „Bereich“ sind die Daten, die Sie sortieren möchten. Wenn Sie beispielsweise die Daten in A1:A10 sortieren, wäre "Range" Range("A1:A10").

Sie können auch einen benannten Bereich erstellen und diesen anstelle der Zellbezüge verwenden. Wenn ich zum Beispiel einen benannten Bereich ‚DataRange‘ für die Zellen A1:A10 erstelle, kann ich auch Range("DataRange") verwenden.

Bei der Sortiermethode müssen Sie einige zusätzliche Informationen über Parameter bereitstellen. Nachfolgend sind die wichtigsten Parameter aufgeführt, die Sie kennen müssen:

• Taste - Hier müssen Sie die Spalte angeben, die Sie sortieren möchten. Wenn Sie beispielsweise Spalte A sortieren möchten, müssen Sie key:=Range("A1") verwenden.
• Befehl - hier legen Sie fest, ob die Sortierung aufsteigend oder absteigend erfolgen soll. Wenn Sie beispielsweise in aufsteigender Reihenfolge sortieren möchten, verwenden Sie Order:=xlAscending
• Header - hier legen Sie fest, ob Ihr Datensatz Header hat oder nicht. Bei Headern beginnt die Sortierung ab der zweiten Zeile des Datensatzes, ansonsten ab der ersten Zeile. Um anzugeben, dass Ihre Daten Header haben, verwenden Sie Header:=xlYes

Obwohl diese drei in den meisten Fällen ausreichen, können Sie in diesem Artikel mehr über die Parameter lesen.

Sehen wir uns nun an, wie Sie die Range.Sort-Methode in VBA verwenden, um Daten in Excel zu sortieren.

Sortieren einer einzelnen Spalte ohne Kopfzeile

Angenommen, Sie haben eine einzelne Spalte ohne Kopfzeile (wie unten gezeigt).

Sie können den folgenden Code verwenden, um sie in aufsteigender Reihenfolge zu sortieren.

Sub SortDataWithoutHeader() Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub

Beachten Sie, dass ich den Datenbereich manuell als Range("A1:A12") angegeben habe.

Für den Fall, dass sich die Daten ändern und Werte hinzugefügt/gelöscht werden können, können Sie den folgenden Code verwenden, der sich automatisch basierend auf den gefüllten Zellen im Datensatz anpasst.

Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub

Beachten Sie, dass ich anstelle von Range("A1:A12") Range("A1", Range("A1").End(xlDown)) verwendet habe.

Dadurch wird die letzte nacheinander gefüllte Zelle in der Spalte überprüft und in die Sortierung einbezogen. Falls Leerzeichen vorhanden sind, werden nur Daten bis zur ersten leeren Zelle berücksichtigt.

Sie können auch einen benannten Bereich erstellen und diesen benannten Bereich anstelle der Zellbezüge verwenden. Wenn der benannte Bereich beispielsweise DataSet ist, sieht Ihr Code jetzt wie unten gezeigt aus.

Sub SortDataWithoutHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub

Lassen Sie mich nun kurz die Parameter erklären, die in den obigen Beispielen verwendet wurden:

• Key1:=Range("A1") - A1 angegeben, damit der Code weiß, welche Spalte zu sortieren ist.
• Order1:=xlAscending - Die Reihenfolge wurde als xlAscending angegeben. Wenn Sie möchten, dass es in absteigender Reihenfolge angezeigt wird, verwenden Sie xlDescending.
• Header:= xlNo - Gibt an, dass keine Header vorhanden sind. Dies ist auch der Standardwert. Selbst wenn Sie dies weglassen, werden Ihre Daten sortiert, da sie keine Kopfzeilen haben.

Sie fragen sich, wo Sie diesen VBA-Code einfügen und wie das Makro ausgeführt werden soll? Lesen Sie dieses Tutorial!

Sortieren einer einzelnen Spalte mit Kopfzeile

Im vorherigen Beispiel hatte der Datensatz keinen Header.

Wenn Ihre Daten Header haben, müssen Sie dies im Code angeben, damit die Sortierung ab der zweiten Zeile des Datasets beginnen kann.

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Unten ist der Code, der die Daten in absteigender Reihenfolge basierend auf den Verkäufen der Geschäfte sortiert.

Sub SortDataWithHeader() Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending End Sub

Beachten Sie, dass ich einen benannten Bereich erstellt habe – „DataRange“ und diesen benannten Bereich im Code verwendet habe.

Sortieren mehrerer Spalten mit Kopfzeilen

Bisher haben wir in diesem Tutorial gesehen, wie eine einzelne Spalte (mit und ohne Überschriften) sortiert wird.

Was ist nun, wenn Sie nach mehreren Spalten sortieren möchten.

Was ist beispielsweise im folgenden Datensatz, wenn ich zuerst nach dem Bundesstaatscode und dann nach dem Geschäft sortieren möchte.

Hier ist der Code, der mehrere Spalten auf einmal sortiert.

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

Unten ist das Ergebnis, das Sie erhalten.

Im obigen Beispiel werden die Daten zunächst nach dem Statuscode (Spalte A) sortiert. Dann werden sie innerhalb der Zustandscodedaten wieder nach dem Store sortiert (Spalte B). Diese Reihenfolge wird durch den Code bestimmt, in dem Sie sie erwähnen.

Sortieren von Daten mit Doppelklick auf Kopfzeile

Wenn Sie ein Dashboard erstellen oder Ihre Berichte benutzerfreundlicher gestalten möchten, können Sie einen VBA-Code schreiben, der die Daten sortiert, wenn Sie auf die Kopfzeilen doppelklicken.

Etwas wie unten gezeigt:

Unten ist der Code, mit dem Sie dies tun können:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes End If End Sub

Beachten Sie, dass ich einen benannten Bereich ("DataRange") erstellt und im Code verwendet habe, anstatt die Zellbezüge zu verwenden.

Sobald Sie auf eine der Kopfzeilen doppelklicken, deaktiviert der Code die übliche Doppelklick-Funktionalität (um in den Bearbeitungsmodus zu gelangen) und verwendet diese Zelle als Schlüssel beim Sortieren der Daten.

Beachten Sie auch, dass dieser Code ab sofort alle Spalten nur in aufsteigender Reihenfolge sortiert.

Beachten Sie, dass ein Doppelklick ein Auslöser ist, mit dem Excel den angegebenen Code ausführen kann. Diese Auslöser wie Doppelklick, Öffnen einer Arbeitsmappe, Hinzufügen eines neuen Arbeitsblatts, Ändern einer Zelle usw. werden als Ereignisse bezeichnet und können zum Ausführen von Makros in Excel verwendet werden. Weitere Informationen zu Excel VBA-Ereignissen finden Sie hier.

Wo soll dieser Code abgelegt werden?

Sie müssen diesen Code in das Codefenster des Blatts einfügen, in dem Sie diese Doppelklick-Sortierungsfunktionalität wünschen.

Um dies zu tun:

• Klicken Sie mit der rechten Maustaste auf das Blattregister.
• Klicken Sie auf Code anzeigen.
• Fügen Sie den Code in das Codefenster des Arbeitsblatts ein, in dem sich Ihre Daten befinden.

Was ist nun, wenn Sie die ersten beiden Spalten (‚State‘ und ‚Store‘) aufsteigend sortieren möchten, die Spalte ‚Sales‘ jedoch absteigend.

Hier ist der Code, der es tut:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes, Order1: =SortOrder End If End Sub

Im obigen Code wird überprüft, ob die Zelle, auf die doppelgeklickt wird, der Header Sales ist oder nicht. Wenn ja, weist es der Variablen SortOrder den Wert xlDescending zu, andernfalls macht es xlAscending.

Gehen wir nun noch einen Schritt weiter und zeigen beim Sortieren einen visuellen Marker (Pfeil und farbige Zelle) in der Kopfzeile an.

Etwas wie unten gezeigt:

Um dies zu erhalten, habe ich ein neues Arbeitsblatt hinzugefügt und die folgenden Änderungen darin vorgenommen (Sie können die Beispieldatei herunterladen und mitverfolgen):

• Der Name des neuen Blatts wurde in „BackEnd“ geändert.
• Geben Sie in Zelle B2 ein Pfeilsymbol ein (gehen Sie dazu auf Einfügen und klicken Sie auf die Option "Symbol").
• Kopieren Sie die Überschriften aus dem Datensatz und fügen Sie sie in Zelle A3:C3 im Blatt "Backend" ein.
• Verwenden Sie die folgende Funktion in Zelle A4:AC4:

  =WENN(A3=$C$1,A3&" "&$B$1,A3)

• Der Rest der Zellen wird automatisch mit dem VBA-Code gefüllt, wenn Sie auf die Überschriften doppelklicken, um die Spalte zu sortieren.

Ihr Back-End-Blatt würde ungefähr wie folgt aussehen:

Jetzt können Sie den folgenden Code verwenden, um die Daten zu sortieren, indem Sie auf die Kopfzeilen doppelklicken. Wenn Sie auf eine Kopfzeile doppelklicken, wird automatisch der Pfeil im Kopfzeilentext angezeigt. Beachten Sie, dass ich auch die bedingte Formatierung verwendet habe, um die Zelle hervorzuheben.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Worksheets("Backend").Range("C1") = Target.Value Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes Worksheets("BackEnd ").Range("A1") = Target.Column For i = 1 To ColumnCount Range("DataRange").Cells(1, i).Value = Worksheets("Backend").Range("A4").Offset (0, i - 1).Value Next i End If End Sub

Beachten Sie, dass dieser Code für die Konstruktion meiner Daten und Arbeitsmappe gut geeignet ist. Wenn Sie die Struktur der Daten ändern, müssen Sie den Code entsprechend anpassen.

Laden Sie die Beispieldatei herunter