Generisk formel
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Sammanfattning
För att rangordna objekt i en lista med hjälp av ett eller flera kriterier kan du använda COUNTIFS-funktionen. I exemplet som visas är formeln i E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
där "grupper" är det namngivna intervallet C5: C14 och "poäng" är det namngivna intervallet D5: D14. Resultatet är en rang för varje person i sin egen grupp.
Obs: även om data sorteras efter grupp i skärmdumpen fungerar formeln bra med osorterad data.
Förklaring
Även om Excel har en RANK-funktion finns det ingen RANKIF-funktion för att utföra en villkorad rangordning. Du kan dock enkelt skapa en villkorad RANK med COUNTIFS-funktionen.
COUNTIFS-funktionen kan utföra en villkorlig räkning med hjälp av två eller flera kriterier. Kriterier anges i intervall / kriteriepar. I det här fallet begränsar de första kriterierna antalet till samma grupp med användning av det namngivna intervallet "grupper" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
I sig kommer detta att returnera totala gruppmedlemmar i grupp "A", vilket är 5.
Det andra kriteriet begränsar räkningen till endast poäng som är större än "aktuell poäng" från D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
De två kriterierna arbetar tillsammans för att räkna rader där gruppen är A och poängen är högre. För förnamnet i listan (Hannah) finns det inga högre poäng i grupp A, så COUNTIFS returnerar noll. I nästa rad (Edward) finns det tre poäng i grupp A högre än 79, så COUNTIFS returnerar 3. Och så vidare.
För att få en ordentlig rankning lägger vi helt enkelt till 1 i det antal som COUNTIFS returnerar.
Omvänd rangordning
För att omvända rangordning och rangordna i ordning (dvs det minsta värdet rankas som nr 1) använder du bara operatoren mindre än ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Istället för att räkna poäng som är större än D5 kommer denna version att räkna poäng mindre än värdet i D5, vilket effektivt vänder rangordningen.
Dubbletter
Liksom RANK-funktionen tilldelar formeln på denna sida dubbletter av samma rang. Till exempel, om ett specifikt värde tilldelas en rankning av 3 och det finns två instanser av värdet i data som rankas, kommer båda instanserna att få en rangordning av 3, och nästa rangordning som tilldelas blir 5. För att efterlikna beteendet för RANK.AVG-funktionen, som skulle tilldela en genomsnittlig rankning på 3,5 i ett sådant fall, kan du beräkna en "korrigeringsfaktor" med en sådan formel:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Resultatet från denna formel ovan kan läggas till den ursprungliga rangordningen för att få en genomsnittlig rangordning. När ett värde inte har några dubbletter returnerar koden ovan noll och har ingen effekt.
