Formula generică
=COUNTIF(range,"<="&A1)
rezumat
Pentru a sorta dinamic valorile textului în ordine alfabetică, puteți utiliza o formulă bazată pe funcția COUNTIF. În exemplul prezentat, formula din C5 este:
=COUNTIF(countries,"<="&B5)
unde „țări” este gama denumită B4: B13
Explicaţie
Această formulă utilizează operatorul „mai mare sau egal cu” cu text, lucru pe care s-ar putea să nu-l fi încercat înainte. Când Excel compară textul, decide care este valoarea „mai mare” decât alte reguli bazate pe urmarea schemei de numerotare ASCII.
În interiorul COUNTIF, argumentul intervalului este furnizat ca interval numit „țări” (B4: B13), iar criteriile sunt furnizate ca „mai mică sau egală cu” valoarea din C5. În fiecare rând, COUNTIFS returnează numărul de valori care sunt mai mici sau egale cu valoarea curentă, ceea ce creează o listă secvențială de numere (adică un rang) în coloana de ajutor.
Listarea valorilor sortate
Coloana de ajutor poate fi utilizată pentru a extrage valorile sortate după rang. În E5, formula utilizată pentru recuperarea valorilor este:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
Aceasta este o formulă INDEX și MATCH care folosește o referință extinsă pentru a genera numere secvențiale, care sunt introduse în MATCH ca valori de căutare. MATCH calculează unde există fiecare număr în date, iar INDEX recuperează valoarea la poziția respectivă. Consultați această pagină pentru o explicație mai detaliată.
Manipularea duplicatelor
Dacă datele conțin valori de text duplicate, secvența numerelor de sortare va conține și duplicate, ceea ce va cauza probleme dacă încercați să recuperați valori cu funcția INDEX. Pentru a rezolva această problemă, puteți utiliza o variantă a formulei care crește duplicatele cu un al doilea COUNTIF:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Rețineți că operatorul logic din prima funcție COUNTIF a fost ajustat, iar intervalul din a doua funcție COUNTIF este o referință în expansiune.
