Sortering af data i Excel VBA
Excel har et glimrende middel til at sortere en række tabulære data ved hjælp af båndet på Excel -frontenden, og på et tidspunkt vil du sandsynligvis gerne bruge denne funktionalitet i din VBA -kode. Heldigvis er dette meget let at gøre.
Frontend-dialogboksen findes ved at klikke på ikonet 'Sorter' i gruppen 'Sorter og filtrer' fanen 'Data' på Excel-båndet. Du skal først vælge en række tabeldata.
Du kan også bruge Alt-A-S-S til at vise dialogboksen til en tilpasset sortering.
Sorteringsmetoden er blevet stærkt forbedret i senere versioner af Excel. Sorten var tidligere begrænset til tre niveauer, men du kan nu indtaste lige så mange niveauer som dit behov, og det gælder også inden for VBA.
Du kan indarbejde alle de tilbudte sorteringsfunktioner i dialogboksen Excel -sortering i din VBA -kode. Sorteringsfunktionen i Excel er hurtig og hurtigere end noget andet, du selv kunne skrive i VBA, så udnyt funktionaliteten.
Bemærk, at når du foretager en sortering i VBA, forbliver sorteringsparametrene de samme i front-end sorteringsdialogboksen. De gemmes også, når projektmappen gemmes.
Hvis en bruger vælger det samme område med tabeldata og klikker på ikonet Sorter, vil de se alle dine parametre, der er indtastet af din VBA -kode. Hvis de vil lave en slags eget design, skal de først slette alle dine sorteringsniveauer, hvilket vil være meget irriterende for dem.
Hvis du ikke ændrer parametrene i din kode og er afhængig af standardværdier, kan du opleve, at brugeren har foretaget ændringer, der afspejler sig i din VBA -sortering og kan give uventede resultater, som kan være meget vanskelige at fejlsøge .
Heldigvis er der en Clear-metode i VBA til at genindstille alle sorteringsparametre, så brugeren kan se en ren sorteringsdialogboks
| 1 | Regneark ("Ark1"). Sort.SortFields.Clear |
Det er god praksis at rydde sorteringsparametrene i VBA før og efter sorteringen er afsluttet.
Praktisk brug af sorteringsmetoden i VBA
Når tabulære data importeres til Excel, er det ofte i en meget tilfældig rækkefølge. Det kan importeres fra en CSV -fil (kommaadskilte værdier), eller det kan komme fra et link til en database eller webside. Du kan ikke stole på, at det er i en bestemt rækkefølge fra en import til en anden.
Hvis du præsenterer disse data for en bruger i dit regneark, kan brugeren godt have svært ved at se på og forstå en enorm mængde data, som ordenmæssigt er overalt. De vil måske gruppere dataene eller klippe og indsætte bestemte sektioner af dem i et andet program.
De vil måske også se for eksempel den bedst betalte medarbejder eller den medarbejder, der har den længste service.
Ved hjælp af sorteringsmetoden i VBA kan du tilbyde muligheder for let sortering for brugeren.
Eksempeldata til demonstration af Excel -sortering med VBA
Vi har først brug for nogle eksempeldata til at blive indtastet i et regneark, så koden kan demonstrere alle de tilgængelige faciliteter inden for VBA.
Kopier disse data til et regneark (kaldet 'Ark1') nøjagtigt som vist.
Bemærk, at forskellige cellebaggrundsfarver og skrifttypefarver er blevet brugt, da disse også kan bruges som sorteringsparametre. Sortering ved hjælp af celle- og skrifttypefarver vil blive demonstreret senere i artiklen. Bemærk også, at på celle E3 er afdelingsnavnet alle små bogstaver.
Du behøver ikke celleinteriøret og skrifttypefarver, hvis du ikke ønsker at bruge eksemplerne på sortering efter celle og skrifttypefarve.
Optagelse af en makro til en VBA -sortering
VBA -kode til sortering kan blive ret kompliceret, og det kan nogle gange være en god idé at foretage sorteringen på forsiden af Excel og optage en makro for at vise dig, hvordan koden fungerer.
Desværre kan optagefunktionen generere en enorm mængde kode, fordi den angiver stort set alle tilgængelige parametre, selvom standardværdierne for mange parametre er acceptable for din sorteringsoperation.
Det giver dig dog en meget god ide om, hvad der er involveret i at skrive VBA -sorteringskode, og en fordel er, at den registrerede kode altid vil fungere for dig. Din egen kode kan have brug for test og fejlfinding for at få den til at fungere korrekt.
Husk, at for en handling udført i VBA, er der ingen fortrydelsesfunktion, så det er en god idé at lave en kopi af tabeldataene til et andet regneark, før du begynder at skrive din sorteringskode.
Som et eksempel, hvis du foretog en simpel sortering af eksempeldataene ovenfor, sortering efter medarbejder, ville optagelsen generere følgende kode:
| 123456789101112131415161718 | Undermakro1 ()Område ("A1: E6"). VælgActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 -nøgle: = 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.AnsøgeAfslut medAfslut Sub |
Dette er en ganske stor del kode, og meget af det er unødvendigt på grund af standardparametre, der bruges. Men hvis du er under tidspres for at fuldføre et projekt, og du hurtigt har brug for en kode, der fungerer, kan du nemt indsætte dette i din egen VBA -kode.
Men hvis du vil gøre din kode forståelig og mere elegant, så er der andre muligheder.
VBA -kode til at foretage en sortering på et enkelt niveau
Hvis du kun vil sortere prøvekoden baseret på medarbejder som før, når du optager en makro, er koden meget enkel:
| 1234567 | Sub SingleLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sorteringsnøgle1: = Område ("A1"), Header: = xlJaAfslut Sub |
Dette er langt lettere at forstå end den registrerede kode, fordi den accepterer standardindstillingerne, f.eks. Sortering stigende, så det er ikke nødvendigt at indstille parametrene til standardværdier. Dette forudsætter, at du på forhånd har brugt en 'Clear' erklæring.
'Ryd' metoden bruges oprindeligt til at sikre, at hver sorteringsparameter for det regneark sættes tilbage til standardværdierne. En bruger kan tidligere have indstillet parametrene til forskellige værdier, eller en tidligere sortering i VBA kan have ændret dem. Det er vigtigt at starte fra en standardposition, når du sorterer, ellers kan du let ende med forkerte resultater.
Metoden Clear nulstiller ikke parameteren Header, og det er tilrådeligt at inkludere dette i din kode, ellers kan Excel prøve at gætte, om der er en header -række eller ej.
Kør denne kode mod eksempeldataene, og dit regneark vil se sådan ud:
VBA-kode til sortering på flere niveauer
Du kan tilføje så mange sorteringsniveauer som krævet i din kode. Antag, at du først ønskede at sortere efter afdeling og derefter efter startdato, men i stigende rækkefølge for afdelingen og faldende rækkefølge for startdato:
| 12345678 | Sub MultiLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sorter nøgle1: = område ("E1"), nøgle2: = område ("C1"), overskrift: = xlJa, _Ordre1: = xlAscending, Order2: = xlDescendendeAfslut Sub |
Bemærk, at der nu er to nøgler i sorteringserklæringen (nøgle1 og nøgle2). Nøgle1 (afdelingskolonne E) sorteres først og derefter og nøgle2 (kolonne Startdato C) sorteres baseret på den første sortering.
Der er også to ordreparametre. Order1 tilknytter Key1 (afdeling) og Order2 associerer med Key2 (startdato). Det er vigtigt at sikre, at nøgler og ordrer holdes i takt med hinanden.
Kør denne kode mod eksempeldataene, og dit regneark vil se sådan ud:
Kolonnen Afdeling (E) er i stigende rækkefølge, og kolonnen Startdato (C) er i faldende rækkefølge.
Effekten af denne slags er mest mærkbar, når man ser på Jane Halfacre (række 3) og John Sutherland (række 4). De er begge i Finance, men Jane Halfacre startede før John Sutherland, og datoerne vises i faldende rækkefølge.
Hvis området med tabeldata kan være af en hvilken som helst længde, kan du bruge UsedRange -objektet til at definere sorteringsområdet. Dette vil kun fungere, hvis der kun er tabeldata på regnearket, da eventuelle værdier uden for dataene vil give forkerte resultater for antallet af rækker og kolonner.
| 1234567 | Sub MultiLevelSort ()Regneark ("Ark1"). Sort.SortFields.ClearRegneark ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlJa, _Ordre1: = xlAscending, Order2: = xlDescendendeAfslut Sub |
Dette forhindrer problemet, hvis du bruger metoden 'End (xlDown)' til at definere sorteringsområdet. Hvis der er en tom celle i midten af dataene, vil alt efter den tomme celle ikke blive inkluderet, mens UsedRange går ned til den sidste aktive celle i regnearket.
Sortering efter cellefarve
Siden Excel 2007 er sortering efter en celles baggrundsfarve nu mulig, hvilket giver enorm fleksibilitet, når du designer din sorteringskode i VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Regneark ("Ark1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, rækkefølge: = xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). Sorter.SetRange -område ("A1: E6").AnsøgeAfslut medAfslut Sub |
Denne kode sorterer eksempeldataområdet (A2: A6) baseret på cellens baggrundsfarve. Bemærk, at der nu er en ekstra parameter kaldet 'SortOn', som har værdien 'xlSortOnCellColor'.
Bemærk, at parameteren 'SortOn' kun kan bruges af et regnearksobjekt og ikke af et områdeobjekt.
På grund af dette er koden mere kompliceret end for en sortering ved hjælp af celleværdier.
Denne kode bruger en nøgleværdi til den sortering, der dækker hele dataområdet, men du kan angive individuelle kolonner som nøglen til baggrundsfarvesorteringen og bruge flere niveauer som vist tidligere.
Efter at have kørt denne kode vil dit regneark nu se sådan ud:
Sortering efter skrifttype
Sorteringsfunktionen i Excel VBA giver endnu mere fleksibilitet, idet du kan sortere efter skrifttypefarver:
| 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.AnsøgeAfslut medAfslut Sub |
Koden til sortering efter skrifttypefarve er langt mere kompliceret end for cellens baggrundsfarve. Parameteren ‘SortOn’ har nu værdien af ‘xlSortOnFontColor’.
Bemærk, at du skal angive retning som 'xlTopToBottom', og du skal angive en farve, der skal sorteres på. Dette er angivet i RGB -termer (rød, grøn, sort) med værdier fra 0 til 255.
Efter at have kørt denne kode mod eksempeldataene, ser dit regneark nu sådan ud:
Sortering ved hjælp af farver i VBA er langt mere kompliceret end en sortering på flere niveauer, men hvis din sorteringskode ikke virker (hvilket kan ske, hvis en parameter mangler, eller du ikke har indtastet koden korrekt), kan du altid falde tilbage til optagelse en makro og integrering af den registrerede kode i din VBA.
Brug af andre parametre i VBA -sortering
Der er en række valgfrie parametre, som du kan bruge i din VBA -kode til at tilpasse din sortering.
SortOn
SortOn vælger, om sorteringen vil bruge celleværdier, cellebaggrundsfarver eller cellefontfarver. Standardindstillingen er celleværdier.
| 1 | SortOn = xlSortOnValues |
Bestille
Ordre vælger, om sorteringen skal foretages i stigende eller faldende rækkefølge. Standarden er Stigende.
| 1 | Ordre = xlAscending |
DataOption
DataOption vælger, hvordan tekst og tal sorteres. Parameteren xlSortNormal sorterer numeriske og tekstdata separat. Parameteren xlSortTextAsNumbers behandler tekst som numeriske data for sorteringen. Standard er xlSortNormal.
| 1 | DataOption = xlSortNormal |
Header
Header vælger, om det tabelformede dataområde har en header -række eller ej. Hvis der er en overskriftsrække, vil du ikke have dette inkluderet i sorteringen.
Parameterværdier er xlYes, xlNo og xlYesNoGuess. xlYesNoGuess overlader det til Excel for at afgøre, om der er en overskriftsrække, som let kan føre til inkonsekvente resultater. Brug af denne værdi anbefales ikke.
Standardværdien er XNo (ingen overskriftsrække i dataene). Med importerede data er der normalt en overskriftsrekke, så sørg for at du indstiller denne parameter til xlYes.
| 1 | Header = xlJa |
MatchCase
Denne parameter bestemmer, om sorteringen er store og små bogstaver. Valgværdier er True eller False. Hvis værdien er Falsk, betragtes små bogstaver som de samme som store bogstaver. Hvis værdien er Sand, viser sorteringen forskellen mellem store og små bogstaver i sorteringen. Standardværdien er Falsk.
| 1 | MatchCase = Falsk |
Orientering
Denne parameter bestemmer, om sorteringen vil finde sted nedad gennem rækkerne eller på tværs af alle kolonnerne. Standardværdien er xlTopToBottom (sorter gennem rækker). Du kan bruge xlLeftToRight, hvis du ønsker at sortere horisontalt. Værdier som xlRows og xlColumns fungerer ikke for denne parameter.
| 1 | Orientering = xlTopToBottom |
SortMetode
Denne parameter bruges kun til sortering af kinesiske sprog. Den har to værdier, xlPinYin og xlStroke. xlPinYin er standardværdien.
xlPinYin sorterer ved hjælp af den fonetiske kinesiske sorteringsrækkefølge for tegn. xlStroke sorterer efter antallet af streger i hvert tegn.
Hvis du optager en sorteringsmakro, vil denne parameter altid være inkluderet i koden, og du har måske spekuleret over, hvad den betød. Men medmindre du har at gøre med data på kinesisk, er det ikke til megen nytte.
| 1 | SortMethod = xlPinYin |
Brug af en dobbeltklikhændelse til at sortere tabeldata
I al den funktionalitet, som Microsoft inkluderede i sorteringsmetoderne for VBA, inkluderede det ikke et enkelt middel til at dobbeltklikke på et kolonneoverskrift og sortere hele tabelledataene baseret på den pågældende kolonne.
Dette er en virkelig nyttig funktion at have, og det er let at skrive koden for at gøre det.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Det antages, at data begynder ved celle A1'Opret tre variabler for at fange den valgte målkolonne og den maksimale kolonne og række _'tabeloplysningerneDim Col As Integer, RCol As Long, RRow Like Long'Kontroller, at brugeren har dobbeltklikket på overskriftsrækken - række 1, afslut ellers subHvis Target.Row 1 Afslut derefter Sub'Fang de maksimale rækker i det tabelformede dataområde ved hjælp af objektet' UsedRange 'RCol = ActiveSheet.UsedRange.Columns.Count'Fang de maksimale kolonner i det tabelformede dataområde ved hjælp af objektet' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Kontroller, at brugeren ikke har dobbeltklikket på en kolonne uden for det tabelformede dataområdeHvis Target.Column> RCol derefter afslutter Sub'Fang den kolonne, som brugeren har dobbeltklikket påCol = Target.Column'Fjern tidligere sorteringsparametreActiveSheet.Sort.SortFields.Clear'Sorter det tabelformede område som defineret af maksimale rækker og kolonner fra objektet' UsedRange ''Sorter de tabelformede data ved hjælp af den kolonne, der dobbeltklikkes af brugeren som sorteringsnøglenActiveSheet.Range (Celler (1, 1), Celler (RCol, RRow)). Sorter nøgle1: = Celler (1, Col), Header: = xlJa'Vælg celle A1 - dette er for at sikre, at brugeren ikke efterlades i redigeringstilstand efter sorteringen er _'afsluttetActiveSheet.Range ("A1"). VælgAfslut Sub |
Denne kode skal placeres på dobbeltklikhændelsen på arket, der indeholder tabelformaterne. Det gør du ved at klikke på regnearksnavnet i vinduet Project Explorer (øverste venstre hjørne af VBE-skærmen) og derefter vælge ‘Regneark’ i den første rulleliste i kodevinduet. Vælg 'BeforeDoubleClick' i den anden rullemenu, og du kan derefter indtaste din kode.
Bemærk, at ingen navne, områder eller cellereferencer er hårdkodet i denne kode undtagen flytning af markøren til celle A1 i slutningen af koden. Koden er designet til at få alle de oplysninger, der kræves fra de cellekoordinater, som brugeren har dobbeltklikket på, og størrelsen på det tabelformede dataområde.
Det er ligegyldigt, hvor stort det tabelformede dataområde er. Koden vil stadig hente alle de nødvendige oplysninger, og den kan bruges på data, der opbevares overalt i din projektmappe uden at skulle hardcode i værdier.
Den eneste antagelse, der er gjort, er, at der er en overskriftsrække i tabeldataene, og at dataområdet starter ved celle A1, men startpositionen for dataområdet kan let ændres inden for koden.
Enhver bruger vil blive passende imponeret over denne nye sorteringsfunktion!
Udvidelse af sorteringsfunktionen ved hjælp af VBA
Microsoft har givet enorm fleksibilitet i sorteringen ved hjælp af en lang række parametre. Men inden for VBA kan du tage dette videre.
Antag, at du ønskede at sortere alle værdier med en fed skrift øverst i dine data. Der er ingen måde at gøre dette i Excel, men du kan skrive VBA -koden for at gøre det:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Opret variabler for at holde antallet af rækker og kolonner for de tabelformede dataDim RRække så længe, RCol så længe, N så længe'Slå skærmopdatering fra, så brugeren ikke kan se, hvad der sker - de kan se _'værdier ændres og spekulerer på hvorforApplication.ScreenUpdating = Falsk'Fang antallet af kolonner i det tabelformede dataområdeRCol = ActiveSheet.UsedRange.Columns.Count'Fang antallet af rækker inden for det tabelformede dataområdeRRow = ActiveSheet.UsedRange.Rows.Count'Gennemgå alle rækkerne i det tabelformede dataområde og ignorer overskriftsrækkenFor N = 2 Til RRow'Hvis en celle har en fed skrift, skal du placere en førende 0 -værdi mod celleværdienHvis ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .VærdiAfslut HvisNæste N'Ryd eventuelle tidligere sorteringsparametreActiveSheet.Sort.SortFields.Clear'Sorter det tabelformede dataområde. Alle værdierne med en førende 0 -værdi flytter til toppenActiveSheet.Range (Celler (1, 1), Celler (RCol, RRow)). Sorter nøgle1: = Celler (1, 1), Header: = xlJa'Gennemgå alle rækkerne i det tabelformede dataområde og ignorer overskriftsrækkenFor N = 2 Til RRow'Hvis en celle har en fed skrift, skal du fjerne den førende 0 -værdi fra celleværdien til _'gendanne de oprindelige værdierHvis ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Værdi = Midt (ActiveSheet.Cells (N, 1). Værdi, 2)Afslut HvisNæste N'Slå skærmopdatering til igenApplication.ScreenUpdating = TrueAfslut Sub |
Koden beregner størrelsen på det tabelformede dataområde ved hjælp af objektet 'UsedRange' og gentager derefter alle rækkerne i det. Når der findes en fed skrift, placeres et førende nul foran celleværdien.
En slags finder derefter sted. Da sorteringen er i stigende rækkefølge, vil alt med et nul foran gå til toppen af listen.
Koden gentager derefter gennem alle rækker og fjerner de førende nuller, og gendanner dataene til dets oprindelige værdier.
Denne kode sorterer ved hjælp af fed skrift som et kriterium, men du kan nemt bruge andre celleegenskaber på samme måde, f.eks. Kursiv skrift, punktstørrelse på tekst, understregning af skrifttype, skrifttypenavn osv.
