Hoppa till huvudinnehåll

Hur skapar man en dynamisk topp 10 eller n-lista i Excel?

En topplista används för att rangordna företagen eller individerna utifrån värderingar. Om du antar att du har en lista över elevernas betyg i en klass, nu vill du skapa en dynamisk topp 10-lista över eleverna enligt skärmbilden nedan. I den här artikeln kommer jag att introducera några formler för att skapa en topp 10 eller n lista i ett Excel-kalkylblad.


Skapa en dynamisk topp 10-lista i Excel

I Excel 2019 och tidigare versioner, för att extrahera topp 10-listan eller topp 10-listan med kriterier, bör du använda följande formler:

Formler för att skapa en dynamisk topp 10-lista

1. Först bör du extrahera de 10 bästa värdena från dataintervallet, använd formeln nedan i en tom cell - G2, och dra fyllningshandtaget nedåt för att få de 10 bästa värdena, se skärmdump:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Anmärkningar: I denna formel, B2: B20 är datalistan där du vill få de 10 bästa värdena, och B2 är den första cellen i datalistan.

2. Fortsätt sedan att tillämpa följande formel i cell - F2 och tryck Ctrl + Skift + Enter knappar tillsammans för att få det första resultatet, se skärmdump:

=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)))
Anmärkningar: I ovanstående formel, A2: A20 är datalistan som du vill hämta namnen på topp 10 värden från, B2: B20 är datalistan som innehåller alla värden, G2 är cellen med det största värdet som extraherats från kolumnen B och B1 är rubrikcellen i värdelistan.

3. Efter att ha fått det första resultatet, välj formelcellen och dra fyllningshandtaget nedåt för att få andra namn på en gång, se skärmdump:


Formler för att skapa en dynamisk topp 10-lista med kriterier

Ibland kan du behöva få topp 10-listan baserat på kriterier. Till exempel för att få de 10 bästa namnen och poängen för klass 1 enligt skärmbilden nedan.

1. För att få topp 10-listan bör du också behöva extrahera de 10 bästa poängen med denna formel:

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

2. Och tryck sedan på Ctrl + Skift + Enter tangenterna tillsammans för att få det första resultatet, dra sedan den här formeln nedåt för att visa andra värden, se skärmdump:

3. Kopiera sedan och klistra in följande formel i cell - I2 och tryck Ctrl + Skift + Enter tangenterna samtidigt för att extrahera det första resultatet, och dra sedan den här formeln för att fylla den till andra celler, och de 10 bästa namnen visas som nedanstående skärmdump:

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

Skapa en dynamisk topp 10-lista i Office 365

Ovanstående formler kan vara svåra för oss att förstå, om du använder Office 365, med dess funktioner INDEX, SORT och SEKVENS, kan du skapa enkla formler för att slutföra denna uppgift.

Formel för att skapa en dynamisk topp 10-lista

För att få 10-listan med data, använd följande formel:

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

Och sedan är det bara att trycka ange tangent, alla data i topp 10-listan visas på en gång, se skärmdump:

tips:

SORTERINGSfunktion:

=SORT(matris, [sorteringsindex], [sorteringsordning], [efter_kolum])

  • array: Omfånget av celler som du vill sortera;
  • [sort_index]: Kolumn- eller radnumret att sortera till matrisen efter. Till exempel, för att sortera efter den andra kolumnen i dataintervallet, skulle sorteringsindexet vara 2;
  • [sorteringsordning]: Siffran 1 (eller utelämnad) anger att sortera i stigande ordning; siffran -1, sortera i fallande ordning;
  • [by_col]: Sorteringsriktningen. TRUE, sortera efter kolumner, FALSE eller utelämnad, sortera efter rader.

I formeln använder vi SORT-funktionen så här:

SORT(A2:B20,2;1;-XNUMX): Innebär att sortera cellområdet A2:A20 i den andra kolumnen i fallande ordning.


SEQUENCE funktion:

=SEKVENS(rader, [kolumner], [start], [steg])

  • rader: Antalet rader som ska returneras,
  • [kolumner]: Antalet kolumner som ska returneras. Om den utelämnas kommer den att returnera en enda kolumn.
  • [Start]: Den första siffran i sekvensen. Om den utelämnas börjar den vid 1.
  • [steg]: Ökningen mellan varje nummer. Om det utesluts blir varje ökning 1.

I den här formeln använder vi SEKVENS(10) för att skapa en lista från 1 till 10.

Lägg till sist både SORT och SEQUENCE-funktionerna i INDEX-funktionen: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), kommer detta att returnera de första 10 posterna från dataområdet och returnera kolumnerna 1 och 2.


Formel för att skapa en dynamisk topp 10-lista med kriterier

För att visa topp 10-listan med kriterier, bör du bädda in FILTER-funktionen i SORT-funktionen så här:

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

Och tryck bara ange tangent, alla data i topp 10-listan baserat på de givna kriterierna visas på en gång, se skärmdump:

tips:

FILTER funktion:

=FILTER(matris, inkludera, [om_tom])

  • array: Omfånget av celler som ska filtreras.
  • innefattar: Villkoret du använder för att filtrera arrayen för att få en array med TRUE eller FALSE resultat, så att TRUE-värdena behålls i filtret.
  • [om_tom]: Värdet som ska visas om inga matchande resultat returneras.

I denna formel: =FILTER(A2:C25,B2:B25=F2) används för att filtrera i området A2:C25, där värdena från B2:B25 är lika med den specifika cellen F2.

Bästa kontorsproduktivitetsverktyg

Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50 + Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

kte-flik 201905


Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje 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