Excel har allerede et par måder til hurtigt at sortere data.
Du kan nemt sortere et datasæt ved at bruge sorteringsikonerne på båndet eller sorteringsdialogboksen.
Hvorfor har du så brug for at vide, hvordan du gør dette ved hjælp af VBA?
At vide, hvordan man sorterer data ved hjælp af VBA, kan være nyttigt, når det indgår som en del af din kode. Antag f.eks., At du får et datasæt dagligt/ugentligt, som du skal formatere og sortere i en bestemt rækkefølge.
Du kan oprette en makro for at gøre alt dette for dig med et enkelt klik. Det vil spare dig for en masse tid og kræfter hver gang du gør det.
Hvis du opretter Excel-dashboards, kan du også tage Excel-sorteringsfunktionen til et nyt niveau ved at lade brugeren sortere dataene bare ved at dobbeltklikke på overskriften (som vist nedenfor).
Jeg vil dække, hvordan du opretter dette senere i denne vejledning. Lad os først hurtigt få det grundlæggende til at stå rigtigt.
Forståelse af rækkevidde. Sorteringsmetode i Excel VBA
Når du sorterer ved hjælp af VBA, skal du bruge Range.Sort -metoden i din kode.
'Området' ville være de data, du prøver at sortere. For eksempel, hvis du sorterer dataene i A1: A10, ville 'Range' være Range ("A1: A10").
Du kan også oprette et navngivet område og bruge det i stedet for cellereferencer. For eksempel, hvis jeg opretter et navngivet område 'DataRange' for cellerne A1: A10, så kan jeg også bruge Range ("DataRange")
Med sorteringsmetoden skal du give nogle yderligere oplysninger gennem parametre. Nedenfor er de vigtigste parametre, du skal vide:
- Nøgle - her skal du angive den kolonne, du vil sortere. For eksempel, hvis du vil sortere kolonne A, skal du bruge nøgle: = Område ("A1")
- Bestille - her angiver du, om du vil have sorteringen i stigende rækkefølge eller faldende rækkefølge. Hvis du f.eks. Vil have sorteringen i stigende rækkefølge, vil du bruge Ordre: = xlAscending
- Header - her angiver du, om dit datasæt har overskrifter eller ej. Hvis den har overskrifter, starter sorteringen fra den anden række i datasættet, ellers starter den fra den første række. For at angive, at dine data har overskrifter, skal du bruge Header: = xlJa
Selvom disse tre er tilstrækkelige i de fleste tilfælde, kan du læse mere om parametrene i denne artikel.
Lad os nu se, hvordan du bruger Range.Sort -metoden i VBA til at sortere data i Excel.
Sortering af en enkelt kolonne uden overskrift
Antag, at du har en enkelt kolonne uden overskrift (som vist nedenfor).
Du kan bruge nedenstående kode til at sortere den i stigende rækkefølge.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xl Ingen Sub Sub
Bemærk, at jeg har angivet dataområdet manuelt som Range ("A1: A12").
Hvis der kan være ændringer i dataene, og værdier kan tilføjes/slettes, kan du bruge nedenstående kode, der automatisk justeres baseret på de fyldte celler i datasættet.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Bemærk, at jeg i stedet for Range ("A1: A12") har brugt, Range ("A1", Range ("A1"). End (xlDown)).
Dette kontrollerer den sidste på hinanden følgende celle i kolonnen og inkluderer den i sorteringen. Hvis der er emner, vil det kun overveje data indtil den første tomme celle.
Du kan også oprette et navngivet område og bruge det navngivne område i stedet for cellereferencer. For eksempel, hvis det navngivne område er DataSet, ville din kode nu være som vist nedenfor.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Lad mig nu hurtigt forklare de parametre, der bruges i ovenstående eksempler:
- Nøgle1: = Område (“A1”) - Angivet A1, så koden ved, hvilken kolonne der skal sorteres.
- Ordre1: = xlAscending - Angav ordren som xlAscending. Hvis du vil have den til at være i faldende rækkefølge, skal du bruge xlDescending.
- Header: = xlNo - Angivet at der ikke er nogen overskrifter. Dette er også standardværdien. Så selvom du udelader dette, bliver dine data sorteret i betragtning af, at de ikke har nogen overskrifter.
Undrer du dig over, hvor du skal placere denne VBA -kode, og hvordan du kører makroen? Læs denne vejledning!
Sortering af en enkelt kolonne med overskrift
I det foregående eksempel havde datasættet ikke en overskrift.
Når dine data har overskrifter, skal du angive det i koden, så sorteringen kan starte fra den anden række i datasættet.
Antag, at du har et datasæt som vist herunder:
Nedenfor er koden, der vil sortere dataene i faldende rækkefølge baseret på butikkernes salg.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Bemærk, at jeg har oprettet et navngivet område - 'DataRange', og brugt dette navngivne område i koden.
Sortering af flere kolonner med overskrifter
Hidtil i denne vejledning har vi set, hvordan man sorterer en enkelt kolonne (med og uden overskrifter).
Hvad nu hvis du vil sortere baseret på flere kolonner.
For eksempel i nedenstående datasæt, hvad hvis jeg først vil sortere efter statskoden og derefter efter butikken.
Her er koden, der vil sortere flere kolonner på én gang.
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. Anvend slut med slut sub
Nedenfor er det resultat, du får.
I ovenstående eksempel sorteres dataene først efter tilstandskoden (kolonne A). Derefter sorteres det igen i tilstandskodedataene efter butikken (kolonne B). Denne rækkefølge bestemmes af den kode, hvor du nævner den.
Sortering af data ved hjælp af dobbeltklik på overskrift
Hvis du opretter et dashboard eller ønsker mere brugervenlighed i dine rapporter, kan du skrive en VBA -kode, der sorterer dataene, når du dobbeltklikker på overskrifterne.
Noget som vist herunder:
Nedenfor er koden, der giver dig mulighed for at gøre dette:
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: = xlJa End If End Sub
Bemærk, at jeg har oprettet et navngivet område ("DataRange") og har brugt det i koden i stedet for at bruge cellereferencer.
Så snart du dobbeltklikker på en af overskrifterne, deaktiverer koden den sædvanlige dobbeltklik-funktionalitet (som er at komme ind i redigeringstilstanden) og bruger cellen som nøgle, mens dataene sorteres.
Bemærk også, at fra nu af sorterer denne kode alle kolonner kun i stigende rækkefølge.
Bemærk, at dobbeltklik er en udløser giver Excel mulighed for at køre den angivne kode. Disse udløsere som dobbeltklik, åbning af en projektmappe, tilføjelse af et nyt regneark, ændring af en celle osv. Kaldes begivenheder og kan bruges til at køre makroer i Excel. Du kan læse mere om Excel VBA -begivenheder her.Hvor skal denne kode placeres?
Du skal indsætte denne kode i kodevinduet på det ark, hvor du vil have denne dobbeltkliksorteringsfunktion.
At gøre dette:
- Højreklik på arkfanen.
- Klik på Vis kode.
- Indsæt koden i kodevinduet på det ark, hvor dine data findes.
Hvad nu hvis du vil sortere de to første kolonner ('State' og 'Store') i stigende rækkefølge, men 'Sales' -kolonne i faldende rækkefølge.
Her er koden, der gør det:
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
I ovenstående kode kontrollerer den, om den celle, der dobbeltklikkes på, er salgsoverskriften eller ej. Hvis ja, tildeler den værdien xlDescending til variablen SortOrder, ellers gør den xlAscending.
Lad os nu tage dette et hak videre og vise en visuel markør (pil og farvet celle) i overskriften, når den er sorteret.
Noget som vist herunder:
For at få dette har jeg tilføjet et nyt regneark og foretaget følgende ændringer i det (du kan downloade eksempelfilen og følge med):
- Ændrede navnet på det nye ark til 'BackEnd'.
- I celle B2 har du indtastet et pilsymbol (for at gøre dette skal du gå til Indsæt og klikke på 'Symbol').
- Kopier og indsæt overskrifterne fra datasættet til celle A3: C3 i "Backend" -arket.
- Brug følgende funktion i celle A4: AC4:
= HVIS (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- Resten af cellerne bliver automatisk udfyldt af VBA -koden, når du dobbeltklikker på overskrifterne for at sortere kolonnen.
Dit backend -ark ville se sådan ud som vist herunder:
Nu kan du bruge nedenstående kode til at sortere dataene ved at dobbeltklikke på overskrifterne. Når du dobbeltklikker på en overskrift, får den automatisk pilen i overskriftsteksten. Bemærk, at jeg også har brugt betinget formatering til også at markere cellen.
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 "). Celler (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1). Værdi Næste i Slut Hvis Afslut Sub
Bemærk, at denne kode fungerer godt til den måde, mine data og projektmappe er konstrueret på. Hvis du ændrer datastrukturen, skal du ændre koden i overensstemmelse hermed.
Download eksempelfilen
