Hoe maak je een dynamische top 10 of n-lijst in Excel?
In veel projecten en bedrijfsprocessen is het vaak noodzakelijk om individuen, organisaties, producten of andere entiteiten te rangschikken op basis van hun prestaties of numerieke waarden. Een 'toplijst' dient om de hoogst scorende items te benadrukken, zoals de beste studenten op basis van cijfers, de beste verkopers, of afdelingen met de meeste omzet. Bijvoorbeeld, je kunt een tabel hebben met studentencijfers en deze willen gebruiken om dynamisch de top 10 scoorders te extraheren voor prijzen, analyse of bewaking van onderwijsresultaten, zoals geïllustreerd in de onderstaande schermafbeelding. Het maken van een dynamische top 10 of top N lijst in Excel stelt je in staat om automatisch bijgewerkte resultaten te zien wanneer je gegevens veranderen, wat tijd bespaart en fouten door handmatig rangschikken voorkomt. Deze handleiding introduceert verschillende praktische oplossingen - inclusief formules, draaitabellen en VBA-macro's - om je te helpen een dynamische top 10 of n-lijst te bouwen om aan diverse data-analysebehoeften te voldoen.
Maak een dynamische top 10-lijst in Excel
- Formules om een dynamische top 10-lijst te maken
- Formules om een dynamische top 10-lijst te maken met criteria
Maak een dynamische top 10-lijst in Office 365
- Formule om een dynamische top 10-lijst te maken
- Formule om een dynamische top 10-lijst te maken met criteria
Maak een dynamische top 10-lijst in Excel
In Excel 2019 en eerdere versies houdt het maken van een dynamische top 10 (of top N) lijst in dat je formules combineert om tegelijkertijd de hoogste waarden en hun bijbehorende namen of ID's te extraheren. Deze oplossing wordt veel gebruikt en is geschikt voor situaties waarin je wilt dat de lijst automatisch wordt bijgewerkt wanneer je gegevens veranderen. De volgende stappen beschrijven hoe je dit kunt bereiken met klassieke Excel-formules. Deze formules bieden flexibiliteit en vereisen geen speciale Excel-invoegtoepassingen, maar het instellingsproces is iets ingewikkelder vergeleken met sommige moderne dynamische arrayfuncties.
Formules om een dynamische top 10-lijst te maken
1. Begin met het extraheren van de top 10 waarden uit je waardenbereik. Voer de volgende formule in een lege cel in (bijvoorbeeld cel G2). Nadat je de formule hebt ingevoerd, sleep je de vulgreep naar beneden om je dynamische top 10-waardenlijst te genereren. Zie schermafbeelding:
2. Vervolgens, om de bijbehorende namen (of ID's) weer te geven die bij die topwaarden horen, voer je de volgende formule in cel F2 in. Dit is een matrixformule, dus nadat je deze hebt ingevoerd, druk je op Ctrl + Shift + Enter om te bevestigen. Deze formule vindt de namen die overeenkomen met de topwaarden die je zojuist hebt geëxtraheerd:
- A2:A20 is het bereik waaruit de namen moeten worden gehaald;
- B2:B20 is het score- of waardebereik;
- G2 is de topwaarde uit de formule hierboven;
- B1 is de koptekst van de waarde-lijst en wordt gebruikt voor offset in RIJ-berekeningen.
Deze formule koppelt dynamisch de hoogste waarden aan hun namen. Als je waardebereik duplicaten bevat, zorgt AANTAL.ALS ervoor dat elke overeenkomende naam slechts één keer verschijnt met zijn score.
3. Na het extraheren van het eerste resultaat, selecteer je de formule in cel F2 en sleep je de vulgreep naar beneden om de formule naar zoveel rijen te kopiëren als nodig is. Dit zal je resultaat uitbreiden om dynamisch de namen van alle topscores weer te geven, die overeenkomen met die scores. Zie schermafbeelding:
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Formules om een dynamische top 10-lijst te maken met criteria
Bij sommige analysetaken heb je mogelijk een toplijst nodig die alleen items weergeeft die aan bepaalde criteria voldoen - zoals het beperken van de beste resultaten tot een specifieke groep, team of categorie. Je wilt bijvoorbeeld de beste 10 scores vinden voor alleen "Klas 1" uit een totale gegevenssheet met cijfers van meerdere klassen. Hier is hoe je formules kunt gebruiken voor dit scenario:
1. Begin met het extraheren van de top 10 waarden die voldoen aan je gespecificeerde criterium (bijvoorbeeld "Klas 1") uit de dataset. Voer deze formule in de doelcel in (bijvoorbeeld J2):
2. Nadat je de formule hebt ingevoerd, druk je op Ctrl + Shift + Enter om te bevestigen als een matrixformule en sleep je vervolgens de vulgreep naar beneden om andere cellen te vullen. De formule retourneert de hoogste 10 waarden die overeenkomen met je gekozen voorwaarde (bijvoorbeeld alle scores van "Klas 1").
3. Om de bijbehorende namen voor deze topwaarden onder je criteria weer te geven, kopieer en plak je de onderstaande formule in cel I2 en druk je op Ctrl + Shift + Enter als een matrixformule. Vul vervolgens naar beneden in zoals nodig om de volledige lijst met namen te genereren.
Zorg ervoor dat je de bereiken in de formules aanpast om overeen te komen met je daadwerkelijke gegevensopstelling. Houd er rekening mee dat het gebruik van grote databereiken met matrixformules de prestaties kan vertragen. Als er dubbele waarden verschijnen in je top 10, zal de formule herhaalde scores correct afhandelen en meerdere studentennamen geven als hun cijfers gelijk zijn.
Maak een dynamische top 10-lijst in Office 365
Terwijl eerdere versies van Excel vereisen dat je verschillende functies combineert met matrixformules, introduceert Office 365 (en Excel 2021) dynamische arrayfuncties zoals INDEX, SORTEREN, VOLGORDE en FILTER die de workflow enorm vereenvoudigen. Deze functies maken het gemakkelijker om dynamische top 10-lijsten te bouwen, verminderen fouten en zijn vooral nuttig voor tabellen die vaak groeien of veranderen. Als je werkt in een omgeving met constant bijwerkende gegevens, kunnen deze functies je analyse stroomlijnen en snellere zakelijke beslissingen mogelijk maken.
Formule om een dynamische top 10-lijst te maken
Om een dynamische top 10-lijst te extraheren en weergeven met Office 365, voer je de onderstaande formule in in je gewenste uitvoercel. Je hoeft alleen de bereiken en getallen aan te passen op basis van je behoeften, en de formule toont automatisch de laatste top 10-resultaten telkens wanneer je gegevens veranderen.
Druk eenvoudig op de Enter-toets. De volledige top 10-lijst verschijnt onmiddellijk en blijft dynamisch, zodat extra gegevens of gewijzigde scores direct worden weerspiegeld in je rangschikking.
SORTEREN-functie:
=SORTEREN(array, [sorteer_index], [sorteer_volgorde], [per_kolom])
- array: Het bereik dat je wilt sorteren.
- [sorteer_index]: Nummer van de kolom waarop je wilt sorteren. Voor een typische cijfertabel is dit vaak de tweede kolom.
- [sorteer_volgorde]: Gebruik 1 voor oplopende volgorde of -1 voor aflopende volgorde. Om de hoogste scores te krijgen, gebruik je -1.
- [per_kolom]: Of je sorteert op kolommen (WAAR) of op rijen (ONWAAR of weggelaten).
Bijvoorbeeld: SORTEREN(A2:B20,2,-1) sorteert A2:B20 op de tweede kolom in aflopende volgorde.
VOLGORDE-functie:
=VOLGORDE(rijen, [kolommen], [start], [stap])
- rijen: Aantal rijen om terug te geven, bijvoorbeeld 10 voor een top 10-lijst.
- [kolommen]: (Optioneel) Aantal kolommen om terug te geven.
- [start]: (Optioneel) Startwaarde.
- [stap]: (Optioneel) Waarde om mee te verhogen.
VOLGORDE(10) genereert de getallen 1 tot en met 10, waardoor INDEX de top 10 gesorteerde resultaten kan kiezen.
Door deze te combineren, =INDEX(SORTEREN(A2:B20,2,-1),VOLGORDE(10),{1,2}) geeft je een dynamische, twee-koloms top 10-lijst.
Formule om een dynamische top 10-lijst te maken met criteria
Als je de top 10 voor een bepaalde groep, zoals "Klas 1", moet extraheren, kunnen deze geavanceerde Office 365-functies een top N-lijst maken terwijl ze alleen die rijen bevatten die aan je criteria voldoen. Plaats de onderstaande formule op de gewenste locatie en pas de bereiken en criteriumcel aan indien nodig:
Nadat je de formule hebt ingevoerd, druk je eenvoudig op de Enter-toets. De top 10-lijst, gefilterd en gerangschikt voor het gespecificeerde criterium, verschijnt onmiddellijk en wordt bijgewerkt telkens wanneer je je gegevens of criterium wijzigt.
FILTER-functie:
=FILTER(array, include, [als_leeg])
- array: Het celbereik dat je wilt filteren.
- include: De voorwaarde (bijv., gelijk aan een bepaalde klasse) voor opname.
- [als_leeg]: (Optioneel) Wat te tonen als er geen resultaten aan de criteria voldoen.
=FILTER(A2:C25,B2:B25=F2) retourneert alleen die rijen waar kolom B overeenkomt met de waarde in F2.
Maak een dynamische top 10-lijst met een Draaitabel
Draaitabel: Toon automatisch interactief de top N-resultaten
Een alternatieve manier om een dynamische top N-lijst te bouwen is door de functie Draaitabel van Excel te gebruiken. Deze methode is vooral geschikt voor grote datasets, interactieve analyse (zoals snel het aantal topscores veranderen of filters toepassen), of wanneer je complexe formules wilt vermijden. Draaitabellen zijn gebruikersvriendelijk en worden automatisch bijgewerkt wanneer gegevens veranderen, waardoor ze ideaal zijn voor dashboards of rapporten die met anderen worden gedeeld.
Om een dynamische top N-lijst te maken met een draaitabel:
- Klik ergens binnen je gegevenstabel, ga dan naar Invoegen > Draaitabel.
- Kies in het Draaitabel-dialoogvenster waar je de draaitabel wilt plaatsen en klik op OK.
- Sleep je 'Naam'-veld (of een vergelijkbare identificator) naar het Rijen-gebied.
- Sleep je 'Score'-veld (of waardekolom) naar het Waarden-gebied. Het zal standaard ingesteld worden op 'Som van' of 'Aantal van' - voor toplijsten wil je meestal 'Som' of 'Max'. Wijzig indien nodig de waardeberekening door met de rechtermuisknop te klikken en Samenvatten op Waarde door te kiezen.
- Sorteer de 'Score'-kolom in aflopende volgorde door met de rechtermuisknop op een waarde te klikken en Sorteren > Sorteer van groot naar klein te selecteren.
- Om de resultaten te beperken tot de top N-resultaten, klik je op de vervolgkeuzepijl op Rijlabels, selecteer Waardefilters > Top 10..., stel het aantal in (bijvoorbeeld Top 10) en het veld waarop je wilt filteren, en klik dan op OK.
Je draaitabel toont nu de dynamische top 10 (of elke N die je specificeert). Om de top N te veranderen, hoef je alleen de filterinstellingen opnieuw te bezoeken. Als je gegevens veranderen, vernieuw dan de draaitabel om de rangschikkingen onmiddellijk bij te werken.
Voordelen van deze aanpak zijn snelle instelling, gemakkelijk sorteren en interactieve aanpassing. Draaitabellen kunnen echter niet automatisch overeenkomende rijen uit andere kolommen toevoegen tenzij ze zijn opgenomen in het Rij- of Waarden-gebied. Geavanceerde gebruikers kunnen rapporten verder aanpassen door groeperen, slicers te maken of het Top N-filter in dashboards te integreren.
Maak een dynamische top 10-lijst met behulp van VBA
VBA Macro: Genereer en vernieuw automatisch een top N-lijst
Het gebruik van een VBA-macro is geschikt voor gebruikers die te maken hebben met uitgebreide of vaak bijgewerkte gegevens, waarbij het automatiseren van het extraheren en vernieuwen van een dynamische top N-lijst vereist is. Macro's zijn ideaal om herhalende taken te verminderen en consistentie te garanderen. Je kunt een routine maken die je gegevens sorteert en alleen de top N rijen naar een specifieke locatie kopieert elke keer dat deze wordt uitgevoerd.
Volg deze stappen om een VBA-macro te gebruiken voor het maken van een dynamische top N-lijst:
- Klik ontwikkelaar > Visual Basic om de VBA-editor te openen. (Als je het tabblad Ontwikkelaar niet ziet, ga je naar Bestand > Opties > Werkbalk aanpassen en schakel 'Ontwikkelaar' in.)
- Klik in het VBA-venster op Invoegen > Module om een nieuwe module toe te voegen.
- Plak de volgende VBA-code in de module:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
Dim DataRange As Range
Dim OutputRange As Range
Dim N As Integer
Dim ws As Worksheet, tempWS As Worksheet
Dim xTitleId As String
Dim LastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
' Create a temporary worksheet to avoid sorting original data
Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
DataRange.Copy tempWS.Range("A1")
' Determine last column for sorting key
LastCol = DataRange.Columns.Count
' Sort in temporary sheet
tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
' Copy headers and top N rows to output
tempWS.Rows(1).Copy Destination:=OutputRange
tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
' Optional: Delete temporary sheet
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub4. Om de macro uit te voeren, zorg je ervoor dat je gegevens goed zijn uitgelegd in een tabel met kopteksten. Druk F5 of klik op de 
knop in de VBA-editor. Je wordt gevraagd om:
- Selecteer je gegevensbereik (inclusief kopteksten voor correct sorteren).
- Selecteer de uitvoercel om de resultaten in te plakken.
- Voer het getal N in (bijvoorbeeld 10 voor Top 10).
De macro kopieert de top N-items (inclusief kopteksten) naar de locatie die je hebt gespecificeerd.
Het wordt aanbevolen om dit eerst in een back-up of kopie van je werkmap te gebruiken bij het testen. Als er fouten optreden (zoals het selecteren van een verkeerd bereik), voer je de macro opnieuw uit en zorg je ervoor dat je bereiken en gegevensindeling correct zijn.
Deze oplossing is ideaal voor het automatiseren van herhalende rapportagetaken, het maken van dashboards of het snel bijwerken van top N-rapporten zonder handmatige formules of sortering. Je kunt het VBA-script verder aanpassen voor complexere rangschikkinglogica, zoals sorteren op een specifieke kolom of resultaten exporteren naar een andere werkmap.
Problemen oplossen: Als de macro niet werkt zoals verwacht, controleer dan of je gegevenstabel juiste kopteksten bevat, corrigeer de gegevenstypen om sorteervraagstukken te voorkomen en zorg ervoor dat celverwijzingen in elke prompt nauwkeurig zijn geselecteerd. Sla altijd je werk op voordat je macro's uitvoert om onbedoelde gegevenswijzigingen te voorkomen.
Samenvattend ondersteunt Excel verschillende methoden om een dynamische top N-lijst te genereren en te onderhouden - van traditionele formules tot krachtige Office 365-functies, draaitabellen voor interactieve analyse en VBA-macro's voor geavanceerde automatisering. Kies de methode die het best past bij je werkstroom en gegevensschaal. Het gebruik van formules is effectief voor de meeste handmatige analyses, Office 365-functies bieden de grootste eenvoud en kracht, draaitabellen zijn uitstekend voor snelle, flexibele samenvattingen, en VBA is vooral handig om grote, herhalende rangschikkingtaken te automatiseren. Controleer altijd de integriteit van je formules of code en pas celverwijzingen aan om overeen te komen met eventuele veranderingen in je gegevensstructuur naarmate je project evolueert.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen