Comment classer des valeurs par groupe dans Excel ?

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.

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

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.

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

2. 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.

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 !