Excel are deja câteva modalități de a sorta rapid datele.
Puteți sorta cu ușurință un set de date utilizând pictogramele de sortare din panglică sau din caseta de dialog Sortare.
Atunci de ce trebuie să știi cum să faci asta folosind VBA?
Știind cum să sortați datele folosind VBA poate fi util atunci când este inclus ca parte a codului dvs. De exemplu, să presupunem că obțineți un set de date zilnic / săptămânal de care trebuie formatat și sortat într-o anumită ordine.
Puteți crea o macro pentru a face toate acestea pentru dvs. cu un singur clic. Acest lucru vă va economisi mult timp și efort de fiecare dată când o faceți.
De asemenea, dacă creați tablouri de bord Excel, puteți duce capacitatea de sortare Excel la un nou nivel permițând utilizatorului să sorteze datele doar făcând dublu clic pe antet (așa cum se arată mai jos).
Voi prezenta cum să creez acest lucru mai târziu în acest tutorial. Mai întâi să înțelegem rapid elementele de bază.
Înțelegerea metodei Range.Sort în Excel VBA
Când sortați utilizând VBA, trebuie să utilizați metoda Range.Sort în codul dvs.
„Gama” ar fi datele pe care încercați să le sortați. De exemplu, dacă sortați datele în A1: A10, atunci „Range” va fi Range („A1: A10”).
De asemenea, puteți crea un interval numit și îl puteți folosi în locul referințelor de celulă. De exemplu, dacă creez un interval numit ‘DataRange’ pentru celulele A1: A10, atunci pot folosi și Range („DataRange”)
Cu metoda de sortare, trebuie să furnizați câteva informații suplimentare prin intermediul parametrilor. Mai jos sunt parametrii cheie pe care trebuie să-i cunoașteți:
- Cheie - aici trebuie să specificați coloana pe care doriți să o sortați. De exemplu, dacă doriți să sortați coloana A, trebuie să utilizați cheia: = Range („A1”)
- Ordin - aici specificați dacă doriți sortarea într-o ordine crescătoare sau descendentă. De exemplu, dacă doriți sortarea în ordine crescătoare, veți folosi Order: = xlAscending
- Antet - aici specificați dacă setul de date are anteturi sau nu. Dacă are antete, sortarea începe de la al doilea rând al setului de date, altfel începe de la primul rând. Pentru a specifica că datele dvs. au anteturi, veți utiliza Antet: = xlDa
În timp ce aceste trei sunt suficiente în majoritatea cazurilor, puteți citi mai multe despre parametrii din acest articol.
Acum să vedem cum să folosim metoda Range.Sort în VBA pentru a sorta datele în Excel.
Sortarea unei singure coloane fără antet
Să presupunem că aveți o singură coloană fără antet (așa cum se arată mai jos).
Puteți utiliza codul de mai jos pentru a-l sorta în ordine crescătoare.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xLNo End Sub
Rețineți că am specificat manual intervalul de date ca Range („A1: A12”).
În cazul în care ar putea exista modificări ale datelor și valorile ar putea fi adăugate / șterse, puteți utiliza codul de mai jos care se ajustează automat pe baza celulelor umplute din setul de date.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Rețineți că, în loc de Range („A1: A12”), am folosit Range („A1”, Range („A1”). End (xlDown)).
Aceasta va verifica ultima celulă completată consecutiv din coloană și o va include în sortare. În cazul în care există spații goale, se vor lua în considerare doar datele până la prima celulă goală.
De asemenea, puteți crea un interval denumit și puteți utiliza acel interval denumit în locul referințelor de celulă. De exemplu, dacă intervalul numit este DataSet, codul dvs. ar fi acum așa cum se arată mai jos.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Acum permiteți-mi să explic rapid parametrii utilizați în exemplele de mai sus:
- Key1: = Range („A1”) - Specificat A1 astfel încât codul să știe ce coloană să sorteze.
- Order1: = xlAscending - A specificat comanda ca xlAscending. Dacă doriți să fie în ordinea descrescătoare, utilizați xlDescending.
- Header: = xlNo - Specificat că nu există anteturi. Aceasta este și valoarea implicită. Deci, chiar dacă omiteți acest lucru, datele dvs. vor fi sortate având în vedere că nu are anteturi.
Vă întrebați unde să puneți acest cod VBA și cum să rulați macro-ul? Citiți acest tutorial!
Sortarea unei singure coloane cu antet
În exemplul anterior, setul de date nu avea un antet.
Când datele dvs. au anteturi, trebuie să specificați acest lucru în cod, astfel încât sortarea să poată începe de la al doilea rând al setului de date.
Să presupunem că aveți un set de date așa cum se arată mai jos:
Mai jos este codul care va sorta datele în ordine descrescătoare pe baza vânzărilor magazinelor.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Rețineți că am creat un interval denumit - „DataRange” și am folosit acest interval denumit în cod.
Sortarea mai multor coloane cu anteturi
Până acum, în acest tutorial, am văzut cum să sortăm o singură coloană (cu și fără anteturi).
Acum, ce se întâmplă dacă doriți să sortați pe mai multe coloane.
De exemplu, în setul de date de mai jos, ce se întâmplă dacă vreau să sortez mai întâi după codul de stat, apoi după magazin.
Iată codul care va sorta mai multe coloane dintr-o dată.
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlDa. Aplicați End With End Sub
Mai jos este rezultatul pe care îl veți obține.
În exemplul de mai sus, datele sunt mai întâi sortate după codul de stat (coloana A). Apoi, în cadrul datelor codului de stat, acestea sunt din nou sortate după Magazin (Coloana B). Această ordine este determinată de codul în care îl menționați.
Sortarea datelor folosind dublu clic pe antet
Dacă creați un tablou de bord sau doriți mai ușor de utilizat în rapoarte, puteți scrie un cod VBA care va sorta datele atunci când faceți dublu clic pe anteturi.
Ceva așa cum se arată mai jos:
Mai jos este codul care vă va permite să faceți acest lucru:
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 Anulare = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlDes End If End Sub
Rețineți că am creat un interval numit („DataRange”) și l-am folosit în cod în loc să folosesc referințele celulei.
De îndată ce faceți dublu clic pe oricare dintre anteturi, codul dezactivează funcționalitatea obișnuită de dublu clic (care urmează să intre în modul de editare) și folosește acea celulă ca cheie în timp ce sortați datele.
De asemenea, rețineți că, de acum, acest cod va sorta toate coloanele numai în ordine crescătoare.
Rețineți că dublu clic este un declanșator care permite Excel să ruleze codul specificat. Aceste declanșatoare, cum ar fi dublu clic, deschiderea unui registru de lucru, adăugarea unei noi foi de lucru, schimbarea unei celule etc. sunt numite evenimente și pot fi utilizate pentru a rula macrocomenzi în Excel. Puteți citi mai multe despre evenimentele Excel VBA aici.Unde se pune acest cod?
Trebuie să lipiți acest cod în fereastra de cod a foii în care doriți această funcționalitate de sortare cu dublu clic.
Pentru a face acest lucru:
- Faceți clic dreapta pe fila de foaie.
- Faceți clic pe Vizualizare cod.
- Lipiți codul în fereastra de cod a foii în care se află datele dvs.
Acum, dacă doriți să sortați primele două coloane („State” și „Store”) în ordine crescătoare, dar coloana „Vânzări” în ordine descendentă.
Iată codul care o va face:
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 Anulare = 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 If End Sub
În codul de mai sus, verifică dacă celula pe care se face dublu clic este antetul Vânzări sau nu. Dacă da, atunci atribuie valoarea xlDescending variabilei SortOrder, altfel o face xlAscending.
Acum, să mergem mai departe și să afișăm un Marker vizual (săgeată și celulă colorată) în antet atunci când este sortat.
Ceva așa cum se arată mai jos:
Pentru a obține acest lucru, am adăugat o nouă foaie de lucru și am făcut următoarele modificări în ea (puteți descărca fișierul de exemplu și urmați-l):
- Am schimbat numele noii foi în „Înapoi la sfârșit”.
- În celula B2, ați introdus un simbol săgeată (pentru a face acest lucru, accesați Insert și faceți clic pe opțiunea „Symbol”).
- Copiați și lipiți anteturile din setul de date în celula A3: C3 din foaia „Backend”.
- Utilizați următoarea funcție în celula A4: AC4:
= IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- Restul celulelor vor fi completate automat de codul VBA atunci când faceți dublu clic pe anteturi pentru a sorta coloana.
Foaia dvs. de backend ar arăta așa cum se arată mai jos:
Acum puteți utiliza codul de mai jos pentru a sorta datele făcând dublu clic pe anteturi. Când faceți dublu clic pe un antet, acesta va primi automat săgeata din textul antetului. Rețineți că am folosit și formatarea condiționată pentru a evidenția și celula.
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 Anulare = Foi de lucru adevărate ("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 "). Celule (1, i) .Value = Foi de lucru (" Backend "). Range (" A4 "). Offset (0, i - 1). Valoare Următorul i Sfârșit Dacă se termină Sub
Rețineți că acest cod funcționează bine pentru modul în care sunt construite datele și registrul meu de lucru. Dacă modificați structura datelor, va trebui să modificați codul în consecință.
Descărcați fișierul de exemplu
