Kako razvrstiti podatke v Excelu z uporabo VBA (Vodnik po korakih)

Excel že ima nekaj načinov za hitro razvrščanje podatkov.

Podatkovni niz lahko preprosto razvrstite z uporabo ikon za razvrščanje na traku ali v pogovornem oknu za razvrščanje.

Zakaj morate potem vedeti, kako to storiti z uporabo VBA?

Poznavanje načina razvrščanja podatkov z uporabo VBA je lahko koristno, če je vključeno kot del vaše kode. Recimo, da dnevno/tedensko dobite nabor podatkov, ki ga morate formatirati in razvrstiti po določenem vrstnem redu.

Ustvarite lahko makro, ki vam vse to naredi z enim klikom. To vam bo prihranilo veliko časa in truda vsakič, ko to storite.

Če ustvarite Excelove nadzorne plošče, lahko zmožnost razvrščanja v Excelu popeljete na novo raven, tako da uporabniku omogočite razvrščanje podatkov samo z dvoklikom na glavo (kot je prikazano spodaj).

V nadaljevanju te vadnice bom opisal, kako to ustvariti. Najprej hitro razčistimo osnove.

Razumevanje metode Range.Sort v Excelu VBA

Pri razvrščanju z uporabo VBA morate v svoji kodi uporabiti metodo Range.Sort.

"Obseg" bi bili podatki, ki jih poskušate razvrstiti. Na primer, če podatke razvrščate v A1: A10, bi bil »Obseg« obseg (»A1: A10«).

Ustvarite lahko tudi imenovani obseg in ga uporabite namesto sklicev na celice. Če na primer za celice A1: A10 ustvarim imenovano območje »DataRange«, lahko uporabim tudi Range (»DataRange«)

Z metodo razvrščanja morate s parametri zagotoviti nekaj dodatnih informacij. Spodaj so navedeni ključni parametri, ki jih morate poznati:

• Ključ - tukaj morate določiti stolpec, ki ga želite razvrstiti. Na primer, če želite razvrstiti stolpec A, morate uporabiti key: = Range ("A1")
• Naročite - tukaj določite, ali želite razvrščanje po naraščajočem ali padajočem vrstnem redu. Na primer, če želite razvrščanje po naraščajočem vrstnem redu, boste uporabili vrstni red: = xlAscending
• Glava - tukaj določite, ali ima vaš nabor podatkov glave ali ne. Če ima glave, se razvrščanje začne iz druge vrstice nabora podatkov, sicer pa se začne iz prve vrstice. Če želite določiti, da imajo vaši podatki glave, uporabite Header: = xlYes

Čeprav ti trije v večini primerov zadostujejo, lahko v tem članku preberete več o parametrih.

Zdaj pa poglejmo, kako uporabiti metodo Range.Sort v VBA za razvrščanje podatkov v Excelu.

Razvrščanje enega stolpca brez glave

Recimo, da imate en sam stolpec brez glave (kot je prikazano spodaj).

Spodnjo kodo lahko razvrstite po naraščajočem vrstnem redu.

Podrazpored SortDataWithoutHeader () Obseg ("A1: A12"). Ključ razvrščanja1: = Obseg ("A1"), Vrstica1: = xlAscending, Glava: = xlNe End Sub

Upoštevajte, da sem ročno določil obseg podatkov kot obseg (“A1: A12”).

V primeru, da bi prišlo do sprememb podatkov in bi lahko bile dodane/izbrisane vrednosti, lahko uporabite spodnjo kodo, ki se samodejno prilagodi glede na zapolnjene celice v naboru podatkov.

PodrazvrstitevDataWithoutHeader () Obseg ("A1", Obseg ("A1"). Konec (xlDown)). Ključ razvrstitve1: = Obseg ("A1"), Vrstica1: = xlAscending, Glava: = xlNo End Sub

Upoštevajte, da sem namesto Range (“A1: A12”) uporabil, Range (“A1”, Range (“A1”). End (xlDown)).

S tem boste preverili zadnjo zaporedno zapolnjeno celico v stolpcu in jo vključili v razvrščanje. Če so prazna polja, bo podatke upošteval le do prve prazne celice.

Ustvarite lahko tudi imenovani obseg in namesto sklicev na celice uporabite ta imenovani obseg. Na primer, če je imenovani obseg DataSet, bi bila vaša koda zdaj prikazana spodaj.

Podrazred SortDataWithoutHeader () Range ("DataRange"). Key Sort1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Zdaj pa naj hitro razložim parametre, uporabljene v zgornjih primerih:

• Key1: = Range (“A1”) - Določen A1, tako da koda ve, kateri stolpec naj razvrsti.
• Vrstni red1: = xlAscending - Naročilo je določeno kot xlAscending. Če želite, da je v padajočem vrstnem redu, uporabite xlDescending.
• Glava: = xlNo - Določeno je, da ni glav. To je tudi privzeta vrednost. Torej, tudi če to izpustite, bodo vaši podatki razvrščeni, saj nimajo naslovov.

Se sprašujete, kam postaviti to kodo VBA in kako zagnati makro? Preberite to vadnico!

Razvrščanje enega stolpca z glavo

V prejšnjem primeru nabor podatkov ni imel glave.

Ko imajo vaši podatki glave, morate to določiti v kodi, da se lahko razvrščanje začne iz druge vrstice nabora podatkov.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Spodaj je koda, ki bo podatke razvrstila po padajočem vrstnem redu glede na prodajo v trgovinah.

Sub SortDataWithHeader () Range ("DataRange"). Key Sort1: = Range ("C1"), Order1: = xl Descending End Sub

Upoštevajte, da sem ustvaril imenovano območje - "DataRange" in uporabil ta imenovani obseg v kodi.

Razvrščanje več stolpcev z glavo

Doslej smo v tej vadnici videli, kako razvrstiti en sam stolpec (z glavami in brez njih).

Kaj pa, če želite razvrstiti na podlagi več stolpcev.

Na primer, v spodnjem nizu podatkov, kaj če želim najprej razvrstiti po kodi stanja in nato po shrambi.

Tu je koda, ki bo hkrati razvrstila več stolpcev.

Podrazvrsti več stolpcev () z ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Glava = xlDa. Uporabi End with End Sub

Spodaj je rezultat, ki ga boste dobili.

V zgornjem primeru so podatki najprej razvrščeni po kodi stanja (stolpec A). Nato se znotraj podatkov kode stanja znova razvrsti po trgovini (stolpec B). Ta vrstni red je določen s kodo, v kateri ga omenjate.

Razvrščanje podatkov z dvojnim klikom na glavo

Če ustvarjate nadzorno ploščo ali želite v poročilih enostavnejšo uporabo, lahko napišete kodo VBA, ki bo razvrstila podatke, ko dvakrat kliknete glave.

Nekaj, kot je prikazano spodaj:

Spodaj je koda, ki vam bo to omogočila:

Zasebni pod -delovni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Če Target.Row = 1 In Target.Column <= ColumnChen Prekliči = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlDa End If End Sub

Upoštevajte, da sem ustvaril imenovano območje (»DataRange«) in ga uporabil v kodi, namesto da bi uporabil sklice na celice.

Takoj, ko dvokliknete katero koli glavo, koda onemogoči običajno funkcijo dvojnega klika (za prehod v način urejanja) in to celico uporabi kot ključ med razvrščanjem podatkov.

Upoštevajte tudi, da bo ta koda doslej vse stolpce razvrščala samo v naraščajočem vrstnem redu.

Upoštevajte, da je dvojni klik sprožilec, ki Excelu omogoča zagon navedene kode. Ti sprožilci, kot so dvoklik, odpiranje delovnega zvezka, dodajanje novega delovnega lista, spreminjanje celice itd., Se imenujejo dogodki in se lahko uporabljajo za izvajanje makrov v Excelu. Več o dogodkih Excel VBA si lahko preberete tukaj.

Kam postaviti to kodo?

To kodo morate prilepiti v kodno okno lista, v katerem želite to funkcijo razvrščanja dvojnega klika.

Storiti to:

• Z desno miškino tipko kliknite zavihek lista.
• Kliknite Ogled kode.
• Kodo prilepite v okno za kode lista, v katerem so vaši podatki.

Kaj pa, če želite prva dva stolpca („Stanje“ in „Shrani“) razvrstiti po naraščajočem vrstnem redu, stolpec „Prodaja“ pa po padajočem.

Tukaj je koda, ki bo to naredila:

Zasebni pod -delovni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Če Target.Row = 1 In Target.Column <= ColumnChen Prekliči = True Set KeyRange = Range (Target.Address) Če je Target.Value = "Prodaja", potem SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Razvrsti Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End Konec End Sub

V zgornji kodi preveri, ali je celica, ki jo dvokliknete, glava Sales ali ne. Če da, potem spremenljivki SortOrder dodeli vrednost xlDescending, sicer pa xlAscending.

Zdaj pa pojdimo še to za zarezo in pri razvrščanju v glavi prikazujmo vizualni označevalec (puščica in barvna celica).

Nekaj, kot je prikazano spodaj:

Da bi to dobil, sem dodal nov delovni list in vanj naredil naslednje spremembe (datoteko z vzorcem lahko prenesete in sledite):

• Ime novega lista je spremenjeno v 'BackEnd'.
• V celico B2 vnesite simbol puščice (če želite to narediti, pojdite na Vstavi in ​​kliknite možnost »Simbol«).
• Kopirajte in prilepite glave iz podatkovnega niza v celico A3: C3 na listu 'Backend'.
• V celici A4 uporabite naslednjo funkcijo: AC4:

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

• Preostale celice bo samodejno zapolnila koda VBA, ko dvokliknete glave, da razvrstite stolpec.

Vaš zaledni list bi izgledal tako, kot je prikazano spodaj:

Zdaj lahko s spodnjo kodo razvrstite podatke tako, da dvokliknete glave. Ko dvokliknete glavo, bo samodejno prejela puščico v besedilu glave. Upoštevajte, da sem za označevanje celice uporabil tudi pogojno oblikovanje.

Zasebni pod -delovni list_BeforeDoubleClick (Cilj ByVal kot obseg, prekliči kot logično) Zatemni obseg ključev kot obseg Zatemni število stolpcev kot celo število ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Če je Target.Row = 1 In Target.Column <= ColumnChen Prekliči = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlDa delovni listi ("BackEnd" ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Delovni listi (" Backend "). Range (" A4 "). Offset (0, i - 1) .Vrednost Naprej i Konec Če je konec Sub

Upoštevajte, da ta koda dobro deluje pri oblikovanju mojih podatkov in delovnega zvezka. Če spremenite strukturo podatkov, boste morali ustrezno spremeniti kodo.

Prenesite datoteko z vzorcem