Comment regrouper et classer des valeurs par groupe dans Excel ?
Lorsque vous travaillez avec des données groupées dans Excel, il est souvent essentiel de comparer des valeurs au sein de chaque groupe — par exemple, classer les chiffres de vente par région, les résultats d’examens par classe ou les montants de transactions par catégorie. Bien qu’Excel offre des outils puissants pour trier des données, le classement à l’intérieur des groupes (aussi appelé « classement par groupe » ou « classement conditionnel ») exige une méthode spécifique. Cette approche s’avère particulièrement précieuse pour évaluer les performances ou repérer les meilleurs et les moins bons éléments au sein de chaque catégorie, sans mélanger les résultats entre groupes. Les méthodes suivantes vous proposent des solutions pratiques pour classer efficacement vos valeurs par groupe, facilitant ainsi une interprétation claire et une analyse précise de vos données au quotidien.
Classer des valeurs par groupe
Code VBA – Utilisez une macro pour automatiser le classement des valeurs au sein de chaque groupe
Classer des valeurs par groupe
Lorsque vous devez classer des valeurs au sein de groupes distincts — par exemple, noter des élèves par classe ou lister des ventes par région — Excel ne propose pas de fonctionnalité intégrée de « classement par groupe ». Toutefois, une formule bien conçue permet d’obtenir ce classement de manière efficace, sans aucune manipulation supplémentaire des données.
Pour ce faire, vous pouvez utiliser une formule matricielle combinant des tests logiques et 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 souhaité pour chaque point de données.
Procédez comme suit :
- Préparez vos données groupées en colonnes, par exemple Groupe (A2:A11) et Valeur (B2:B11).
- Sélectionnez une cellule vide adjacente à vos données — généralement située sur la première ligne à côté de vos valeurs, par exemple la cellule C2.
- Saisissez la formule suivante :
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1Cette formule fonctionne en comptant combien de valeurs appartenant au même groupe sont inférieures à la valeur actuelle. Voici ce que signifie chaque élément :
- ($A$2:$A$11=A2)
→ Vérifie si chaque cellule de la plage A2:A11 est égale à la valeur située en A2.
→ Renvoie un tableau de valeurs VRAI/FAUX (ou 1/0) indiquant si chaque ligne appartient au même groupe que la cellule A2. - (B2<$B$2:$B$11)
→ Vérifie combien de valeurs dans la plage B2:B11 sont supérieures à celle de la cellule B2.
→ Renvoie VRAI (1) si la valeur de B2 est inférieure à une valeur donnée, FAUX (0) sinon. - * (Multiplication)
→ Combine les deux conditions : - Correspondance de groupe (A2)
Valeur en B2 inférieure aux autres
→ Seules les lignes du même groupe dont la valeur est inférieure seront prises en compte. - SOMMEPROD(...)
→ Calcule le nombre total de lignes qui répondent aux deux conditions. - +1
→ Le classement commence à 1 (au lieu de 0), nous ajoutons donc 1 au nombre de valeurs inférieures.
Une fois la formule saisie en C2, faites glisser la poignée de recopie vers le bas pour l’appliquer à toutes les lignes pertinentes de votre jeu de données. La formule s’ajustera automatiquement afin de prendre en compte le groupe et la valeur propres à chaque ligne, et renverra ainsi le rang au sein de ce groupe.
Conseils et précautions :
- Si votre plage est étendue, pensez à mettre à jour les références de cellules en conséquence.
- Pour obtenir un classement en ordre décroissant (par exemple, la valeur la plus élevée se voit attribuer le rang 1), remplacez la comparaison dans la formule de
B2<$B$2:$B$11parB2>$B$2:$B$11. - Pour gérer les valeurs en double, cette formule attribue le même rang aux valeurs identiques au sein d’un même groupe. Si vous avez besoin de rangs séquentiels uniques, envisagez d’utiliser des colonnes auxiliaires supplémentaires.
Cette méthode fondée sur les formules est flexible et s’applique facilement à la plupart des structures de tableaux groupés dans Excel. Toutefois, avec des jeux de données très volumineux, les performances de calcul peuvent ralentir en raison de la logique matricielle utilisée.
Code VBA – Utilisez une macro pour automatiser le classement des valeurs au sein de chaque groupe
Pour les utilisateurs souhaitant automatiser le processus de classement ou traiter des jeux de données plus volumineux de manière plus efficace, la rédaction d’une macro VBA constitue une approche précieuse. Les macros permettent d’automatiser les étapes répétitives, offrent davantage de personnalisation et traitent les données plus rapidement que des formules complexes. Cette solution est idéale pour des scénarios tels que la génération automatisée de rapports, des tâches de classement répétées ou lorsque vous souhaitez éviter d’encombrer votre feuille de calcul avec des formules.
Avant de commencer, veillez à sauvegarder votre travail et à activer les macros dans les paramètres d’Excel. Voici comment implémenter et exécuter cette solution :
- Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. Dans la fenêtre Microsoft Visual Basic pour Applications qui s’affiche, 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- Cliquez sur Exécuter. Une boîte de dialogue s’ouvrira pour vous demander de sélectionner votre plage de données complète, la colonne de groupe et la colonne de valeurs. La macro générera alors une nouvelle colonne contenant le rang de chaque valeur au sein de son groupe.
Remarques et dépannage :
- Vérifiez que vos sélections de colonnes correspondent bien à vos données : les colonnes de groupe et de valeur doivent être parfaitement alignées.
- Si l’en-tête des données est inclus, ajustez l’indice de départ de la boucle dans le code pour obtenir un classement correct, en fonction de la structure de vos données.
- Pour classer en ordre décroissant, modifiez la comparaison
If dictGroups(GroupKey)(j) < arrValues(i,1)en conséquence. - Si vous rencontrez des avertissements liés aux autorisations ou à la sécurité des macros, vérifiez les paramètres de sécurité des macros d’Excel dans Fichier > Options > Centre de gestion de la confidentialité.
Cette méthode VBA offre flexibilité et performances robustes pour des applications plus avancées ou à grande échelle, notamment lorsqu’elle est intégrée à des flux de travail automatisés de génération de rapports.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels