Rangeringsfunktion i Excel med formeleksempler

Selvstudiet forklarer detaljerne i funktion Ranger i Excel og viser, hvordan man sorterer i Excel baseret på forskellige kriterier, sorterer data efter gruppe, beregner percentilrangering og mere. Når du har brug for det bestemme den relative placering af et tal i en liste med tal, er den nemmeste måde at arrangere tallene i stigende eller faldende rækkefølge. Hvis sortering af en eller anden grund ikke er mulig, er en rækkeviddeformel i Excel det perfekte værktøj til at få arbejdet gjort.

Excel RANGE funktion

La RANK funktion i Excel returnerer rækkefølgen (eller rangeringen) af en numerisk værdi sammenlignet med andre værdier på samme liste. Med andre ord fortæller den dig, hvilken værdi der er den højeste, den næsthøjeste, blandt andre.

Måske kan du være interesseret: Excel VÆLG Funktion. Formler og eksempler

I en ordnet liste vil rangen af ​​et bestemt nummer være positionen. Funktionen Range i Excel kan bestemme området, der starter med den største værdi (som om den er sorteret i faldende rækkefølge) eller den mindste værdi (som om den er sorteret i stigende rækkefølge). De RANK funktion syntaks af Excel er følgende:

RANK (nummer, ref, [rækkefølge])

hvor:

• Nummer (påkrævet): den værdi, hvis placering du gerne vil finde.
• Ref (påkrævet): en liste over numeriske værdier at sortere med. Det kan leveres som en række numre eller som en reference til listen over numre.
• Ordre (valgfrit): et tal, der angiver, hvordan værdierne skal sorteres:
  • Hvis 0 eller udelades, sorteres værdierne i faldende rækkefølge, det vil sige fra højeste til laveste.
  • Hvis det er 1 eller en anden ikke-nul værdi, sorteres værdierne i stigende rækkefølge, det vil sige fra laveste til højeste.

RANK.EQ funktion i Excel

RANK.EQ Det er en forbedret version af RANK-funktionen, introduceret i Excel 2010. Det har samme syntaks som RANK og fungerer med samme logik: Hvis flere værdier rangeres ligeligt, tildeles den højeste rang til alle disse værdier. (EQ betyder "lige").

RANK.EQ (nummer, ref, [rækkefølge])

I Excel 2007 og lavere bør du altid bruge RANK-funktionen. I Excel 2010, Excel 2013 og Excel 2016 kan du vælge RANK eller RANK.EQ. Det vil dog være tilrådeligt at bruge RANK.EQ, fordi RANK kan suspenderes til enhver tid.

Excel RANGE.AVG-funktion

RANK.AVG er en anden funktion til find rækkevidde i excel som kun er tilgængelig i Excel 2010, Excel 2013, Excel 2016 og senere versioner. Det har samme syntaks som de to andre funktioner:

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

Forskellen er, at hvis mere end et tal har samme rang, returneres den gennemsnitlige rang (AVG betyder "gennemsnit").

4 ting du bør vide om RANK i Excel

Her er nogle ting, du skal huske på om Rank-funktionen i Excel:

• Enhver rækkeformel i Excel virker kun for numeriske værdier: positive og negative tal, nuller, dato- og tidsværdier. Ikke-numeriske værdier i ref-argumentet ignoreres.
• Alle RANGE-funktioner returnerer det samme interval for duplikerede værdier og springer yderligere sortering over, som vist i følgende eksempel.
• I Excel 2010 og senere er RANK-funktionen blevet erstattet med RANK.EQ og RANK.AVG. For bagudkompatibilitet fungerer RANK stadig i alle versioner af Excel, men er muligvis ikke tilgængelig i fremtiden.
• Hvis nummeret ikke findes i referencen, vil enhver Excel-områdefunktion returnere #N/A-fejlen.

Grundlæggende Excel-områdeformel (højeste til laveste)

For at lære mere om rangering med Rank-funktionen i Excel, tag et kig på dette skærmbillede:

Alle tre formler sorterer tallene i kolonne B i faldende rækkefølge (rækkefølgeargumentet er udeladt):

I alle versioner af 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)

Forskellen ligger i, hvordan disse formler behandler duplikerede værdier. Som du kan se, vises den samme score to gange, i cellerne B5 og B6, hvilket påvirker den efterfølgende sortering:

• Formlerne RANK og RANK.EQ giver en rang på 2 til begge duplikatresultater. Den næsthøjeste score (Daniela) er på fjerdepladsen. Ingen får en rang på 3.
• RANK.AVG-formlen tildeler en anden rangordning til hvert duplikat bag kulisserne (2 og 3 i dette eksempel) og returnerer gennemsnittet af disse rangeringer (2.5). Igen er den tredje rang ikke tildelt nogen.

Sådan bruges RANK i Excel – formeleksempler

Vejen til ekspertise, siger de, er brolagt med praksis. Så for bedre at lære at bruge RANK-funktionen i Excel, alene eller i kombination med andre funktioner, lad os se på løsninger til nogle virkelige opgaver.

Sådan sorterer du i Excel fra mindste til største

Som vist i eksemplet ovenfor, for at sortere tal fra største til mindste, skal du bruge en af ​​Excels områdeformler med ordensargumentet sat til 0 eller udeladt (standard).

For at få et tal sorteret i forhold til andre tal sorteret i stigende rækkefølge, skal du sætte 1 eller en anden værdi, der ikke er nul, i det valgfri tredje argument. For at bedømme elevernes 100 meter sprinttider kan du f.eks. bruge en af ​​følgende formler:

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

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

Vær opmærksom på, at vi låser rækkevidden i argumentet ref ved at bruge absolutte cellereferencer, så det ikke ændrer sig, når vi kopierer formlen ind i kolonnen.

Som følge heraf får den laveste værdi (hurtigste tid) førstepladsen, og den største værdi (langsomste tid) får den laveste rang på 6. Lige gange (B2 og B7) får samme rang.

Sådan klassificeres data unikt i Excel

Som nævnt ovenfor returnerer alle Excel-områdefunktioner det samme område for elementer af samme værdi. Hvis det ikke er det, du ønsker, skal du bruge en af ​​følgende formler til at løse uafgjorte situationer og give hvert tal en unik rang.

Unik placering fra højeste til laveste

Brug denne formel for kun at sortere elevernes matematikresultater i faldende rækkefølge:

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

Unik placering fra laveste til højeste

For at sortere dine 100 meter løbsresultater i stigende rækkefølge uden dubletter, brug denne formel:

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

Hvordan disse formler fungerer

Som du har bemærket, Den eneste forskel mellem de to formler er rækkefølge-argumentet for RANK.EQ-funktionen: udeladt for sortering af faldende værdier, 1 for sortering af stigende. I begge formler er det COUNTIF-funktionen med den smarte brug af relative og absolutte cellereferencer, der gør tricket.

Kort sagt, brug TÆL.HVIS for at finde ud af, hvor mange forekomster af nummeret, der sorteres, er i de foregående celler, inklusive talcellen. I den øverste række, hvor du indtaster formlen, består området af en enkelt celle ($B$2:B2). Men fordi den kun låser den første reference ($B$2), ændres den sidste relative reference (B2) afhængigt af rækken, hvor formlen er kopieret.

Derfor, for række 7, udvides området til $B$2:B7, og værdien i B7 sammenlignes med hver af de foregående celler. Derfor returnerer COUNTIF for alle første forekomster 1; og træk 1 fra slutningen af ​​formlen for at gendanne det oprindelige område.

For anden forekomst returnerer COUNTIF 2. Ved at trække 1 fra, øger du området med 1 point og undgår dermed dubletter. Hvis der er 3 forekomster af samme værdi, vil COUNTIF() – 1 tilføje 2 til din sortering, og så videre.

Løsning for at bryde Excel RANK-bånd

En anden form for sortere tal i excel Den eneste måde er ved at tilføje to COUNTIF-funktioner:

• Den første funktion bestemmer, hvor mange værdier der er større eller mindre end det antal, der skal sorteres, afhængigt af om du sorterer henholdsvis faldende eller stigende.
• Den anden funktion (med det "udvidende område" $B $2:B2 som i det foregående eksempel) får du antallet af værdier svarende til tallet.

Hvis du f.eks. kun vil sortere tal fra højeste til laveste, skal du bruge denne formel:

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

Som vist på skærmbilledet nedenfor er tiebreaket løst, og hver elev tildeles en unik rang:

Klassificering i Excel baseret på flere kriterier

Det foregående eksempel har vist to fungerende løsninger til en situation med Excel RANK tiebreaker. Det kan dog virke uretfærdigt, at lige mange rangeres forskelligt alene baseret på deres placering på listen.

For at forbedre din placering kan du tilføje et kriterium mere, der skal tages i betragtning i tilfælde af uafgjort. Lad os i vores eksempeldatasæt tilføje samlede scores i kolonne C og beregne intervallet som følger:

• Først klassificeres med matematisk score (hovedkriterium).
• Når der er uafgjort, brydes det med den samlede score (sekundært kriterium)

For at gøre dette bruger vi en almindelig RANK/RANK.EQ-formel til at finde rangeringen og COUNTIF-funktionen til at bryde uafgjort:

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

Sammenlignet med det foregående eksempel er denne rangformel mere objektiv: Timothy er placeret på en andenplads, fordi hans samlede score er højere end Julias:

Hvordan denne formel virker

RANK-delen af ​​formlen er indlysende, og COUNTIFS-funktionen gør følgende:

• Det første par criteria_range/criterion ($B$2:$B$7,$B2) tæller forekomsterne af den værdi, du rangerer. Bemærk, at vi fikser området med absolutte referencer, men vi låser ikke kriterierækken ($B2), så formlen kontrollerer værdien i hver række individuelt.
• Det andet criterion_range/criterion-par ($C$2:$C$7, ">" & $C2) finder ud af, hvor mange samlede scorer der er større end den samlede score for den værdi, der rangeres.

Fordi COUNTIFS arbejder med OG-logik, det vil sige, at den kun tæller celler, der opfylder alle de angivne betingelser, returnerer den 0 for Timothy, da ingen anden elev med samme matematikscore har en højere totalscore. Derfor ændres Timothys rang, returneret af RANK.EQ, ikke.

For Julia, funktionen TÆL.HVIS returnerer 1, fordi en elev med samme matematikscore har en højere samlet score, så hans rangnummer øges med 1. Hvis en elev mere havde samme matematikscore og en lavere totalscore end Timothy og Julia, vil din rangering stige med 2 , og så videre.

Alternative løsninger til at sortere tal med flere kriterier

I stedet for funktionen RANK eller RANK.EQ kan du bruge COUNTIF til at kontrollere hovedkriterierne og COUNTIFS eller SUMPRODUCT til at løse 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 af disse formler er nøjagtigt det samme som vist ovenfor.

Sådan beregnes percentilrangering i Excel

I statistik er en percentil den værdi, under hvilken en vis procentdel af værdier i et givet datasæt falder. For eksempel, hvis 70 % af eleverne er på eller under dit testresultat, er din percentilrangering 70.

For at få percentilrangeringen i Excel skal du bruge funktionen RANK eller RANK.EQ med et argument, der ikke er nul, til at rangere tallene fra laveste til højeste og derefter dividere rangeringen med antallet af tal. Så den generiske Excel-percentilrangeringsformel er som følger:

RANK.EQ(øverste_celle, rang, 1) / ANTAL(rang)

For at beregne elevernes percentilrangering tager formlen følgende form:

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

For at resultaterne skal vises korrekt, skal du sørge for at indstille procentformatet i formelcellerne:

Måske vil du vide: Sådan tilføjes efter kategori i Excel

Sådan sorteres tal i ikke-tilstødende celler

I situationer, hvor du skal sortere ikke-sammenhængende celler, skal du angive disse celler direkte i argumentet ref af din Excel-områdeformel i form af en referenceforening, der blokerer referencer med $-tegnet. For eksempel:

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

Juster for at undgå fejl i usorterede celler RANG i funktion IFERROR, Så:

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

Bemærk, at et dubletnummer også tildeles et område, selvom celle B5 ikke er inkluderet i formlen:

Hvis du har brug for at sortere flere ikke-sammenhængende celler, kan ovenstående formel være for lang. I dette tilfælde ville en mere elegant løsning være at definere et navngivet område og henvise til dette navn i formlen:

=IFEJL(RANK(B2;område),»»)

Sådan sorterer du i Excel efter gruppe

Når du arbejder med poster organiseret i en eller anden type datastruktur, kan dataene tilhøre flere grupper, og du vil måske sortere tallene inden for hver gruppe individuelt. Excels RANK-funktion kan ikke løse denne udfordring, så lad os bruge en mere kompleks SUMPRODUCT-formel:

Sorter efter gruppe i faldende rækkefølge:

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

Sorter efter gruppe i stigende rækkefølge:

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

hvor:

• A2: A7 er grupper tildelt numre.
• C2: C7 er tal til klassificering.

I dette eksempel bruger vi den første formel til at sortere tallene i hver gruppe fra største til mindste:

Hvordan denne formel virker

Grundlæggende evaluerer formlen 2 forhold:

• Tjek først gruppen (A2=$A$2:$A$7). Denne del returnerer en matrix af SAND og FALSK baseret på, om et områdeelement tilhører samme gruppe som A2.
• For det andet, tjek scoren. For at sortere værdier fra højeste til laveste (faldende rækkefølge), skal du bruge betingelsen (C2 < $C$2:$C$11), som returnerer SAND for celler større end eller lig med C2, ellers FALSK.

Da i Microsoft Excel-termer, SAND = 1 og FALSK = 0, giver multiplikation af de to matricer en matrix på 1 og 0, hvor 1 kun returneres for rækker, hvor begge betingelser er opfyldt. Så, SUMPRODUCT Den tilføjer array-elementerne 1 og 0, så den returnerer 0 for det største tal i hver gruppe. Og føj 1 til resultatet for at begynde at sortere med 1.

Formlen, der klassificerer tal inden for grupper fra mindste til største (stigende rækkefølge), fungerer med samme logik. Forskellen er det SUMPRODUKT returnerer 0 for det mindste tal i en bestemt gruppe, da intet tal i den gruppe opfylder den 2. betingelse (C2 > $C$2:$C$7). Igen skal du erstatte nulrangen med den første rangering ved at tilføje 1 til formelresultatet.

I stedet for SUMPRODUCTdu kan brug funktionen SUM at tilføje elementerne i matrixen. Men dette ville kræve brug af en matrixformel, afsluttet via Ctrl + Shift + Enter. For eksempel:

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

Sådan klassificeres positive og negative tal separat

Hvis din liste med tal indeholder både positive og negative værdier, vil RANK-funktionen i Excel rangere dem alle på ingen tid. Men hvad nu hvis du ville have positive og negative tal klassificeret separat? Med tal i cellerne A2 til A10 skal du bruge en af ​​følgende formler til at få en individuel rangering for de positive og negative værdier:

Sorter positive tal faldende:

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

Sorter de positive tal i stigende rækkefølge:

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

Sorter faldende negative tal:

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

Sorter de negative tal i stigende rækkefølge:

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

Resultaterne vil se noget lignende ud:

Hvordan disse formler fungerer

Lad os starte med at se på formlen, der sorterer positive tal i faldende rækkefølge:

• I den logiske test af funktionen IF, kontrollerer, om tallet er større end nul.
• Hvis tallet er større end 0, returnerer COUNTIF-funktionen antallet af værdier, der er højere end det tal, der sorteres.

I dette eksempel indeholder A2 det næsthøjeste positive tal, for hvilket COUNTIF returnerer 1, hvilket betyder, at der kun er ét tal større end det. For at starte vores sortering med 1, ikke 0, tilføjer vi 1 til formelresultatet, så det returnerer en slags 2 for A2.

Hvis tallet er større end 0, returnerer formlen en tom streng (""). Formlen, der sorterer positive tal i stigende rækkefølge, fungerer lidt anderledes: Hvis tallet er større end 0, vil den første TÆL.HVIS får det samlede antal positive tal i datasættet og det andet TÆL.HVIS Find ud af, hvor mange værdier der er højere end det tal.

Så trækker du det sidste fra det første og får det ønskede interval. I dette eksempel er der 5 positive værdier, hvoraf 1 er større end A2. Træk derefter 1 fra 5, og opnå en rang på 4 for A2. Formler til klassificering af negative tal er baseret på lignende logik.

Bemærk: Alle ovenstående formler ignorerer nulværdier, fordi 0 hverken hører til sættet af positive tal eller til sættet af negative tal. For at inkludere nuller i din sortering skal du erstatte >0 og <0 med henholdsvis >=0 og <=0, hvor formellogikken kræver det. For at sortere positive tal og nuller fra størst til mindst, skal du f.eks. bruge denne formel:

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

Sådan sorterer du data i Excel og ignorerer nulværdier

Som du allerede ved, a RANGE formel er Excel håndterer alle tal: positive, negative og nuller. Men i nogle tilfælde ønsker vi kun at rangere celler med data, der ignorerer 0-værdier. Du kan finde et par mulige løsninger til denne opgave, men Excel RANK IF-formlen er den mest universelle:

Faldende områdetal ignorerer nul:

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

Stigende områdetal ignorerer nul:

=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)))

hvor B2:B10 er rækken af ​​tal, der skal sorteres. Det bedste ved denne formel er, at den fungerer vidunderligt for både positive og negative tal, og efterlader nul værdier ude af klassifikationen:

Hvordan denne formel virker

Ved første øjekast kan formlen virke lidt kompliceret. Ser man nærmere på, er logikken meget enkel. Sådan rangerer Excels RANK IF-formel tal fra højeste til laveste og ignorerer nuller:

• Den første HVIS kontrollerer, om tallet er 0, og hvis det er, returnerer en tom streng:

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

• Hvis tallet ikke er nul, kontrollerer det andet HVIS, om det er større end 0, og hvis det er, beregner en normal RANK/RANK.EQ-funktion rangeringen:

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

• Hvis tallet er mindre end 0, skal du justere sorteringen med nul. I dette eksempel er der 4 positive tal og 2 nuller. Så for det største negative tal i B10 ville en Range-funktion i Excel returnere 7. Men vi har udeladt nuller og skal derfor justere området med 2 point. For at gøre dette trækker vi antallet af nuller fra området:

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

Ja, det er så nemt! Formlen til at sortere tal fra mindste til største ignorere nuller fungerer på lignende måde, og kunne være en god mental øvelse til at udlede dens logik.

Sådan beregnes rækkevidde i Excel efter absolut værdi

Når man har at gøre med en liste over positive og negative værdier, kan det være nødvendigt at sortere tallene efter deres absolutte værdier, idet man ignorerer tegnet. Opgaven kan udføres med en af ​​følgende formler, hvori hjertet er ABS-funktionen, der returnerer absolut værdi af et nummer:

Nedadgående ABS-område:

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

Opadgående ABS-område:

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

Som et resultat klassificeres negative tal, som om de var positive tal:

Sådan får du N større eller mindre værdier

Hvis du ønsker at få et reelt N-tal af de største eller mindste værdier i stedet for deres rangering, skal du bruge henholdsvis LARGE eller SMALL-funktionen. For eksempel kan vi få de 3 bedste elevresultater med denne formel:

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

hvor B2: B7 er nodelisten og D3 er det ønskede område. Derudover kan du hente elevnavne ved hjælp af formlen INDEX MATCH (så længe der ikke er duplikatresultater i top 3):

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

På samme måde kan du bruge SMALL-funktionen til at udtrække de nederste 3 værdier:

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

Tag et kig på: Sådan kombineres Excel med Word: Importer data fra Excel til Word

Pensamientos finales

Sådan rangerer du ved hjælp af Rank-funktionen i Excel. Tak fordi du læste med, og vi håber at se dig på vores blog i næste uge! Der er et stort antal tutorials til rådighed for at afklare tvivl relateret til computerområdet. For at vende tilbage til emnet, er der mange alternative måder at beregne rækkevidden på i Excel, hver med sine egne særegenheder. Du bestemmer selv, hvordan du vil arbejde.