Splošna formula
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Povzetek
Če želite razvrstiti elemente na seznamu z enim ali več merili, lahko uporabite funkcijo COUNTIFS. V prikazanem primeru je formula v E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kjer je "skupine" imenovani obseg C5: C14, "scores" pa imenovani obseg D5: D14. Rezultat je rang za vsako osebo v svoji skupini.
Opomba: čeprav so podatki na posnetku zaslona razvrščeni po skupinah, bo formula dobro delovala z nerazvrščenimi podatki.
Pojasnilo
Čeprav ima Excel funkcijo RANK, ni funkcije RANKIF, ki bi izvedla pogojno uvrstitev. Lahko pa enostavno ustvarite pogojni RANK s funkcijo COUNTIFS.
Funkcija COUNTIFS lahko izvede pogojno štetje z uporabo dveh ali več meril. Merila se vnašajo v parih obseg / kriterij. V tem primeru prvo merilo šteje omejitev na isto skupino z uporabo imenovanih "skupin" obsega (C5: C14):
=COUNTIFS(groups,C5) // returns 5
To samo po sebi vrne skupno število članov skupine v skupini "A", kar je 5.
Drugi kriterij omejuje štetje le na ocene, večje od "trenutne ocene" iz D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Merila skupaj preštevata vrstice, kjer je skupina A in je rezultat višji. Za prvo ime na seznamu (Hannah) v skupini A ni višjih rezultatov, zato COUNTIFS vrne nič. V naslednji vrstici (Edward) so v skupini A tri ocene višje od 79, zato COUNTIFS vrne 3. In tako naprej.
Da bi dobili pravilno uvrstitev, preprosto dodamo 1 številu, ki ga vrne COUNTIFS.
Obrnitev vrstnega reda
Če želite obrniti vrstni red in razvrstiti vrstni red (tj. Najmanjša vrednost je uvrščena na 1. mesto), uporabite operator manj kot ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Namesto štetja rezultatov, večjih od D5, bo ta različica štela rezultate, manjše od vrednosti v D5, kar bo dejansko spremenilo vrstni red rangov.
Dvojniki
Tako kot funkcija RANK bo tudi formula na tej strani podvojene vrednosti dodelila enak rang. Če je na primer določeni vrednosti dodeljen rang 3 in sta v podatkih, ki se uvrščajo, dva primera vrednosti, bosta oba primerka prejela rang 3, naslednji rang pa bo 5. Če posnemate funkcije RANK.AVG, ki bi v takem primeru dodelila povprečni rang 3,5, lahko izračunate "korekcijski faktor" s formulo, kot je ta:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Rezultat te zgornje formule lahko dodate prvotnemu rangu, da dobite povprečni uvrstitev. Ko vrednost nima dvojnikov, zgornja koda vrne nič in nima učinka.
