Sortarea integrată a datelor Excel este uimitoare, dar nu este dinamică. Dacă sortați date și apoi adăugați date la acesta, va trebui să le sortați din nou.
Sortați datele în ordine alfabetică
În această postare, vă voi arăta diverse moduri de a sorta datele în ordine alfabetică folosind formule. Aceasta înseamnă că puteți adăuga date și le va sorta automat pentru dvs.
Când datele sunt toate text fără duplicate
Să presupunem că aveți date așa cum se arată mai jos:
În acest exemplu, toate datele sunt în format text (fără numere, spații goale sau duplicate). Pentru a sorta acest lucru, voi folosi o coloană de ajutor. În coloana de lângă date, utilizați următoarea formulă COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Această formulă compară o valoare de text cu toate celelalte valori de text și îi returnează rangul relativ. De exemplu, în celula B2, returnează 8, deoarece există 8 valori de text mai mici sau egale cu textul „SUA” (ordine alfabetică).
Acum, pentru a sorta valorile, utilizați următoarea combinație de funcții INDEX, MATCH și ROWS:
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Această formulă extrage pur și simplu numele în ordinea alfabetică. În prima celulă (C2), caută numele țării care are cel mai mic număr (Australia are 1). În a doua celulă, returnează Canada (care are numărul 2) și așa mai departe …
Alergic la coloanele de ajutor ??
Iată o formulă care va face același lucru fără coloana de ajutor.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Aceasta este o formulă matrice, deci utilizați Control + Shift + Enter în loc de Enter.
Vă voi lăsa să decodificați.
Încercați-l singur … Descărcați fișierul de exemplu
Această formulă funcționează bine dacă aveți valori text sau alfanumerice.
Dar eșuează lamentabil dacă:
- Aveți duplicate în date (încercați să puneți SUA de două ori).
- Există date goale în date.
- Aveți un amestec de numere și text (încercați să puneți 123 într-una dintre celule).
Când datele sunt un amestec de numere, text, duplicate și goluri
Acum acesta este un pic dificil. Voi folosi 4 coloane de ajutor pentru a vă arăta cum funcționează (și apoi vă voi oferi o formulă uriașă care o va face fără coloanele de ajutor). Să presupunem că aveți date așa cum se arată mai jos:
Puteți vedea că există valori duplicat, gol și numere. Deci, voi folosi coloane de ajutor pentru a aborda fiecare dintre aceste probleme.
Coloana de ajutor 1
Introduceți următoarea formulă COUNTIF în coloana 1 de ajutor
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Această formulă face următoarele:
- Returnează 0 pentru spațiile goale.
- În cazul duplicatelor, acesta returnează același număr.
- Textul și numerele sunt procesate în paralel și această formulă returnează același număr pentru text și număr (de exemplu, 123 și India obțin ambele 1).
Coloana de ajutor 2
Introduceți următoarea funcție IS în coloana 2 Helper:
= - ISNUMBER (A2)
Coloana de ajutor 3
Introduceți următoarea formulă în coloana de ajutor 3:
= - ISBLANK (A2)
Coloana de ajutor 4
Introduceți următoarea formulă în coloana de ajutor 4
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2 + $ C $ 10)) + $ D $ 10
Ideea pentru această formulă este de a separa spațiile goale, numerele și valorile textului.
- Dacă celula este necompletată, returnează valoarea din celula B2 (care ar fi întotdeauna 0) și adaugă valoarea în celula D10. Pe scurt, va returna numărul total de celule goale din date
- Dacă celula este o valoare numerică, va returna rangul comparativ și va adăuga numărul total de spații goale. De exemplu, pentru 123 returnează 2 (1 este rangul 123 din date și există 1 celulă necompletată)
- Dacă este text, returnează rangul comparativ și adaugă numărul total de valori numerice și spații libere. De exemplu, pentru India, adaugă rangul comparativ al textului în text (care este 1) și adaugă numărul de celule goale și numărul de valori numerice.
Rezultatul final - Date sortate
Acum vom folosi aceste coloane de ajutor pentru a obține lista sortată. Iată formula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MIC ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2) + $ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Această metodă de sortare devine acum nepotrivită. V-am arătat metoda pentru 8 articole, dar o puteți extinde la câte articole doriți.
Încercați-l singur … Descărcați fișierul de exemplu
O formulă pentru a le sorta pe toate (fără coloane de ajutor)
Dacă puteți gestiona formule extreme, iată o formulă all-in-one care va sorta datele în ordine alfabetică (fără nicio coloană de ajutor).
Iată formula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MIC (NU ($ A $ 2: $ A $ 9 = "") * IF (ISNUMBER ($ A $ 2: $ A $ 9)), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9 $, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), ROWS ($ A $ 2: A2) + SUM (- ISBLANK ($ A $ 2: $ A $ 9)))), NU ($ A $ 2: $ A $ 9 = "") * IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUMĂ (- ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Introduceți această formulă într-o celulă și trageți-o în jos pentru a obține lista sortată. De asemenea, deoarece aceasta este o formulă matrice, utilizați Control + Shift + Enter în loc de Enter.
Această formulă are utilitate în lumea reală. Tu ce crezi? Mi-ar plăcea să învăț de la tine. Lasă-ți urmele în secțiunea de comentarii!
