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:
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:
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:
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:
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:
Och sedan är det bara att trycka ange tangent, alla data i topp 10-listan visas på en gång, se skärmdump:
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:
Och tryck bara ange tangent, alla data i topp 10-listan baserat på de givna kriterierna visas på en gång, se skärmdump:
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
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...
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!
