Kako sortirati podatke u Excelu pomoću VBA (Korak po korak vodič)

Excel već ima nekoliko načina za brzo sortiranje podataka.

Skup podataka možete jednostavno sortirati pomoću ikona za sortiranje na vrpci ili u dijaloškom okviru za sortiranje.

Zašto onda morate znati kako to učiniti pomoću VBA?

Znati kako sortirati podatke pomoću VBA -e može biti korisno ako je uključeno kao dio vašeg koda. Na primjer, pretpostavimo da dnevno/tjedno dobivate skup podataka koji morate oblikovati i sortirati prema određenom redoslijedu.

Možete stvoriti makronaredbu koja će sve to učiniti umjesto vas jednim klikom. To će vam uštedjeti puno vremena i truda svaki put kada to učinite.

Također, ako kreirate nadzorne ploče programa Excel, možete prenijeti mogućnost sortiranja programa Excel na novu razinu dopuštajući korisniku da sortira podatke samo dvostrukim klikom na zaglavlje (kao što je prikazano u nastavku).

Kasnije u ovom vodiču pokazat ću kako to stvoriti. Hajdemo prvo brzo razjasniti osnove.

Razumijevanje metode Range.Sort u Excelu VBA

Prilikom razvrstavanja pomoću VBA -e morate koristiti metodu Range.Sort u svom kodu.

'Raspon' bi bili podaci koje pokušavate sortirati. Na primjer, ako sortirate podatke u A1: A10, tada bi 'Raspon' bio Raspon ("A1: A10").

Također možete stvoriti imenovani raspon i koristiti ga umjesto referenci ćelije. Na primjer, ako stvorim imenovani raspon "DataRange" za ćelije A1: A10, tada mogu koristiti i raspon ("DataRange")

S metodom sortiranja morate pružiti neke dodatne informacije kroz parametre. U nastavku su navedeni ključni parametri koje morate znati:

• Ključ - ovdje morate navesti stupac koji želite sortirati. Na primjer, ako želite sortirati stupac A, morate koristiti key: = Range ("A1")
• Narudžba - ovdje određujete želite li sortiranje uzlaznim ili silaznim redoslijedom. Na primjer, ako želite sortiranje uzlaznim redoslijedom, upotrijebit ćete redoslijed: = xlAscending
• Zaglavlje - ovdje navodite ima li vaš skup podataka zaglavlja ili ne. Ako ima zaglavlja, sortiranje počinje od drugog retka skupa podataka, inače počinje od prvog retka. Da biste naveli da vaši podaci imaju zaglavlja, upotrijebit ćete Zaglavlje: = xlDa

Iako su ova tri u većini slučajeva dovoljna, u ovom članku možete pročitati više o parametrima.

Pogledajmo sada kako koristiti metodu Range.Sort u VBA za sortiranje podataka u Excelu.

Sortiranje jedne kolone bez zaglavlja

Pretpostavimo da imate jedan stupac bez zaglavlja (kao što je prikazano u nastavku).

Pomoću donjeg koda možete ga sortirati uzlaznim redoslijedom.

PodrazvrstaDataWithoutHeader () Raspon ("A1: A12"). Sort Key1: = Raspon ("A1"), Order1: = xlAscending, Header: = xlNo End Sub

Imajte na umu da sam ručno odredio raspon podataka kao Raspon (“A1: A12”).

U slučaju da može doći do promjena u podacima, a vrijednosti se mogu dodati/izbrisati, možete upotrijebiti donji kod koji se automatski prilagođava na temelju ispunjenih ćelija u skupu podataka.

PodrazvrstajDataWithoutHeader () Raspon ("A1", Raspon ("A1"). Kraj (xlDown)). Ključ razvrstavanja1: = Raspon ("A1"), Red1: = xlAscending, Zaglavlje: = xlNo Kraj

Imajte na umu da sam umjesto Range (“A1: A12”), koristio, Range (“A1”, Range (“A1”). End (xlDown)).

Time će se provjeriti posljednja uzastopno ispunjena ćelija u stupcu i uključiti je u sortiranje. U slučaju da postoje prazna polja, podaci će se uzeti u obzir samo do prve prazne ćelije.

Također možete stvoriti imenovani raspon i koristiti taj imenovani raspon umjesto referenci ćelije. Na primjer, ako je imenovani raspon DataSet, vaš bi kôd sada bio kao što je prikazano u nastavku.

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

Dopustite mi sada da brzo objasnim parametre korištene u gornjim primjerima:

• Key1: = Raspon (“A1”) - Naveden A1 tako da kôd zna koji stupac treba sortirati.
• Redoslijed1: = xlAscending - Određen je red xlAscending. Ako želite da bude u opadajućem redoslijedu, upotrijebite xlDescending.
• Zaglavlje: = xlNo - Određeno je da nema zaglavlja. Ovo je također zadana vrijednost. Dakle, čak i ako to izostavite, vaši će se podaci sortirati s obzirom da nemaju zaglavlja.

Pitate se gdje staviti ovaj VBA kôd i kako pokrenuti makro? Pročitajte ovaj vodič!

Razvrstavanje jednog stupca s zaglavljem

U prethodnom primjeru skup podataka nije imao zaglavlje.

Kad vaši podaci imaju zaglavlja, morate to navesti u kodu kako bi sortiranje moglo započeti iz drugog retka skupa podataka.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

Dolje se nalazi kôd koji će podatke sortirati u opadajućem redoslijedu na temelju prodaje trgovina.

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

Imajte na umu da sam stvorio imenovani raspon - 'DataRange' i koristio taj imenovani raspon u kodu.

Razvrstavanje više stupaca s zaglavljima

Do sada smo u ovom vodiču vidjeli kako sortirati jedan stupac (sa i bez zaglavlja).

Što ako želite sortirati na temelju više stupaca.

Na primjer, u donjem skupu podataka, što ako želim prvo sortirati prema kodu države, a zatim prema trgovini.

Ovdje je kôd koji će sortirati više stupaca odjednom.

PodrazvrstajVišestrukih stupaca () s ActiveSheet.Sort .SortFields.Add Key: = Raspon ("A1"), Redoslijed: = xlAscending .SortFields.Add Ključ: = Raspon ("B1"), Redoslijed: = xlAscending .SetRange Raspon ("A1 : C13 "). Glava = xlDa. Nanesite End With End Sub

Ispod je rezultat koji ćete dobiti.

U gornjem primjeru podaci se prvo sortiraju prema kodu države (stupac A). Zatim se unutar podataka koda države ponovno sortiraju prema trgovini (stupac B). Ovaj redoslijed određen je kodom u kojem ga spominjete.

Razvrstavanje podataka dvostrukim klikom na zaglavlje

Ako stvarate nadzornu ploču ili želite jednostavniju upotrebu u svojim izvješćima, možete napisati VBA kôd koji će sortirati podatke kada dvaput kliknete zaglavlja.

Nešto kao što je prikazano ispod:

Ispod je kôd koji će vam to omogućiti:

Privatni pod -radni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Ako je Target.Row = 1 i Target.Column <= ColumnCount Cancel = True Set KeyRange = Raspon (Target.Address) Raspon ("DataRange"). Sort Key1: = KeyRange, Header: = xlDa End If End Sub

Imajte na umu da sam stvorio imenovani raspon ("DataRange") i koristio sam ga u kodu umjesto da koristim reference ćelija.

Čim dvaput kliknete na bilo koje od zaglavlja, kôd onemogućuje uobičajenu funkciju dvostrukog klika (za ulazak u način uređivanja) i tu ćeliju koristi kao ključ prilikom razvrstavanja podataka.

Također imajte na umu da će od sada ovaj kod sortirati sve stupce samo uzlaznim redoslijedom.

Imajte na umu da je dvostruki klik okidač koji Excelu omogućuje pokretanje navedenog koda. Ti se okidači, poput dvostrukog klika, otvaranja radne knjige, dodavanja novog radnog lista, mijenjanja ćelije itd. Nazivaju događajima i mogu se koristiti za pokretanje makronaredbi u Excelu. Ovdje možete pročitati više o Excel VBA događajima.

Gdje staviti ovaj kod?

Ovaj kôd morate zalijepiti u kôdni prozor lista u kojem želite ovu funkciju sortiranja dvostrukim klikom.

Uraditi ovo:

• Desnom tipkom miša kliknite karticu lista.
• Kliknite na View Code.
• Zalijepite kôd u kôdni prozor lista na kojem se nalaze vaši podaci.

Što ako želite prva dva stupca ("Stanje" i "Pohrana") sortirati uzlaznim redoslijedom, ali stupac "Prodaja" opadajućim redoslijedom.

Evo koda koji će to učiniti:

Privatni pod -radni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Ako je Target.Row = 1 i Target.Column <= ColumnCount 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: = xlYes, Order1: = SortOrder End End End End Sub

U gornjem kodu provjerava je li ćelija koja je dvaput kliknuta zaglavlje prodaje ili nije. Ako je odgovor da, tada vrijednost xlDescending dodjeljuje varijabli SortOrder, inače je čini xlAscending.

Idemo sada dalje i pokazati vizualni označivač (strelica i obojena ćelija) u zaglavlju kada se sortira.

Nešto kao što je prikazano ispod:

Da bih to postigao, dodao sam novi radni list i u njega unio sljedeće promjene (možete preuzeti datoteku primjera i pratiti je dalje):

• Promijenjeno je ime novog lista u "BackEnd".
• U ćeliju B2 unesite simbol strelice (da biste to učinili, idite na Umetni i kliknite opciju "Simbol").
• Kopirajte i zalijepite zaglavlja iz skupa podataka u ćeliju A3: C3 na listu "Pozadina".
• U ćeliji A4 upotrijebite sljedeću funkciju: AC4:

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

• Ostatak ćelija automatski će se napuniti VBA kodom kada dvaput kliknete zaglavlja da biste sortirali stupac.

Vaš pozadinski list izgledao bi ovako kako je prikazano u nastavku:

Sada pomoću donjeg koda možete sortirati podatke dvostrukim klikom na zaglavlja. Kada dvaput kliknete na zaglavlje, ono će automatski dobiti strelicu u tekstu zaglavlja. Imajte na umu da sam također koristio uvjetno oblikovanje da istaknem ćeliju.

Privatni pod -radni list_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Ako je Target.Row = 1 i Target.Column <= ColumnCount Odustani = Istinski radni listovi ("Pozadina"). Raspon ("C1") = Ciljna vrijednost. Raspon vrijednosti (raspon podataka (ciljni raspon)). Rasporedni ključ 1: = KeyRange, zaglavlje: = xlDa Radni listovi ("Natrag" ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1). Vrijednost Sljedeće i Kraj Ako je završilo Sub

Imajte na umu da ovaj kod dobro funkcionira za način na koji su konstruirani moji podaci i radna knjiga. Ako promijenite strukturu podataka, morat ćete prema tome izmijeniti kôd.

Preuzmite datoteku primjera