Så här sorterar du data i Excel med VBA (en steg-för-steg-guide)

Excel har redan ett par sätt att snabbt sortera data.

Du kan enkelt sortera en datamängd med hjälp av sorteringsikonerna i menyfliksområdet eller sorteringsdialogrutan.

Varför behöver du då veta hur du gör detta med VBA?

Att veta hur man sorterar data med VBA kan vara till hjälp när den ingår som en del av din kod. Anta till exempel att du får en datamängd dagligen/veckovis som du behöver formatera och sortera i en specifik ordning.

Du kan skapa ett makro för att göra allt detta åt dig med ett enda klick. Det kommer att spara mycket tid och ansträngning varje gång du gör det.

Om du skapar Excel-instrumentpaneler kan du ta Excel-sorteringsfunktionen till en ny nivå genom att låta användaren sortera data bara genom att dubbelklicka på rubriken (som visas nedan).

Jag kommer att täcka hur man skapar detta senare i denna handledning. Låt oss först snabbt få reda på grunderna.

Förstå intervallet. Sorteringsmetod i Excel VBA

När du sorterar med VBA måste du använda Range.Sort -metoden i din kod.

"Området" skulle vara data som du försöker sortera. Till exempel, om du sorterar data i A1: A10, skulle 'Område' vara intervall ("A1: A10").

Du kan också skapa ett namngivet område och använda det istället för cellreferenser. Om jag till exempel skapar ett namngivet område 'DataRange' för cellerna A1: A10, kan jag också använda Range ("DataRange")

Med sorteringsmetoden måste du tillhandahålla ytterligare information genom parametrar. Nedan följer de viktigaste parametrarna du behöver veta:

• Nyckel - här måste du ange den kolumn som du vill sortera. Om du till exempel vill sortera kolumn A måste du använda nyckeln: = Område ("A1")
• Ordning - här anger du om du vill sortera i stigande ordning eller fallande ordning. Om du till exempel vill sortera i stigande ordning använder du Order: = xlAscending
• Rubrik - här anger du om din datamängd har rubriker eller inte. Om den har rubriker börjar sorteringen från den andra raden i datamängden, annars börjar den från den första raden. För att ange att din data har rubriker använder du Header: = xlJa

Även om dessa tre räcker i de flesta fall kan du läsa mer om parametrarna i den här artikeln.

Låt oss nu se hur du använder Range.Sort -metoden i VBA för att sortera data i Excel.

Sortera en enda kolumn utan rubrik

Antag att du har en enda kolumn utan rubrik (som visas nedan).

Du kan använda koden nedan för att sortera den i stigande ordning.

Sub SortDataWithoutHeader () Område ("A1: A12"). Sorteringsnyckel1: = Område ("A1"), Order1: = xlAscending, Header: = xl Ingen slutsub

Observera att jag har specificerat dataområdet manuellt som intervall ("A1: A12").

Om det kan bli ändringar i data och värden kan läggas till/tas bort kan du använda koden nedan som automatiskt justeras baserat på de fyllda cellerna i datamängden.

Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Observera att jag istället för Range ("A1: A12") har använt, Range ("A1", Range ("A1"). End (xlDown)).

Detta kommer att kontrollera den sista på varandra följande cellen i kolumnen och inkludera den i sorteringen. Om det finns ämnen kommer det bara att överväga data tills den första tomma cellen.

Du kan också skapa ett namngivet område och använda det namngivna intervallet istället för cellreferenser. Till exempel, om det namngivna intervallet är DataSet, skulle din kod nu vara som visas nedan.

Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Låt mig nu snabbt förklara parametrarna som används i exemplen ovan:

• Nyckel1: = Område (“A1”) - Specificerad A1 så att koden skulle veta vilken kolumn som ska sorteras.
• Order1: = xlAscending - Specificerade ordern som xlAscending. Om du vill att den ska vara i fallande ordning använder du xlDescending.
• Rubrik: = xlNo - Anger att det inte finns några rubriker. Detta är också standardvärdet. Så även om du utelämnar detta kommer dina data att sorteras med tanke på att det inte har några rubriker.

Undrar du var du ska lägga den här VBA -koden och hur du kör makrot? Läs denna handledning!

Sortera en enda kolumn med rubrik

I föregående exempel hade datamängden ingen rubrik.

När din data har rubriker måste du ange det i koden så att sorteringen kan börja från den andra raden i datamängden.

Anta att du har en dataset som visas nedan:

Nedan finns koden som kommer att sortera data i fallande ordning baserat på butikernas försäljning.

Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub

Observera att jag har skapat ett namngivet intervall - 'DataRange' och använt det här namngivna intervallet i koden.

Sortera flera kolumner med rubriker

Hittills i denna handledning har vi sett hur man sorterar en enda kolumn (med och utan rubriker).

Vad händer nu om du vill sortera baserat på flera kolumner.

Till exempel i nedanstående datamängd, vad händer om jag först vill sortera efter statskoden och sedan efter butiken.

Här är koden som kommer att sortera flera kolumner på en gång.

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. Applicera Slut med slut Sub

Nedan är resultatet som du kommer att få.

I exemplet ovan sorteras data först efter tillståndskoden (kolumn A). Sedan inom statskoddata sorteras det igen efter butiken (kolumn B). Denna ordning bestäms av koden där du nämner den.

Sortera data med dubbelklick på rubrik

Om du skapar en instrumentpanel eller vill ha mer användarvänlighet i dina rapporter kan du skriva en VBA -kod som sorterar data när du dubbelklickar på rubrikerna.

Något som visas nedan:

Nedan är koden som gör att du kan göra detta:

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"). Sortera Key1: = KeyRange, Header: = xlJa End If End Sub

Observera att jag har skapat ett namngivet intervall (“DataRange”) och har använt det i koden istället för att använda cellreferenser.

Så snart du dubbelklickar på någon av rubrikerna, inaktiverar koden den vanliga dubbelklickfunktionen (som är att komma in i redigeringsläget) och använder den cellen som nyckel när du sorterar data.

Observera också att från och med nu kommer denna kod att sortera alla kolumner endast i stigande ordning.

Observera att dubbelklick är en utlösare som gör att Excel kan köra den angivna koden. Dessa triggers som att dubbelklicka, öppna en arbetsbok, lägga till ett nytt kalkylblad, ändra en cell etc. kallas händelser och kan användas för att köra makron i Excel. Du kan läsa mer om Excel VBA -evenemang här.

Var ska man lägga den här koden?

Du måste klistra in den här koden i kodfönstret i arket där du vill ha denna dubbelklicksorteringsfunktion.

Att göra detta:

• Högerklicka på bladfliken.
• Klicka på Visa kod.
• Klistra in koden i kodfönstret på arket där dina data finns.

Vad händer nu om du vill sortera de två första kolumnerna ("State" och "Store") i stigande ordning, men "Sales" -kolumn i fallande ordning.

Här är koden som 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: = xlJa, Order1: = SortOrder End If End Sub

I koden ovan kontrollerar den om cellen som dubbelklickas är försäljningshuvudet eller inte. Om ja, tilldelar det xlDescending -värdet till variabeln SortOrder, annars gör det xlAscending.

Låt oss nu ta detta ett snäpp längre och visa en visuell markör (pil och färgad cell) i rubriken när den är sorterad.

Något som visas nedan:

För att få detta har jag lagt till ett nytt kalkylblad och gjort följande ändringar i det (du kan ladda ner exempelfilen och följa med):

• Ändrade namnet på det nya bladet till 'BackEnd'.
• I cell B2 anger du en pilsymbol (för att göra detta, gå till Infoga och klicka på alternativet "Symbol").
• Kopiera och klistra in rubrikerna från datamängden till cell A3: C3 i "Backend" -arket.
• Använd följande funktion i cell A4: AC4:

  = OM (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)

• Resten av cellerna fylls automatiskt av VBA -koden när du dubbelklickar på rubrikerna för att sortera kolumnen.

Ditt backend -ark skulle se ut något som visas nedan:

Nu kan du använda koden nedan för att sortera data genom att dubbelklicka på rubrikerna. När du dubbelklickar på en rubrik får den automatiskt pilen i rubriktexten. Observera att jag också har använt villkorlig formatering för att markera cellen också.

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ärde Nästa i Slut Om Slut Sub

Observera att den här koden fungerar bra för hur min data och arbetsbok är konstruerad. Om du ändrar datastrukturen måste du ändra koden i enlighet därmed.

Ladda ner exempelfilen