Comment créer une liste dynamique des 10 meilleurs ou des n premiers dans Excel ?
Dans de nombreux projets et processus commerciaux, il est souvent nécessaire de classer des individus, des organisations, des produits ou d'autres entités en fonction de leur performance ou de leurs valeurs numériques. Une « liste des meilleurs » sert à mettre en avant les entrées les plus performantes, comme les meilleurs étudiants par leurs notes, les meilleurs vendeurs, ou les départements ayant généré le plus de revenus. Par exemple, vous pourriez avoir un tableau de notes d'étudiants et vouloir extraire dynamiquement les 10 meilleurs scores pour des récompenses, des analyses ou la surveillance des résultats éducatifs, comme illustré dans la capture d'écran ci-dessous. Créer une liste dynamique des 10 meilleurs ou des n premiers dans Excel vous permet de voir automatiquement les résultats mis à jour lorsque vos données changent, ce qui fait gagner du temps et réduit les erreurs dues aux classements manuels. Ce guide introduit plusieurs solutions pratiques – y compris des formules, des tableaux croisés dynamiques et des macros VBA – pour vous aider à construire une liste dynamique des 10 meilleurs ou des n premiers afin de répondre efficacement à divers besoins d'analyse de données.
Créer une liste dynamique des 10 meilleurs dans Excel
- Formules pour créer une liste dynamique des 10 meilleurs
- Formules pour créer une liste dynamique des 10 meilleurs avec critères
Créer une liste dynamique des 10 meilleurs dans Office 365
- Formule pour créer une liste dynamique des 10 meilleurs
- Formule pour créer une liste dynamique des 10 meilleurs avec critères
Créer une liste dynamique des 10 meilleurs avec un Tableau Croisé Dynamique
Créer une liste dynamique des 10 meilleurs dans Excel
Dans Excel 2019 et les versions antérieures, créer une liste dynamique des 10 meilleurs (ou des n premiers) consiste à combiner des formules pour extraire simultanément les valeurs les plus élevées et leurs noms ou identifiants associés. Cette solution est largement utilisée et convient aux situations où vous souhaitez que la liste se mette à jour automatiquement lorsque vos données changent. Les opérations suivantes décrivent comment réaliser cela en utilisant des formules Excel classiques. Ces formules offrent de la flexibilité et ne nécessitent pas d'add-ins spéciaux pour Excel, mais le processus de configuration est un peu plus complexe par rapport à certaines fonctions modernes de tableau dynamique.
Formules pour créer une liste dynamique des 10 meilleurs
1. Pour commencer, vous devez extraire les 10 valeurs les plus élevées de votre plage de valeurs. Entrez la formule suivante dans une cellule vide (par exemple, la cellule G2). Après avoir entré la formule, faites glisser la poignée de recopie vers le bas pour générer votre liste dynamique des 10 meilleures valeurs. Voir capture d'écran :
2. Ensuite, pour afficher les noms (ou ID) correspondants associés à ces valeurs élevées, entrez la formule suivante dans la cellule F2. Il s'agit d'une formule matricielle, donc après l'avoir saisie, appuyez sur Ctrl + Maj + Entrée pour confirmer. Cette formule trouve les noms correspondant aux valeurs élevées que vous venez d'extraire :
- A2:A20 est la plage à partir de laquelle extraire les noms ;
- B2:B20 est la plage des scores ou des valeurs ;
- G2 est la valeur maximale issue de la formule ci-dessus ;
- B1 est l'en-tête de la liste des valeurs et est utilisé pour le décalage dans les calculs de LIGNE.
Cette formule lie dynamiquement les valeurs les plus élevées à leurs noms. Si votre plage de valeurs contient des doublons, NB.SI garantit que chaque nom correspondant apparaît une seule fois avec son score.
3. Après avoir extrait le premier résultat, sélectionnez la formule dans la cellule F2 et faites glisser la poignée de recopie vers le bas pour copier la formule sur autant de lignes que nécessaire. Cela étendra vos résultats pour afficher dynamiquement les noms de toutes les entrées principales correspondant à ces scores. Voir capture d'écran :
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Formules pour créer une liste dynamique des 10 meilleurs avec critères
Dans certaines tâches d'analyse, vous devrez peut-être créer une liste des meilleurs qui n'affiche que les entrées répondant à certains critères - comme limiter les résultats supérieurs à un groupe, une équipe ou une catégorie spécifique. Par exemple, vous souhaiterez peut-être identifier les 10 meilleures notes uniquement pour la « Classe 1 » à partir d'une feuille de données totales contenant des notes provenant de plusieurs classes. Voici comment utiliser des formules pour ce scénario :
1. Commencez par extraire les 10 valeurs les plus élevées répondant à votre critère spécifié (par ex., « Classe 1 ») depuis l'ensemble de données. Entrez cette formule dans la cellule cible (par exemple, J2) :
2. Après avoir entré la formule, appuyez sur Ctrl + Maj + Entrée pour la valider en tant que formule matricielle, puis tirez la poignée de recopie vers le bas pour remplir les autres cellules. La formule renverra les 10 valeurs les plus élevées qui correspondent à votre condition choisie (par ex., toutes les notes de la « Classe 1 »).
3. Pour lister les noms correspondants à ces valeurs élevées sous vos critères, copiez et collez la formule ci-dessous dans la cellule I2 et appuyez sur Ctrl + Maj + Entrée comme formule matricielle. Ensuite, remplissez vers le bas selon les besoins pour générer la liste complète des noms.
Assurez-vous d'ajuster les plages dans les formules pour qu'elles correspondent à votre configuration de données réelle. Soyez conscient que l'utilisation de grandes plages de données avec des formules matricielles peut ralentir les performances. Si des valeurs en double apparaissent dans votre top 10, la formule traitera correctement les scores répétés et donnera plusieurs noms d'étudiants si leurs notes sont égales.
Créer une liste dynamique des 10 meilleurs dans Office 365
Alors que les versions antérieures d'Excel nécessitent la combinaison de plusieurs fonctions avec des formules matricielles, Office 365 (et Excel 2021) introduit des fonctions de tableau dynamique telles que INDEX, TRIER, SEQUENCE et FILTRER qui simplifient grandement le flux de travail. Ces fonctions facilitent la création de listes dynamiques des 10 meilleurs, réduisent les erreurs et sont particulièrement utiles pour les tableaux qui augmentent ou changent fréquemment. Si vous travaillez dans un environnement avec des données constamment mises à jour, ces fonctions peuvent rationaliser votre analyse et permettre des décisions commerciales plus rapides.
Formule pour créer une liste dynamique des 10 meilleurs
Pour extraire et afficher une liste dynamique des 10 meilleurs en utilisant Office 365, entrez la formule ci-dessous dans votre cellule de sortie souhaitée. Tout ce que vous avez à faire est d'ajuster les plages et les nombres en fonction de vos besoins, et la formule affichera automatiquement les 10 derniers résultats chaque fois que vos données changeront.
Appuyez simplement sur la touche Entrée. La liste complète des 10 meilleurs apparaît instantanément et restera dynamique, de sorte que des données supplémentaires ou des scores modifiés seront immédiatement reflétés dans votre classement.
Fonction TRIER :
=TRIER(tableau, [index_tri], [ordre_tri], [par_col])
- tableau : La plage que vous souhaitez trier.
- [index_tri] : Numéro de la colonne à trier. Pour une table typique de notes, il s'agit souvent de la deuxième colonne.
- [ordre_tri] : Utilisez 1 pour l'ordre croissant ou -1 pour l'ordre décroissant. Pour obtenir les meilleures notes, utilisez -1.
- [par_col] : Indique si le tri se fait par colonnes (VRAI) ou par lignes (FAUX ou omis).
Par exemple : TRIER(A2:B20,2,-1) trie A2:B20 par la deuxième colonne dans l'ordre décroissant.
Fonction SEQUENCE :
=SEQUENCE(lignes, [colonnes], [début], [pas])
- lignes : Nombre de lignes à retourner, par exemple, 10 pour une liste des 10 meilleurs.
- [colonnes] : (Facultatif) Nombre de colonnes à retourner.
- [début] : (Facultatif) Valeur de départ.
- [pas] : (Facultatif) Valeur d'incrémentation.
SEQUENCE(10) génère les nombres de 1 à 10, permettant à INDEX de sélectionner les 10 premiers résultats triés.
En combinant ces deux fonctions, =INDEX(TRIER(A2:B20,2,-1),SEQUENCE(10),{1,2}) vous donne une liste dynamique des 10 meilleurs à deux colonnes.
Formule pour créer une liste dynamique des 10 meilleurs avec critères
Si vous devez extraire les 10 meilleurs pour un groupe particulier, comme « Classe 1 », ces fonctions avancées d'Office 365 peuvent créer une liste des N premiers tout en incluant uniquement les lignes qui répondent à vos critères. Placez la formule ci-dessous à l'emplacement souhaité et ajustez les plages et la cellule de critères au besoin :
Après avoir entré la formule, appuyez simplement sur la touche Entrée. La liste des 10 meilleurs filtrée et classée uniquement pour le critère spécifié s'affiche immédiatement, se mettant à jour à chaque modification de vos données ou de votre critère.
Fonction FILTRER :
=FILTRER(tableau, inclure, [si_vide])
- tableau : La plage de cellules à filtrer.
- inclure : La condition (par ex., égal à une classe donnée) pour inclusion.
- [si_vide] : (Facultatif) Ce qui doit être affiché si aucun résultat ne répond aux critères.
=FILTRER(A2:C25,B2:B25=F2) renvoie uniquement les lignes où la colonne B correspond à la valeur dans F2.
Créer une liste dynamique des 10 meilleurs avec un Tableau Croisé Dynamique
Tableau Croisé Dynamique : Afficher automatiquement les résultats des N premiers de manière interactive
Une autre façon de créer une liste dynamique des N premiers est d'utiliser la fonctionnalité de Tableau Croisé Dynamique d'Excel. Cette méthode est particulièrement adaptée pour les grands ensembles de données, l'analyse interactive (comme modifier rapidement le nombre de premiers éléments ou appliquer des filtres), ou lorsque vous souhaitez éviter des formules complexes. Les tableaux croisés dynamiques sont conviviaux et se mettent à jour automatiquement lorsque les données changent, ce qui en fait un excellent outil pour les tableaux de bord ou les rapports partagés avec d'autres personnes.
Pour créer une liste dynamique des N premiers à l'aide d'un tableau croisé dynamique :
- Cliquez n'importe où à l'intérieur de votre table de données, puis allez dans Insertion > Tableau Croisé Dynamique.
- Dans la boîte de dialogue Tableau Croisé Dynamique, choisissez où vous souhaitez placer le tableau croisé dynamique et cliquez sur OK.
- Faites glisser votre champ « Nom » (ou identifiant similaire) dans la zone Lignes.
- Faites glisser votre champ « Score » (ou colonne de valeurs) dans la zone Valeurs. Elle se définit généralement par défaut sur « Somme de » ou « Compte de » - pour les listes des meilleurs, vous préférez généralement « Somme » ou « Max ». Modifiez le calcul du champ de valeur si nécessaire en faisant un clic droit et en choisissant Résumer la valeur par.
- Triez la colonne « Score » dans l'ordre décroissant en faisant un clic droit sur une valeur et en sélectionnant Trier > Trier du plus grand au plus petit.
- Pour limiter aux résultats des N premiers, cliquez sur la flèche déroulante sur Étiquettes de ligne, sélectionnez Filtres de valeurs > 10 premiers..., définissez le nombre (par exemple, Top 10) et le champ à filtrer, puis cliquez sur OK.
Votre tableau croisé dynamique affiche maintenant la liste dynamique des 10 meilleurs (ou tout N que vous spécifiez). Pour modifier le N supérieur, revisitez simplement les paramètres de filtre. Si vos données changent, actualisez le tableau croisé dynamique pour mettre à jour instantanément les classements.
Les avantages de cette approche incluent une configuration rapide, un tri facile et des ajustements interactifs. Cependant, les tableaux croisés dynamiques ne peuvent pas ajouter automatiquement des lignes correspondantes d'autres colonnes à moins qu'ils ne soient inclus dans la zone Lignes ou Valeurs. Les utilisateurs avancés peuvent personnaliser davantage les rapports en regroupant, en créant des segments ou en incorporant le filtre des N premiers dans des tableaux de bord.
Créer une liste dynamique des 10 meilleurs avec VBA
Macro VBA : Générer et actualiser automatiquement une liste des N premiers
L'utilisation d'une macro VBA convient bien aux utilisateurs qui manipulent des données volumineuses ou fréquemment mises à jour, où l'automatisation de l'extraction et de l'actualisation d'une liste dynamique des N premiers est requise. Les macros sont idéales pour réduire les tâches répétitives et assurer la cohérence. Vous pouvez créer une routine qui trie vos données et copie uniquement les N premières lignes à un emplacement spécifique à chaque exécution.
Pour utiliser une macro VBA afin de créer une liste dynamique des N premiers, suivez ces étapes :
- Cliquez sur Développeur > Visual Basic pour ouvrir l'éditeur VBA. (Si vous ne voyez pas l'onglet Développeur, allez dans Fichier > Options > Personnaliser le Ruban et activez « Développeur ».)
- Dans la fenêtre VBA, cliquez sur Insertion > Module pour ajouter un nouveau module.
- Collez le code VBA suivant dans le 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. Pour exécuter la macro, assurez-vous que vos données sont disposées correctement dans un tableau avec des en-têtes. Appuyez sur F5 ou cliquez sur le 
bouton dans l'éditeur VBA. Vous serez invité à :
- Sélectionner votre plage de données (y compris les en-têtes pour un tri correct).
- Sélectionner la cellule de sortie pour coller les résultats.
- Entrer le nombre N (par exemple, 10 pour Top 10).
La macro copiera les N premières entrées (y compris les en-têtes) à l'emplacement que vous avez spécifié.
Il est conseillé d'utiliser cela dans une sauvegarde ou une copie de votre classeur lors des premiers tests. En cas d'erreurs (comme une plage incorrecte sélectionnée), relancez et assurez-vous que vos plages et la disposition des données sont correctes.
Cette solution est idéale pour automatiser des tâches de reporting répétitives, créer des tableaux de bord ou mettre à jour rapidement des rapports des N premiers sans formules manuelles ni tris. Vous pouvez personnaliser davantage le script VBA pour une logique de classement plus complexe, comme trier par une colonne spécifique ou exporter les résultats vers un autre classeur.
Dépannage : Si la macro ne fonctionne pas comme prévu, vérifiez que votre table de données inclut des en-têtes appropriés, corrigez les types de données pour éviter des problèmes de tri, et assurez-vous que les références de cellules sont sélectionnées avec précision dans chaque invite. Sauvegardez toujours votre travail avant d'exécuter des macros pour éviter des modifications accidentelles des données.
En résumé, Excel prend en charge diverses méthodes pour générer et maintenir une liste dynamique des N premiers – des formules traditionnelles aux puissantes fonctions d'Office 365, en passant par les tableaux croisés dynamiques pour une analyse interactive, et les macros VBA pour une automatisation avancée. Choisissez la méthode qui convient le mieux à votre flux de travail et à l'échelle de vos données. L'utilisation de formules est efficace pour la plupart des analyses manuelles, les fonctions d'Office 365 offrent la plus grande simplicité et puissance, les tableaux croisés dynamiques sont excellents pour des résumés rapides et flexibles, et VBA est particulièrement utile pour automatiser des tâches de classement volumineuses et répétitives. Vérifiez toujours l'intégrité de vos formules ou code, et adaptez les références de cellules pour correspondre à toute modification de votre structure de données au fur et à mesure que votre projet évolue.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels