Comment utiliser la fonction Rank et SUMPRODUCT dans Excel avec COUNTIF
Le classement des éléments par ordre croissant ou décroissant à l'aide de la combinaison des fonctions COUNTIF, RANK.EQ et SUMPRODUCT est relativement simple. Vous pouvez trier les éléments avec ou sans duplication et conserver le contrôle pour ignorer ou ne pas sauter les rangs de la séquence.
En attribuant les mêmes rangs à plusieurs entrées identiques, la fonction RANK.EQ saute des numéros dans la séquence globale. Afin d'éviter ce saut, utilisez la fonction SUMPRODUCT avec la fonction COUNTIF. Cette combinaison classe les valeurs en double sans sauter les nombres.
Différentes fonctions de classement dans Excel
Avant d'examiner de plus près son implémentation, passons brièvement en revue ses deux autres formulaires trouvés dans Excel; RANK.AVG et RANK.
Dans les versions antérieures d'Excel, seule la fonction RANK était disponible. Cependant, depuis Excel 2010, deux fonctions de classement supplémentaires ont été ajoutées, à savoir, RANK.AVG et RANK.EQ. Explorons leurs différences, car vous les verrez probablement toutes dans Excel.
Fonction RANK.EQ: RANK.EQ est une nouvelle version améliorée de la fonction RANK et est prise en charge dans les dernières versions d'Excel. Les deux fonctions, cependant, sont les mêmes et ont le même objectif. Quoi que vous utilisiez, cela donne les mêmes résultats.
Fonction RANK: La fonction RANK, en revanche, est disponible pour compatibilité avec Excel 2007 et versions antérieures et peut être supprimée dans l'une des nouvelles versions d'Excel. Aujourd'hui, tout le monde utilise les versions les plus récentes d'Excel, utilisez donc toujours la fonction RANK.EQ lorsque vous devez partager des feuilles avec d'autres.
RANG. AVG: lorsqu'une séquence de rangs contient les doublons, la fonction RANK.AVG attribue un rang moyen à chaque ensemble de doublons. Par conséquent, il sert le même objectif de classement des éléments, mais son objectif principal est de détecter les doublons.
Comme cet article traite du calcul des classements pour la liste des éléments, voyons comment la fonction RANK.EQ les classera. De plus, vous verrez son utilisation en combinaison avec la fonction COUNTIF.
Avant d'implémenter la formule, examinons brièvement la syntaxe de la fonction RANK.EQ.
Un aperçu de la fonction de classement d'Excel
Donc, il y a trois arguments dans la fonction RANK.EQ; numéro, référence et ordre. Les deux premiers arguments sont obligatoires, tandis que le troisième est facultatif. Pour exécuter RANK.EQ sur un tableau de nombres, vous devez toujours spécifier les deux premiers arguments.
Jetons un coup d'œil à la fonction de chaque argument:
- Numéro: cet argument fait référence au numéro dont vous souhaitez connaître le rang.
- Ref: L' argument Ref fait référence à un tableau de listes de nombres dans la séquence. Gardez à l'esprit que la référence ignore toutes les valeurs non numériques trouvées dans le tableau.
- Ordre: bien qu'il s'agisse d'un argument facultatif, vous pouvez spécifier la fonction RANK pour classer les éléments par ordre décroissant ou croissant en choisissant 0 ou 1 dans cet argument. Par défaut, si vous l'omettez, il classera les éléments par ordre décroissant.
Exemple de fonction de classement Excel
Donc, maintenant que vous comprenez les arguments de la fonction RANK.EQ, mettons-la en pratique sur un jeu de données pour voir comment cela fonctionne.
À titre d'exemple, jetez un œil aux exemples de données contenant des pourcentages de dix étudiants. Vous pouvez utiliser la fonction Rank.EQ pour calculer rapidement les classements en fonction des pourcentages de ces étudiants. Cela élimine le besoin de les calculer manuellement.
Cependant, il ignore les nombres lorsque des doublons sont impliqués, vous devrez donc peut-être utiliser d'autres fonctions décrites plus loin dans l'article. Voici la liste des étudiants avec leurs pourcentages finaux.
Le premier argument est le nombre que vous souhaitez classer, c'est-à-dire le pourcentage de James Walker et des autres élèves de la ligne pour cet exemple. Donc, sélectionnez la cellule B2 comme premier argument.
La plage de référence, dans ce cas, sera des cellules B2 à B11, et choisissons l'ordre croissant dans l'argument d'ordre. Ci-dessous vous pouvez voir l'implémentation de la fonction RANK.EQ. La formule ressemble à ceci:
=RANK.EQ(B2,$B$2:$B$11,0)
Appuyer sur Entrée exécutera la formule et vous obtiendrez le rang de James Walker.
Dans cet exemple, James Walker se classe 5 e dans la classe en fonction de son pourcentage, ce qui signifie que son pourcentage est 5 e le plus élevé par rapport au reste des nombres d'un tableau.
Utilisation de la fonction RANK en combinaison avec la fonction COUNTIF
Il est possible d'utiliser la fonction RANK.EQ combinée à la fonction COUNTIF pour arrêter le saut de nombres, mais elle ignorera également la duplication de rang. Bien qu'aucun numéro ne se répète deux fois, deux élèves ayant le même numéro peuvent obtenir des grades différents.
Pour mieux comprendre cela, voyons comment RANK.EQ fonctionne avec COUNTIF. La formule ressemble à ceci:
=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1La mise en œuvre de cette formule résoudra le problème du saut de nombres.
Il n'y a pas de duplication dans les rangs ci-dessus. Mais, James Walker et Gillian Tillman, qui étaient tous deux censés obtenir le même rang, sont désormais classés différemment.
Ainsi, l'utilisation de RANK.EQ avec COUNTIF a résolu la moitié du problème, mais ne parvient pas à générer le résultat souhaité.
Utilisation de la fonction SUMPRODUCT avec la fonction COUNTIF
Pour classer les étudiants dans une liste en attribuant les mêmes rangs pour des pourcentages équivalents sans sauter aucun nombre, vous pouvez utiliser la fonction SOMMEPROD avec COUNTIF.
Jetez un œil à la formule ci-dessous:
La formule peut sembler compliquée, mais c'est la meilleure façon de classer correctement les éléments. De cette façon, vous pouvez obtenir les résultats souhaités avec la duplication de rang et aucun saut de numéro.
Lors de la création du résultat pour vos élèves, vous pouvez directement utiliser la formule SOMMEPROD en remplacement de la fonction RANG. Pour les calculs de classement non duplicatifs, vous pouvez utiliser la fonction RANK.EQ seule ou avec la fonction COUNTIF.
Modification de l'ordre du résultat final
Sous l' onglet Données , cliquez sur le groupe Trier et filtrer et choisissez un ordre croissant pour classer les classements.
Comparez les résultats sur trois lignes côte à côte pour mieux comprendre le fonctionnement de chaque méthode de classement des éléments.
Classez facilement les éléments à l'aide des fonctions RANK.EQ et SUMPRODUCT
Voici comment utiliser les fonctions RANK et SUMPRODUCT en combinaison avec la fonction COUNTIF pour classer les éléments, avec ou sans duplications. Vous pouvez également décider s'il saute ou non un nombre de la séquence.
Selon la situation, vous pouvez utiliser diverses méthodes pour classer les nombres selon un ensemble de critères donnés. Voici un exemple de la façon dont les fonctions d'Excel peuvent vous aider à économiser beaucoup de temps et d'efforts dans les calculs manuels. Il y a beaucoup plus à explorer pour concevoir des feuilles de calcul Excel de qualité professionnelle.