Rangordna funktion i Excel med formelexempel

Handledningen förklarar detaljerna i funktion Rank i Excel och visar hur man sorterar i Excel baserat på olika kriterier, sorterar data efter grupp, beräknar percentilrankning med mera. När du behöver bestämma den relativa positionen för ett tal i en lista med tal, är det enklaste sättet att ordna siffrorna i stigande eller fallande ordning. Om sortering av någon anledning inte är möjlig är en intervallformel i Excel det perfekta verktyget för att få jobbet gjort.

Excel RANGE-funktion

La RANK funktion i excel returnerar ordningen (eller rangordningen) för ett numeriskt värde jämfört med andra värden i samma lista. Med andra ord, det talar om vilket värde som är det högsta, det näst högsta, bland annat.

Kanske kan du vara intresserad: Excel VÄLJ-funktion. Formler och exempel

I en ordnad lista skulle rangordningen för ett visst nummer vara positionen. Range-funktionen i Excel kan bestämma intervallet som börjar med det största värdet (som om det sorterades i fallande ordning) eller det minsta värdet (som om det sorterades i stigande ordning). De RANK funktionssyntax i Excel är följande:

RANK (nummer, ref, [ordning])

var:

• Nummer (obligatoriskt): värdet vars rankning du vill hitta.
• Ref (obligatoriskt): en lista med numeriska värden att sortera med. Den kan tillhandahållas som en uppsättning nummer eller som en referens till listan med nummer.
• Beställning (valfritt): ett nummer som anger hur man sorterar värdena:
  • Om 0 eller utelämnas, sorteras värdena i fallande ordning, det vill säga från högsta till lägsta.
  • Om det är 1 eller något annat värde som inte är noll, sorteras värdena i stigande ordning, det vill säga från lägsta till högsta.

RANK.EQ-funktion i Excel

RANK.EQ Det är en förbättrad version av RANK-funktionen, introducerad i Excel 2010. Den har samma syntax som RANK och fungerar med samma logik: om flera värden rankas lika, tilldelas den högsta rankningen till alla dessa värden. (EQ betyder "lika").

RANK.EQ (nummer, ref, [order])

I Excel 2007 och lägre bör du alltid använda funktionen RANK. I Excel 2010, Excel 2013 och Excel 2016 kan du välja RANK eller RANK.EQ. Det skulle dock vara tillrådligt att använda RANK.EQ eftersom RANK kan avbrytas när som helst.

Excel RANGE.AVG-funktion

RANK.AVG är en annan funktion för hitta utbud i excel som endast är tillgänglig i Excel 2010, Excel 2013, Excel 2016 och senare versioner. Den har samma syntax som de andra två funktionerna:

RANK.AVG (nummer, ref, [order])

Skillnaden är att om mer än ett nummer har samma rang, returneras medelrankningen (AVG betyder "genomsnitt").

4 saker du bör veta om RANK i Excel

Här är några saker att tänka på om Rank-funktionen i Excel:

• Alla intervallformler i Excel fungerar bara för numeriska värden: positiva och negativa siffror, nollor, datum- och tidsvärden. Icke-numeriska värden i ref-argumentet ignoreras.
• Alla RANGE-funktioner returnerar samma intervall för dubbletter av värden och hoppar över ytterligare sortering, som visas i följande exempel.
• I Excel 2010 och senare har RANK-funktionen ersatts med RANK.EQ och RANK.AVG. För bakåtkompatibilitet fungerar RANK fortfarande i alla versioner av Excel, men kanske inte är tillgängligt i framtiden.
• Om numret inte hittas i referensen, skulle valfri Excel-intervallfunktion returnera #N/A-felet.

Grundläggande formel för Excel-intervall (högst till lägst)

För att lära dig mer om rangordning med Rank-funktionen i Excel, ta en titt på den här skärmdumpen:

Alla tre formlerna sorterar siffrorna i kolumn B i fallande ordning (ordningsargumentet utelämnas):

I alla versioner av Excel 2003 – 2016:

=RANK($B2,$B$2:$B$7)

I Excel 2010 – 2016:

=RANK.EQ($B2,$B$2:$B$7)

=RANK.AVG($B2,$B$2:$B$7)

Skillnaden ligger i hur dessa formler behandlar dubbletter av värden. Som du kan se visas samma poäng två gånger, i cellerna B5 och B6, vilket påverkar efterföljande sortering:

• Formlerna RANK och RANK.EQ ger en rankning på 2 till båda duplikatpoängen. Näst högsta poäng (Daniela) är på fjärde plats. Ingen får rang 3.
• Formeln RANK.AVG tilldelar en annan rangordning till varje dubblett bakom kulisserna (2 och 3 i det här exemplet) och returnerar genomsnittet av dessa rangordnar (2.5). Återigen, den tredje rangen tilldelas inte någon.

Hur man använder RANK i Excel – formelexempel

Vägen till excellens, säger de, är kantad av övning. Så, för att bättre lära oss hur man använder RANK-funktionen i Excel, ensam eller i kombination med andra funktioner, låt oss titta på lösningar för några verkliga uppgifter.

Hur man sorterar i Excel från minsta till största

Som visas i exemplet ovan, för att sortera siffror från största till minsta, använd en av Excels intervallformler med orderargumentet satt till 0 eller utelämnat (standard).

För att få ett nummer sorterat mot andra tal i stigande ordning, sätt 1 eller något annat värde som inte är noll i det valfria tredje argumentet. För att till exempel betygsätta elevernas 100-meters sprinttider kan du använda någon av följande formler:

= RANK (B2, $ B $ 2: $ B $ 7,1)

=RANK.EQ(B2,$B$2:$B$7,1)

Var uppmärksam på att vi låser intervallet i argumentet ref genom att använda absoluta cellreferenser, så att det inte ändras när vi kopierar formeln till kolumnen.

Som ett resultat tar det lägsta värdet (snabbaste tiden) första platsen och det största värdet (långsammaste tiden) får den lägsta rangen 6. Lika tider (B2 och B7) får samma rang.

Hur man unikt klassificerar data i Excel

Som nämnts ovan returnerar alla Excel-intervallfunktioner samma intervall för element med lika värde. Om det inte är vad du vill, använd någon av följande formler för att lösa tiebreak-situationer och ge varje nummer en unik rankning.

Unik ranking från högsta till lägsta

För att sortera elevernas matematikpoäng endast i fallande ordning, använd den här formeln:

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Unik ranking från lägsta till högsta

För att sortera dina 100 meter loppresultat i stigande ordning utan dubbletter, använd denna formel:

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Hur dessa formler fungerar

Som ni har lagt märke till Den enda skillnaden mellan de två formlerna är ordningsargumentet för RANK.EQ-funktionen: utelämnat för sortering av fallande värden, 1 för sortering av stigande. I båda formlerna är det COUNTIF-funktionen med den smarta användningen av relativa och absoluta cellreferenser som gör susen.

Kort sagt, använd COUNTIF för att ta reda på hur många förekomster av numret som sorteras som finns i de föregående cellerna, inklusive nummercellen. I den översta raden där du anger formeln består intervallet av en enda cell ($B$2:B2). Men eftersom det bara låser den första referensen ($B$2), ändras den sista relativa referensen (B2) beroende på raden där formeln kopieras.

Därför utökas intervallet för rad 7 till $B$2:B7 och värdet i B7 jämförs med var och en av de tidigare cellerna. Följaktligen, för alla första förekomster, returnerar COUNTIF 1; och subtrahera 1 från slutet av formeln för att återställa det ursprungliga intervallet.

För andra förekomster returnerar COUNTIF 2. Genom att subtrahera 1 ökar du intervallet med 1 poäng och undviker på så sätt dubbletter. Om det finns 3 förekomster av samma värde, skulle COUNTIF() – 1 lägga till 2 till din sortering, och så vidare.

Lösning för att bryta Excel RANK-band

En annan form av sortera siffror i excel Det enda sättet är att lägga till två COUNTIF-funktioner:

• Den första funktionen bestämmer hur många värden som är större eller mindre än antalet som ska sorteras, beroende på om du sorterar fallande respektive stigande.
• Den andra funktionen (med det "expanderande intervallet" $B $2:B2 som i föregående exempel) får du antalet värden lika med antalet.

Till exempel, för att bara sortera siffror från högsta till lägsta, skulle du använda den här formeln:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

Som visas i skärmdumpen nedan är tiebreaket framgångsrikt löst och en unik rang tilldelas varje elev:

Klassificering i Excel baserat på flera kriterier

Det tidigare exemplet har visat två fungerande lösningar för en situation med Excel RANK tiebreaker. Det kan dock tyckas orättvist att lika många rankas olika baserat enbart på deras placering på listan.

För att förbättra din ranking kanske du vill lägga till ytterligare ett kriterium som ska beaktas vid oavgjort. Låt oss i vår exempeldatauppsättning lägga till totalpoäng i kolumn C och beräkna intervallet enligt följande:

• Klassificera först med matematisk poäng (huvudkriteriet).
• När det är oavgjort, bryt det med totalpoängen (sekundärt kriterium)

För att göra detta använder vi en vanlig RANK/RANK.EQ-formel för att hitta rankningen, och COUNTIF-funktionen för att bryta en oavgjort:

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)

Jämfört med föregående exempel är den här rangformeln mer objektiv: Timothy är rankad 2:a eftersom hans totala poäng är högre än Julias:

Hur denna formel fungerar

RANK-delen av formeln är uppenbar och COUNTIFS-funktionen gör följande:

• Det första paret criteria_range/criterion ($B$2:$B$7,$B2) räknar förekomsterna av det värde du rankar. Observera att vi fixar intervallet med absoluta referenser, men vi låser inte kriterieraden ($B2) så att formeln kontrollerar värdet i varje rad individuellt.
• Det andra criterion_range/criterion-paret ($C$2:$C$7, ">" & $C2) tar reda på hur många totalpoäng som är större än totalpoängen för det värde som rankas.

Eftersom COUNTIFS arbetar med AND-logik, det vill säga att den bara räknar celler som uppfyller alla angivna villkor, returnerar den 0 för Timothy, eftersom ingen annan elev med samma mattepoäng har högre totalpoäng. Därför ändras inte Timothys rang som returneras av RANK.EQ.

För Julia, funktionen COUNTIF returnerar 1 eftersom en elev med samma mattepoäng har högre totalpoäng, så hans rangnummer ökas med 1. Om ytterligare en elev hade samma mattepoäng och lägre totalpoäng än Timothy och Julia, kommer din rang att öka med 2 , och så vidare.

Alternativa lösningar för att sortera siffror med flera kriterier

Istället för RANK eller RANK.EQ-funktionen kan du använda COUNTIF för att kontrollera huvudkriterierna och COUNTIFS eller SUMPRODUCT för att lösa en tiebreak:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

Resultatet av dessa formler är exakt detsamma som visas ovan.

Hur man beräknar percentilrankning i Excel

I statistik är en percentil det värde under vilket en viss procentandel av värdena i en given datamängd faller. Till exempel, om 70 % av eleverna är på eller under ditt testresultat, är din percentilrankning 70.

För att få percentilrankningen i Excel använder du funktionen RANK eller RANK.EQ med ett argument som inte är nollordning för att rangordna siffrorna från lägsta till högsta och dividera sedan rangordningen med antalet siffror. Så den generiska Excel-percentilrankningsformeln är som följer:

RANK.EQ(översta_cellen, rang, 1) / ANTAL(rank)

För att beräkna elevernas percentilrankning tar formeln följande form:

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

För att resultaten ska visas korrekt, se till att du ställer in procentformatet i formelcellerna:

Kanske vill du veta: Hur man lägger till efter kategori i Excel

Hur man sorterar siffror i icke-intilliggande celler

I situationer där du behöver sortera icke-angränsande celler, ange dessa celler direkt i argumentet ref av din Excel-intervallformel i form av en referensunion, blockerar referenser med $-tecknet. Till exempel:

=RANK(B2,($B$2,$B$4,$B$6))

För att undvika fel i osorterade celler, justera RANG i funktion IFERROR, Så:

=FEL(RANK(B2,($B$2,$B$4,$B$6)),»»)

Observera att ett dubblettnummer också tilldelas ett intervall, även om cell B5 inte ingår i formeln:

Om du behöver sortera flera icke-sammanhängande celler kan formeln ovan vara för lång. I det här fallet skulle en mer elegant lösning vara att definiera ett namngivet intervall och referera till det namnet i formeln:

=FEL(RANK(B2;intervall),»»)

Hur man sorterar i Excel efter grupp

När du arbetar med poster organiserade i någon typ av datastruktur kan data tillhöra flera grupper och du kanske vill sortera siffrorna inom varje grupp individuellt. Excels RANK-funktion kan inte lösa denna utmaning, så låt oss använda en mer komplex SUMPRODUCT-formel:

Sortera efter grupp i fallande ordning:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Sortera efter grupp i stigande ordning:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

var:

• A2: A7 är grupper tilldelade till nummer.
• C2: C7 är nummer för klassificering.

I det här exemplet använder vi den första formeln för att sortera talen i varje grupp från största till minsta:

Hur denna formel fungerar

I grund och botten utvärderar formeln 2 villkor:

• Kontrollera först gruppen (A2=$A$2:$A$7). Denna del returnerar en array av TRUE och FALSE baserat på om ett områdeselement tillhör samma grupp som A2.
• För det andra, kontrollera poängen. För att sortera värdena från högsta till lägsta (fallande ordning), använd villkoret (C2 < $C$2:$C$11), som returnerar TRUE för celler större än eller lika med C2, annars FALSE.

Eftersom i Microsoft Excel-termer, TRUE = 1 och FALSE = 0, ger multiplicering av de två matriserna en matris på 1 och 0, där 1 endast returneras för rader där båda villkoren är uppfyllda. Sedan, SUMPRODUCT Den lägger till arrayelementen 1 och 0, så den returnerar 0 för det största antalet i varje grupp. Och lägg till 1 till resultatet för att börja sortera med 1.

Formeln som klassificerar tal inom grupper från minsta till största (stigande ordning) fungerar med samma logik. Skillnaden är att SUMPRODUKT returnerar 0 för det minsta talet i en viss grupp, eftersom inget tal i den gruppen uppfyller det andra villkoret (C2 > $C$2:$C$2). Återigen, ersätt nollrankningen med den första rangordningen genom att lägga till 7 till formelresultatet.

Istället för SUMPRODUCTdu kan använda funktionen SUMMA för att lägga till elementen i matrisen. Men detta skulle kräva att man använder en matrisformel, slutförd via Ctrl + Shift + Enter. Till exempel:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Hur man klassificerar positiva och negativa tal separat

Om din lista med siffror innehåller både positiva och negativa värden kommer RANK-funktionen i Excel att rangordna dem alla på nolltid. Men vad händer om du vill ha positiva och negativa siffror klassificerade separat? Med siffror i cellerna A2 till A10, använd en av följande formler för att få en individuell rangordning för de positiva och negativa värdena:

Sortera positiva tal fallande:

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Sortera de positiva talen i stigande ordning:

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

Sortera fallande negativa tal:

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

Sortera de negativa talen i stigande ordning:

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

Resultaten kommer att se ut ungefär så här:

Hur dessa formler fungerar

Till att börja med, låt oss titta på formeln som sorterar positiva tal i fallande ordning:

• I det logiska testet av funktionen IF, kontrollerar om siffran är större än noll.
• Om talet är större än 0, returnerar COUNTIF-funktionen antalet värden högre än talet som sorteras.

I det här exemplet innehåller A2 det näst högsta positiva talet, för vilket COUNTIF returnerar 1, vilket betyder att det bara finns ett tal större än det. För att börja vår sortering med 1, inte 0, lägger vi till 1 till formelresultatet, så det returnerar en sorts 2 för A2.

Om talet är större än 0, returnerar formeln en tom sträng (""). Formeln som sorterar positiva tal i stigande ordning fungerar lite annorlunda: Om talet är större än 0, kommer den första COUNTIF får det totala antalet positiva tal i datamängden och den andra COUNTIF Ta reda på hur många värden som är högre än den siffran.

Sedan subtraherar du det sista från det första och får önskat intervall. I det här exemplet finns det 5 positiva värden, varav 1 är större än A2. Subtrahera sedan 1 från 5 och erhåll på så sätt rankningen 4 för A2. Formler för att klassificera negativa tal är baserade på liknande logik.

Obs: Alla ovanstående formler ignorerar nollvärden eftersom 0 varken tillhör uppsättningen positiva tal eller uppsättningen negativa tal. För att inkludera nollor i din sortering, ersätt >0 och <0 med >=0 respektive <=0, där formellogiken kräver det. Till exempel, för att sortera positiva tal och nollor från störst till minst, använd den här formeln:

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Hur man sorterar data i Excel och ignorerar nollvärden

Som du redan vet, a RANGE formel är Excel hanterar alla tal: positiva, negativa och nollor. Men i vissa fall vill vi bara rangordna celler med data som ignorerar 0-värden. Du kan hitta några möjliga lösningar för denna uppgift, men Excel RANK IF-formeln är den mest universella:

Fallande intervallnummer som ignorerar noll:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

Stigande intervallnummer som ignorerar noll:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

var B2:B10 är intervallet för nummer som ska sorteras. Det bästa med den här formeln är att den fungerar utmärkt för både positiva och negativa tal, vilket lämnar nollvärden utanför klassificeringen:

Hur denna formel fungerar

Vid första anblicken kan formeln verka lite komplicerad. Om man tar en närmare titt är logiken väldigt enkel. Så här rangordnar Excels RANK IF-formel siffror från högsta till lägsta och ignorerar nollor:

• Den första OM kontrollerar om talet är 0 och om det är det returnerar en tom sträng:

OM ($B2 = 0, «»,...)

• Om siffran inte är noll, kontrollerar den andra IF för att se om den är större än 0, och om den är det, beräknar en normal RANK/RANK.EQ-funktion rankningen:

OM ($B2 > 0, RANK ($B2, $B$2: $B$10),...)

• Om siffran är mindre än 0, justera sorteringen med noll. I det här exemplet finns det 4 positiva tal och 2 nollor. Så för det största negativa talet i B10 skulle en Range-funktion i Excel returnera 7. Men vi utelämnade nollor och behöver därför justera intervallet med 2 poäng. För att göra detta subtraherar vi antalet nollor från intervallet:

RANGE($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

Ja, så enkelt är det! Formeln för att sortera siffror från minsta till största ignorera nollor fungerar på liknande sätt, och kan vara en bra mental övning för att härleda dess logik.

Hur man beräknar intervall i Excel efter absolut värde

När man har att göra med en lista med positiva och negativa värden kan det vara nödvändigt att sortera siffrorna efter deras absoluta värden och ignorera tecknet. Uppgiften kan utföras med en av följande formler, i hjärtat av dessa är ABS-funktionen som returnerar absolutvärde av ett nummer:

Nedåtgående ABS-intervall:

=SUMMAPRODUKT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUKT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

Uppåtgående ABS-intervall:

=SUMMAPRODUKT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMMAPRODUKT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

Som ett resultat klassificeras negativa tal som om de vore positiva tal:

Hur man får N större eller mindre värden

Om du vill få ett riktigt N-tal av de största eller minsta värdena istället för deras rankning, använd funktionen LARGE respektive SMALL. Till exempel kan vi få de tre bästa studentpoängen med denna formel:

=LARGE($B$2:$B$7, $D3)

var B2: B7 är poänglistan och D3 är det önskade intervallet. Dessutom kan du hämta elevnamn med hjälp av formeln INDEXMATCH (så länge det inte finns några dubbletter i topp 3):

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

På samma sätt kan du använda SMALL-funktionen för att extrahera de tre nedersta värdena:

=SMALL($B$2:$B$7, $D3)

Ta en titt på: Hur man kombinerar Excel med Word: Importera data från Excel till Word

Pensamientos finales

Så här rangordnar du med Rank-funktionen i Excel. Tack för att du läser och vi hoppas att vi ses på vår blogg nästa vecka! Det finns ett stort antal handledningar tillgängliga för att klargöra tvivel relaterade till datorområdet. För att återgå till ämnet, det finns många alternativa sätt att beräkna intervallet i Excel, var och en med sina egna särdrag. Du bestämmer hur du vill arbeta.