Sortering av data i Excel VBA
Excel har et utmerket middel til å sortere en rekke tabelldata ved hjelp av båndet på Excel -grensesnittet, og på et tidspunkt vil du sannsynligvis bruke denne funksjonaliteten i VBA -koden. Heldigvis er dette veldig enkelt å gjøre.
Dialogboksen i grensesnittet finner du ved å klikke på "Sorter" -ikonet i "Sorter og filtrer" -gruppen i "Data" -fanen på Excel-båndet. Du må velge et område med tabelldata først.
Du kan også bruke Alt-A-S-S til å vise dialogboksen for en tilpasset sortering.
Sorteringsmetoden har blitt sterkt forbedret i senere versjoner av Excel. Sorten var tidligere begrenset til tre nivåer, men du kan nå angi så mange nivåer som du trenger, og dette gjelder også innen VBA.
Du kan innlemme alle sorteringsfunksjonene som tilbys i Excel -sorteringsdialogen i din VBA -kode. Sorteringsfunksjonen i Excel er rask og raskere enn alt du kan skrive selv i VBA, så dra fordel av funksjonaliteten.
Vær oppmerksom på at når du gjør en sortering i VBA, forblir sorteringsparametrene de samme i frontend-sorteringsdialogboksen. De blir også lagret når arbeidsboken er lagret.
Hvis en bruker velger det samme området med tabelldata og klikker på Sorter -ikonet, vil de se alle parametrene dine som er angitt av VBA -koden din. Hvis de vil lage en slags eget design, må de først slette alle sorteringsnivåene dine, noe som vil være veldig irriterende for dem.
Hvis du ikke endrer parametrene i koden din og stoler på standardverdier, kan du oppdage at brukeren har gjort endringer som vil gjenspeile VBA -sorteringen din og kan gi uventede resultater, som kan være svært vanskelige å feilsøke. .
Heldigvis er det en Clear-metode i VBA for å sette alle sorteringsparametrene på nytt slik at brukeren vil se en ren sorteringsdialogboks
| 1 | Regneark ("Ark1"). Sort.SortFields.Clear |
Det er god praksis å slette sorteringsparametrene i VBA før og etter at sorteringen er fullført.
Praktisk bruk av sorteringsmetoden i VBA
Når tabelldata importeres til Excel, er det ofte i en veldig tilfeldig rekkefølge. Den kan importeres fra en CSV -fil (kommaadskilte verdier) eller den kan komme fra en lenke til en database eller webside. Du kan ikke stole på at den er i en bestemt rekkefølge fra en import til en annen.
Hvis du presenterer disse dataene for en bruker i regnearket ditt, kan det hende at brukeren har vanskelig for å se på og forstå en enorm mengde data som ordenmessig er overalt. Det kan være lurt å gruppere dataene, eller klippe og lime inn bestemte deler av dem i et annet program.
De vil kanskje også se for eksempel den høyest betalte medarbeideren, eller den ansatte med den lengste tjenesten.
Ved å bruke sorteringsmetoden i VBA kan du tilby alternativer for enkel sortering for brukeren.
Eksempeldata for å demonstrere Excel -sortering med VBA
Vi trenger først noen eksempeldata for å legges inn i et regneark, slik at koden kan demonstrere alle fasilitetene som er tilgjengelige i VBA.
Kopier disse dataene til et regneark (kalt 'Ark1') nøyaktig som vist.
Vær oppmerksom på at forskjellige cellebakgrunnsfarger og skriftfarger har blitt brukt, da disse også kan brukes som sorteringsparametere. Sortering ved hjelp av celle- og skriftfarger vil bli demonstrert senere i artikkelen. Vær også oppmerksom på at avdelingsnavnet på celle E3 er små bokstaver.
Du trenger ikke celleinteriøret og skriftfarger hvis du ikke ønsker å bruke eksemplene på sortering etter celle og skriftfarge.
Ta opp en makro for en VBA -sortering
VBA -kode for sortering kan bli ganske komplisert, og det kan noen ganger være en god idé å gjøre sorteringen på forsiden av Excel og spille inn en makro for å vise deg hvordan koden fungerer.
Dessverre kan opptaksfunksjonen generere en enorm mengde kode fordi den angir praktisk talt alle tilgjengelige parametere, selv om standardverdiene for mange parametere er akseptable for sorteringsoperasjonen.
Det gir deg imidlertid en veldig god ide om hva som er involvert i å skrive VBA -sorteringskode, og en fordel er at den innspilte koden alltid vil fungere for deg. Din egen kode kan trenge testing og feilsøking for at den skal fungere skikkelig.
Husk at for en operasjon som er utført i VBA, er det ingen angrefunksjon, så det er en god idé å kopiere tabelldataene til et annet regneark før du begynner å skrive sorteringskoden.
Som et eksempel, hvis du gjorde en enkel sortering av eksempeldataene ovenfor, sortert etter ansatt, ville opptaket generere følgende kode:
| 123456789101112131415161718 | Undermakro1 ()Område ("A1: E6"). VelgActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). Sorter.SetRange -område ("A1: E6").Header = xlJa.MatchCase = Falsk.Orientation = xlTopToBottom.SortMethod = xlPinYin.Søke omSlutt medSlutt Sub |
Dette er en ganske stor del av koden, og mye av det er unødvendig på grunn av standardparametere som brukes. Men hvis du er under tidspress for å fullføre et prosjekt og du trenger litt kode raskt som fungerer, kan du enkelt lime dette inn i din egen VBA -kode.
Men hvis du vil gjøre koden forståelig og mer elegant, er det andre alternativer tilgjengelig.
VBA -kode for å sortere på ett nivå
Hvis du bare vil sortere eksempelkoden basert på ansatt som før når du spiller inn en makro, er koden veldig enkel:
| 1234567 | Sub SingleLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sorteringsnøkkel1: = Område ("A1"), topptekst: = xlJaSlutt Sub |
Dette er langt lettere å forstå enn den registrerte koden fordi den godtar standardinnstillingene, f.eks. Sortering stigende, så det er ikke nødvendig å sette parametrene til standardverdier. Dette forutsetter at du har brukt en "Clear" -uttalelse på forhånd.
"Clear" -metoden brukes i utgangspunktet for å sikre at alle sorteringsparametere for regnearket settes tilbake til standardverdiene. En bruker kan ha angitt parametrene til forskjellige verdier tidligere, eller en tidligere sortering i VBA kan ha endret dem. Det er viktig å starte fra en standardposisjon når du sorterer, ellers kan du lett ende opp med feil resultat.
Clear -metoden tilbakestiller ikke overskriftsparameteren, og det er lurt å inkludere dette i koden, ellers kan Excel prøve å gjette om det er en overskriftsrad eller ikke.
Kjør denne koden mot eksempeldataene, og regnearket ditt vil se slik ut:
VBA-kode for å sortere på flere nivåer
Du kan legge til så mange sorteringsnivåer som kreves i koden. Anta at du ønsket å sortere først etter avdeling og deretter etter startdato, men i stigende rekkefølge for avdelingen og synkende rekkefølge for startdato:
| 12345678 | Sub MultiLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sorter nøkkel1: = Område ("E1"), nøkkel2: = område ("C1"), topptekst: = xlJa, _Order1: = xlAscending, Order2: = xlDesendingSlutt Sub |
Vær oppmerksom på at det nå er to nøkler i sorteringserklæringen (nøkkel1 og nøkkel2). Nøkkel1 (avdelingskolonne E) blir først sortert og deretter nøkkel2 (kolonne C for startdato) sortert basert på den første sorteringen.
Det er også to bestillingsparametere. Order1 knytter til Key1 (avdeling) og Order2 knytter til Key2 (startdato). Det er viktig å sikre at nøkler og bestillinger holdes i takt med hverandre.
Kjør denne koden mot eksempeldataene, og regnearket ditt vil se slik ut:
Avdelingskolonnen (E) er i stigende rekkefølge, og kolonnen Startdato (C) er i synkende rekkefølge.
Effekten av denne typen er mest merkbar når man ser på Jane Halfacre (rad 3) og John Sutherland (rad 4). De er begge i Finance, men Jane Halfacre startet før John Sutherland og datoene vises i synkende rekkefølge.
Hvis området med tabelldata kan være av hvilken som helst lengde, kan du bruke UsedRange -objektet til å definere sorteringsområdet. Dette fungerer bare hvis det bare er tabelldataene i regnearket, ettersom eventuelle verdier utenfor dataene vil gi feil resultat for antall rader og kolonner.
| 1234567 | Sub MultiLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearRegneark ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlJa, _Order1: = xlAscending, Order2: = xlDesendingSlutt Sub |
Dette forhindrer problemet hvis du bruker metoden 'End (xlDown)' for å definere sorteringsområdet. Hvis det er en tom celle i midten av dataene, vil alt etter den tomme cellen ikke bli inkludert, mens UsedRange går ned til den siste aktive cellen i regnearket.
Sortering etter cellefarge
Siden Excel 2007 er sortering etter bakgrunnsfargen til en celle nå mulig, noe som gir enorm fleksibilitet når du designer sorteringskoden i VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Regneark ("Ark1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). Sorter.SetRange -område ("A1: E6").Søke omSlutt medSlutt Sub |
Denne koden vil sortere eksempeldataområdet (A2: A6) basert på cellebakgrunnsfargen. Vær oppmerksom på at det nå er en ekstra parameter kalt 'SortOn' som har verdien 'xlSortOnCellColor'.
Vær oppmerksom på at parameteren ‘SortOn’ bare kan brukes av et regnearkobjekt og ikke av et områdeobjekt.
På grunn av dette er koden mer komplisert enn for en sortering ved hjelp av celleverdier.
Denne koden bruker en nøkkelverdi for sorteringen som dekker hele dataområdet, men du kan angi individuelle kolonner som nøkkelen for bakgrunnsfargesorteringen, og bruke flere nivåer som vist tidligere.
Etter at du har kjørt denne koden, vil regnearket nå se slik ut:
Sortering etter skriftfarge
Sorteringsfunksjonen i Excel VBA gir enda mer fleksibilitet ved at du kan sortere etter skriftfarger:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Regneark ("Ark1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Med ActiveWorkbook.Worksheets ("Ark1"). Sorter.SetRange -område ("A1: E6").Header = xlJa.Orientation = xlTopToBottom.Søke omSlutt medSlutt Sub |
Koden for sortering etter skriftfarge er langt mer komplisert enn for cellebakgrunnsfargen. Parameteren ‘SortOn’ holder nå verdien av ‘xlSortOnFontColor’.
Vær oppmerksom på at du må angi retning som ‘xlTopToBottom’, og du må angi en farge du vil sortere på. Dette er spesifisert i RGB -termer (rødt, grønt, svart) med verdier fra 0 til 255.
Etter å ha kjørt denne koden mot eksempeldataene, vil regnearket nå se slik ut:
Sortering ved hjelp av farger i VBA er langt mer komplisert enn en sortering på flere nivåer, men hvis sorteringskoden din ikke fungerer (noe som kan skje hvis en parameter mangler eller du ikke har angitt koden riktig), kan du alltid falle tilbake på innspilling en makro og integrere den registrerte koden i din VBA.
Bruke andre parametere i VBA -sortering
Det er en rekke valgfrie parametere du kan bruke i VBA -koden for å tilpasse sorteringen.
SortOn
SortOn velger om sorteringen skal bruke celleverdier, cellebakgrunnsfarger eller cellefontfarger. Standardinnstillingen er celleverdier.
| 1 | SortOn = xlSortOnValues |
Rekkefølge
Ordren velger om sorteringen skal gjøres i stigende eller synkende rekkefølge. Standarden er stigende.
| 1 | Ordre = xlAscending |
DataOption
DataOption velger hvordan tekst og tall sorteres. Parameteren xlSortNormal sorterer numeriske og tekstdata separat. Parameteren xlSortTextAsNumbers behandler tekst som numeriske data for sorteringen. Standard er xlSortNormal.
| 1 | DataOption = xlSortNormal |
Overskrift
Overskrift velger om tabelldataområdet har en overskriftsrad eller ikke. Hvis det er en overskriftsrad, vil du ikke at dette skal inkluderes i sorteringen.
Parameterverdier er xlYes, xlNo og xlYesNoGuess. xlYesNoGuess overlater til Excel for å avgjøre om det er en topptekst, noe som lett kan føre til inkonsekvente resultater. Bruk av denne verdien anbefales ikke.
Standardverdien er XNo (ingen overskriftsrad i dataene). Med importerte data er det vanligvis en overskriftsrad, så sørg for at du setter denne parameteren til xlYes.
| 1 | Overskrift = xlJa |
MatchCase
Denne parameteren avgjør om sorteringen er store og små bokstaver. Valgverdiene er True eller False. Hvis verdien er Falsk, regnes små bokstaver som de samme som store bokstaver. Hvis verdien er True, viser sorteringen forskjellen mellom store og små bokstaver i sorteringen. Standardverdien er False.
| 1 | MatchCase = False |
Orientering
Denne parameteren avgjør om sorteringen vil skje nedover gjennom radene eller på tvers av alle kolonnene. Standardverdien er xlTopToBottom (sorter gjennom rader). Du kan bruke xlLeftToRight hvis du ønsker å sortere horisontalt. Verdier som xlRows og xlColumns fungerer ikke for denne parameteren.
| 1 | Orientering = xlTopToBottom |
SortMethod
Denne parameteren brukes bare for å sortere kinesiske språk. Den har to verdier, xlPinYin og xlStroke. xlPinYin er standardverdien.
xlPinYin sorterer ved hjelp av den fonetiske kinesiske sorteringsrekkefølgen for tegn. xlStroke sorterer etter antall streker i hvert tegn.
Hvis du registrerer en sorteringsmakro, vil denne parameteren alltid være inkludert i koden, og du kan godt ha lurt på hva det betydde. Imidlertid, med mindre du har å gjøre med data på kinesisk, er det til liten nytte.
| 1 | SortMethod = xlPinYin |
Bruk en dobbeltklikk-hendelse til å sortere tabelldata
I all funksjonaliteten som Microsoft inkluderte i sorteringsmetodene for VBA, inkluderte den ikke en enkel måte å dobbeltklikke på en kolonneoverskrift og sortere hele tabelldataene basert på den aktuelle kolonnen.
Dette er en veldig nyttig funksjon å ha, og det er lett å skrive koden for å gjøre det.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Det antas at data begynner ved celle A1'Lag tre variabler for å fange målkolonnen som er valgt og maksimal kolonne og rad med _'tabelldataeneDim Col As Integer, RCol As Long, RRow Like Long'Kontroller at brukeren har dobbeltklikket på overskriftsraden - rad 1, ellers går du ut av subHvis Target.Row 1, avslutt deretter Sub'Fang de maksimale radene i tabelldataområdet med objektet' UsedRange 'RCol = ActiveSheet.UsedRange.Columns.Count'Fang de maksimale kolonnene i tabelldataområdet med objektet' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Kontroller at brukeren ikke har dobbeltklikket på en kolonne utenfor tabelldataområdetHvis Target.Column> RCol, avslutt deretter Sub'Fang kolonnen som brukeren har dobbeltklikket påCol = Target.Column'Fjern tidligere sorteringsparametereActiveSheet.Sort.SortFields.Clear'Sorter tabellområdet som definert av maksimale rader og kolonner fra' UsedRange '-objektet'Sorter tabelldataene ved hjelp av kolonnen som dobbeltklikkes av brukeren som sorteringsnøkkelActiveSheet.Range (Celler (1, 1), Cells (RCol, RRow)). Sorter nøkkel1: = Celler (1, Col), Header: = xlJa'Velg celle A1 - dette er for å sikre at brukeren ikke står i redigeringsmodus etter at sorteringen er _'fullførtActiveSheet.Range ("A1"). VelgSlutt Sub |
Denne koden må plasseres på dobbeltklikkhendelsen på arket som inneholder tabelldataene. Du gjør dette ved å klikke på regnearknavnet i Project Explorer-vinduet (øverste venstre hjørne av VBE-skjermen), og deretter velge "Regneark" i den første rullegardinmenyen i kodevinduet. Velg 'BeforeDoubleClick' i den andre rullegardinmenyen, og du kan deretter skrive inn koden din.
Vær oppmerksom på at ingen navn, områder eller cellereferanser er hardkodet i denne koden bortsett fra å flytte markøren til celle A1 på slutten av koden. Koden er designet for å få all informasjonen som kreves fra cellekoordinater som brukeren har dobbeltklikket på og størrelsen på tabelldataområdet.
Det spiller ingen rolle hvor stort tabelldataområdet er. Koden vil fremdeles hente all nødvendig informasjon, og den kan brukes på data som finnes hvor som helst i arbeidsboken din uten å måtte kode inn verdier.
Den eneste antagelsen som er gjort er at det er en overskriftsrad i tabelldataene, og at dataområdet starter ved celle A1, men startposisjonen for dataområdet kan enkelt endres innenfor koden.
Enhver bruker vil bli passende imponert over denne nye sorteringsfunksjonaliteten!
Utvidelse av sorteringsfunksjonen ved hjelp av VBA
Microsoft har gitt enorm fleksibilitet i sorteringen ved hjelp av et bredt spekter av parametere. Innen VBA kan du imidlertid ta dette videre.
Anta at du ønsket å sortere verdier med fet skrift til toppen av dataene dine. Det er ingen måte å gjøre dette i Excel, men du kan skrive VBA -koden for å gjøre det:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Lag variabler for å holde antall rader og kolonner for tabelldataeneDim RRow As Long, RCol As Long, N As Long'Slå av skjermoppdateringen slik at brukeren ikke kan se hva som skjer - de kan se _'verdier blir endret og lurer på hvorforApplication.ScreenUpdating = Falsk'Registrer antall kolonner i tabelldataområdetRCol = ActiveSheet.UsedRange.Columns.Count'Fang antall rader i tabelldataområdetRRow = ActiveSheet.UsedRange.Rows.Count'Skift gjennom alle radene i tabelldataområdet og ignorer overskriftsradenFor N = 2 Til rad'Hvis en celle har en fet skrift, plasserer du en ledende 0 -verdi mot celleverdienHvis ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" og ActiveSheet.Cells (N, 1) .ValueSlutt omNeste N'Fjern alle tidligere sorteringsparametereActiveSheet.Sort.SortFields.Clear'Sorter tabelldataområdet. Alle verdiene med en ledende 0 -verdi flyttes til toppenActiveSheet.Range (Celler (1, 1), Cells (RCol, RRow)). Sorter nøkkel1: = Celler (1, 1), Header: = xlJa'Skift gjennom alle radene i tabelldataområdet og ignorer overskriftsradenFor N = 2 Til rad'Hvis en celle har en fet skrift, fjern den ledende 0 -verdien fra celleverdien til _'gjenopprette de opprinnelige verdieneHvis ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = Midt (ActiveSheet.Cells (N, 1). Value, 2)Slutt omNeste NSlå på oppdatering av skjermen igjenApplication.ScreenUpdating = TrueSlutt Sub |
Koden regner ut størrelsen på tabelldataområdet med objektet ‘UsedRange’ og går deretter gjennom alle radene i det. Når en fet skrift er funnet, plasseres en ledende null foran celleverdien.
En sortering finner da sted. Siden sorteringen er i stigende rekkefølge, vil alt med null foran gå til toppen av listen.
Koden gjentar seg deretter gjennom alle radene og fjerner de ledende nullene, og gjenoppretter dataene til de opprinnelige verdiene.
Denne koden sorterer ved å bruke fet skrift som et kriterium, men du kan enkelt bruke andre celleegenskaper på samme måte, for eksempel kursiv skrift, punktstørrelse på tekst, understreket skrift, skriftnavn, etc.
