Razvrščanje podatkov v Excelu VBA
Excel ima odlično orodje za razvrščanje različnih tabelarnih podatkov s pomočjo traku na sprednjem delu Excela in v določeni točki boste verjetno želeli uporabiti to funkcijo v kodi VBA. Na srečo je to zelo enostavno narediti.
Prednje pogovorno okno najdete tako, da kliknete ikono »Razvrsti« v skupini »Razvrsti in filtriraj« na zavihku »Podatki« na Excelovem traku. Najprej morate izbrati vrsto tabelarnih podatkov.
Z Alt-A-S-S lahko prikažete tudi pogovorno okno za razvrščanje po meri.
Metoda razvrščanja je bila v poznejših različicah Excela močno izboljšana. Razvrščanje je bilo prej omejeno na tri ravni, zdaj pa lahko vnesete toliko ravni, kot jih potrebujete, kar velja tudi v okviru VBA.
V kodo VBA lahko vključite vse ponujene funkcije razvrščanja v pogovornem oknu Excel Sort. Funkcija razvrščanja v Excelu je hitra in hitrejša od vsega, kar bi lahko sami zapisali v VBA, zato izkoristite funkcionalnost.
Upoštevajte, da pri sortiranju v VBA parametri razvrščanja ostanejo enaki v pogovornem oknu za razvrščanje na sprednji strani. Shranijo se tudi, ko se shrani delovni zvezek.
Če uporabnik izbere isti obseg tabelarnih podatkov in klikne ikono za razvrščanje, bo videl vse vaše parametre, ki jih je vnesla vaša koda VBA. Če želijo narediti lastno zasnovo, bodo morali najprej izbrisati vse stopnje razvrščanja, kar jih bo zelo motilo.
Če tudi ne spremenite parametrov v svoji kodi in se zanašate na privzete vrednosti, boste morda ugotovili, da je uporabnik naredil spremembe, ki se bodo odrazile v vašem razvrščanju VBA, in lahko dajo nepričakovane rezultate, ki jih bo morda težko odpraviti .
Na srečo v VBA obstaja metoda Clear za ponovno nastavitev vseh parametrov razvrščanja, tako da bo uporabnik videl pogovorno okno za čisto razvrščanje
| 1 | Delovni listi ("List1"). Razvrsti.SortFields.Clear |
Dobra praksa je, da počistite parametre razvrščanja v VBA pred in po razvrščanju.
Praktična uporaba metode razvrščanja v VBA
Ko se tabelarni podatki uvozijo v Excel, so pogosto v zelo naključnem vrstnem redu. Lahko se uvozi iz datoteke CSV (vrednosti, ločene z vejicami) ali pa iz povezave do baze podatkov ali spletne strani. Ne morete se zanašati, da bo od enega do drugega uvoza v določenem vrstnem redu.
Če te podatke predstavite uporabniku na svojem delovnem listu, bo uporabnik morda težko pogledal in razumel ogromno podatkov, ki so glede na vrstni red povsod. Morda bodo želeli združiti podatke ali izrezati in prilepiti določene odseke v drugo aplikacijo.
Morda bodo želeli videti tudi na primer najbolje plačanega zaposlenega ali zaposlenega z najdaljšo delovno dobo.
Z metodo razvrščanja v VBA lahko ponudite možnosti, ki uporabniku omogočajo enostavno razvrščanje.
Vzorčni podatki za prikaz razvrščanja v Excelu z VBA
Najprej potrebujemo nekaj vzorčnih podatkov za vnos na delovni list, tako da lahko koda prikaže vse zmogljivosti, ki so na voljo v VBA.
Kopirajte te podatke na delovni list (imenovan "List1"), kot je prikazano.
Upoštevajte, da so bile uporabljene različne barve ozadja celic in barve pisave, saj jih je mogoče uporabiti tudi kot parametre razvrščanja. Razvrščanje po barvah celic in pisav bo prikazano kasneje v članku. Upoštevajte tudi, da je v celici E3 ime oddelka vse z malimi črkami.
Notranjost celice in barve pisave ne potrebujete, če ne želite uporabiti primerov razvrščanja po barvi celice in pisave.
Snemanje makra za sortiranje VBA
Koda VBA za razvrščanje je lahko precej zapletena in včasih je dobro, da to naredite na sprednjem delu Excela in posnamete makro, ki vam pokaže, kako koda deluje.
Na žalost lahko funkcija snemanja ustvari veliko količino kode, ker nastavi skoraj vse razpoložljive parametre, čeprav so privzete vrednosti za številne parametre sprejemljive za vašo operacijo razvrščanja.
Vendar pa vam daje zelo dobro predstavo o tem, kaj je vključeno v pisanje kode za razvrščanje VBA, ena prednost pa je, da vam bo posneta koda vedno delovala. Če želite, da vaša koda deluje pravilno, boste morda potrebovali testiranje in odpravljanje napak.
Ne pozabite, da za operacijo, izvedeno v VBA, ni funkcije razveljavitve, zato je dobro, da kopirate tabelarne podatke na drug delovni list, preden začnete pisati kodo za razvrščanje.
Na primer, če bi zgoraj navedene vzorčne podatke preprosto razvrstili in razvrstili po zaposlenem, bi posnetek ustvaril naslednjo kodo:
| 123456789101112131415161718 | Podmakro1 ()Razpon ("A1: E6"). IzberiteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, vrstni red: = xlAscending, DataOption: = xlSortNormalZ ActiveWorkbook.Worksheets ("List1"). RazvrstiObseg .SetRange ("A1: E6").Glavica = xlDa.MatchCase = Neresnično.Orientation = xlTopToBottom.SortMethod = xlPinYin.UveljaviKončaj sEnd Sub |
To je precej velik del kode in veliko je odveč zaradi uporabljenih privzetih parametrov. Če pa imate čas za dokončanje projekta in potrebujete hitro kodo, ki deluje, jo lahko preprosto prilepite v svojo kodo VBA.
Če pa želite svojo kodo narediti razumljivo in elegantnejšo, so na voljo druge možnosti.
Koda VBA za enostopenjsko razvrščanje
Če želite pri snemanju makra vzorčno kodo razvrstiti samo na podlagi zaposlenega, je koda zelo preprosta:
| 1234567 | Sub SingleLevelSort ()Delovni listi ("List1"). Razvrsti.SortFields.ClearRazpon ("A1: E6"). Tipka za razvrščanje1: = obseg ("A1"), glava: = xlDaEnd Sub |
To je veliko lažje razumeti kot posneta koda, ker sprejema privzete vrednosti, na primer razvrščanje po naraščajoči vrednosti, zato parametrov ni treba nastaviti na privzete vrednosti. To predpostavlja, da ste predhodno uporabili izjavo „Počisti“.
Metoda »Počisti« se najprej uporablja za zagotovitev, da se vsak parameter razvrščanja za ta delovni list nastavi na privzete vrednosti. Uporabnik je morda že nastavil parametre na različne vrednosti ali pa jih je prejšnje razvrščanje v VBA spremenilo. Pomembno je, da pri razvrščanju začnete s privzetega položaja, sicer lahko z lahkoto pridete do napačnih rezultatov.
Metoda Clear ne ponastavi parametra glave, zato je priporočljivo, da to vključite v kodo, sicer lahko Excel poskuša uganiti, ali je vrstica glave prisotna ali ne.
Zaženite to kodo proti vzorčnim podatkom in vaš delovni list bo videti tako:
Koda VBA za razvrščanje na več ravneh
V kodi lahko dodate toliko ravni razvrščanja, kot je potrebno. Recimo, da želite najprej razvrstiti po oddelku in nato po datumu začetka, vendar v naraščajočem vrstnem redu za oddelek in padajočem vrstnem redu za začetni datum:
| 12345678 | Sub MultiLevelSort ()Delovni listi ("List1"). Razvrsti.SortFields.ClearRazpon ("A1: E6"). Tipka za razvrščanje1: = obseg ("E1"), ključ2: = obseg ("C1"), glava: = xlDa, _Vrstni red1: = xlV naraščajočem vrstnem redu, vrstni red2: = xlNadajočEnd Sub |
Upoštevajte, da sta v stavku za razvrščanje zdaj dva ključa (Key1 in Key2). Ključ 1 (stolpec E oddelka) se najprej razvrsti, nato pa se ključ 2 (stolpec Začetni datum C) razvrsti glede na prvo razvrščanje.
Obstajata tudi dva parametra naročila. Order1 je povezan s Key1 (oddelek), Order2 pa s Key2 (datum začetka). Pomembno je zagotoviti, da so ključi in naročila usklajeni.
Zaženite to kodo proti vzorčnim podatkom in vaš delovni list bo videti tako:
Stolpec Oddelek (E) je v naraščajočem vrstnem redu, stolpec Datum začetka (C) pa v padajočem.
Učinek te vrste je najbolj opazen, če pogledamo Jane Halfacre (vrstica 3) in John Sutherland (vrstica 4). Oba sta v financah, Jane Halfacre pa je začela pred Johnom Sutherlandom, datumi pa so prikazani v padajočem vrstnem redu.
Če je obseg tabelarnih podatkov lahko poljuben, lahko uporabite predmet UsedRange za določitev obsega razvrščanja. To bo delovalo le, če so na delovnem listu samo tabelarni podatki, saj bodo vse vrednosti zunaj podatkov napačne rezultate za število vrstic in stolpcev.
| 1234567 | Sub MultiLevelSort ()Delovni listi ("List1"). Razvrsti.SortFields.ClearDelovni listi ("List1"). UsedRange.Sort Key1: = Razpon ("E1"), Key2: = Razpon ("C1"), Glava: = xlDa, _Vrstni red1: = xlV naraščajočem vrstnem redu, vrstni red2: = xlNadajočEnd Sub |
S tem preprečite težavo, če za določitev obsega razvrščanja uporabite metodo ‘End (xlDown)’. Če je na sredini podatkov prazna celica, potem nič za prazno celico ne bo vključeno, medtem ko se UsedRange spusti do zadnje aktivne celice na delovnem listu.
Razvrščanje po barvi celice
Od Excela 2007 je zdaj možno razvrščanje po barvi ozadja celice, kar zagotavlja ogromno prilagodljivosti pri oblikovanju kode za razvrščanje v VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Delovni listi ("List1"). Razvrsti.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Vrstni red: = xlV naraščajoči, DataOption: = xlSortNormalZ ActiveWorkbook.Worksheets ("List1"). Razvrsti.SetRange Range ("A1: E6").UveljaviKončaj sEnd Sub |
Ta koda bo razvrstila območje vzorčnih podatkov (A2: A6) glede na barvo ozadja celice. Upoštevajte, da zdaj obstaja dodaten parameter, imenovan "SortOn", ki ima vrednost "xlSortOnCellColor".
Upoštevajte, da lahko parameter 'SortOn' uporablja le predmet delovnega lista in ne objekt obsega.
Zaradi tega je koda bolj zapletena kot za razvrščanje, ki uporablja vrednosti celic.
Ta koda uporablja vrednost ključa za razvrščanje, ki zajema celoten obseg podatkov, vendar lahko določite posamezne stolpce kot ključ za razvrščanje barv ozadja in uporabite več ravni, kot je prikazano prej.
Po zagonu te kode bo vaš delovni list zdaj videti tako:
Razvrščanje po barvi pisave
Funkcija razvrščanja v Excelu VBA ponuja še večjo prilagodljivost, saj lahko razvrščate po barvah pisav:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Delovni listi ("List1"). Razvrsti.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Z ActiveWorkbook.Worksheets ("List1"). RazvrstiObseg .SetRange ("A1: E6").Glavica = xlDa.Orientation = xlTopToBottom.UveljaviKončaj sEnd Sub |
Koda za razvrščanje po barvi pisave je veliko bolj zapletena kot za barvo ozadja celice. Parameter »SortOn« zdaj vsebuje vrednost »xlSortOnFontColor«.
Upoštevajte, da morate določiti orientacijo kot »xlTopToBottom« in določiti barvo za razvrščanje. To je določeno v izrazih RGB (rdeča, zelena, črna) z vrednostmi od 0 do 255.
Ko zaženete to kodo za vzorčne podatke, bo vaš delovni list zdaj videti tako:
Razvrščanje z uporabo barv v VBA je veliko bolj zapleteno kot razvrščanje na več ravneh, če pa koda za razvrščanje ne bo delovala (kar se lahko zgodi, če parameter manjka ali kode niste vnesli pravilno), se lahko pri snemanju vedno vrnete nazaj makro in vključitev posnete kode v vaš VBA.
Uporaba drugih parametrov pri razvrščanju VBA
Za prilagoditev razvrščanja lahko v kodi VBA uporabite številne neobvezne parametre.
Razvrsti
SortOn izbere, ali bo razvrščanje uporabljalo vrednosti celic, barve ozadja celice ali barve pisave celice. Privzeta nastavitev je Cell Values.
| 1 | SortOn = xlSortOnValues |
Naročite
Vrstni red izbere, ali bo razvrščanje v naraščajočem ali padajočem vrstnem redu. Privzeto je naraščajoče.
| 1 | Naročilo = xlV naraščajočem vrstnem redu |
DataOption
DataOption izbere način razvrščanja besedila in številk. Parameter xlSortNormal številske in besedilne podatke ločuje ločeno. Parameter xlSortTextAsNumbers obravnava besedilo kot številske podatke za razvrščanje. Privzeto je xlSortNormal.
| 1 | DataOption = xlSortNormal |
Glava
Glava izbere, ali ima obseg tabelarnih podatkov vrstico glave ali ne. Če obstaja vrstica z glavo, ne želite, da je to vključeno v razvrščanje.
Vrednosti parametrov so xlYes, xlNo in xlYesNoGuess. xlYesNoGuess prepušča Excelu, da ugotovi, ali obstaja vrstica z glavo, kar lahko zlahka privede do nedoslednih rezultatov. Uporaba te vrednosti ni priporočljiva.
Privzeta vrednost je XNo (v podatkih ni vrstice glave). Pri uvoženih podatkih je običajno vrstica glave, zato nastavite ta parameter na xlDa.
| 1 | Glava = xlDa |
MatchCase
Ta parameter določa, ali je razvrščanje občutljivo na velike in male črke. Vrednosti možnosti so True ali False. Če je vrednost False, se male vrednosti štejejo za enake kot velike. Če je vrednost True, bo razvrščanje pokazalo razliko med velikimi in malimi vrednostmi znotraj razvrščanja. Privzeta vrednost je False.
| 1 | MatchCase = False |
Usmerjenost
Ta parameter določa, ali bo razvrščanje potekalo navzdol po vrsticah ali po vseh stolpcih. Privzeta vrednost je xlTopToBottom (razvrščanje po vrsticah). Če želite razvrstiti vodoravno, lahko uporabite xlLeftToRight. Vrednosti, kot sta xlRows in xlColumns, ne delujejo za ta parameter.
| 1 | Usmerjenost = xlTopToBottom |
SortMethod
Ta parameter se uporablja samo za razvrščanje kitajskih jezikov. Ima dve vrednosti, xlPinYin in xlStroke. xlPinYin je privzeta vrednost.
xlPinYin razvršča po fonetičnem kitajskem vrstnem redu razvrščanja znakov. xlStroke razvrsti po količini potez v vsakem znaku.
Če posnamete makro za razvrščanje, bo ta parameter vedno vključen v kodo in morda ste se spraševali, kaj to pomeni. Če pa s podatki ne govorite v kitajščini, je to malo koristno.
| 1 | SortMethod = xlPinYin |
Uporaba dogodka dvojnega klika za razvrščanje tabelarnih podatkov
Med vsemi funkcijami, ki jih je Microsoft vključil v metode razvrščanja za VBA, ni vključeval preprostega načina za dvoklik na glavo stolpca in razvrščanje celotnih tabelarnih podatkov na podlagi tega stolpca.
To je zelo uporabna funkcija in kodo je enostavno napisati.
| 12345678910111213141516171819202122232425262728293031323334 | Zasebni pod -delovni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)"Domneva se, da se podatki začnejo v celici A1'Ustvarite tri spremenljivke za zajem izbranega ciljnega stolpca ter največji stolpec in vrstico _"tabelarni podatkiZatemni Col kot celo število, RCol tako dolgo, RRow tako dolgo'Preverite, ali je uporabnik dvakrat kliknil na vrstico glave - vrstica 1, sicer zapustite podrejenoČe je Target.Row 1, nato zapustite Sub'Z objektom' UsedRange 'zajemite največje število vrstic v obsegu tabelarnih podatkovRCol = ActiveSheet.UsedRange.Columns.Count'Z objektom' UsedRange 'zajemite največje število stolpcev v obsegu tabelarnih podatkovRRow = ActiveSheet.UsedRange.Rows.Count"Preverite, ali uporabnik ni dvakrat kliknil stolpca izven obsega tabelarnih podatkovČe Target.Column> RCol Potem zapustite Sub'Ujemite stolpec, na katerega je uporabnik dvakrat kliknilStolpec = cilj'Počisti prejšnje parametre razvrščanjaActiveSheet.Sort.SortFields.Clear'Razvrstite obseg tabel, kot je določeno z največ vrsticami in stolpci iz predmeta' UsedRange ''Razvrstite tabelarne podatke tako, da uporabnik dvakrat klikne stolpec kot ključ za razvrščanjeActiveSheet.Range (celice (1, 1), celice (RCol, RRow)). Tip razvrščanja1: = celice (1, stolpec), glava: = xl da'Izberite celico A1 - s tem zagotovite, da uporabnik po razvrščanju ne ostane v načinu za urejanje _'dokončanoActiveSheet.Range ("A1"). IzberiteEnd Sub |
To kodo je treba postaviti na dogodek dvojnega klika na listu, ki vsebuje tabelarne podatke. To naredite tako, da kliknete ime delovnega lista v oknu Raziskovalca projektov (zgornji levi kot zaslona VBE) in nato v prvem spustnem meniju v oknu za kodo izberete "Delovni list". V drugem spustnem meniju izberite »BeforeDoubleClick« in nato lahko vnesete svojo kodo.
Upoštevajte, da v to kodo ni trdo kodirano nobeno ime, obseg ali sklic na celico, razen za premik kurzorja v celico A1 na koncu kode. Koda je zasnovana tako, da pridobi vse potrebne podatke iz koordinat celic, na katere je uporabnik dvakrat kliknil, in velikosti obsega tabelarnih podatkov.
Ni pomembno, kako velik je obseg tabelarnih podatkov. Koda bo še vedno pobrala vse zahtevane podatke in jo lahko uporabite za podatke, ki so shranjeni kjer koli v vašem delovnem zvezku, ne da bi morali trdo kodirati vrednosti.
Edina domneva je, da je v tabelarnih podatkih vrstica glave in da se obseg podatkov začne v celici A1, vendar se lahko začetni položaj podatkovnega območja zlahka spremeni v kodi.
Vsak uporabnik bo nad to novo funkcijo razvrščanja ustrezno navdušen!
Razširitev funkcije razvrščanja z uporabo VBA
Microsoft je omogočil izjemno prilagodljivost pri razvrščanju z uporabo širokega nabora parametrov. V okviru VBA pa lahko to nadaljujete.
Predpostavimo, da želite poljubno vrednost s krepko pisavo razvrstiti na vrh podatkov. V Excelu tega ni mogoče storiti, vendar lahko za to napišete kodo VBA:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | PodrazvrstiByBold ()'Ustvarite spremenljivke, ki bodo imele število vrstic in stolpcev za tabelarne podatkeDim RRow As Long, RCol As Long, N As Long"Izklopite posodabljanje zaslona, tako da uporabnik ne vidi, kaj se dogaja - lahko vidi _"se vrednote spreminjajo in se sprašujem, zakajApplication.ScreenUpdating = Napačno'Zajemite število stolpcev v obsegu tabelarnih podatkovRCol = ActiveSheet.UsedRange.Columns.Count'Zajemite število vrstic v obsegu tabelarnih podatkovRRow = ActiveSheet.UsedRange.Rows.Count'Ponovite vse vrstice v obsegu tabelarnih podatkov, pri čemer ne upoštevate vrstice glaveZa N = 2 Za RRow"Če ima celica krepko pisavo, postavite vrednost 0 pred vrednostjo celiceČe je ActiveSheet.Cells (N, 1) .Font.Bold = True PotemActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .VrednostKonec ČeNaslednji N.'Počisti vse prejšnje parametre razvrščanjaActiveSheet.Sort.SortFields.Clear'Razvrsti obseg tabelarnih podatkov. Vse vrednosti z začetno vrednostjo 0 se premaknejo na vrhActiveSheet.Range (celice (1, 1), celice (RCol, RRow)). Tip razvrščanja1: = celice (1, 1), glava: = xl da'Ponovite vse vrstice v obsegu tabelarnih podatkov, pri čemer ne upoštevate vrstice glaveZa N = 2 Za RRow"Če ima celica krepko pisavo, odstranite začetno vrednost 0 iz vrednosti celice v _'obnovite prvotne vrednostiČe je ActiveSheet.Cells (N, 1) .Font.Bold = True PotemActiveSheet.Cells (N, 1) .Vrednost = sredina (ActiveSheet.Cells (N, 1) .Vrednost, 2)Konec ČeNaslednji N.'Ponovno vklopite posodabljanje zaslonaApplication.ScreenUpdating = ResEnd Sub |
Koda določi velikost obsega tabelarnih podatkov s predmetom 'UsedRange' in nato ponovi vse vrstice v njem. Ko najdemo krepko pisavo, se pred vrednostjo celice postavi začetna ničla.
Nato pride do neke vrste. Ker je razvrščanje v naraščajočem vrstnem redu, bo vse, kar ima nič pred seboj, na vrhu seznama.
Koda nato ponovi vse vrstice in odstrani začetne ničle ter obnovi podatke na prvotne vrednosti.
Ta koda razvršča z uporabo krepkih pisav kot merilo, lahko pa na enak način preprosto uporabite druge značilnosti celic, na primer poševno pisavo, velikost točke besedila, podčrtano pisavo, ime pisave itd.
