Acest ghid vă va arăta cum să alfabetizați în Excel folosind funcția Sortare și filtrare și formule pentru a aranja și formata datele în ordine.
Microsoft Excel este un software eficient pentru organizarea datelor importante, cum ar fi finanțele, datele de vânzări, informațiile despre clienți etc. Alfabetizarea datelor în foile de lucru este una dintre cele mai bune modalități de a organiza, accesa și face referire rapid la acele date. Alfabetizarea unui interval de celule înseamnă sortarea alfabetică a unui interval în Excel. Datele pot fi sortate în ambele moduri, fie în ordine crescătoare, fie în ordine descrescătoare.
De exemplu, să presupunem că aveți un set mare de date cu informații despre comenzile clienților și doriți să găsiți istoricul comenzilor unui anumit client. Parcurgerea întregii liste va deveni o corvoadă. În loc să pierzi timpul derulând, poți să-ți alfabetizezi coloanele pentru a-ți găsi rapid datele.
Există diferite moduri pe care le puteți folosi pentru a alfabetiza datele în Excel, în funcție de tipul de set de date la care lucrați. Excel vă permite să sortați o singură coloană sau un singur rând sau un interval selectat sau o întreagă foaie de lucru, precum și opțiuni pentru a grupa mai multe coloane sau rânduri împreună în timp ce le sortați. Să învățăm cum să alfabetizăm datele din foile de calcul Excel după rânduri sau coloane.
De ce alfabetizați datele în Excel
Alfabetizarea datelor va avea numeroase avantaje:
- Face datele mai sensibile și le face mai ușor de citit.
- Îi este mai ușor pentru un utilizator să caute o anumită valoare sau un nume de client într-o foaie de date Excel.
- Ajută utilizatorul să identifice vizual înregistrările duplicate, astfel încât să putem preveni erorile de introducere a datelor.
- Permite utilizatorilor să grupeze cu ușurință coloanele sau listele, astfel încât utilizatorii să le poată privi unul lângă altul.
Clasificarea alfabetică (sortarea alfabetică) este o modalitate ușoară și comună de a vă sorta rapid informațiile. Este util în special pentru seturi de date foarte mari. În total, există 4 moduri de a alfabetiza datele în Excel: butonul A-Z sau Z-A, funcția de sortare, funcția de filtrare și formule.
Alfabetizarea unei coloane în Excel
Cea mai rapidă modalitate de a sorta alfabetic în Excel este prin utilizarea funcției de sortare încorporată.
Mai întâi, deschideți foaia de calcul care conține setul de date. Selectați celulele sau coloana pe care doriți să le sortați și asigurați-vă că nu selectați nicio celulă goală. Apoi, accesați fila „Date” și faceți clic fie pe „A-Z” pentru a sorta crescător, fie „Z-A” pentru a sorta descendent în grupul Sortare și filtrare.
Dacă există celule goale între ele, Excel presupune că toate datele au fost sortate și se oprește deasupra celulei negre. Dacă doriți să sortați întreaga coloană, selectați orice celulă din coloană și utilizați opțiunea de mai sus.
Aceleași opțiuni pot fi accesate și sub instrumentul „Sortare și filtrare” din secțiunea Editare a filei „Acasă”.
După ce faceți clic pe butonul, excelați imediat lista în ordine alfabetică.
Cum să sortați și să păstrați rândurile împreună în Excel
Puteți sorta cu ușurință o coloană dacă aveți doar o coloană în foaia de lucru, dar devine puțin complicat când foaia de lucru conține mai multe coloane lângă coloana pe care doriți să o sortați. De asemenea, uneori, lucrăm pe foi de calcul în care trebuie să păstrăm intacte rândurile corespunzătoare după sortarea coloanei.
De exemplu, dacă avem o foaie de calcul cu lista de note a studenților. Când sortăm coloana de nume de studenți în ordine alfabetică, vrem, de asemenea, ca notele introduse în rândurile de lângă ei să se miște în consecință.
În astfel de cazuri, puteți folosi butonul „A-Z” sau „Z-A” pentru a sorta una dintre coloane în ordine alfabetică și va aranja automat datele în alte coloane, păstrând rândurile împreună. Să vedem cum funcționează cu un alt exemplu.
Mai întâi, selectați prima coloană în funcție de care doriți să sortați alte coloane. Apoi selectați fie „A-Z” fie „Z-A” sub fila Date sau fila Acasă pentru a sorta datele într-o coloană, iar Excel va rearanja automat celelalte coloane în consecință.
După ce faceți clic pe butonul de sortare, va apărea o fereastră de dialog Sort Warning. Selectați butonul radio pentru „Extindeți selecția” și faceți clic pe butonul „Sortați”.
Acest lucru va menține rândurile împreună în timpul sortării coloanelor, fără a se potrivi nicio dată (după cum se arată mai jos).
Dacă selectați opțiunea „Continuați cu selecția curentă”, Excel va sorta doar coloana selectată.
Cum să sortați alfabetic după mai multe coloane
Dacă doriți să sortați alfabetic un set de date după mai multe coloane, puteți face acest lucru cu funcția de sortare a Excel.
De exemplu, dorim să aranjam următorul tabel în ordine alfabetică, mai întâi după țară și apoi după prenume:
Mai întâi, selectați întregul tabel pe care doriți să îl sortați în ordine alfabetică, apoi accesați fila „Date” și, în grupul Sortare și filtrare, faceți clic pe „Sortați”.
Sau faceți clic pe pictograma „Sortare și filtrare” din secțiunea Editare din fila „Acasă” și selectați „Sortare personalizată” pentru a accesa aceeași funcție.
Va apărea caseta de dialog Sortare și va afișa prima coloană (parametru) în care va fi sortat tabelul. În caseta derulantă „Sortare după”, selectați coloana principală după care doriți să alfabetizați datele mai întâi, „Țara” în cazul nostru, iar în meniul derulant „Comandă” alegeți fie „A la Z”, fie „Z la A” .
Apoi, faceți clic pe butonul „Adăugați nivel” pentru a adăuga al doilea nivel de sortare. Selectați a doua coloană (în cazul nostru Prenume) după care doriți să alfabetizați datele în caseta „Sortare după” și selectați fie „A la Z”, fie „Z la A”. Dacă tabelul dvs. are antete în partea de sus, bifați caseta de selectare „Datele mele au antete”, pentru a putea sări peste antete în timp ce le sortați. Adăugați mai multe niveluri de sortare dacă este necesar și faceți clic pe „OK”.
Tabelul este aranjat alfabetic: mai întâi după țară și apoi după prenume, așa cum se arată mai jos.
Cum să sortați rândurile alfabetic în Excel
Uneori este posibil să doriți să alfabetizați rândurile în Excel, mai degrabă decât coloanele. Acest lucru se poate face folosind funcția de sortare din Excel. De exemplu, să presupunem că aveți o foaie de calcul în care coloana din primul rând de la B la T conține numele orașelor în care numărul de magazine disponibile în diferite tipuri de comerț cu amănuntul și coloana A conține tipul de comerciant cu amănuntul. Celulele sunt folosite pentru a ține evidența câte magazine din fiecare oraș din diferite categorii de comercianți cu amănuntul.
Mai întâi, selectați toate rândurile pe care doriți să le alfabetizați; Dacă nu doriți să mutați etichetele rândurilor, lăsați-le în afara selecției dvs. Acum, navigați la fila „Date” și faceți clic pe „Sortați” în grupul Sortare și filtrare.
În caseta de dialog Sortare, faceți clic pe butonul „Opțiuni”.
Va apărea o altă casetă de dialog mică, oferindu-vă opțiunea de a selecta „Sortează de la stânga la dreapta” sau „Sortează de sus în jos”. Selectați opțiunea „Sortare de la stânga la dreapta” pentru a alfabetiza după rând și faceți clic pe „OK” pentru a reveni la caseta de dialog Sortare.
Acum, selectați rândul pe care doriți să îl alfabetizați în lista derulantă „Sortare după” (Rândul 1 în acest exemplu). În celălalt câmp, păstrați valorile celulei în caseta „Sort on” și selectați „A la Z” (ordine crescătoare) în caseta Order. Apoi, faceți clic pe „OK” pentru a închide caseta de dialog.
Acum, primul rând din tabelul nostru este sortat în ordine alfabetică (crescător), iar rândurile rămase sunt rearanjate în mod corespunzător, așa cum se arată mai jos.
Cum să sortați alfabetic o coloană utilizând un filtru
O altă modalitate rapidă de a sorta alfabetic în Excel este utilizarea funcției Filtru. După ce aplicați filtre coloanelor, opțiunile de sortare pentru toate coloanele pot fi accesate cu un singur clic de mouse.
Pentru a adăuga un filtru la coloane, selectați una sau mai multe coloane și faceți clic pe opțiunea „Filtrare” din grupul Sortare și filtrare din fila „Date”. Dacă doriți să aplicați filtrul întregului tabel, selectați orice celulă din tabel și faceți clic pe opțiunea „Filtrare”.
De asemenea, puteți accesa opțiunea Filtru făcând clic pe instrumentul „Sortare și filtrare” din grupul de editare din fila „Acasă” și selectând „Filtrare”.
În fiecare antet de coloană va apărea o mică săgeată derulantă. Faceți clic pe săgeata oricărei coloane pe care doriți să o sortați alfabetic și selectați fie „Sortați de la A la Z”, fie „Sortați de la Z la A”.
Aceasta va aranja coloana dorită în ordinea alfabetică aleasă, iar dacă ați ales „Sortare de la A la Z”, va apărea o mică săgeată în sus pe butonul de filtru care indică ordinea de sortare (crescător):
Sortare avansată într-o comandă personalizată
Sortarea alfabetică nu este ideală pentru toate datele alfabetice. Uneori, datele pot fi alfabetizate, dar nu este cel mai eficient mod de a le sorta.
Să ne imaginăm o situație în care este posibil să aveți o listă care conține numele lunilor sau zilelor săptămânii, sortarea alfabetică nu este utilă în această situație. Este mai logic să sortăm acea listă cronologic. Dacă sortați de la A la Z, va aranja lunile în ordine alfabetică, va veni mai întâi aprilie, apoi august, februarie, iunie și așa mai departe. Dar asta nu este ceea ce vrei tu. Din fericire, este foarte ușor să aranjați cronologic în Excel folosind opțiunea Advanced Custom Sort.
Începeți prin a selecta tabelul pe care doriți să îl sortați. Apoi selectați „Sortare personalizată” sub „Sortare și filtrare” în secțiunea Editare a filei Acasă.
În caseta de dialog Sortare, alegeți coloana care conține lunile anului în secțiunea coloane, sortați pe „Valori”, ca de obicei, iar în secțiunea Comandă, alegeți „Lista personalizată”.
În caseta de dialog Liste personalizate, puteți crea o listă proprie. Există câteva liste personalizate implicite, inclusiv zile ale săptămânii, luni abreviate, luni ale anului și așa mai departe. Alegeți opțiunea de sortare potrivită în funcție de nevoile dvs. (în cazul nostru ianuarie, februarie, ..) și faceți clic pe „OK”. Apoi faceți clic din nou pe „OK” pentru a vă sorta lista cronologic.
Veți observa că ați sortat cu succes lista conform comenzii lunii.
Cum să sortați alfabetic în Excel folosind formule Excel
Dacă sunteți un fan al formulelor, atunci puteți utiliza formulele Excel pentru a alfabetiza o listă. Cele două formule care pot fi folosite pentru alfabetizarea datelor sunt COUNTIF și VLOOKUP.
De exemplu, avem o listă de nume pe care dorim să le alfabetizăm folosind formula.
Pentru a sorta acest lucru, vom adăuga o coloană temporară numită „Ordine de sortare” la tabelul existent.
În celula (A2) de lângă date, utilizați următoarea formulă COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Formula de mai sus compară o valoare de text din celula B2 cu toate celelalte valori de text (B3:B20) și returnează rangul său relativ. De exemplu, în celula B2, returnează 11, deoarece există 11 valori de text care sunt mai mici sau egale cu textul „Nancy” în ordine alfabetică. Rezultatul este ordinea de sortare a numelui angajatului din celula B2.
Apoi utilizați mânerul de umplere trageți această formulă pentru a o completa în întregul interval. Aceasta oferă ordinea de sortare a fiecăruia dintre numele din listă.
Acum, trebuie să aranjam datele afișate în captura de ecran de mai sus pe baza numărului de ordine de sortare, iar pentru a face acest lucru vom folosi o funcție VLOOKUP.
Sintaxa:
=CĂUTAREV(,A:B,2,0)Aici „numărul de sortare” reprezintă numerele în ordine crescătoare de la 1 la 20. Pentru ordinea descrescătoare numerele ar trebui să fie de la 20 la 1.
Aici, avem Coloana D pentru nume sortate. Pentru celula D2, introduceți următoarea formulă CĂUTARE V:
=CĂUTAREV(1,A:B,2,0)În mod similar, pentru a doua și a treia celulă, trebuie să utilizați formula ca:
=CĂUTAREV(2,A:B,2,0) și =CĂUTAREV(3,A:B,2,0) și așa mai departe…
După introducerea acestei formule de CĂUTARE V în fiecare celulă de lângă date, lista este alfabetizată.
În loc să introduceți manual formula menționată mai sus (1-20) în fiecare celulă, puteți utiliza și funcția rând pentru a vă ușura munca. Funcția Row () returnează numărul de rând al celulei curente. Deci, cu ajutorul funcției rând, formula va fi:
=CĂUTAREV(RÂND()-1,A:B,2,0)Această formulă vă va oferi același rezultat ca și formula de mai sus.
Și apoi utilizați mânerul de umplere pentru a trage această formulă pentru a o completa în întregul interval.
Cum să alfabetizezi intrările după nume de familie
Ocazional, adesea trebuie să sortăm alfabetic foile de date cu nume de familie. În astfel de cazuri, chiar dacă numele încep cu prenume, trebuie să le alfabetizați după nume de familie. Puteți face acest lucru cu formule Excel Text.
Pentru a face asta, mai întâi, va trebui să extrageți numele și prenumele din numele complete în două coloane diferite. Apoi inversați numele, sortați-le și apoi inversați-le înapoi la forma lor originală.
Cu un nume complet în A2, introduceți următoarele formule în două celule diferite (B2 și C2), apoi copiați formulele în jos pe coloane (folosind mânerul de umplere) până la ultima celulă cu date:
Pentru a extrage prenumele, introduceți această formulă în celula C2:
=STÂNGA(A2,CĂUTARE(” “,A2)-1)
Pentru a extrage numele de familie, introduceți această formulă în celula D2:
=DREAPTA(A2,LEN(A2)-CĂUTARE(" ",A2,1))
Și apoi, în celula E2, au concatenat numele și prenumele în ordine inversă, separate prin virgulă:
=D2&", „&C2
Am separat numele și prenumele și le-am inversat. Acum trebuie să le alfabetizăm și să le inversăm. După cum puteți vedea mai sus, când extragem nume folosind formula, coloanele C, D și E conțin de fapt formule, dar sunt formatate pentru a arăta ca valori. Deci trebuie să convertim formula în valori.
Pentru a face asta, mai întâi selectați toate celulele formulei (E1:E31) și apăsați Ctrl + C pentru a le copia. Faceți clic dreapta pe celulele selectate, faceți clic pe pictograma „Valori” sub „Opțiuni de lipire” și apăsați tasta „Enter”.
Apoi, selectați orice celulă din coloana rezultată și faceți clic pe opțiunea „A la Z” sau „Z la A” din fila „Date” sau „Acasă”, în funcție de nevoile dvs.
O casetă de dialog Sort Warning va apărea după ce faceți clic pe butonul. Alegeți opțiunea „Extindeți selecția” și faceți clic pe butonul „Sortați”.
După cum puteți vedea, întreaga coloană este alfabetizată după numele de familie.
Acum, dacă doriți să îl inversați înapoi la „formatul de nume de familie” original, trebuie doar să introduceți aceleași formule, dar cu referințe de celule diferite.
Trebuie să separați din nou numele (E2) în două părți folosind formulele de mai jos:
În G2, extrageți prenumele:
=DREAPTA(E2,LEN(E2)-CĂUTARE(" ",E2))
În G2, trageți numele de familie:
=STÂNGA(E2,CĂUTARE(" ",E2)-2)
Și unește cele două părți împreună pentru a obține numele complet original:
=G2&" "&H2
De fiecare dată când introduceți formula în prima celulă, asigurați-vă că copiați formula în coloană (folosind mânerul de umplere) până la ultima celulă cu date.
Acum tot ce trebuie să faceți este să convertiți formulele în conversie de valori încă o dată, așa cum am făcut mai sus, și ați terminat. aceasta făcută și.