Generisk formel
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Sammendrag
For å rangere elementer i en liste ved hjelp av ett eller flere kriterier, kan du bruke COUNTIFS-funksjonen. I eksemplet vist er formelen i E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
hvor "grupper" er det navngitte området C5: C14, og "score" er det navngitte området D5: D14. Resultatet er en rangering for hver person i sin egen gruppe.
Merk: selv om data er sortert etter gruppe i skjermbildet, vil formelen fungere bra med usorterte data.
Forklaring
Selv om Excel har en RANK-funksjon, er det ingen RANKIF-funksjon for å utføre en betinget rangering. Du kan imidlertid enkelt opprette en betinget RANK med COUNTIFS-funksjonen.
COUNTIFS-funksjonen kan utføre en betinget telling ved å bruke to eller flere kriterier. Kriterier legges inn i rekkevidde / kriteriepar. I dette tilfellet begrenser de første kriteriene tellingen til samme gruppe, ved å bruke det navngitte området "grupper" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
I seg selv vil dette returnere totale gruppemedlemmer i gruppe "A", som er 5.
Det andre kriteriet begrenser tellingen til bare poeng større enn "nåværende poengsum" fra D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
De to kriteriene jobber sammen for å telle rader der gruppen er A og poengsummen er høyere. For fornavnet i listen (Hannah) er det ingen høyere score i gruppe A, så COUNTIFS returnerer null. I neste rad (Edward) er det tre poeng i gruppe A som er høyere enn 79, så COUNTIFS returnerer 3. Og så videre.
For å få en ordentlig rangering legger vi ganske enkelt til 1 i nummeret som returneres av COUNTIFS.
Omvendt rangordre
For å reversere rangorden og rangere i rekkefølge (dvs. at den minste verdien er rangert som nr. 1), bruk bare operatoren mindre enn ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
I stedet for å telle poeng større enn D5, vil denne versjonen telle poeng mindre enn verdien i D5, og effektivt reversere rangordren.
Duplikater
I likhet med RANK-funksjonen vil formelen på denne siden tildele duplikatverdier samme rang. For eksempel, hvis en spesifikk verdi er tildelt en rangering på 3, og det er to forekomster av verdien i dataene som rangeres, vil begge forekomster få en rangering på 3, og neste rangering som blir tildelt vil være 5. For å etterligne atferden av RANK.AVG-funksjonen, som i et slikt tilfelle vil tildele en gjennomsnittlig rangering på 3,5, kan du beregne en "korreksjonsfaktor" med en formel som denne:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Resultatet fra denne formelen ovenfor kan legges til den opprinnelige rangeringen for å få en gjennomsnittlig rangering. Når en verdi ikke har duplikater, returnerer koden ovenfor null og har ingen effekt.
