Sortera data i Excel VBA
Excel har ett utmärkt sätt att sortera en rad tabelldata med hjälp av menyfliksområdet på Excel -gränssnittet, och någon gång kommer du förmodligen att vilja använda den här funktionen i din VBA -kod. Lyckligtvis är detta mycket enkelt att göra.
Dialogrutan i framkant hittar du genom att klicka på ikonen "Sortera" i gruppen "Sortera och filtrera" på fliken "Data" i Excel-bandet. Du måste först välja en rad tabelldata.
Du kan också använda Alt-A-S-S för att visa dialogrutan för en anpassad sortering.
Sorteringsmetoden har förbättrats kraftigt i senare versioner av Excel. Sorten var tidigare begränsad till tre nivåer, men du kan nu ange så många nivåer som du behöver, och detta gäller även inom VBA.
Du kan integrera alla sorteringsfunktioner som erbjuds i Excel -sorteringsdialogen i din VBA -kod. Sorteringsfunktionen i Excel är snabb och snabbare än någonting som du kan skriva själv i VBA, så dra nytta av funktionen.
Observera att när du gör en sortering i VBA förblir sorteringsparametrarna desamma i dialogrutan för gränssnittssortering. De sparas också när arbetsboken sparas.
Om en användare väljer samma intervall med tabelldata och klickar på ikonen Sortera ser de alla dina parametrar som har angetts av din VBA -kod. Om de vill göra en sorts egen design måste de först ta bort alla dina sorteringsnivåer, vilket kommer att vara väldigt irriterande för dem.
Om du inte ändrar parametrarna i din kod och förlitar dig på standardvärden kan du upptäcka att användaren har gjort ändringar som kommer att återspeglas i din VBA -sortering och kan ge oväntade resultat, vilket kan vara mycket svårt att felsöka .
Lyckligtvis finns det en Clear-metod i VBA för att ställa om alla sorteringsparametrar så att användaren ser en ren sorteringsdialogruta
| 1 | Arbetsblad ("Sheet1"). Sort.SortFields.Clear |
Det är bra att rensa sorteringsparametrarna i VBA före och efter att sorteringen har slutförts.
Praktisk användning av sorteringsmetoden i VBA
När tabelldata importeras till Excel är det ofta i en mycket slumpmässig ordning. Den kan importeras från en CSV -fil (kommaseparerade värden) eller från en länk till en databas eller webbsida. Du kan inte lita på att det är i en bestämd ordning från en import till en annan.
Om du presenterar dessa data för en användare i ditt kalkylblad kan det mycket väl vara svårt för användaren att titta på och förstå en enorm mängd data som är ordnade överallt. De kanske vill gruppera data eller klippa ut och klistra in vissa delar av den i en annan applikation.
De kanske också vill se till exempel den högst betalda medarbetaren eller den medarbetare som har längst tjänst.
Med hjälp av sorteringsmetoden i VBA kan du erbjuda alternativ för enkel sortering för användaren.
Exempeldata för att visa Excel -sortering med VBA
Vi behöver först några exempeldata för att matas in i ett kalkylblad, så att koden kan visa alla tillgängliga faciliteter inom VBA.
Kopiera dessa data till ett kalkylblad (kallat 'Sheet1') exakt som visas.
Observera att olika cellbakgrundsfärger och teckensnittsfärger har använts, eftersom dessa också kan användas som sorteringsparametrar. Sortering med hjälp av cell- och teckensnittsfärger kommer att demonstreras senare i artikeln. Observera också att på cell E3 är avdelningsnamnet alla små bokstäver.
Du behöver inte cellinredning och teckensnittsfärger om du inte vill använda exemplen på sortering efter cell och teckensnittsfärg.
Spela in ett makro för en VBA -sortering
VBA -kod för sortering kan bli ganska komplicerad, och det kan ibland vara en bra idé att göra sorteringen på framsidan av Excel och spela in ett makro för att visa dig hur koden fungerar.
Tyvärr kan inspelningsfunktionen generera en enorm mängd kod eftersom den anger nästan alla tillgängliga parametrar, även om standardvärdena för många parametrar är acceptabla för din sorteringsoperation.
Det ger dig dock en mycket bra uppfattning om vad som är inblandat i att skriva VBA -sorteringskod, och en fördel är att den inspelade koden alltid fungerar för dig. Din egen kod kan behöva testas och felsökas för att den ska fungera korrekt.
Kom ihåg att för en operation som utförs i VBA finns det ingen ångra -funktion, så det är en bra idé att kopiera tabelldata till ett annat kalkylblad innan du börjar skriva din sorteringskod.
Som ett exempel, om du gjorde en enkel sortering av provdata ovan, sortering efter anställd, skulle inspelningen generera följande kod:
| 123456789101112131415161718 | Undermakro1 ()Område ("A1: E6"). VäljActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Sheet1"). Sortera.SetRange -intervall ("A1: E6").Header = xlJa.MatchCase = Falskt.Orientation = xlTopToBottom.SortMethod = xlPinYin.TillämpaSluta medAvsluta Sub |
Detta är en ganska stor bit kod, och mycket av det är onödigt på grund av att standardparametrar används. Men om du är under tidspress för att slutföra ett projekt och du behöver lite kod snabbt som fungerar, kan du enkelt klistra in detta i din egen VBA -kod.
Men om du vill göra din kod begriplig och mer elegant, finns det andra alternativ.
VBA -kod för att göra en enkelnivåsortering
Om du bara vill sortera provkoden baserat på medarbetare som tidigare när du spelar in ett makro är koden väldigt enkel:
| 1234567 | Sub SingleLevelSort ()Arbetsblad ("Sheet1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sorteringsnyckel1: = Område ("A1"), Rubrik: = xlJaAvsluta Sub |
Detta är mycket lättare att förstå än den inspelade koden eftersom den accepterar standardinställningarna, t.ex. sortera stigande, så det är inte nödvändigt att ställa in parametrarna till standardvärden. Detta förutsätter att du har använt ett "Clear" -uttalande i förväg.
Metoden "Rensa" används initialt för att säkerställa att varje sorteringsparameter för det kalkylbladet återställs till standardvärdena. En användare kan tidigare ha ställt in parametrarna till olika värden, eller en tidigare sortering i VBA kan ha ändrat dem. Det är viktigt att börja från en standardposition när du sorterar, annars kan du lätt hamna med felaktiga resultat.
Metoden Clear nollställer inte Header -parametern, och det är lämpligt att inkludera detta i din kod, annars kan Excel försöka gissa om det finns en rubrikrad eller inte.
Kör den här koden mot exempeldata och ditt kalkylblad kommer att se ut så här:
VBA-kod för att göra en sortering på flera nivåer
Du kan lägga till så många sorteringsnivåer som krävs inom din kod. Antag att du först ville sortera efter avdelning och sedan efter startdatum men i stigande ordning för avdelningen och fallande ordning för startdatum:
| 12345678 | Sub MultiLevelSort ()Arbetsblad ("Sheet1"). Sort.SortFields.ClearOmråde ("A1: E6"). Sortera nyckel1: = intervall ("E1"), nyckel2: = intervall ("C1"), rubrik: = xlJa, _Order1: = xlAscending, Order2: = xlDescendingAvsluta Sub |
Observera att det nu finns två nycklar i sorteringsuttalandet (nyckel1 och nyckel2). Nyckel1 (avdelningskolumn E) sorteras först och sedan sorteras nyckel2 (kolumn C startdatum) baserat på den första sorteringen.
Det finns också två orderparametrar. Order1 associerar med Key1 (avdelning) och Order2 associerar med Key2 (startdatum). Det är viktigt att se till att nycklar och order hålls i takt med varandra.
Kör den här koden mot exempeldata och ditt kalkylblad kommer att se ut så här:
Avdelningskolumnen (E) är i stigande ordning och kolumnen Startdatum (C) är i fallande ordning.
Effekten av denna sort är mest märkbar när man tittar på Jane Halfacre (rad 3) och John Sutherland (rad 4). De är båda i Finance men Jane Halfacre började innan John Sutherland och datumen visas i fallande ordning.
Om intervallet i tabelldata kan vara av vilken längd som helst kan du använda UsedRange -objektet för att definiera sorteringsintervallet. Detta fungerar bara om det bara finns tabelldata i kalkylbladet eftersom alla värden utanför data ger felaktiga resultat för antalet rader och kolumner.
| 1234567 | Sub MultiLevelSort ()Arbetsblad ("Sheet1"). Sort.SortFields.ClearArbetsblad ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Order1: = xlAscending, Order2: = xlDescendingAvsluta Sub |
Detta förhindrar problemet om du använder metoden 'End (xlDown)' för att definiera sorteringsintervallet. Om det finns en tom cell i mitten av data kommer allt efter den tomma cellen inte att inkluderas, medan UsedRange går ner till den sista aktiva cellen i kalkylbladet.
Sortera efter cellfärg
Sedan Excel 2007 är sortering efter cellens bakgrundsfärg nu möjlig, vilket ger enorm flexibilitet när du utformar din sorteringskod i VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Arbetsblad ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalMed ActiveWorkbook.Worksheets ("Sheet1"). Sortera.SetRange -intervall ("A1: E6").TillämpaSluta medAvsluta Sub |
Denna kod kommer att sortera provdataintervallet (A2: A6) baserat på cellens bakgrundsfärg. Observera att det nu finns en ytterligare parameter som heter 'SortOn' som har värdet 'xlSortOnCellColor'.
Observera att parametern ‘SortOn’ endast kan användas av ett kalkylbladsobjekt och inte av ett intervallobjekt.
På grund av detta är koden mer komplicerad än för en sortering med cellvärden.
Denna kod använder ett nyckelvärde för sorteringen som täcker hela dataområdet, men du kan ange enskilda kolumner som nyckeln för bakgrundsfärgsorteringen och använda flera nivåer som visas tidigare.
När du har kört den här koden kommer ditt kalkylblad att se ut så här:
Sortera efter teckensnittsfärg
Sorteringsfunktionen i Excel VBA erbjuder ännu mer flexibilitet genom att du kan sortera efter teckensnittsfärger:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Arbetsblad ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Med ActiveWorkbook.Worksheets ("Sheet1"). Sortera.SetRange -intervall ("A1: E6").Header = xlJa.Orientation = xlTopToBottom.TillämpaSluta medAvsluta Sub |
Koden för sortering efter teckensnittsfärg är mycket mer komplicerad än för cellens bakgrundsfärg. Parametern 'SortOn' har nu värdet 'xlSortOnFontColor'.
Observera att du måste ange riktning som 'xlTopToBottom' och du måste ange en färg att sortera på. Detta anges i RGB -termer (rött, grönt, svart) med värden från 0 till 255.
När du har kört den här koden mot exempeldata kommer ditt kalkylblad nu att se ut så här:
Att sortera med hjälp av färger i VBA är mycket mer komplicerat än en flernivåsortering, men om din sorteringskod inte fungerar (vilket kan hända om en parameter saknas eller om du inte har angett koden korrekt) kan du alltid falla tillbaka på inspelningen ett makro och integrera den inspelade koden i din VBA.
Använda andra parametrar i VBA -sortering
Det finns ett antal valfria parametrar som du kan använda i din VBA -kod för att anpassa din sortering.
SortOn
SortOn väljer om sorteringen ska använda cellvärden, cellbakgrundsfärger eller cellfärgsfärger. Standardinställningen är cellvärden.
| 1 | SortOn = xlSortOnValues |
Beställa
Order väljer om sorteringen kommer att ske i stigande eller fallande ordning. Standard är stigande.
| 1 | Order = xlAscending |
DataOption
DataOption väljer hur text och siffror sorteras. Parametern xlSortNormal sorterar numeriska och textdata separat. Parametern xlSortTextAsNumbers behandlar text som numeriska data för sorteringen. Standard är xlSortNormal.
| 1 | DataOption = xlSortNormal |
Rubrik
Rubrik väljer om tabelldataintervallet har en rubrikrad eller inte. Om det finns en rubrikrad vill du inte att detta ska ingå i sorteringen.
Parametervärden är xlYes, xlNo och xlYesNoGuess. xlYesNoGuess lämnar det till Excel för att avgöra om det finns en rubrikrad, vilket lätt kan leda till inkonsekventa resultat. Användning av detta värde rekommenderas inte.
Standardvärdet är XNo (ingen rubrikrad i data). Med importerade data finns det vanligtvis en rubrikrad, så se till att du ställer in denna parameter till xlYes.
| 1 | Rubrik = xlJa |
Liknande fall
Denna parameter avgör om sorteringen är skiftlägeskänslig eller inte. Alternativvärden är True eller False. Om värdet är falskt betraktas små bokstäver som desamma som stora bokstäver. Om värdet är sant visar sorteringen skillnaden mellan stora och små bokstäver inom sorteringen. Standardvärdet är Falskt.
| 1 | MatchCase = Falskt |
Orientering
Denna parameter avgör om sorteringen kommer att ske nedåt genom raderna eller över alla kolumner. Standardvärdet är xlTopToBottom (sortera genom rader). Du kan använda xlLeftToRight om du vill sortera horisontellt. Värden som xlRows och xlColumns fungerar inte för denna parameter.
| 1 | Orientering = xlTopToBottom |
SortMethod
Denna parameter används endast för att sortera kinesiska språk. Den har två värden, xlPinYin och xlStroke. xlPinYin är standardvärdet.
xlPinYin sorterar med hjälp av den fonetiska kinesiska sorteringsordningen för tecken. xlStroke sorterar efter antalet drag i varje tecken.
Om du spelar in ett sorteringsmakro kommer denna parameter alltid att ingå i koden, och du kan mycket väl ha undrat vad det innebar. Men om du inte har att göra med data på kinesiska är det till liten nytta.
| 1 | SortMethod = xlPinYin |
Använd en dubbelklickhändelse för att sortera tabelldata
I all funktionalitet som Microsoft inkluderade i sorteringsmetoderna för VBA inkluderade det inte ett enkelt sätt att dubbelklicka på en kolumnrubrik och sortera hela tabelldatan baserat på just den kolumnen.
Detta är en riktigt användbar funktion att ha, och det är lätt att skriva koden för att göra det.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Antagandet görs att data börjar vid cell A1'Skapa tre variabler för att fånga den valda målkolumnen och den maximala kolumnen och raden av _'tabelldataDim Col As Integer, RCol As Long, RRow As Long'Kontrollera att användaren har dubbelklickat på rubrikraden - rad 1, lämna annars subOm Target.Row 1 Avsluta sedan Sub"Fånga de maximala raderna i tabelldataintervallet med objektet" UsedRange "RCol = ActiveSheet.UsedRange.Columns.Count"Fånga de maximala kolumnerna i tabelldataområdet med objektet" UsedRange "RRow = ActiveSheet.UsedRange.Rows.Count'Kontrollera att användaren inte har dubbelklickat på en kolumn utanför tabelldataområdetOm Target.Column> RCol Avsluta sedan Sub'Fånga kolumnen som användaren har dubbelklickat påCol = Target.Column'Rensa bort tidigare sorteringsparametrarActiveSheet.Sort.SortFields.Clear'Sortera det tabellintervall som definieras av maximala rader och kolumner från objektet' UsedRange ''Sortera tabelldata med hjälp av kolumnen som dubbelklickas av användaren som sorteringsnyckelnActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sortera Key1: = Cells (1, Col), Header: = xlJa'Välj cell A1 - detta är för att säkerställa att användaren inte lämnas i redigeringsläge efter sorteringen _'klarActiveSheet.Range ("A1"). VäljAvsluta Sub |
Denna kod måste placeras på dubbelklickhändelsen på arket som innehåller tabelldata. Du gör detta genom att klicka på kalkylbladets namn i Project Explorer-fönstret (övre vänstra hörnet på VBE-skärmen) och sedan välja "Arbetsblad" i den första rullgardinsmenyn i kodfönstret. Välj "BeforeDoubleClick" i den andra rullgardinsmenyn, och du kan sedan ange din kod.
Observera att inga namn, intervall eller cellreferenser är hårdkodade i denna kod förutom att flytta markören till cell A1 i slutet av koden. Koden är utformad för att få all information som krävs från cellkoordinaterna som användaren har dubbelklickat på och storleken på tabelldataintervallet.
Det spelar ingen roll hur stort tabelldataområdet är. Koden hämtar fortfarande all nödvändig information och den kan användas på data som finns var som helst i din arbetsbok utan att behöva hårdkoda värden.
Det enda antagandet som görs är att det finns en rubrikrad i tabelldatan och att dataintervallet börjar vid cell A1, men startpositionen för dataområdet kan enkelt ändras inom koden.
Alla användare kommer att bli imponerade av den nya sorteringsfunktionen!
Utöka sorteringsfunktionen med hjälp av VBA
Microsoft har tillåtit enorm flexibilitet i sorteringen med hjälp av ett brett spektrum av parametrar. Men inom VBA kan du ta detta vidare.
Antag att du ville sortera alla värden med ett fetstil till toppen av din data. Det finns inget sätt att göra detta i Excel, men du kan skriva VBA -koden för att göra det:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Skapa variabler för att hålla antalet rader och kolumner för tabelldataDim RRow As Long, RCol As Long, N As Long'Stäng av skärmuppdatering så att användaren inte kan se vad som händer - de kan se _'värden förändras och undrar varförApplication.ScreenUpdating = Falskt'Fånga antalet kolumner i tabelldataområdetRCol = ActiveSheet.UsedRange.Columns.Count'Fånga antalet rader inom tabelldataintervalletRRow = ActiveSheet.UsedRange.Rows.Count'Iterera genom alla rader i tabelldataområdet och ignorera rubrikradenFör N = 2 Till rad'Om en cell har en fetstil placerar du ett ledande 0 -värde mot cellvärdetOm ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueAvsluta omNästa N'Rensa alla tidigare sorteringsparametrarActiveSheet.Sort.SortFields.Clear'Sortera tabelldataintervallet. Alla värden med ett ledande 0 -värde flyttas till toppenActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sortera Key1: = Cells (1, 1), Header: = xlJa'Iterera genom alla rader i tabelldataområdet och ignorera rubrikradenFör N = 2 Till rad'Om en cell har en fetstil tar du bort det ledande 0 -värdet från cellvärdet till _'återställa de ursprungliga värdenaOm ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1). Value, 2)Avsluta omNästa N'Slå på skärmuppdateringen igenApplication.ScreenUpdating = TrueAvsluta Sub |
Koden räknar ut storleken på tabelldataintervallet med hjälp av objektet "UsedRange" och går sedan igenom alla rader inom det. När en fetstil hittas placeras en ledande nolla framför cellvärdet.
Då sker en sortering. Eftersom sorteringen är i stigande ordning kommer allt med en nolla framför att gå till toppen av listan.
Koden itererar sedan genom alla rader och tar bort de ledande nollorna och återställer data till sina ursprungliga värden.
Den här koden sorterar med fetstil som ett kriterium, men du kan enkelt använda andra cellegenskaper på samma sätt, t.ex. kursiv typsnitt, textstorlek, understrykningsteckensnitt, typsnitt, etc.
