Formule générique
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Sommaire
Pour classer les éléments dans une liste en utilisant un ou plusieurs critères, vous pouvez utiliser la fonction COUNTIFS. Dans l'exemple illustré, la formule en E5 est:
=COUNTIFS(groups,C5,scores,">"&D5)+1
où "groupes" est la plage nommée C5: C14 et "scores" est la plage nommée D5: D14. Le résultat est un classement pour chaque personne dans son propre groupe.
Remarque: bien que les données soient triées par groupe dans la capture d'écran, la formule fonctionnera correctement avec les données non triées.
Explication
Bien qu'Excel dispose d'une fonction RANK, il n'existe aucune fonction RANKIF pour effectuer un classement conditionnel. Cependant, vous pouvez facilement créer un RANK conditionnel avec la fonction COUNTIFS.
La fonction COUNTIFS peut effectuer un comptage conditionnel en utilisant deux ou plusieurs critères. Les critères sont saisis par paires plage / critères. Dans ce cas, le premier critère limite le décompte au même groupe, en utilisant la plage nommée "groupes" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
En soi, cela renverra le nombre total de membres du groupe dans le groupe «A», soit 5.
Le deuxième critère limite le décompte aux seuls scores supérieurs au "score actuel" de D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
Les deux critères fonctionnent ensemble pour compter les lignes où le groupe est A et le score est plus élevé. Pour le prénom de la liste (Hannah), il n'y a pas de scores plus élevés dans le groupe A, donc COUNTIFS renvoie zéro. Dans la rangée suivante (Edward), il y a trois scores dans le groupe A supérieurs à 79, donc COUNTIFS renvoie 3. Et ainsi de suite.
Pour obtenir un classement correct, nous ajoutons simplement 1 au nombre retourné par COUNTIFS.
Inversion de l'ordre de classement
Pour inverser l'ordre de classement et le classement dans l'ordre (c'est-à-dire que la plus petite valeur est classée n ° 1), utilisez simplement l'opérateur inférieur à ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Au lieu de compter les scores supérieurs à D5, cette version comptera les scores inférieurs à la valeur de D5, inversant ainsi l'ordre de classement.
Doublons
Comme la fonction RANK, la formule de cette page attribuera aux valeurs en double le même rang. Par exemple, si une valeur spécifique se voit attribuer un rang de 3 et qu'il existe deux instances de la valeur dans les données en cours de classement, les deux instances recevront un rang de 3 et le rang suivant attribué sera de 5. Pour imiter le comportement de la fonction RANK.AVG, qui attribuerait un rang moyen de 3,5 dans un tel cas, vous pouvez calculer un «facteur de correction» avec une formule comme celle-ci:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Le résultat de cette formule ci-dessus peut être ajouté au classement d'origine pour obtenir un classement moyen. Lorsqu'une valeur n'a pas de doublons, le code ci-dessus renvoie zéro et n'a aucun effet.
