Formula generică
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
rezumat
Pentru a clasa elementele dintr-o listă utilizând unul sau mai multe criterii, puteți utiliza funcția COUNTIFS. În exemplul prezentat, formula din E5 este:
=COUNTIFS(groups,C5,scores,">"&D5)+1
unde „grupuri” este intervalul numit C5: C14, iar „scoruri” este intervalul numit D5: D14. Rezultatul este un rang pentru fiecare persoană din propriul grup.
Notă: deși datele sunt sortate după grup în captura de ecran, formula va funcționa bine cu datele nesortate.
Explicaţie
Deși Excel are o funcție RANK, nu există o funcție RANKIF pentru a efectua un rang condițional. Cu toate acestea, puteți crea cu ușurință un RANK condiționat cu funcția COUNTIFS.
Funcția COUNTIFS poate efectua un număr condiționat folosind două sau mai multe criterii. Criteriile sunt introduse în perechi interval / criterii. În acest caz, primul criteriu restrânge numărul la același grup, folosind intervalul numit „grupuri” (C5: C14):
=COUNTIFS(groups,C5) // returns 5
În sine, acest lucru va returna membrii grupului total în grupul „A”, care este 5.
Al doilea criteriu restricționează numărul la scoruri mai mari decât „scorul actual” din D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Cele două criterii funcționează împreună pentru a număra rândurile în care grupul este A și scorul este mai mare. Pentru prenumele din listă (Hannah), nu există scoruri mai mari în grupa A, deci COUNTIFS returnează zero. În rândul următor (Edward), există trei scoruri în grupa A mai mare de 79, deci COUNTIFS returnează 3. Și așa mai departe.
Pentru a obține un rang adecvat, pur și simplu adăugăm 1 la numărul returnat de COUNTIFS.
Inversarea ordinii de rang
Pentru a inversa ordinea rangului și rangul în ordine (adică cea mai mică valoare este clasată pe locul 1) folosiți doar operatorul mai puțin decât
=COUNTIFS(groups,C5,scores,"<"&D5)+1
În loc să numere scoruri mai mari decât D5, această versiune va conta scoruri mai mici decât valoarea din D5, inversând efectiv ordinea de rang.
Dubluri
La fel ca funcția RANK, formula de pe această pagină va atribui valori duplicat de același rang. De exemplu, dacă unei valori specifice i se atribuie un rang de 3 și există două instanțe ale valorii în datele care sunt clasificate, ambele instanțe vor primi un rang de 3, iar următorul rang atribuit va fi 5. Pentru a imita comportamentul funcției RANK.AVG, care ar atribui un rang mediu de 3,5 într-un astfel de caz, puteți calcula un „factor de corecție” cu o formulă de genul acesta:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Rezultatul din această formulă de mai sus poate fi adăugat la rangul inițial pentru a obține un rang mediu. Când o valoare nu are duplicate, codul de mai sus returnează zero și nu are efect.
