Je suis certaine que vous trouverez ces astuces pour la mise en forme conditionnelle du classement des percentiles très utiles. Vous pouvez les utiliser dans vos tâches quotidiennes de feuilles de calcul Google Sheets.
Mise en Forme Conditionnelle du Classement des Percentiles
Imaginons un exemple de mise en forme conditionnelle du classement des percentiles. Dans un test écrit, si votre classement percentil est de 0,75, cela signifie que vous êtes meilleur que 75 % des autres candidats qui ont passé le test. En d’autres termes, vous avez fait mieux que 75 % des autres candidats.
Comment mettre en évidence les 25 % des meilleurs scores ?
Une formule personnalisée de mise en forme conditionnelle utilisant la fonction Percentile peut mettre en évidence une valeur qui se situe à un certain percentile, par exemple le 0,25e percentile, le 0,5e percentile, le 0,75e percentile, etc.
De plus, vous pouvez mettre en évidence les lignes situées entre certains classements percentiles, comme les valeurs percentiles situées entre le 0,5e et le 0,75e. Voir la capture d’écran ci-dessous.
Dans cet exemple, le score de 991 dans la cellule B2 est au 100e percentile, le score de 979 dans la cellule B6 est au 75e percentile, le score de 928 dans la cellule B10 est au 50e percentile et le score de 820 dans la cellule B14 est au 25e percentile.
Bien qu’il existe des fonctions Percentile et Percentrank dans Google Sheets, la première fonction est très utile pour la mise en forme conditionnelle.
Dans la cellule E5, j’ai utilisé la formule de tableau suivante pour obtenir les valeurs percentiles mentionnées ci-dessus :
=ArrayFormula(percentile(B2:B19,G5:G8))
Cette formule n’est pas nécessaire pour la mise en forme conditionnelle du classement des percentiles dans Google Sheets. Je l’ai insérée simplement à titre d’information.
Alors, comment mettre en évidence les valeurs en fonction du Classement Percentile dans Google Sheets ?
Formule Personnalisée pour la Mise en Forme Conditionnelle du Classement Percentile
Tout d’abord, comprenez les couleurs que j’ai utilisées dans l’exemple ci-dessus.
Les lignes mises en évidence en vert foncé : Les valeurs de la colonne B qui se situent entre le 0,75e et le 1er percentile (les classements percentiles sont attribués de 0 à 1).
Bleu foncé : Représente le classement percentil entre le 0,5e et le 0,75e.
Couleur jaune foncé : Représente le classement percentil entre le 0,25e et le 0,5e.
Rouge : Représente le classement percentil entre 0 et 0,25.
Maintenant, vous devez appliquer quatre formules personnalisées à la mise en forme conditionnelle. Voici les quatre formules personnalisées pour la mise en forme conditionnelle du classement percentiles dans les feuilles de calcul.
Formule 1 :
=$B2<=percentile(B$2:B$18,1)Formule 2 :
=$B2<=percentile(B$2:B$18,0.75)Remarque : Les deux formules personnalisées ci-dessus et les deux formules suivantes sont basées sur l’ordre de couleur mentionné ci-dessus.
Formule 3 :
=$B2<=percentile(B$2:B$18,0.5)Formule 4 :
=$B2<=percentile(B$2:B$18,0.25)Maintenant que vous avez les formules en main, comment les appliquer ?
La mise en forme conditionnelle est liée à la mise en forme des feuilles. Vous pouvez trouver l’option « Mise en forme conditionnelle… » dans le menu Format.
Voici le paramètre que vous devez suivre :
- Sélectionnez la plage de cellules à appliquer (par exemple, B2:B18).
- Sous le menu « Format », sélectionnez « Mise en forme conditionnelle… ».
- Ajoutez les règles une par une en utilisant les formules personnalisées ci-dessus.
- Assurez-vous que les règles personnalisées sont dans l’ordre indiqué.
- Cliquez sur « Terminé » une fois que vous avez fini d’ajouter toutes les règles.
- Si l’ordre des règles personnalisées n’est pas respecté, vous pouvez les déplacer en les faisant glisser.
Voilà tout ce qu’il faut savoir sur la mise en forme conditionnelle du classement des percentiles dans Google Sheets.
Remarque : Dans l’exemple ci-dessus, j’ai trié les valeurs (les scores) par ordre décroissant. Mais ce n’est pas obligatoire.
Conseils Supplémentaires
Si vous souhaitez simplement mettre en évidence les valeurs percentiles sans mettre en évidence toutes les valeurs dans une certaine plage de pourcentages, voici comment faire :
Cela semble simple, n’est-ce pas ? Oui, c’est simple, mais attention à un « piège ». Je vais vous expliquer cela dans le nouveau titre ci-dessous.
Comment Mettre en Évidence le Percentile dans Google Sheets
Par rapport aux classements percentiles, il est assez facile de mettre en évidence les percentile dans Google Sheets.
Supposons que vous souhaitiez mettre en évidence le 75e percentile dans les mêmes données que précédemment. Voici la formule qui renvoie le 75e percentile :
=PERCENTILE(B2:B18,0.75)
Mais pour la mise en forme conditionnelle, utilisez la formule personnalisée suivante :
=B2=PERCENTILE($B$2:$B$18,0.75)Cela mettra en évidence la valeur 979 dans la cellule B6. Mais il y a une subtilité !
La formule ci-dessus ne fonctionne pas toujours. Savez-vous pourquoi ?
C’est parce que parfois, la valeur renvoyée par la fonction Percentile peut ne pas être membre de la plage B2:B18. Cela se produit lorsque la fonction interpole entre les valeurs pour calculer le percentile.
Par exemple, voici cette formule :
=PERCENTILE($B$2:$B$18,0.7)
Elle renvoie la valeur 947,8 qui n’est pas un membre de la plage B2:B18. Quelle est donc la solution ?
La solution consiste à faire en sorte que la valeur qui n’est pas membre devienne un membre de la plage.
Mettre en Évidence le Percentile Quand Il ne Fait Pas Partie des Données
Assurez-vous que les données sont triées (triées par colonne B) par ordre croissant (du plus bas au plus haut).
Utilisez la formule suivante avec la fonction Match pour renvoyer la position relative de la valeur percentile dans la plage B2:B18 :
=match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)
Utilisez cela comme décalage de ligne dans l’Index :
=index(B2:B18,match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1))
Cela renverra la valeur 940 qui correspond au 70e percentile et qui fait partie de la plage de données. Mais cela ne fonctionnera pas pour la mise en forme conditionnelle.
Au lieu d’utiliser la fonction Index, utilisez la formule suivante comme formule personnalisée pour la mise en forme conditionnelle :
=address(row(),column(),4)=("B"&match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)+1)La deuxième partie de la formule qui commence par « B » renvoie l’adresse de cellule $B$13 de la valeur percentile.
La fonction Adresse avec les fonctions Ligne et Colonne comme arguments renvoie l’adresse actuelle de la cellule. Lorsque les deux correspondences, cette cellule est mise en évidence.
Le + 1 à la fin de la formule indique le nombre de lignes au-dessus de B2:B18.
Ressources Supplémentaires
- Comment Mettre en Évidence la Valeur Renvoyée par la Fonction Vlookup dans Google Sheets.
- Trouver Toutes les Cellules ayant une Mise en Forme Conditionnelle dans Google Sheets.
- Mettre en Évidence la Valeur d’Intersection dans Google Sheets lors d’une Recherche à Double Sens
- Comparer Deux Cellules dans Google Sheets et les Mettre en Évidence
C’était tout pour la mise en forme conditionnelle du classement des percentiles dans Google Sheets. J’espère que ces astuces vous seront utiles dans vos feuilles de calcul.
