Sortarea datelor în Excel VBA
Excel are un mijloc excelent de sortare a unei game de date tabulare utilizând panglica de pe partea frontală Excel și, la un moment dat, probabil că veți dori să utilizați această funcționalitate în codul dvs. VBA. Din fericire, acest lucru este foarte ușor de făcut.
Caseta de dialog front-end se găsește făcând clic pe pictograma „Sortare” din grupul „Sortare și filtrare” din fila „Date” de pe panglica Excel. Mai întâi trebuie să selectați o gamă de date tabulare.
De asemenea, puteți utiliza Alt-A-S-S pentru a afișa caseta de dialog pentru un sortare personalizată.
Metoda de sortare a fost mult îmbunătățită în versiunile ulterioare ale Excel. Sortarea a fost restrânsă la trei niveluri, dar acum puteți introduce oricâte niveluri aveți nevoie și acest lucru se aplică și în VBA.
Puteți încorpora toate funcțiile de sortare oferite în dialogul Sortare Excel în codul dvs. VBA. Funcția de sortare în Excel este rapidă și mai rapidă decât orice ați putea scrie dvs. în VBA, deci profitați de funcționalitate.
Rețineți că atunci când efectuați o sortare în VBA, parametrii de sortare rămân aceiași în caseta de dialog sortare front-end. De asemenea, acestea sunt salvate atunci când este salvat registrul de lucru.
Dacă un utilizator selectează aceeași gamă de date tabulare și face clic pe pictograma Sortare, va vedea toți parametrii care au fost introduși de codul dvs. VBA. Dacă vor să facă un fel de design propriu, atunci vor trebui să șteargă mai întâi toate nivelurile de sortare, ceea ce va fi foarte enervant pentru ei.
De asemenea, dacă nu modificați parametrii din codul dvs. și vă bazați pe valorile implicite, puteți constata că utilizatorul a făcut modificări care se vor reflecta în sortarea dvs. VBA și poate da rezultate neașteptate, care pot fi foarte dificil de depanat. .
Din fericire, există o metodă Clear în VBA pentru a restabili toți parametrii de sortare, astfel încât utilizatorul să poată vedea o casetă de dialog de sortare curată
| 1 | Foi de lucru („Sheet1”). Sort.SortFields.Clear |
Este o practică bună să ștergeți parametrii de sortare în VBA înainte și după finalizarea sortării.
Utilizarea practică a metodei de sortare în VBA
Când datele tabulare sunt importate în Excel, acestea sunt adesea într-o ordine foarte aleatorie. Ar putea fi importat dintr-un fișier CSV (valori separate prin virgulă) sau ar putea proveni dintr-un link către o bază de date sau o pagină web. Nu vă puteți baza pe faptul că este într-o ordine stabilită de la un import la altul.
Dacă prezentați aceste date unui utilizator în foaia dvs. de lucru, este posibil ca utilizatorul să aibă dificultăți să privească și să înțeleagă o cantitate imensă de date care, din punct de vedere al ordinii, sunt peste tot. Este posibil să dorească să grupeze datele sau să taie și să lipească anumite secțiuni ale acestora într-o altă aplicație.
De asemenea, ar putea dori să vadă, de exemplu, cel mai bine plătit angajat sau angajatul cu cel mai lung serviciu.
Folosind metoda Sortare în VBA, puteți oferi opțiuni pentru a permite sortarea ușoară pentru utilizator.
Exemple de date pentru a demonstra sortarea Excel cu VBA
Mai întâi avem nevoie de câteva exemple de date pentru a fi introduse într-o foaie de lucru, astfel încât codul să poată demonstra toate facilitățile disponibile în cadrul VBA.
Copiați aceste date într-o foaie de lucru (numită „Foaie1”) exact așa cum se arată.
Rețineți că au fost utilizate diferite culori de fundal de celulă și culori de font, deoarece acestea pot fi folosite și ca parametri de sortare. Sortarea folosind culorile celulei și fontului va fi demonstrată mai târziu în articol. De asemenea, rețineți că în celula E3, numele departamentului este minuscul.
Nu aveți nevoie de interiorul celulei și de culorile fontului dacă nu doriți să utilizați exemplele de sortare după celulă și culoarea fontului.
Înregistrarea unei macro pentru un sortare VBA
Codul VBA pentru sortare poate deveni destul de complicat și uneori poate fi o idee bună să faceți sortarea pe partea frontală a Excelului și să înregistrați o macro pentru a vă arăta cum funcționează codul.
Din păcate, funcția de înregistrare poate genera o cantitate imensă de cod deoarece setează practic fiecare parametru disponibil, chiar dacă valorile implicite pentru mulți parametri sunt acceptabile pentru operația de sortare.
Cu toate acestea, vă oferă o idee foarte bună despre ceea ce este implicat în scrierea codului de sortare VBA și un avantaj este că codul înregistrat va funcționa întotdeauna pentru dvs. Este posibil ca propriul dvs. cod să aibă nevoie de testare și depanare pentru a funcționa corect.
Amintiți-vă că pentru o operație efectuată în VBA, nu există o caracteristică de anulare, deci este o idee bună să faceți o copie a datelor tabulare pe o altă foaie de lucru înainte de a începe să scrieți codul de sortare.
De exemplu, dacă ați făcut o sortare simplă pe eșantionul de date de mai sus, sortând după angajat, înregistrarea ar genera următorul cod:
| 123456789101112131415161718 | Sub Macro1 ()Range ("A1: E6"). SelectațiActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalCu ActiveWorkbook.Worksheets („Sheet1”). Sortați.SetRange Range („A1: E6”).Header = xlDa.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicaSe termina cuSfârșitul Sub |
Acesta este o cantitate destul de mare de cod și o mulțime din acesta nu este necesară din cauza utilizării parametrilor impliciți. Cu toate acestea, dacă vă confruntați cu presiunea timpului pentru a finaliza un proiect și aveți nevoie de un cod rapid care funcționează, îl puteți lipi cu ușurință în propriul cod VBA.
Cu toate acestea, dacă doriți să vă faceți codul mai ușor de înțeles și mai elegant, atunci există și alte opțiuni disponibile.
Cod VBA pentru a efectua o sortare la nivel unic
Dacă doriți să sortați eșantionul de cod pe baza angajaților doar ca înainte, atunci când înregistrați o macro, codul este foarte simplu:
| 1234567 | Sub SingleLevelSort ()Foi de lucru („Sheet1”). Sort.SortFields.ClearRange ("A1: E6"). Tasta de sortare1: = Range ("A1"), Header: = xlDaSfârșitul Sub |
Acest lucru este mult mai ușor de înțeles decât codul înregistrat, deoarece acceptă valorile implicite, de exemplu, sortează crescător, deci nu este nevoie să setați parametrii la valorile implicite. Aceasta presupune că ați utilizat o declarație „Clear” în prealabil.
Metoda „Șterge” este utilizată inițial pentru a se asigura că fiecare parametru de sortare pentru foaia de lucru respectivă este setat înapoi la valorile implicite. Este posibil ca un utilizator să fi setat anterior parametrii la valori diferite sau un sortare anterioară în VBA poate să-i fi schimbat. Este important să începeți de la o poziție implicită atunci când sortați, altfel ați putea ajunge cu ușurință cu rezultate incorecte.
Metoda Clear nu resetează parametrul Header și este recomandabil să includeți acest lucru în codul dvs., altfel Excel poate încerca să ghicească dacă este prezent sau nu un rând de header.
Rulați acest cod pe baza eșantionului de date, iar foaia dvs. de lucru va arăta astfel:
Cod VBA pentru a face o sortare pe mai multe niveluri
Puteți adăuga cât mai multe niveluri de sortare necesare în codul dvs. Să presupunem că ați dorit să sortați mai întâi după departament și apoi după data de începere, dar în ordine crescătoare pentru departament și ordine descrescătoare pentru data de începere:
| 12345678 | Sub MultiLevelSort ()Foi de lucru („Sheet1”). Sort.SortFields.ClearRange ("A1: E6"). Sortare cheie1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlDa, _Order1: = xlAscending, Order2: = xlDescendingSfârșitul Sub |
Rețineți că există acum două chei în instrucțiunea de sortare (Key1 și Key2). Cheia1 (coloana departamentului E) este sortată mai întâi de toate și apoi cheia2 (data de începere coloana C) este sortată pe baza primului sortare.
Există, de asemenea, doi parametri de comandă. Order1 se asociază cu Key1 (Departament) și Order2 se asociază cu Key2 (Data de începere). Este important să vă asigurați că cheile și comenzile sunt menținute la pas între ele.
Rulați acest cod pe baza eșantionului de date, iar foaia dvs. de lucru va arăta astfel:
Coloana Departament (E) este în ordine crescătoare, iar coloana Data de început (C) este în ordine descrescătoare.
Efectul acestui tip este cel mai vizibil atunci când ne uităm la Jane Halfacre (rândul 3) și John Sutherland (rândul 4). Amândoi sunt în finanțe, dar Jane Halfacre a început înainte de John Sutherland, iar datele sunt afișate în ordine descrescătoare.
Dacă intervalul de date tabulare ar putea fi de orice lungime, puteți utiliza obiectul UsedRange pentru a defini intervalul de sortare. Acest lucru va funcționa numai dacă există doar date tabulare pe foaia de lucru, deoarece orice valoare în afara datelor va da rezultate incorecte pentru numărul de rânduri și coloane.
| 1234567 | Sub MultiLevelSort ()Foi de lucru („Sheet1”). Sort.SortFields.ClearFoi de lucru ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Order1: = xlAscending, Order2: = xlDescendingSfârșitul Sub |
Aceasta previne problema dacă utilizați metoda „End (xlDown)” pentru a defini intervalul de sortare. Dacă există o celulă goală în mijlocul datelor, atunci orice element după celula goală nu va fi inclus, în timp ce UsedRange coboară la ultima celulă activă din foaia de lucru.
Sortarea după culoarea celulei
Din Excel 2007, acum este posibilă sortarea după culoarea de fundal a unei celule, ceea ce oferă o flexibilitate enormă atunci când proiectați codul de sortare în VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Foi de lucru („Sheet1”). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalCu ActiveWorkbook.Worksheets („Sheet1”). Sortați.SetRange Range („A1: E6”).AplicaSe termina cuSfârșitul Sub |
Acest cod va sorta intervalul de date eșantion (A2: A6) pe baza culorii de fundal a celulei. Rețineți că există acum un parametru suplimentar numit „SortOn” care are valoarea „xlSortOnCellColor”.
Rețineți că parametrul „SortOn” poate fi utilizat numai de un obiect foaie de lucru și nu de un obiect interval.
Din această cauză, codul este mai complicat decât pentru un sortare folosind valori de celulă.
Acest cod folosește o valoare cheie pentru sortare care acoperă întreaga gamă de date, dar puteți specifica coloane individuale ca cheie pentru sortarea culorilor de fundal și puteți utiliza mai multe niveluri așa cum se arată mai devreme.
După rularea acestui cod, foaia dvs. de lucru va arăta astfel:
Sortarea după culoarea fontului
Funcția de sortare din Excel VBA oferă și mai multă flexibilitate în sensul că puteți sorta după culorile fontului:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Foi de lucru („Sheet1”). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Cu ActiveWorkbook.Worksheets („Sheet1”). Sortați.SetRange Range („A1: E6”).Header = xlDa.Orientation = xlTopToBottom.AplicaSe termina cuSfârșitul Sub |
Codul pentru sortarea după culoarea fontului este mult mai complicat decât pentru culoarea de fundal a celulei. Parametrul „SortOn” deține acum valoarea „xlSortOnFontColor”.
Rețineți că trebuie să specificați orientarea ca „xlTopToBottom” și trebuie să specificați o culoare pe care să sortați. Acest lucru este specificat în termeni RGB (roșu, verde, negru) cu valori cuprinse între 0 și 255.
După ce rulați acest cod pe datele de eșantion, foaia dvs. de lucru va arăta astfel:
Sortarea folosind culorile în VBA este mult mai complicată decât o sortare pe mai multe niveluri, dar dacă codul dvs. de sortare nu va funcționa (ceea ce se poate întâmpla dacă lipsește un parametru sau dacă nu ați introdus corect codul), atunci puteți reveni oricând la înregistrare un macro și integrarea codului înregistrat în VBA.
Utilizarea altor parametri în sortarea VBA
Există o serie de parametri opționali pe care îi puteți utiliza în codul VBA pentru a vă personaliza sortarea.
SortOn
SortOn alege dacă sortarea va utiliza valorile celulei, culorile de fundal ale celulei sau culorile fontului celulei. Setarea implicită este Valori celulare.
| 1 | SortOn = xlSortOnValues |
Ordin
Ordinea alege dacă sortarea se va face în ordine crescătoare sau descendentă. Valoarea implicită este Ascendentă.
| 1 | Order = xlAscending |
DataOption
DataOption alege modul de sortare a textului și numerelor. Parametrul xlSortNormal sortează separat datele numerice și text. Parametrul xlSortTextAsNumbers tratează textul ca date numerice pentru sortare. Valoarea implicită este xlSortNormal.
| 1 | DataOption = xlSortNormal |
Antet
Antet alege dacă intervalul de date tabulare are sau nu un rând de antet. Dacă există un rând de antet, nu doriți ca acesta să fie inclus în sortare.
Valorile parametrilor sunt xlYes, xlNo și xlYesNoGuess. xlYesNoGuess îl lasă pe Excel să stabilească dacă există un rând de antet, care ar putea duce cu ușurință la rezultate inconsistente. Nu este recomandată utilizarea acestei valori.
Valoarea implicită este XNo (fără rând de antet în cadrul datelor). Cu datele importate, există de obicei un rând de antet, deci asigurați-vă că setați acest parametru la xlYes.
| 1 | Header = xlDa |
MatchCase
Acest parametru determină dacă sortarea este sensibilă la majuscule sau nu. Valorile opțiunilor sunt adevărate sau false. Dacă valoarea este Falsă, atunci valorile minuscule sunt considerate la fel ca valorile majuscule. Dacă valoarea este True, atunci sortarea va arăta diferența dintre valorile majuscule și minuscule din sortare. Valoarea implicită este False.
| 1 | MatchCase = False |
Orientare
Acest parametru determină dacă sortarea va avea loc în jos prin rânduri sau pe toate coloanele. Valoarea implicită este xlTopToBottom (sortează rândurile). Puteți utiliza xlLeftToRight dacă doriți să sortați orizontal. Valori precum xlRows și xlColumns nu funcționează pentru acest parametru.
| 1 | Orientare = xlTopToBottom |
Metoda de sortare
Acest parametru este utilizat numai pentru sortarea limbilor chinezești. Are două valori, xlPinYin și xlStroke. xlPinYin este valoarea implicită.
xlPinYin sortează folosind ordinea de sortare fonetică chineză pentru caractere. xlStroke sortează după cantitatea de linii din fiecare caracter.
Dacă înregistrați o macrocomandă de sortare, acest parametru va fi întotdeauna inclus în cod și este posibil să vă fi întrebat ce înseamnă. Cu toate acestea, dacă nu aveți de-a face cu date în limba chineză, este de puțin folos.
| 1 | SortMethod = xlPinYin |
Utilizarea unui eveniment cu dublu clic pentru a sorta datele tabulare
În toate funcționalitățile pe care Microsoft le-a inclus în metodele de sortare pentru VBA, nu a inclus un mijloc simplu de a face dublu clic pe un antet de coloană și de a sorta întregul datelor tabulare pe baza acelei coloane.
Aceasta este o caracteristică foarte utilă și este ușor să scrieți codul pentru ao face.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sheet Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean)„Se presupune că datele încep de la celula A1„Creați trei variabile pentru a captura coloana țintă selectată și coloana maximă și rândul _'datele tabulareDim Col As Integer, RCol As Long, Row As Long'Verificați dacă utilizatorul a făcut dublu clic pe rândul de antet - rândul 1 altfel ieșiți din subDacă țintă.Rândul 1, apoi ieșiți din Sub„Capturați rândurile maxime din intervalul de date tabulare utilizând obiectul„ UsedRange ”RCol = ActiveSheet.UsedRange.Columns.Count„Capturați coloanele maxime din intervalul de date tabulare utilizând obiectul„ UsedRange ”RRow = ActiveSheet.UsedRange.Rows.Count„Verificați dacă utilizatorul nu a dat dublu clic pe o coloană din afara intervalului de date tabulareDacă Target.Column> RCol, apoi ieșiți din Sub„Capturați coloana pe care utilizatorul a făcut dublu clicCol = Target.Column„Ștergeți parametrii de sortare anterioriActiveSheet.Sort.SortFields.Clear„Sortați intervalul tabelar așa cum este definit de rândurile și coloanele maxime din obiectul„ UsedRange ”'Sortați datele tabulare utilizând coloana dublu-clic de către utilizator ca cheie de sortareActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, Col), Header: = xlYes„Selectați celula A1 - aceasta pentru a vă asigura că utilizatorul nu este lăsat în modul de editare după ce sortarea este _' efectuatActiveSheet.Range ("A1"). SelectațiSfârșitul Sub |
Acest cod trebuie plasat pe evenimentul cu dublu clic pe foaia care conține datele tabulare. Faceți acest lucru făcând clic pe numele foii de lucru din fereastra Project Explorer (colțul din stânga sus al ecranului VBE), apoi selectând „Foaie de lucru” în primul meniu derulant din fereastra de cod. Selectați „BeforeDoubleClick” în al doilea meniu derulant și puteți introduce codul.
Rețineți că niciun nume, intervale sau referințe de celulă nu sunt codificate în acest cod, cu excepția mutării cursorului în celula A1 la sfârșitul codului. Codul este conceput pentru a obține toate informațiile necesare din coordonatele celulei pe care utilizatorul a făcut dublu clic și dimensiunea intervalului de date tabelare.
Nu contează cât de mare este intervalul de date tabulare. Codul va prelua în continuare toate informațiile solicitate și poate fi utilizat pe datele păstrate oriunde în registrul dvs. de lucru fără a fi nevoie să codificați în valori.
Singura presupunere făcută este că există un rând de antet în datele tabulare și că intervalul de date începe de la celula A1, dar poziția inițială pentru intervalul de date poate fi ușor modificată în cadrul codului.
Orice utilizator va fi impresionat în mod adecvat de această nouă funcționalitate de sortare!
Extinderea funcției de sortare folosind VBA
Microsoft a permis o flexibilitate extraordinară în sortare utilizând o gamă largă de parametri. Cu toate acestea, în cadrul VBA, puteți face acest lucru mai departe.
Să presupunem că ați dorit să sortați orice valori cu un font aldinat în partea de sus a datelor. Nu există nicio modalitate de a face acest lucru în Excel, dar puteți scrie codul VBA pentru a face acest lucru:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Creați variabile pentru a menține numărul de rânduri și coloane pentru datele tabulareDim RRow As Long, RCol As Long, N At Long„Dezactivați actualizarea ecranului, astfel încât utilizatorul să nu poată vedea ce se întâmplă - poate vedea _valorile fiind modificate și mă întreb de ceApplication.ScreenUpdating = Fals„Capturați numărul de coloane din intervalul de date tabulareRCol = ActiveSheet.UsedRange.Columns.Count„Capturați numărul de rânduri din intervalul de date tabulareRRow = ActiveSheet.UsedRange.Rows.Count'Iterează toate rândurile din intervalul de date tabulare, ignorând rândul antetPentru N = 2 To Row„Dacă o celulă are un font aldin, atunci plasați o valoare 0 principală față de valoarea celuleiDacă ActiveSheet.Cells (N, 1) .Font.Bold = True AtunciActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueEnd IfUrmătorul N'Ștergeți parametrii de sortare anterioriActiveSheet.Sort.SortFields.Clear'Sortați intervalul de date tabulare. Toate valorile cu o valoare 0 principală se vor deplasa în partea de susActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, 1), Header: = xlYes'Iterează toate rândurile din intervalul de date tabulare, ignorând rândul antetPentru N = 2 To Row„Dacă o celulă are un font aldin, atunci eliminați valoarea 0 principală din valoarea celulei la _'restabiliți valorile originaleDacă ActiveSheet.Cells (N, 1) .Font.Bold = True AtunciActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)End IfUrmătorul N„Porniți din nou actualizarea ecranuluiApplication.ScreenUpdating = AdevăratSfârșitul Sub |
Codul calculează dimensiunea intervalului de date tabulare utilizând obiectul „UsedRange” și apoi iterează prin toate rândurile din cadrul acestuia. Atunci când se găsește un font aldin, un zero de pornire este plasat în fața valorii celulei.
Apoi are loc un fel. Deoarece sortarea este în ordine crescătoare, orice cu un zero în față va merge în partea de sus a listei.
Codul apoi iterează prin toate rândurile și elimină zero-urile inițiale, restabilind datele la valorile inițiale.
Acest cod sortează folosind fonturi aldine ca criteriu, dar puteți utiliza cu ușurință alte caracteristici ale celulei în același mod, de exemplu, font italic, dimensiunea punctului textului, fontul de subliniere, numele fontului etc.
