Generisk formel
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Resumé
Hvis du vil rangere elementer på en liste ved hjælp af et eller flere kriterier, kan du bruge COUNTIFS-funktionen. I det viste eksempel er formlen i E5:
=COUNTIFS(groups,C5,scores,">"&D5)+1
hvor "grupper" er det navngivne område C5: C14, og "scores" er det navngivne område D5: D14. Resultatet er en rang for hver person i deres egen gruppe.
Bemærk: selvom data er sorteret efter gruppe i skærmbilledet, fungerer formlen fint med usorterede data.
Forklaring
Selvom Excel har en RANK-funktion, er der ingen RANKIF-funktion til at udføre en betinget rang. Du kan dog nemt oprette en betinget RANK med COUNTIFS-funktionen.
COUNTIFS-funktionen kan udføre en betinget optælling ved hjælp af to eller flere kriterier. Kriterier er angivet i række- / kriteriepar. I dette tilfælde begrænser de første kriterier optællingen til den samme gruppe ved hjælp af det navngivne interval "grupper" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
I sig selv vil dette returnere de samlede gruppemedlemmer i gruppe "A", hvilket er 5.
Det andet kriterium begrænser antallet til kun at score større end den "aktuelle score" fra D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
De to kriterier arbejder sammen for at tælle rækker, hvor gruppen er A, og scoren er højere. For det første navn på listen (Hannah) er der ingen højere score i gruppe A, så COUNTIFS returnerer nul. I den næste række (Edward) er der tre scoringer i gruppe A, der er højere end 79, så COUNTIFS returnerer 3. Og så videre.
For at få en ordentlig rang tilføjer vi simpelthen 1 til det antal, der returneres af COUNTIFS.
Omvendt rangordning
For at vende rangorden og rangordne (dvs. den mindste værdi er rangeret som nr. 1) skal du bare bruge operatoren mindre end ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
I stedet for at tælle scoringer større end D5, tæller denne version scoringer mindre end værdien i D5, hvilket effektivt vender rangordenen.
Kopier
Ligesom RANK-funktionen tildeler formlen på denne side duplikatværdier samme rang. For eksempel, hvis en bestemt værdi tildeles en rang på 3, og der er to forekomster af værdien i de data, der rangeres, modtager begge forekomster en rang på 3, og den næste tildelte rang vil være 5. For at efterligne adfærden af RANK.AVG-funktionen, som i et sådant tilfælde ville tildele en gennemsnitlig rang på 3,5, kan du beregne en "korrektionsfaktor" 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 formel ovenfor kan føjes til den oprindelige rang for at få en gennemsnitlig rang. Når en værdi ikke har dubletter, returnerer ovenstående kode nul og har ingen effekt.
