Note: The other languages of the website are Google-translated. Back to English

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.


De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2021 och 365. Stöder alla språk. Enkel implementering i ditt företag eller organisation. Fullständiga funktioner 30 dagars gratis provperiod. 60 dagars pengarna tillbaka-garanti.
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!
officetab botten
Sortera kommentarer efter
Kommentarer (0)
Inga betyg än. Bli först med att betygsätta!
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser

Följ oss

Copyright © 2009 - www.extendoffice.com. | Alla rättigheter förbehållna. Drivs av ExtendOffice. | | Sitemap
Microsoft och Office-logotypen är varumärken eller registrerade varumärken som tillhör Microsoft Corporation i USA och / eller andra länder.
Skyddad av Sectigo SSL