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:
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:
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:
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:
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:
Og så skal du bare trykke på Indtast tast, vises alle data på top 10-listen på én gang, se skærmbillede:
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:
Og bare tryk Indtast tast, vises alle data på top 10-listen baseret på de givne kriterier på én gang, se skærmbillede:
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
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...
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!