Excel heeft al een aantal manieren om gegevens snel te sorteren.
U kunt een gegevensset eenvoudig sorteren met behulp van de sorteerpictogrammen in het lint of het sorteervenster.
Waarom moet je dan weten hoe je dit moet doen met VBA?
Weten hoe u gegevens kunt sorteren met VBA kan handig zijn wanneer deze als onderdeel van uw code worden opgenomen. Stel bijvoorbeeld dat u dagelijks/wekelijks een gegevensset ontvangt die u in een specifieke volgorde moet opmaken en sorteren.
U kunt een macro maken om dit allemaal voor u te doen met een enkele klik. Dat bespaart u elke keer dat u het doet veel tijd en moeite.
Als u Excel-dashboards maakt, kunt u de sorteermogelijkheden van Excel naar een nieuw niveau tillen door de gebruiker toe te staan de gegevens te sorteren door te dubbelklikken op de koptekst (zoals hieronder weergegeven).
Ik zal later in deze tutorial bespreken hoe je dit kunt maken. Laten we eerst snel de basis op een rijtje zetten.
De Range.Sorte-methode in Excel VBA begrijpen
Wanneer u sorteert met VBA, moet u de Range.Sort-methode in uw code gebruiken.
Het 'Bereik' zou de gegevens zijn die u probeert te sorteren. Als u bijvoorbeeld de gegevens sorteert in A1:A10, is 'Bereik' Bereik ("A1:A10").
U kunt ook een benoemd bereik maken en dit gebruiken in plaats van de celverwijzingen. Als ik bijvoorbeeld een benoemd bereik 'DataRange' maak voor de cellen A1:A10, dan kan ik ook Range ("DataRange") gebruiken
Met de sorteermethode moet u wat aanvullende informatie verstrekken via parameters. Hieronder staan de belangrijkste parameters die u moet weten:
- Sleutel - hier moet u de kolom specificeren die u wilt sorteren. Als u bijvoorbeeld kolom A wilt sorteren, moet u key:=Range(“A1”) gebruiken
- Bestellen - hier geeft u aan of u de sortering in oplopende of aflopende volgorde wilt. Als u bijvoorbeeld wilt sorteren in oplopende volgorde, gebruikt u Order:=xlAscending
- kop - hier geeft u op of uw dataset headers heeft of niet. Als het kopteksten heeft, begint het sorteren vanaf de tweede rij van de gegevensset, anders begint het vanaf de eerste rij. Om aan te geven dat uw gegevens headers hebben, gebruikt u Header:=xlJa
Hoewel deze drie in de meeste gevallen voldoende zijn, leest u in dit artikel meer over de parameters.
Laten we nu eens kijken hoe we de Range.Sort-methode in VBA kunnen gebruiken om gegevens in Excel te sorteren.
Een enkele kolom sorteren zonder koptekst
Stel dat u een enkele kolom zonder kop heeft (zoals hieronder weergegeven).
U kunt de onderstaande code gebruiken om het in oplopende volgorde te sorteren.
Sub SortDataWithoutHeader() Range("A1:A12").Sort Key1:=Bereik("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Merk op dat ik het gegevensbereik handmatig heb opgegeven als Bereik ("A1: A12").
Als er wijzigingen in de gegevens zijn en er waarden worden toegevoegd/verwijderd, kunt u de onderstaande code gebruiken die zich automatisch aanpast op basis van de gevulde cellen in de gegevensset.
Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Merk op dat ik in plaats van Range ("A1: A12"), Range ("A1", Range ("A1").End (xlDown)) heb gebruikt.
Hiermee wordt de laatst opeenvolgend gevulde cel in de kolom gecontroleerd en meegenomen in de sortering. Als er lege cellen zijn, worden alleen gegevens tot de eerste lege cel in aanmerking genomen.
U kunt ook een benoemd bereik maken en dat benoemde bereik gebruiken in plaats van de celverwijzingen. Als het benoemde bereik bijvoorbeeld DataSet is, ziet uw code er nu uit zoals hieronder wordt weergegeven.
Sub SortDataWithoutHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Laat me nu snel de parameters uitleggen die in de bovenstaande voorbeelden worden gebruikt:
- Key1:=Bereik ("A1") - Specificeerde A1 zodat de code zou weten welke kolom moet worden gesorteerd.
- Order1:=xlAscending - De volgorde is opgegeven als xlAscending. Als u wilt dat het in aflopende volgorde staat, gebruikt u xlDescending.
- Header:= xlNo - Opgegeven dat er geen headers zijn. Dit is ook de standaardwaarde. Dus zelfs als u dit weglaat, worden uw gegevens gesorteerd aangezien deze geen koppen hebben.
Vraagt u zich af waar u deze VBA-code moet plaatsen en hoe u de macro moet uitvoeren? Lees deze tutorial!
Een enkele kolom sorteren met koptekst
In het vorige voorbeeld had de dataset geen header.
Als uw gegevens headers hebben, moet u die in de code specificeren, zodat het sorteren kan beginnen vanaf de tweede rij van de dataset.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Hieronder vindt u de code die de gegevens in aflopende volgorde sorteert op basis van de verkoop van de winkels.
Sub SortDataWithHeader() Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending End Sub
Merk op dat ik een benoemd bereik heb gemaakt - 'DataRange', en dit benoemde bereik in de code heb gebruikt.
Meerdere kolommen sorteren met kopteksten
Tot nu toe hebben we in deze zelfstudie gezien hoe u een enkele kolom kunt sorteren (met en zonder kopteksten).
Wat als u nu wilt sorteren op basis van meerdere kolommen.
In de onderstaande dataset, wat als ik bijvoorbeeld eerst wil sorteren op de staatscode en vervolgens op de winkel.
Hier is de code die meerdere kolommen in één keer sorteert.
Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlJa .Apply End With End Sub
Hieronder ziet u het resultaat dat u krijgt.
In het bovenstaande voorbeeld worden de gegevens eerst gesorteerd op de statuscode (kolom A). Vervolgens wordt het binnen de staatscodegegevens opnieuw gesorteerd door de winkel (kolom B). Deze volgorde wordt bepaald door de code waarin u deze vermeldt.
Gegevens sorteren met dubbelklik op koptekst
Als u een dashboard aan het maken bent of meer gebruiksgemak in uw rapporten wilt, kunt u een VBA-code schrijven die de gegevens sorteert wanneer u dubbelklikt op de koppen.
Iets zoals hieronder weergegeven:
Hieronder vindt u de code waarmee u dit kunt doen:
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 Annuleren = True Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes End If End Sub
Merk op dat ik een benoemd bereik ("DataRange") heb gemaakt en het in de code heb gebruikt in plaats van de celverwijzingen te gebruiken.
Zodra u dubbelklikt op een van de koppen, schakelt de code de gebruikelijke dubbelklikfunctionaliteit uit (die is om in de bewerkingsmodus te komen) en gebruikt die cel als de sleutel tijdens het sorteren van de gegevens.
Houd er ook rekening mee dat deze code vanaf nu alle kolommen alleen in oplopende volgorde sorteert.
Merk op dat dubbelklikken een trigger is waarmee Excel de opgegeven code kan uitvoeren. Deze triggers, zoals dubbelklikken, een werkmap openen, een nieuw werkblad toevoegen, een cel wijzigen, enz. worden gebeurtenissen genoemd en kunnen worden gebruikt om macro's in Excel uit te voeren. U kunt hier meer lezen over Excel VBA-gebeurtenissen.Waar plaats je deze code?
U moet deze code in het codevenster van het blad plakken waarin u deze dubbelklik-sorteerfunctie wilt hebben.
Om dit te doen:
- Klik met de rechtermuisknop op de bladtab.
- Klik op Bekijk code.
- Plak de code in het codevenster van het blad waarin uw gegevens zich bevinden.
Wat nu als u de eerste twee kolommen ('State' en 'Store') in oplopende volgorde wilt sorteren, maar de kolom 'Verkoop' in aflopende volgorde.
Hier is de code die het zal doen:
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 Annuleren = True Set KeyRange = Range(Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange").Sorteersleutel1:=KeyRange, Header:=xlJa, Order1: =Sorteervolgorde Einde Als Einde Sub
In de bovenstaande code wordt gecontroleerd of de cel waarop wordt gedubbelklikt de Sales-header is of niet. Zo ja, dan wijst het de xlDescending-waarde toe aan de variabele SortOrder, anders wordt het xlAscending.
Laten we nu een tandje verder gaan en een visuele markering (pijl en gekleurde cel) in de kop tonen wanneer deze is gesorteerd.
Iets zoals hieronder weergegeven:
Om dit te krijgen, heb ik een nieuw werkblad toegevoegd en daarin de volgende wijzigingen aangebracht (u kunt het voorbeeldbestand downloaden en volgen):
- De naam van het nieuwe blad gewijzigd in 'BackEnd'.
- Voer in cel B2 een pijlsymbool in (ga hiervoor naar Invoegen en klik op de optie 'Symbool').
- Kopieer en plak de headers van de dataset naar cel A3:C3 in het blad 'Backend'.
- Gebruik de volgende functie in cel A4:AC4:
=ALS(A3=$C$1,A3&" "&$B$1,A3)
- De rest van de cellen wordt automatisch gevuld door de VBA-code wanneer u dubbelklikt op de koppen om de kolom te sorteren.
Uw backend-blad ziet er ongeveer uit zoals hieronder wordt weergegeven:
Nu kunt u de onderstaande code gebruiken om de gegevens te sorteren door op de koppen te dubbelklikken. Wanneer u dubbelklikt op een koptekst, krijgt deze automatisch de pijl in de koptekst. Merk op dat ik ook voorwaardelijke opmaak heb gebruikt om de cel ook te markeren.
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 Annuleren = 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). Waarde Volgende i Einde Als Einde Sub
Merk op dat deze code goed werkt voor de manier waarop mijn gegevens en werkmap zijn opgebouwd. Als u de structuur van de gegevens wijzigt, moet u de code dienovereenkomstig aanpassen.
Download het voorbeeldbestand
