Excel

Rang hvis formel

Rank If Formula

Excel-formel: Rang, hvis formelGenerisk 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 navngivet rækkevidde C5: C14, og 'scores' er det navngivne interval 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 , der er 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 navngivet rækkevidde '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 fornavnet 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 rangordre og rangordne i rækkefølge (dvs. den mindste værdi rangeres som nr. 1) skal du bare bruge operatoren mindre end ():

hvordan man finder Q1 i Excel
 
= 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-funktion , vil formlen på denne side tildele duplikatværdier den 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, begge tilfælde vil modtage en rang på 3, og den næste rang, der tildeles, vil være 5. For at efterligne adfærden for RANK.AVG-funktion , 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.

Forfatter Dave Bruns


^