Sortiranje podataka u Excelu VBA
Excel ima izvrsno sredstvo za razvrstavanje niza tabličnih podataka pomoću vrpce na prednjem dijelu programa Excel, a u jednom ćete trenutku vjerojatno htjeti koristiti ovu funkcionalnost unutar svog VBA koda. Srećom, to je vrlo lako učiniti.
Prednji dijaloški okvir nalazi se klikom na ikonu "Sortiraj" u grupi "Sortiraj i filtriraj" na kartici "Podaci" na vrpci programa Excel. Najprije morate odabrati niz tabličnih podataka.
Također možete koristiti Alt-A-S-S za prikaz dijaloškog okvira za prilagođeno sortiranje.
Način sortiranja uvelike je poboljšan u kasnijim verzijama programa Excel. Nekada je vrsta bila ograničena na tri razine, ali sada možete unijeti onoliko razina koliko vam je potrebno, a to se također odnosi na VBA.
Sve ponuđene funkcije razvrstavanja možete uključiti u dijaloški okvir Sortiranje programa Excel u svoj VBA kôd. Funkcija sortiranja u Excelu je brza i brža od svega što biste sami mogli napisati u VBA -u, pa iskoristite tu funkcionalnost.
Imajte na umu da pri sortiranju u VBA parametri sortiranja ostaju isti u dijaloškom okviru za sortiranje na prednjoj strani. Također se spremaju kada se spremi radna knjiga.
Ako korisnik odabere isti raspon tabličnih podataka i klikne na ikonu Sortiranje, vidjet će sve vaše parametre koje je unijeo vaš VBA kôd. Ako žele napraviti neku vrstu vlastitog dizajna, morat će prvo izbrisati sve razine razvrstavanja, što će im biti jako neugodno.
Također, ako ne promijenite parametre u svom kodu i oslanjate se na zadane vrijednosti, možda ćete otkriti da je korisnik napravio promjene koje će se odraziti na vašu VBA sortiranje i mogu dati neočekivane rezultate, što može biti vrlo teško otkloniti pogreške .
Srećom, u VBA postoji metoda Clear za ponovno postavljanje svih parametara sortiranja tako da će korisnik vidjeti čisti dijaloški okvir za sortiranje
| 1 | Radni listovi ("List1"). Sortiraj.SortFields.Clear |
Dobra je praksa brisanje parametara sortiranja u VBA -i prije i nakon dovršetka sortiranja.
Praktična uporaba metode sortiranja u VBA
Kada se tablični podaci uvezu u Excel, oni su često nasumičnim redoslijedom. Može se uvesti iz CSV datoteke (vrijednosti odvojene zarezima) ili može doći s veze na bazu podataka ili web stranicu. Ne možete se osloniti na to da će od jednog uvoza dolaziti u određenom redoslijedu.
Ako prezentirate te podatke korisniku na svom radnom listu, korisniku će možda biti teško pogledati i razumjeti ogromnu količinu podataka koja je u smislu redoslijeda posvuda. Možda će htjeti grupirati podatke ili izrezati i zalijepiti određene dijelove istih u drugu aplikaciju.
Možda bi također htjeli vidjeti, na primjer, najplaćenijeg zaposlenika ili zaposlenika s najdužim stažom.
Korištenjem metode Sortiranje u VBA -u možete ponuditi opcije koje omogućuju jednostavno sortiranje za korisnika.
Uzorci podataka za demonstriranje Excel sortiranja s VBA
Prvo nam je potrebno neke uzorke podataka za unos u radni list, tako da kôd može pokazati sve sadržaje dostupne unutar VBA -e.
Kopirajte ove podatke u radni list (nazvan "List1") točno kako je prikazano.
Imajte na umu da su korištene različite boje pozadine ćelije i boje fonta jer se one mogu koristiti i kao parametri sortiranja. Razvrstavanje pomoću boja ćelija i fontova bit će prikazano kasnije u članku. Također imajte na umu da je u ćeliji E3 naziv odjela mala slova.
Unutrašnjost ćelije i boje fonta ne trebaju vam ako ne želite koristiti primjere razvrstavanja po ćeliji i boji fonta.
Snimanje makronaredbi za VBA sortiranje
VBA kôd za razvrstavanje može se prilično zakomplicirati, a ponekad je dobra ideja izvršiti sortiranje na prednjem kraju programa Excel i snimiti makronaredbu koja će vam pokazati kako kôd radi.
Nažalost, funkcija snimanja može generirati ogromnu količinu koda jer postavlja gotovo sve dostupne parametre, iako su zadane vrijednosti za mnoge parametre prihvatljive za vašu operaciju sortiranja.
Međutim, daje vam vrlo dobru predodžbu o tome što je uključeno u pisanje VBA koda za sortiranje, a jedna je prednost što će vam snimljeni kod uvijek raditi. Za vlastiti kôd možda će biti potrebno testiranje i ispravljanje pogrešaka kako bi ispravno radio.
Zapamtite da za operaciju obavljenu u VBA -i nema funkcije poništavanja pa je dobra ideja napraviti kopiju tabličnih podataka na drugom radnom listu prije nego počnete pisati kôd za razvrstavanje.
Na primjer, ako ste izvršili jednostavno sortiranje gore navedenih primjera podataka, sortirajući ih po zaposleniku, snimka bi generirala sljedeći kôd:
| 123456789101112131415161718 | Podmakro1 ()Raspon ("A1: E6"). OdaberiteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Raspon ("A2: A6"), _Sortiraj: = xlSortOnValues, Redoslijed: = xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("List1"). Sortiraj.Domet raspona ("A1: E6").Header = xlDa.MatchCase = Netačno.Orijentacija = xlTopToBottom.SortMethod = xlPinYin.Prijavi seZavrši sKraj podm |
Ovo je prilično velik dio koda, a mnogo toga je nepotrebno zbog korištenja zadanih parametara. Međutim, ako ste pod vremenskim pritiskom da dovršite projekt i brzo vam je potreban neki kôd koji funkcionira, možete ga jednostavno zalijepiti u svoj vlastiti VBA kôd.
Međutim, ako želite svoj kôd učiniti razumljivim i elegantnijim, dostupne su i druge mogućnosti.
VBA kod za sortiranje na jednoj razini
Ako želite razvrstati uzorak koda na temelju zaposlenika samo kao i prije pri snimanju makronaredbe, kôd je vrlo jednostavan:
| 1234567 | Sub SingleLevelSort ()Radni listovi ("List1"). Sortiraj.SortFields.ClearRaspon ("A1: E6"). Tipka za sortiranje1: = Raspon ("A1"), zaglavlje: = xlDaKraj podm |
To je daleko lakše razumjeti od snimljenog koda jer prihvaća zadane vrijednosti, npr. Sortiranje uzlazno, pa nema potrebe postavljati parametre na zadane vrijednosti. To pretpostavlja da ste prethodno upotrijebili izjavu 'Obriši'.
Metoda 'Clear' se u početku koristi kako bi se osiguralo da se svaki parametar sortiranja za taj radni list vrati na zadane vrijednosti. Korisnik je možda prethodno postavio parametre na različite vrijednosti ili ih je ranije sortiranje u VBA -i promijenilo. Prilikom sortiranja važno je krenuti od zadanog položaja, jer biste u protivnom mogli lako doći do netočnih rezultata.
Metoda Clear ne poništava parametar zaglavlja i preporučljivo je to uključiti u vaš kôd, u protivnom Excel može pokušati pogoditi je li redak zaglavlja prisutan ili ne.
Pokrenite ovaj kôd za uzorke podataka i vaš će radni list izgledati ovako:
VBA kôd za sortiranje na više razina
U svom kodu možete dodati onoliko razina razvrstavanja koliko je potrebno. Pretpostavimo da želite prvo sortirati prema odjelu, a zatim prema datumu početka, ali uzlaznim redoslijedom za odjel i silaznim redoslijedom za datum početka:
| 12345678 | Sub MultiLevelSort ()Radni listovi ("List1"). Sortiraj.SortFields.ClearRaspon ("A1: E6"). Tipka za sortiranje1: = raspon ("E1"), tipka 2: = raspon ("C1"), zaglavlje: = xlDa, _Redoslijed1: = xlAscending, Red2: = xlDescendingKraj podm |
Imajte na umu da sada postoje dva ključa u naredbi sortiranja (Key1 i Key2). Ključ1 (stupac E odjela) prvo se sortira, a zatim se ključ2 (stupac C datuma početka) sortira na temelju prvog sortiranja.
Postoje i dva parametra narudžbe. Order1 pridružuje Key1 (odjel) i Order2 pridružuje Key2 (datum početka). Važno je osigurati da su ključevi i narudžbe međusobno usklađeni.
Pokrenite ovaj kôd za uzorke podataka i vaš će radni list izgledati ovako:
Stupac Odjel (E) je u rastućem redoslijedu, a stupac Datum početka (C) u opadajućem redoslijedu.
Učinak ove vrste najuočljiviji je kada se pogledaju Jane Halfacre (red 3) i John Sutherland (red 4). Oboje su u financijama, ali Jane Halfacre započela je prije Johna Sutherlanda, a datumi su prikazani silaznim redoslijedom.
Ako raspon tabličnih podataka može biti bilo koje duljine, možete koristiti objekt UsedRange za definiranje raspona sortiranja. To će funkcionirati samo ako na radnom listu postoje samo tablični podaci jer će sve vrijednosti izvan podataka dati netočne rezultate za broj redaka i stupaca.
| 1234567 | Sub MultiLevelSort ()Radni listovi ("List1"). Sortiraj.SortFields.ClearRadni listovi ("List1"). UsedRange.Sort Key1: = Raspon ("E1"), Key2: = Raspon ("C1"), Zaglavlje: = xlDa, _Redoslijed1: = xlAscending, Red2: = xlDescendingKraj podm |
To sprječava problem ako koristite metodu 'Kraj (xlDown)' za definiranje raspona sortiranja. Ako se u sredini podataka nalazi prazna ćelija, ništa nakon prazne ćelije neće biti uključeno, dok se UsedRange spušta do posljednje aktivne ćelije na radnom listu.
Sortiranje prema boji ćelije
Od Excela 2007, sada je moguće sortiranje prema boji pozadine ćelije, što pruža ogromnu fleksibilnost pri dizajniranju koda za sortiranje u VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Radni listovi ("List1"). Sortiraj.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Raspon ("A2: A6"), _Sortiraj: = xlSortOnCellColor, Redoslijed: = xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("List1"). Sortiraj.Domet raspona ("A1: E6").Prijavi seZavrši sKraj podm |
Ovaj će kod sortirati raspon uzorka podataka (A2: A6) na temelju boje pozadine ćelije. Imajte na umu da sada postoji dodatni parametar pod nazivom "SortOn" koji ima vrijednost "xlSortOnCellColor".
Imajte na umu da parametar "SortOn" može koristiti samo objekt radnog lista, a ne objekt raspona.
Zbog toga je kôd složeniji nego za sortiranje koje koristi vrijednosti ćelija.
Ovaj kôd koristi vrijednost ključa za sortiranje koje pokriva cijeli raspon podataka, ali možete navesti pojedinačne stupce kao ključ za sortiranje boje pozadine i koristiti više razina kao što je prikazano ranije.
Nakon pokretanja ovog koda, vaš će radni list sada izgledati ovako:
Sortiranje prema boji fonta
Funkcija sortiranja u programu Excel VBA nudi još veću fleksibilnost jer možete sortirati prema bojama fonta:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Radni listovi ("List1"). Sortiraj.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)S ActiveWorkbook.Worksheets ("List1"). Sortiraj.Domet raspona ("A1: E6").Header = xlDa.Orijentacija = xlTopToBottom.Prijavi seZavrši sKraj podm |
Kôd za sortiranje prema boji fonta daleko je složeniji nego za boju pozadine ćelije. Parametar "SortOn" sada ima vrijednost "xlSortOnFontColor".
Imajte na umu da morate navesti orijentaciju kao "xlTopToBottom" i morate navesti boju za sortiranje. To je navedeno u RGB izrazima (crveno, zeleno, crno) s vrijednostima od 0 do 255.
Nakon pokretanja ovog koda za uzorke podataka, vaš će radni list sada izgledati ovako:
Sortiranje pomoću boja u VBA-i daleko je složenije od sortiranja na više razina, ali ako kôd za sortiranje neće funkcionirati (što se može dogoditi ako nedostaje parametar ili niste unijeli kôd ispravno), uvijek se možete vratiti na snimanje makronaredbu i integriranje snimljenog koda u vaš VBA.
Korištenje drugih parametara u VBA sortiranju
Postoje brojni izborni parametri koje možete koristiti u svom VBA kodu za prilagodbu sortiranja.
Sortiraj
SortOn odabire hoće li sortiranje koristiti vrijednosti ćelija, boje pozadine ćelije ili boje fonta ćelije. Zadana postavka je Cell Values.
| 1 | SORTOn = xlSortOnValues |
Narudžba
Narudžba bira hoće li se sortiranje vršiti uzlaznim ili silaznim redoslijedom. Zadana vrijednost je Uzlazno.
| 1 | Narudžba = xlPovećano |
DataOption
DataOption odabire način sortiranja teksta i brojeva. Parametar xlSortNormal numeričke i tekstualne podatke sortira zasebno. Parametar xlSortTextAsNumbers tretira tekst kao numeričke podatke za sortiranje. Zadana vrijednost je xlSortNormal.
| 1 | DataOption = xlSortNormal |
Zaglavlje
Zaglavlje odabire ima li raspon tabličnih podataka redak zaglavlja ili ne. Ako postoji redak zaglavlja, ne želite da se to uključuje u sortiranje.
Vrijednosti parametara su xlYes, xlNo i xlYesNoGuess. xlYesNoGuess prepušta Excelu da utvrdi postoji li redak zaglavlja, što bi lako moglo dovesti do nedosljednih rezultata. Korištenje ove vrijednosti se ne preporučuje.
Zadana vrijednost je XNo (nema reda zaglavlja unutar podataka). Kod uvezenih podataka obično postoji redak zaglavlja, stoga obavezno postavite ovaj parametar na xlDa.
| 1 | Zaglavlje = xlDa |
Kutija šibica
Ovaj parametar određuje je li sortiranje osjetljivo na velika ili mala slova. Vrijednosti opcija su True ili False. Ako je vrijednost False, male se vrijednosti smatraju istim kao i velike. Ako je vrijednost True, tada će sortiranje pokazati razliku između velikih i malih vrijednosti unutar sortiranja. Zadana vrijednost je False.
| 1 | MatchCase = False |
Orijentacija
Ovaj parametar određuje hoće li se sortiranje odvijati prema dolje kroz retke ili preko svih stupaca. Zadana vrijednost je xlTopToBottom (sortiranje po redovima). Možete koristiti xlLeftToRight ako želite vodoravno sortirati. Vrijednosti kao što su xlRows i xlColumns ne rade za ovaj parametar.
| 1 | Orijentacija = xlTopToBottom |
SortMethod
Ovaj se parametar koristi samo za sortiranje kineskih jezika. Ima dvije vrijednosti, xlPinYin i xlStroke. xlPinYin je zadana vrijednost.
xlPinYin sortira pomoću fonetskog kineskog redoslijeda sortiranja znakova. xlStroke sortira prema količini poteza u svakom znaku.
Ako snimite makronaredbu razvrstavanja, ovaj će parametar uvijek biti uključen u kôd, a možda ste se i pitali što to znači. Međutim, osim ako se ne bavite podacima na kineskom, oni nemaju nikakve koristi.
| 1 | SortMethod = xlPinYin |
Korištenje događaja dvostrukog klika za sortiranje tabličnih podataka
U svim funkcionalnostima koje je Microsoft uključio u metode razvrstavanja za VBA, nije uključivao jednostavno sredstvo za dvostruki klik na zaglavlje stupca i sortiranje cijelih tabličnih podataka na temelju tog posebnog stupca.
Ovo je zaista korisna značajka i lako je napisati kôd za to.
| 12345678910111213141516171819202122232425262728293031323334 | Privatni pod -radni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Pretpostavlja se da podaci počinju u ćeliji A1'Izradite tri varijable za snimanje odabranog ciljnog stupca i maksimalnog stupca i retka od _'tablične podatkePriguši kolonu kao cijeli broj, RCol koliko je dug, redak koliko dugo'Provjerite je li korisnik dvaput kliknuo na red zaglavlja - redak 1, u suprotnom izađite iz podnaslovaAko Target.Row 1 Zatim izađite iz Sub'Snimite maksimalne retke u tabličnom rasponu podataka pomoću objekta' UsedRange 'RCol = ActiveSheet.UsedRange.Columns.Count'Snimite maksimalne stupce u tabličnom rasponu podataka pomoću objekta' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Provjerite nije li korisnik dvaput kliknuo stupac izvan raspona tabličnih podatakaAko Target.Column> RCol Zatim izađite iz Sub'Snimite stupac na koji je korisnik dvaput kliknuoCol = Target.Column'Uklonite prethodne parametre sortiranjaActiveSheet.Sort.SortFields.Clear'Sortirajte tablični raspon kako je definirano maksimalnim redovima i stupcima iz objekta' UsedRange ''Sortirajte tablične podatke pomoću stupca koji je korisnik dvaput kliknuo kao ključ za sortiranjeActiveSheet.Range (ćelije (1, 1), ćelije (RCol, RRow)). Ključ razvrstavanja1: = ćelije (1, stupac), zaglavlje: = xlDa'Odaberite ćeliju A1 - ovo služi kako bi se osiguralo da korisnik ne ostane u načinu uređivanja nakon razvrstavanja _'dovršenoActiveSheet.Range ("A1"). OdaberiteKraj podm |
Ovaj kôd treba postaviti na događaj dvostrukog klika na listu koji sadrži tablične podatke. To možete učiniti klikom na naziv radnog lista u prozoru Project Explorera (gornji lijevi kut zaslona VBE), a zatim u prvom padajućem izborniku u prozoru koda odaberite "Radni list". Odaberite "BeforeDoubleClick" u drugom padajućem izborniku, a zatim možete unijeti svoj kôd.
Imajte na umu da se u ovaj kôd ne unose tvrdo kodirani nazivi, rasponi ili reference ćelija, osim premještanja kursora u ćeliju A1 na kraju koda. Kôd je osmišljen tako da dobije sve potrebne podatke iz koordinata ćelije na koje je korisnik dvaput kliknuo i veličine raspona tabličnih podataka.
Nije važno koliko je velik raspon tabličnih podataka. Kôd će i dalje pokupiti sve potrebne podatke i može se koristiti za podatke pohranjene bilo gdje u vašoj radnoj knjizi bez potrebe za strogim kodiranjem vrijednosti.
Jedina pretpostavka je da u tabličnim podacima postoji redak zaglavlja i da raspon podataka počinje od ćelije A1, ali se početna pozicija za raspon podataka može lako promijeniti unutar koda.
Svaki korisnik bit će prikladno impresioniran ovom novom funkcijom sortiranja!
Proširenje funkcije sortiranja pomoću VBA
Microsoft je dopustio ogromnu fleksibilnost u sortiranju pomoću širokog raspona parametara. Međutim, unutar VBA -e to možete odvesti dalje.
Pretpostavimo da ste htjeli sortirati bilo koje vrijednosti podebljanim fontom na vrh podataka. Ne postoji način da to učinite u Excelu, ali za to možete napisati VBA kôd:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Pod SortByBold ()'Izradite varijable za držanje broja redaka i stupaca za tablične podatkeDim RRow As Long, RCol As Long, N As Long'Isključite ažuriranje zaslona kako korisnik ne bi mogao vidjeti što se događa - mogao bi vidjeti _'vrijednosti se mijenjaju i pitamo se zaštoApplication.ScreenUpdating = Netačno'Snimite broj stupaca u rasponu tabličnih podatakaRCol = ActiveSheet.UsedRange.Columns.Count'Snimite broj redaka unutar raspona tabličnih podatakaRRow = ActiveSheet.UsedRange.Rows.Count'Ponovite sve retke u tabličnom rasponu podataka zanemarujući redak zaglavljaZa N = 2 do RRow'Ako ćelija ima podebljani font, postavite vrijednost 0 ispred vrijednosti ćelijeAko je ActiveSheet.Cells (N, 1) .Font.Bold = TačnoActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .VrijednostZavrši akoSljedeći N.'Obrišite sve prethodne parametre sortiranjaActiveSheet.Sort.SortFields.Clear'Sortirajte raspon tabličnih podataka. Sve vrijednosti s vodećom 0 vrijednošću pomaknut će se na vrhRaspon ActiveSheet.Raspon (ćelije (1, 1), ćelije (RCol, RRow)). Ključ razvrstavanja1: = ćelije (1, 1), zaglavlje: = xlDa'Ponovite sve retke u tabličnom rasponu podataka zanemarujući redak zaglavljaZa N = 2 do RRow'Ako ćelija ima podebljani font, uklonite početnu vrijednost 0 iz vrijednosti ćelije u _'vratiti izvorne vrijednostiAko je ActiveSheet.Cells (N, 1) .Font.Bold = TačnoActiveSheet.Cells (N, 1) .Vrijednost = sredina (ActiveSheet.Cells (N, 1) .Vrijednost, 2)Završi akoSljedeći N.'Ponovo uključite ažuriranje zaslonaApplication.ScreenUpdating = IstinaKraj podm |
Kôd utvrđuje veličinu tabličnog raspona podataka pomoću objekta 'UsedRange', a zatim ponavlja kroz sve retke unutar njega. Kad se pronađe podebljani font, ispred vrijednosti ćelije stavlja se nula na početku.
Tada se odvija sortiranje. Kako je sortiranje u rastućem redoslijedu, sve s nulom ispred ide na vrh popisa.
Kôd zatim ponavlja kroz sve retke i uklanja početne nule, vraćajući podatke na izvorne vrijednosti.
Ovaj kôd razvrstava pomoću podebljanih fontova kao kriterij, ali na isti način možete lako koristiti i druge karakteristike ćelije, npr. Kurziv, veličinu točke teksta, podvlaku, naziv fonta itd.
