Obecný vzorec
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
souhrn
Chcete-li hodnotit položky v seznamu podle jednoho nebo více kritérií, můžete použít funkci COUNTIFS. V zobrazeném příkladu je vzorec v E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
kde „skupiny“ je pojmenovaný rozsah C5: C14 a „skóre“ je pojmenovaný rozsah D5: D14. Výsledkem je hodnocení pro každou osobu v její vlastní skupině.
Poznámka: Ačkoli jsou data na snímku obrazovky tříděna podle skupin, vzorec bude fungovat dobře s netříděnými daty.
Vysvětlení
Přestože Excel má funkci RANK, neexistuje žádná funkce RANKIF k provedení podmíněného hodnocení. Podmíněné RANK však můžete snadno vytvořit pomocí funkce COUNTIFS.
Funkce COUNTIFS může provádět podmíněné počítání pomocí dvou nebo více kritérií. Kritéria se zadávají v párech rozsah / kritéria. V tomto případě první kritérium omezuje počet na stejnou skupinu pomocí pojmenovaného rozsahu „groups“ (C5: C14):
=COUNTIFS(groups,C5) // returns 5
To samo o sobě vrátí celkový počet členů skupiny ve skupině „A“, což je 5.
Druhé kritérium omezuje počet pouze na skóre větší než „aktuální skóre“ z D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Tato dvě kritéria společně počítají řádky, kde je skupina A a skóre je vyšší. U křestního jména v seznamu (Hannah) neexistují ve skupině A žádné vyšší skóre, takže funkce COUNTIFS vrátí nulu. V dalším řádku (Edward) jsou ve skupině A tři skóre vyšší než 79, takže COUNTIFS vrátí 3. A tak dále.
Abychom získali správnou hodnost, jednoduše přidáme 1 k číslu vrácenému COUNTIFS.
Obrácené pořadí
Chcete-li obrátit pořadí a pořadí v pořadí (tj. Nejmenší hodnota je hodnocena # 1), použijte operátor less than ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Namísto počítání skóre větších než D5 bude tato verze počítat skóre menší než hodnota v D5, čímž se efektivně obrátí pořadí.
Duplikáty
Stejně jako funkce RANK i vzorec na této stránce přiřadí duplicitním hodnotám stejné hodnocení. Například pokud je konkrétní hodnotě přiřazeno pořadí 3 a v hodnocených datech jsou dva výskyty hodnoty, obě instance obdrží pořadí 3 a další přiřazené pořadí bude 5. Napodobování chování funkce RANK.AVG, která by v takovém případě přiřadila průměrnou hodnost 3,5, můžete vypočítat "korekční faktor" pomocí tohoto vzorce:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Výsledek z tohoto vzorce výše lze přidat k původnímu hodnocení, abyste získali průměrné hodnocení. Pokud hodnota nemá žádné duplikáty, výše uvedený kód vrátí nulu a nemá žádný účinek.
