Všeobecný vzorec
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Zhrnutie
Ak chcete zoradiť položky v zozname podľa jedného alebo viacerých kritérií, môžete použiť funkciu COUNTIFS. V zobrazenom príklade je vzorec v E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kde „skupiny“ je pomenovaný rozsah C5: C14 a „skóre“ je pomenovaný rozsah D5: D14. Výsledkom je hodnotenie pre každého človeka v jeho vlastnej skupine.
Poznámka: Aj keď sú údaje na snímke obrazovky zoradené podľa skupín, vzorec bude fungovať dobre aj s netriedenými údajmi.
Vysvetlenie
Aj keď má Excel funkciu RANK, neexistuje žiadna funkcia RANKIF na vykonanie podmieneného poradia. Podmienenú RANKU však môžete ľahko vytvoriť pomocou funkcie COUNTIFS.
Funkcia COUNTIFS môže vykonávať podmienené počítanie pomocou dvoch alebo viacerých kritérií. Kritériá sa zadávajú v pároch rozsah / kritérium. V takom prípade prvé kritérium obmedzuje počet na rovnakú skupinu pomocou pomenovaného rozsahu „groups“ (C5: C14):
=COUNTIFS(groups,C5) // returns 5
To samo o sebe vráti celkových členov skupiny v skupine „A“, čo je 5.
Druhé kritérium obmedzuje počet iba na skóre väčšie ako „aktuálne skóre“ z D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Tieto dve kritériá spolupracujú na spočítaní riadkov, kde je skupina A a skóre je vyššie. Pokiaľ ide o krstné meno v zozname (Hannah), v skupine A nie sú vyššie skóre, takže funkcia COUNTIFS vráti nulu. V ďalšom riadku (Edward) sú v skupine A tri skóre vyššie ako 79, takže COUNTIFS vráti 3. A tak ďalej.
Aby sme dosiahli správne hodnotenie, jednoducho pridáme 1 k číslu vrátenému COUNTIFS.
Poradie obrátenia
Na obrátenie poradia a poradie v poradí (tj. Najmenšia hodnota je zaradená na 1. mieste) stačí použiť operátor menej ako ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Namiesto počítania skóre väčších ako D5 bude táto verzia počítať skóre menšie ako hodnota v D5, čo efektívne obráti poradie.
Duplikáty
Rovnako ako funkcia RANK, aj vzorec na tejto stránke priradí duplicitným hodnotám rovnaké hodnotenie. Napríklad, ak je konkrétnej hodnote priradené poradie 3 a v hodnotených dátach sú dva výskyty hodnoty, obidva výskyty dostanú poradie 3 a ďalšie priradené poradie bude 5. Napodobňovanie správania funkcie RANK.AVG, ktorá by v takom prípade priradila priemerné hodnotenie 3,5, môžete vypočítať „korekčný faktor“ pomocou tohto vzorca:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Výsledok z tohto vzorca vyššie je možné pridať k pôvodnému hodnoteniu, aby ste získali priemerné hodnotenie. Ak hodnota nemá duplikáty, vyššie uvedený kód vráti nulu a nebude mať žiadny vplyv.
