Gå til hovedindhold

Hvordan opretter man dynamisk top 10 eller n liste i Excel?

En topliste bruges til at rangere virksomhederne eller personerne ud fra værdier. Hvis du antager, at du har en liste over elevernes karakterer i en klasse, vil du nu oprette en dynamisk top 10-liste over eleverne som vist nedenfor. I denne artikel vil jeg introducere nogle formler til at oprette en top 10 eller n liste i et Excel-regneark.


Opret en dynamisk top 10 liste i Excel

I Excel 2019 og tidligere versioner skal du anvende følgende formler for at udtrække top 10-listen eller top 10-listen med kriterier:

Formler til at skabe en dynamisk top 10 liste

1. Først skal du udtrække de 10 bedste værdier fra dataområdet, brug venligst nedenstående formel i en tom celle - G2, og træk fyldhåndtaget ned for at få de 10 bedste værdier, se skærmbillede:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Bemærk: I denne formel B2: B20 er den dataliste, hvor du ønsker at få top 10 værdier, og B2 er den første celle i datalisten.

2. Fortsæt derefter med at anvende følgende formel i celle - F2, og tryk på Ctrl + Skift + Enter nøgler sammen for at få det første resultat, se skærmbillede:

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
Bemærk: I ovenstående formel A2: A20 er den dataliste, du vil hente navnene på top 10 værdier fra, B2: B20 er datalisten, der indeholder alle værdierne, G2 er cellen med den største værdi udtrukket fra kolonnen B , B1 er overskriftscellen på værdilisten.

3. Når du har fået det første resultat, skal du vælge formelcellen og trække udfyldningshåndtaget ned for at få andre navne på én gang, se skærmbillede:


Formler til at skabe en dynamisk top 10 liste med kriterier

Nogle gange skal du muligvis få top 10-listen baseret på kriterier. For eksempel for at få de 10 bedste navne og scores i klasse 1 som vist nedenfor.

1. For at få top 10-listen skal du også udtrække top 10-scorerne med denne formel:

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. Og tryk derefter på Ctrl + Skift + Enter taster sammen for at få det første resultat, og træk derefter denne formel ned for at vise andre værdier, se skærmbillede:

3. Derefter skal du kopiere og indsætte følgende formel i celle - I2, og trykke på Ctrl + Skift + Enter taster samtidigt for at udtrække det første resultat, og træk derefter denne formel for at udfylde den til andre celler, og de øverste 10 navne vises som vist nedenfor:

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

Opret en dynamisk top 10-liste i Office 365

Ovenstående formler kan være svære for os at forstå, hvis du bruger Office 365, med dets INDEX, SORT og SEQUENCE funktioner, kan du oprette simple formler til at udføre denne opgave.

Formel til at skabe en dynamisk top 10 liste

For at få listen over 10 data, brug venligst nedenstående formel:

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

Og så skal du bare trykke på Indtast tast, vises alle data på top 10-listen på én gang, se skærmbillede:

Tips:

SORTERING funktion:

=SORT(matrix, [sorteringsindeks], [sorteringsrækkefølge], [efter_kolonne])

  • matrix: Området af celler, du vil sortere;
  • [sort_indeks]: Det kolonne- eller rækkenummer, der skal sorteres til arrayet efter. For at sortere efter den anden kolonne i dataområdet vil sorteringsindekset f.eks. være 2;
  • [sorteringsrækkefølge]: Tallet 1 (eller udeladt) angiver at sortere i stigende rækkefølge; tallet -1, sorter i faldende rækkefølge;
  • [af_kol]: Sorteringsretningen. SAND, sorter efter kolonner, FALSK eller udeladt, sorter efter rækker.

I formlen bruger vi SORT-funktionen som denne:

SORT(A2:B20,2;1;-XNUMX): Betyder at sortere celleområdet A2:A20 i den anden kolonne i faldende rækkefølge.


SEQUENCE funktion:

=SEKVENS(rækker, [kolonner], [start], [trin])

  • rækker: Antallet af rækker, der skal returneres,
  • [kolonner]: Antallet af kolonner, der skal returneres. Hvis den udelades, returnerer den en enkelt kolonne.
  • [Start]: Det første tal i rækkefølgen. Hvis den udelades, starter den 1.
  • [trin]: Forøgelsen mellem hvert tal. Hvis ekskluderet, vil hver stigning være 1.

I denne formel bruger vi SEQUENCE(10) til at oprette en liste fra 1 til 10.

Til sidst skal du sætte både SORT og SEQUENCE-funktionerne i INDEX-funktionen: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), vil dette returnere de første 10 poster fra dataområdet og returnere kolonne 1 og 2.


Formel til at skabe en dynamisk top 10 liste med kriterier

For at få vist top 10-listen med kriterier, skal du indlejre FILTER-funktionen i SORT-funktionen som denne:

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

Og bare tryk Indtast tast, vises alle data på top 10-listen baseret på de givne kriterier på én gang, se skærmbillede:

Tips:

FILTER funktion:

=FILTER(matrix, inkludere, [hvis_tom])

  • matrix: Området af celler, der skal filtreres.
  • omfatter: Betingelsen du bruger til at filtrere arrayet for at få et array med SAND eller FALSK resultat, så SAND-værdierne bevares i filteret.
  • [hvis_tom]: Værdien, der skal vises, hvis der ikke returneres matchende resultater.

I denne formel: =FILTER(A2:C25,B2:B25=F2) bruges til at filtrere i området A2:C25, hvor værdierne fra B2:B25 er lig med den specifikke celle F2.

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier    Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar    Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler    Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse


Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations