Accéder au contenu principal

Kutools for Office — Une Suite. Cinq Outils. Accomplissez Plus.

Comment classer des valeurs par groupe dans Excel ?

Author Sun Last modified

Le travail avec des données groupées dans Excel nécessite souvent de comparer des valeurs au sein de chaque groupe, comme le classement des chiffres de vente par région, des notes par classe ou des montants de transactions par catégorie. Bien qu'Excel propose des outils robustes pour le classement des données, le classement au sein des groupes (également appelé « classement par groupe » ou « classement conditionnel ») nécessite une approche spécifique. Cela est particulièrement utile lorsque vous devez évaluer les performances ou identifier les meilleurs et les pires enregistrements parmi différentes catégories sans mélanger les résultats entre les groupes. Les méthodes suivantes explorent des solutions pratiques pour classer les valeurs par groupe, facilitant l'interprétation et l'analyse précise de vos données dans les tâches quotidiennes.
A screenshot showing a grouped data set with ranked values in Excel

Classer les valeurs par groupe
Code VBA - Utiliser une macro pour automatiser le classement des valeurs dans chaque groupe


arrow blue right bubble Classer les valeurs par groupe

Pour les situations où vous avez besoin de classer des valeurs dans des groupes distincts, comme la notation des élèves par classe ou la liste des ventes par différentes régions, Excel ne dispose pas d'une fonctionnalité directe de « classement par groupe ». Cependant, une formule bien conçue peut atteindre un classement par groupe de manière efficace et sans manipulation supplémentaire de données.

Pour ce faire, vous pouvez utiliser une formule matricielle qui combine des tests logiques avec des fonctions d'agrégation. Cette approche vous permet de comparer chaque valeur uniquement au sein de son groupe désigné, produisant ainsi le rang requis pour chaque point de donnée.

Suivez ces étapes :

  • Préparez vos données groupées en colonnes, telles que Groupe (A2:A11) et Valeur (B2:B11).
  • Sélectionnez une cellule vide adjacente à vos données—généralement, dans la première ligne à côté de vos valeurs, par exemple, la cellule C2.
  • Entrez la formule suivante :
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1

Cette formule fonctionne en comptant combien de valeurs dans le même groupe sont inférieures à la valeur actuelle. Voici ce que signifie chaque paramètre :

  • ($A$2:$A$11=A2)
    → Cela vérifie si chaque cellule dans la plage A2:A11 est égale à la valeur dans A2.
    → Elle renvoie un tableau de valeurs VRAI/FAUX (ou 1/0) représentant si chaque ligne appartient au même groupe que A2.
  • (B2<$B$2:$B$11)
    → Cela vérifie combien de valeurs dans B2:B11 sont supérieures à B2.
    → Elle renvoie VRAI (1) si B2 est inférieur à une valeur donnée, FAUX (0) sinon.
  • * (Multiplication)
    → Cela combine les deux conditions :
  • Correspondance de groupe (A2)
    La valeur dans B2 est plus petite que les autres
    → Donc, seules les lignes qui sont dans le même groupe et ont une valeur plus petite seront comptées.
  • SOMMEPROD(...)
    → Fait la somme du nombre de lignes qui remplissent les deux conditions.
  • +1
    → Les rangs commencent à 1 (au lieu de 0), donc nous ajoutons 1 au nombre de valeurs plus petites.

Une fois que la formule est entrée dans C2, faites glisser la poignée de recopie automatique vers le bas pour remplir cette formule pour toutes les lignes pertinentes de votre ensemble de données. La formule s'ajustera automatiquement pour utiliser le groupe et la valeur de chaque ligne, renvoyant le rang au sein de ce groupe. A screenshot showing the Excel formula applied to rank values by group in a worksheet

Conseils et précautions :

  • Si votre plage est grande, n'oubliez pas de mettre à jour les références de cellules en conséquence.
  • Pour les rangs dans l'ordre décroissant (par exemple, la plus haute valeur est 1), modifiez la comparaison de la formule de B2<$B$2:$B$11 à B2>$B$2:$B$11.
  • Pour gérer les valeurs en double, cette formule attribue le même rang aux valeurs égales au sein du même groupe. Si vous avez besoin de rangs uniques séquentiels, envisagez d'utiliser des colonnes auxiliaires supplémentaires.

Cette méthode basée sur une formule est flexible et s'applique facilement à la plupart des structures de tableaux groupés dans Excel. Cependant, pour des ensembles de données très volumineux, les performances de calcul peuvent ralentir en raison de la dépendance à la logique matricielle.


Code VBA - Utiliser une macro pour automatiser le classement des valeurs dans chaque groupe

Pour les utilisateurs souhaitant automatiser le processus de classement ou traiter des ensembles de données plus volumineux de manière plus efficace, écrire une macro VBA peut être une approche précieuse. Les macros peuvent automatiser les étapes répétitives, offrir davantage de personnalisation et traiter les données rapidement par rapport aux formules complexes. Cela est idéal pour des scénarios comme la génération de rapports planifiés, des tâches de classement répétées ou lorsque vous souhaitez éviter l'encombrement des formules dans votre feuille de calcul.

Avant de continuer, assurez-vous de sauvegarder votre travail et d'activer les macros dans les paramètres d'Excel. Voici comment vous pouvez scripter et exécuter cette solution :

  1. Appuyez sur Alt + F11 pour activer l'éditeur VBA. Dans la fenêtre Microsoft Visual Basic for Applications qui apparaît, cliquez sur Insertion > Module, puis collez le code suivant dans le module ouvert :
Sub RankValuesByGroup()
    Dim DataRange As Range
    Dim GroupRng As Range
    Dim ValueRng As Range
    Dim OutCol As Range
    Dim dictGroups As Object
    Dim arrValues, arrRanks
    Dim i As Long, j As Long
    Dim GroupKey As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the data table range (including group and value columns)", xTitleId, Selection.Address, Type:=8)
    If DataRange Is Nothing Then Exit Sub
    
    Set GroupRng = Application.InputBox("Select the group column within your range", xTitleId, DataRange.Columns(1).Address, Type:=8)
    Set ValueRng = Application.InputBox("Select the value column to rank within your range", xTitleId, DataRange.Columns(2).Address, Type:=8)
    
    Set OutCol = DataRange.Offset(0, DataRange.Columns.Count).Resize(DataRange.Rows.Count, 1)
    OutCol.Cells(1).Value = "RankByGroup"
    
    Set dictGroups = CreateObject("Scripting.Dictionary")
    arrValues = ValueRng.Value
    arrRanks = ValueRng.Value
    
    ' Build group dictionaries for ranking
    For i = 2 To UBound(arrValues, 1)
        GroupKey = GroupRng.Cells(i, 1).Value
        If Not dictGroups.Exists(GroupKey) Then
            dictGroups.Add GroupKey, CreateObject("System.Collections.ArrayList")
        End If
        dictGroups(GroupKey).Add arrValues(i, 1)
    Next i
    
    ' Rank within each group
    For i = 2 To UBound(arrValues, 1)
        GroupKey = GroupRng.Cells(i, 1).Value
        Dim countLower As Long
        countLower = 0
        For j = 0 To dictGroups(GroupKey).Count - 1
            If dictGroups(GroupKey)(j) < arrValues(i, 1) Then
                countLower = countLower + 1
            End If
        Next j
        arrRanks(i, 1) = countLower + 1
    Next i
    
    ' Output results
    For i = 2 To UBound(arrRanks, 1)
        OutCol.Cells(i, 1).Value = arrRanks(i, 1)
    Next i
    
    MsgBox "Ranking by group completed.", vbInformation, xTitleId
End Sub
  1. Cliquez sur Exécuter. Une boîte de dialogue vous invitera à sélectionner la plage complète de vos données, la colonne de groupe et la colonne de valeur. La macro générera ensuite une nouvelle colonne avec les rangs pour chaque valeur au sein de son groupe.

Remarques et dépannage :

  • Assurez-vous que les sélections de colonnes correspondent à vos données : les colonnes de groupes et de valeurs doivent être correctement alignées.
  • Si l'en-tête des données est inclus, ajustez l'index initial de la boucle dans le code pour un classement correct (selon la structure de vos données).
  • Pour classer dans l'ordre décroissant, modifiez la comparaison Si dictGroups(GroupKey)(j) < arrValues(i,1) en conséquence.
  • Si vous rencontrez des avertissements de permission ou de sécurité des macros, vérifiez les paramètres de Sécurité des macros sous Fichier > Options > Centre de gestion de la confidentialité.

Cette méthode VBA offre flexibilité et performances robustes pour des applications plus avancées ou à plus grande échelle, surtout lorsqu'elle est intégrée à des flux de travail de création de rapports automatisés.


a screenshot of kutools for excel ai

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.
Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !

Meilleurs outils de productivité pour Office

🤖 Kutools AI Aide : Révolutionnez l'analyse de données avec : Exécution intelligente | Générer du code | Créer des formules personnalisées | Analyser des données et générer des graphiques | Appeler les Fonctions améliorées de Kutools
Fonctionnalités populaires : Trouver, mettre en évidence ou marquer les doublons | Supprimer les lignes vides | Combinez les colonnes ou les cellules sans perte de données | Arrondir sans formule...
Super RECHERCHEV : Recherche multi-critères | Recherche multi-valeurs | Recherche multi-feuilles | Correspondance floue...
Liste déroulante avancée : Créer rapidement une liste déroulante | Liste déroulante dépendante | Liste déroulante à sélection multiple...
Gestionnaire de colonnes : Ajouter un nombre spécifique de Colonnes | Déplacer des Colonnes | Alterner l’état de visibilité des Colonnes masquées | Comparer des plages & Colonnes...
Fonctionnalités phares : Mise au point de la grille | Affichage de conception | Barre de formule améliorée | Gestionnaire de Classeur & Feuille de calcul | Bibliothèque d’AutoTexte | Sélecteur de date | Merge Worksheets | Chiffrer/Déchiffrer les cellules | Envoyer un e-mail par liste | Super Filtre | Filtre spécial (filtrer les cellules avec une police en gras/italique/barré...)...
Top15 des ensembles d’outils :12 outils de texte (Ajouter du texte, Supprimer des caractères spécifiques, ...) |50+ Types de graphiques (Diagramme de Gantt, ...) |40+ Formules pratiques (Calculer l’âge en fonction de la date de naissance, ...) |19 outils d’insertion (Insérer un code QR, Insérer une image depuis le chemin, ...) |12 outils de conversion (Convertir en mots, Conversion de devises, ...) |7 outils de fusion & division (Fusion avancée des lignes, Diviser les cellules, ...) | ... et bien plus encore
Utilisez Kutools dans la langue de votre choix – disponible en Anglais, Espagnol, Allemand, Français, Chinois et plus de40 autres !

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.

Excel Word Outlook Tabs PowerPoint
  • 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