Téléchargez l'exemple de classeur
Ce tutoriel montrera comment calculer le « centile si », en récupérant le ke centile dans une rage de valeurs avec critères.
Fonction PERCENTILE
La fonction PERCENTILE est utilisée pour calculer le ke centile des valeurs dans une plage où k est la valeur du centile entre 0 et 1 inclus.
=CENTILE($D$2:$D$10,075)
Cependant, cela prend le centile de toute la plage de valeurs. Au lieu de cela, pour créer un "centile si", nous utiliserons la fonction PERCENTILE avec la fonction SI dans une formule matricielle.
POURCENTILE SI
En combinant PERCENTILE et IF dans une formule matricielle, nous pouvons essentiellement créer une fonction "PERCENTLE IF" qui fonctionne de la même manière que la fonction intégrée AVERAGEIF. Parcourons un exemple.
Nous avons une liste des scores obtenus par les étudiants dans deux matières différentes :
Supposons qu'on nous demande de trouver les 75e centiles des scores obtenus pour chaque matière comme suit :
Pour ce faire, nous pouvons imbriquer une fonction SI avec le matière comme nos critères à l'intérieur de la fonction PERCENTILE comme ceci :
=CENTILE(SI(=, ),)
=CENTILE(SI($C$2:$C$10=$F3,$D$2:$D$10),0.75)
Lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule matricielle en appuyant sur CTRL + MAJ + ENTRÉE (à la place de ENTRER), indiquant à Excel que la formule dans une formule matricielle. Vous saurez que c'est une formule matricielle par les accolades qui apparaissent autour de la formule (voir l'image du haut).
Comment fonctionne la formule ?
La fonction If évalue chaque cellule de notre plage de critères comme VRAI ou FAUX, créant deux tableaux :
=CENTILE(SI({VRAI; FAUX;FAUX; VRAI; FAUX; VRAI; FAUX; VRAI; FAUX}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Ensuite, la fonction SI crée un seul tableau, remplaçant chaque valeur par FALSE si sa condition n'est pas remplie.
=CENTILE({0.81;FAUX;FAUX;0.42;FAUX;0.63;FAUX;0.58;FAUX}, 0.75)
Maintenant, la fonction PERCENTILE ignore les valeurs FAUX et calcule le 75e centile des valeurs restantes (0,72 est le 75e centile).
PERCENTILE SI avec plusieurs critères
Pour calculer PERCENTILE IF avec plusieurs critères (similaire au fonctionnement de la fonction intégrée AVERAGEIFS), vous pouvez simplement multiplier les critères ensemble :
=CENTILE(SI((=)*(=),),)
=CENTILE(SI(($D$2:$D$10=$H2)*($C$2:$C$10=$G2),$E$2:$E$10),0.75)
Une autre façon d'inclure plusieurs critères consiste à imbriquer plus d'instructions IF dans la formule.
Trucs et astuces:
- Dans la mesure du possible, référencez toujours la position (k) à partir d'une cellule d'aide et verrouillez la référence (F4) car cela facilitera le remplissage automatique des formules.
- Si vous utilisez Excel 2022 ou une version plus récente, vous pouvez entrer la formule sans CTRL + MAJ + ENTRÉE.
- Pour récupérer les noms des étudiants qui ont obtenu les meilleures notes, combinez-le avec INDEX MATCH.
POURCENTILE SI dans Google Sheets
La fonction PERCENTILE SI fonctionne exactement de la même manière dans Google Sheets que dans Excel :
