Excel har allerede et par måter å sortere data raskt på.
Du kan enkelt sortere et datasett ved å bruke sorteringsikonene på båndet eller sorteringsdialogboksen.
Så hvorfor trenger du å vite hvordan du gjør dette ved hjelp av VBA?
Å vite hvordan du sorterer data ved hjelp av VBA kan være nyttig når det er inkludert som en del av koden din. Anta for eksempel at du får et datasett daglig/ukentlig som du må formatere og sortere i en bestemt rekkefølge.
Du kan lage en makro for å gjøre alt dette for deg med et enkelt klikk. Det vil spare deg for mye tid og krefter hver gang du gjør det.
Hvis du oppretter Excel-dashbord, kan du også ta Excel-sorteringsfunksjonen til et nytt nivå ved å la brukeren sortere dataene bare ved å dobbeltklikke på overskriften (som vist nedenfor).
Jeg vil dekke hvordan du lager dette senere i denne opplæringen. La oss først få det grunnleggende rett.
Forstå Range.Sort Method i Excel VBA
Når du skal sortere med VBA, må du bruke Range.Sort -metoden i koden din.
"Området" vil være dataene du prøver å sortere. For eksempel, hvis du sorterer dataene i A1: A10, vil 'Range' være Range ("A1: A10").
Du kan også opprette et navngitt område og bruke det i stedet for cellereferansene. For eksempel, hvis jeg oppretter et navngitt område 'DataRange' for cellene A1: A10, kan jeg også bruke Range ("DataRange")
Med sorteringsmetoden må du gi litt tilleggsinformasjon gjennom parametere. Nedenfor er de viktigste parameterne du trenger å vite:
- Nøkkel - her må du spesifisere kolonnen du vil sortere. For eksempel, hvis du vil sortere kolonne A, må du bruke nøkkelen: = Område ("A1")
- Rekkefølge - her angir du om du vil sortere i stigende rekkefølge eller synkende rekkefølge. Hvis du for eksempel vil sortere i stigende rekkefølge, vil du bruke Order: = xlAscending
- Overskrift - her angir du om datasettet ditt har overskrifter eller ikke. Hvis den har overskrifter, starter sorteringen fra den andre raden i datasettet, ellers starter den fra den første raden. For å angi at dataene dine har overskrifter, bruker du Header: = xlJa
Selv om disse tre er tilstrekkelig i de fleste tilfellene, kan du lese mer om parameterne i denne artikkelen.
La oss nå se hvordan du bruker Range.Sort -metoden i VBA til å sortere data i Excel.
Sortere en enkelt kolonne uten overskrift
Anta at du har en enkelt kolonne uten overskrift (som vist nedenfor).
Du kan bruke koden nedenfor for å sortere den i stigende rekkefølge.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Vær oppmerksom på at jeg har spesifisert dataområdet manuelt som Range ("A1: A12").
I tilfelle det kan være endringer i dataene og verdier kan bli lagt til/slettet, kan du bruke koden nedenfor som automatisk justeres basert på de fylte cellene i datasettet.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Legg merke til at i stedet for Range ("A1: A12"), har jeg brukt, Range ("A1", Range ("A1"). End (xlDown)).
Dette vil kontrollere den siste påfølgende cellen i kolonnen og inkludere den i sorteringen. Hvis det er blanke, vil det bare vurdere data til den første tomme cellen.
Du kan også opprette et navngitt område og bruke det navngitte området i stedet for cellereferansene. For eksempel, hvis det navngitte området er DataSet, vil koden din nå være som vist nedenfor.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
La meg nå raskt forklare parameterne som brukes i eksemplene ovenfor:
- Nøkkel 1: = Område (“A1”) - Spesifisert A1 slik at koden vet hvilken kolonne som skal sorteres.
- Order1: = xlAscending - Spesifiserte ordren som xlAscending. Hvis du vil at den skal være i synkende rekkefølge, bruker du xlDescending.
- Header: = xlNo - Spesifisert at det ikke er noen overskrifter. Dette er også standardverdien. Så selv om du utelater dette, blir dataene dine sortert med tanke på at de ikke har noen overskrifter.
Lurer du på hvor du skal sette denne VBA -koden og hvordan du kjører makroen? Les denne opplæringen!
Sortere en enkelt kolonne med topptekst
I forrige eksempel hadde datasettet ikke en overskrift.
Når dataene dine har overskrifter, må du spesifisere det i koden slik at sorteringen kan starte fra den andre raden i datasettet.
Anta at du har et datasett som vist nedenfor:
Nedenfor er koden som vil sortere dataene i synkende rekkefølge basert på salget av butikkene.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Vær oppmerksom på at jeg har opprettet et navngitt område - 'DataRange', og brukte dette navngitte området i koden.
Sortering av flere kolonner med overskrifter
Så langt i denne opplæringen har vi sett hvordan vi sorterer en enkelt kolonne (med og uten overskrifter).
Hva om du vil sortere basert på flere kolonner.
For eksempel, i datasettet nedenfor, hva om jeg først vil sortere etter statskoden, og deretter etter butikken.
Her er koden som vil sortere flere kolonner samtidig.
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. Søk på slutten med slutten
Nedenfor er resultatet du får.
I eksemplet ovenfor blir dataene først sortert etter tilstandskoden (kolonne A). Deretter sorteres det igjen i tilstandskodedataene etter butikken (kolonne B). Denne rekkefølgen bestemmes av koden du nevner den i.
Sortere data ved hjelp av dobbeltklikk på topptekst
Hvis du oppretter et dashbord eller ønsker mer brukervennlighet i rapportene dine, kan du skrive en VBA -kode som sorterer dataene når du dobbeltklikker på overskriftene.
Noe som vist nedenfor:
Nedenfor er koden som lar deg gjø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 Avbryt = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Sorter Key1: = KeyRange, Header: = xlJa End If End Sub
Vær oppmerksom på at jeg har opprettet et navngitt område (“DataRange”) og har brukt det i koden i stedet for å bruke cellereferansene.
Så snart du dobbeltklikker på en av overskriftene, deaktiverer koden den vanlige dobbeltklikkfunksjonaliteten (som skal komme inn i redigeringsmodus) og bruker den cellen som nøkkel mens du sorterer dataene.
Vær også oppmerksom på at fra nå av vil denne koden sortere alle kolonnene bare i stigende rekkefølge.
Vær oppmerksom på at dobbeltklikk er en utløser som gjør at Excel kan kjøre den angitte koden. Disse utløser som dobbeltklikk, åpne en arbeidsbok, legge til et nytt regneark, endre en celle, etc. kalles hendelser og kan brukes til å kjøre makroer i Excel. Du kan lese mer om Excel VBA -arrangementer her.Hvor skal jeg legge denne koden?
Du må lime inn denne koden i kodevinduet på arket der du vil ha denne dobbeltklikksorteringsfunksjonen.
Å gjøre dette:
- Høyreklikk på arkfanen.
- Klikk på Vis kode.
- Lim inn koden i kodevinduet på arket som dataene dine ligger i.
Hva om du vil sortere de to første kolonnene ("Stat" og "Lagre") i stigende rekkefølge, men "Salg" -kolonne i synkende rekkefølge.
Her er koden som vil gjøre 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 Avbryt = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlJa, Order1: = SortOrder End If End Sub
I koden ovenfor sjekker den om cellen som er dobbeltklikket er salgsoverskriften eller ikke. Hvis ja, tildeler den xlDescending -verdien til variabelen SortOrder, ellers gjør den xlAscending.
La oss ta dette et hakk videre og vise en visuell markør (pil og farget celle) i overskriften når den er sortert.
Noe som vist nedenfor:
For å få dette, har jeg lagt til et nytt regneark og gjort følgende endringer i det (du kan laste ned eksempelfilen og følge med):
- Endret navnet på det nye arket til 'BackEnd'.
- I celle B2 skrev du inn et pilsymbol (for å gjøre dette, gå til Sett inn og klikk på "Symbol" -alternativet).
- Kopier og lim inn overskriftene fra datasettet til celle A3: C3 i "Backend" -arket.
- Bruk følgende funksjon i celle A4: AC4:
= HVIS (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- Resten av cellene blir automatisk fylt av VBA -koden når du dobbeltklikker på overskriftene for å sortere kolonnen.
Backend -arket ditt vil se noe ut som vist nedenfor:
Nå kan du bruke koden nedenfor til å sortere dataene ved å dobbeltklikke på overskriftene. Når du dobbeltklikker på en overskrift, får den automatisk pilen i overskriftsteksten. Vær oppmerksom på at jeg også har brukt betinget formatering for å markere cellen også.
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 Avbryt = 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). Verdi Neste i Slutt Hvis slutt Sub
Vær oppmerksom på at denne koden fungerer godt for måten dataene og arbeidsboken min er konstruert på. Hvis du endrer strukturen til dataene, må du endre koden tilsvarende.
Last ned eksempelfilen
