Exceller

Rang si formule

Rank If Formula

Formule Excel: classer si formuleFormule générique
= COUNTIFS (criteria_range,criteria,values,'>'&value)+1
Résumé

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 le 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 ait un Fonction RANK , il n'y a pas de fonction RANKIF pour effectuer un rang 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 le plage nommée 'groupes' (C5: C14):



 
= COUNTIFS (groups,C5) // returns 5

En soi, cela retournera le nombre total de membres du groupe dans le groupe 'A', qui est de 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 à ():

calculer l'âge de dob dans excel
 
= 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 le 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 recevra un rang de 3, et le prochain rang attribué sera 5. Pour imiter le comportement du Fonction RANK.AVG , ce 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.

Auteur Dave Bruns


^