Generička formula
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Sažetak
Da biste rangirali stavke na popisu pomoću jednog ili više kriterija, možete koristiti funkciju COUNTIFS. U prikazanom primjeru formula u E5 je:
=COUNTIFS(groups,C5,scores,">"&D5)+1
gdje je "grupe" imenovani raspon C5: C14, a "ocjene" imenovani raspon D5: D14. Rezultat je rang svake osobe u svojoj grupi.
Napomena: iako su podaci na snimci zaslona sortirani po skupinama, formula će dobro funkcionirati s nerazvrstanim podacima.
Obrazloženje
Iako Excel ima funkciju RANK, ne postoji funkcija RANKIF koja bi izvršila uvjetni rang. Međutim, lako možete stvoriti uvjetni RANK pomoću funkcije COUNTIFS.
Funkcija COUNTIFS može izvesti uvjetno brojanje pomoću dva ili više kriterija. Kriteriji se unose u parove raspona / kriterija. U ovom slučaju, prvi kriterij ograničava brojanje na istu skupinu, koristeći imenovane "skupine" raspona (C5: C14):
=COUNTIFS(groups,C5) // returns 5
To će samo po sebi vratiti ukupni broj članova grupe "A", što je 5.
Drugi kriterij ograničava brojanje na samo bodove veće od "trenutnih rezultata" iz D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Dva kriterija zajedno rade kako bi se prebrojali redovi u kojima je skupina A, a rezultat je veći. Za prvo ime na popisu (Hannah) nema viših rezultata u skupini A, pa COUNTIFS vraća nulu. U sljedećem redu (Edward) u skupini A nalaze se tri rezultata viša od 79, pa COUNTIFS vraća 3. I tako dalje.
Da bismo dobili ispravan rang, jednostavno dodamo 1 broju koji je vratio COUNTIFS.
Obrtanje redoslijeda rangova
Da biste poništili poredak i poredak po redoslijedu (tj. Najmanja vrijednost je rangirana na 1. mjestu), samo upotrijebite operator manje od ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Umjesto da broji ocjene veće od D5, ova verzija računat će ocjene manje od vrijednosti u D5, efektivno obrćući poredak ranga.
Duplikati
Poput funkcije RANK, formula na ovoj stranici dodijelit će duplicirane vrijednosti istog ranga. Na primjer, ako je određenoj vrijednosti dodijeljen rang 3, a postoje dvije instance vrijednosti u podacima koji se rangiraju, obje instance dobit će rang 3, a sljedeći rang bit će 5. Za oponašanje ponašanja funkcije RANK.AVG, koja bi u takvom slučaju dodijelila prosječni rang 3,5, možete izračunati "faktor korekcije" s formulom poput ove:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Rezultat iz gornje formule može se dodati izvornom rangu kako bi se dobio prosječni rang. Kada vrijednost nema duplikate, gornji kôd vraća nulu i nema učinka.
