Formula generică
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
rezumat
Pentru a sorta dinamic datele atât cu cifre, cât și cu text în ordine alfabetică, puteți utiliza o formulă pentru a genera un rang numeric într-o coloană de ajutor, apoi utilizați INDEX și MATCH pentru a afișa valori bazate pe rang. În exemplul prezentat, formula din C5 este:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
unde „date” este intervalul numit B5: B13.
Explicaţie
Această formulă generează mai întâi o valoare de rang folosind o expresie bazată pe COUNTIF:
=COUNTIF(data,"<="&B5)
ceea ce este explicat mai detaliat aici. Dacă datele conțin toate valorile textului sau toate valorile numerice, rangul va fi corect. Cu toate acestea, dacă datele includ atât text cât și numere, trebuie să „schimbăm” rangul tuturor valorilor textului pentru a ține cont de valorile numerice. Acest lucru se face cu a doua parte a formulei de aici:
+(COUNT(data)*ISTEXT(B7))
Aici, folosim funcția COUNT pentru a obține un număr de valori numerice în date, apoi înmulțim rezultatul cu rezultatul logic al ISTEXT, care testează dacă valoarea este text și returnează fie TRUE, fie FALSE. Acest lucru anulează efectiv rezultatul COUNT atunci când lucrăm cu un număr din rândul curent.
Manipularea duplicatelor
Dacă datele conțin duplicate, formula poate fi modificată așa cum se arată mai jos pentru a atribui un rang secvențial valorilor care apar de mai multe ori:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Această versiune ajustează logica funcției COUNTIF inițiale și adaugă un alt COUNTIF cu o referință extinsă pentru a crește duplicatele.
Afișați valorile sortate
Pentru a prelua și afișa valorile sortate în ordine alfabetică folosind valoarea de rang calculată, E5 conține următoarea formulă INDEX și MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
unde „date” este intervalul numit B5: B13, iar „rang” este intervalul numit C5: C13.
Pentru mai multe informații despre cum funcționează această formulă, consultați exemplul aici.
Tratarea golurilor
Celulele goale vor genera un rang zero. Presupunând că doriți să ignorați celulele goale, acest lucru funcționează bine, deoarece formula INDEX și MATCH de mai sus începe la 1. Cu toate acestea, veți vedea erori # N / A la sfârșitul valorilor sortate, câte una pentru fiecare celulă goală. O modalitate ușoară de a gestiona acest lucru este de a înfășura formula INDEX și MATCH în IFERROR astfel:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
