Comment mettre en évidence les données en fonction du rang centile dans Excel

Un rang centile indique la position d'une valeur de données particulière par rapport à d'autres valeurs de données dans un échantillon. Les centiles sont notamment utilisés pour déterminer les performances aux tests standardisés. Si un enfant obtient un score au 90e centile à un test standardisé, cela signifie que son score est supérieur à 90 pour cent des autres enfants qui passent le test.

Une autre façon de voir les choses est de dire que le score de l'enfant se situe dans le top 10 pour cent de tous les enfants qui passent le test.

Les centiles sont souvent utilisés dans l'analyse des données comme méthode de mesure des performances d'un sujet par rapport au groupe dans son ensemble - par exemple, pour déterminer le classement en centiles de chaque employé sur la base d'un revenu annuel.

Dans Excel, vous pouvez facilement obtenir des rangs centiles clés à l'aide de la fonction PERCENTILE. Cette fonction nécessite deux arguments : une plage de données et le score centile que vous souhaitez voir.

Dans l'exemple illustré, la valeur de la cellule D7 est le résultat de la formule suivante, qui tire le 75e centile en fonction des données de la plage B3:B20 :

=CENTILE($B$3:$B$20,0.75)

Cette formule vous indique que tout employé dont le chiffre d'affaires dépasse 52 651 $ fait partie des 75 % des plus performants.

La valeur de la cellule D16 est le résultat de la formule suivante, qui tire le 25e centile en fonction des données de la plage B3:B20 :

=CENTILE($B$3:$B$20,0.25)

Cette formule vous indique que tout employé dont les revenus sont inférieurs à 24 656 $ se situe dans les 25 pour cent inférieurs des artistes interprètes ou exécutants.

À l'aide de ces marqueurs de centile, cet exemple applique une mise en forme conditionnelle de sorte que toute valeur du 75e centile soit colorée en vert et toute valeur du 25e centile soit colorée en rouge.

Pour créer cette règle de mise en forme de base, procédez comme suit :

Sélectionnez les cellules de données dans votre plage cible (cellules B3:B20 dans cet exemple), cliquez sur l'onglet Accueil du ruban Excel, puis sélectionnez Mise en forme conditionnelle → Nouvelle règle.

Cela ouvre la boîte de dialogue Nouvelle règle de formatage.

Dans la zone de liste en haut de la boîte de dialogue, cliquez sur l'option Utiliser une formule pour déterminer les cellules à formater.

Cette sélection évalue les valeurs en fonction d'une formule que vous spécifiez. Si une valeur particulière est évaluée à VRAI, la mise en forme conditionnelle est appliquée à cette cellule.

Dans la zone de saisie de la formule, entrez la formule affichée avec cette étape.

Dans cette formule, vous évaluez si les données de la cellule cible (B3) se situent dans le 25e centile. Si tel est le cas, la mise en forme conditionnelle sera appliquée.

=B3<>

Notez que dans la formule, vous excluez les symboles de référence absolue du dollar ($) pour la cellule cible (B3). Si vous cliquez sur la cellule B3 au lieu de taper la référence de cellule, Excel rend automatiquement votre référence de cellule absolue. Il est important de ne pas inclure les symboles de référence absolue du dollar dans votre cellule cible, car vous avez besoin d'Excel pour appliquer cette règle de mise en forme en fonction de la valeur de chaque cellule.

Cliquez sur le bouton Formater.

Cela ouvre la boîte de dialogue Format de cellule, dans laquelle vous disposez d'un ensemble complet d'options pour formater la police, la bordure et le remplissage de votre cellule cible. Une fois que vous avez terminé de choisir vos options de formatage, cliquez sur le bouton OK pour confirmer vos modifications et revenir à la boîte de dialogue Nouvelle règle de formatage.

De retour dans la boîte de dialogue Nouvelle règle de mise en forme, cliquez sur le bouton OK pour confirmer votre règle de mise en forme.

À ce stade, vous devriez être dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle.

Cliquez sur le bouton Nouvelle règle.

Cela ouvre la boîte de dialogue Nouvelle règle de formatage.

Dans la zone de liste en haut de la boîte de dialogue, cliquez sur l'option Utiliser une formule pour déterminer les cellules à formater. Cette sélection évalue les valeurs en fonction d'une formule que vous spécifiez. Si une valeur particulière est évaluée à VRAI, la mise en forme conditionnelle est appliquée à cette cellule.

Dans la zone de saisie de la formule, entrez la formule affichée ici.

Dans cette formule, vous évaluez si les données de la cellule cible (B3) se situent dans le 75e centile. Si tel est le cas, la mise en forme conditionnelle sera appliquée.

=B3>=PERCENTILE($B$3:$B$20,0.75)

Cliquez sur le bouton Formater.

Cela ouvre la boîte de dialogue Format de cellule, dans laquelle vous disposez d'un ensemble complet d'options pour formater la police, la bordure et le remplissage de votre cellule cible. Une fois que vous avez terminé de choisir vos options de formatage, cliquez sur le bouton OK pour confirmer vos modifications et revenir à la boîte de dialogue Nouvelle règle de formatage.

De retour dans la boîte de dialogue Nouvelle règle de mise en forme, cliquez sur le bouton OK pour confirmer votre règle de mise en forme.

Si vous devez modifier votre règle de mise en forme conditionnelle, placez simplement votre curseur dans l'une des cellules de données de votre plage de mise en forme, puis accédez à l'onglet Accueil et sélectionnez Mise en forme conditionnelle → Gérer les règles. Cela ouvre la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle. Cliquez sur la règle que vous souhaitez modifier, puis cliquez sur le bouton Modifier la règle.