Formule SI CENTILE – Excel et Google Sheets
Télécharger le Classeur Exemple
Ce tutoriel montre comment calculer une formule « Percentile Si », en récupérant le kième percentile seulement des valeurs qui répondent à certains critères.
Fonction CENTILE
La fonction CENTILE est utilisée pour calculer le Kième percentile des valeurs d’un intervalle où k est la valeur du percentile entre 0 et 1 inclusivement.
=CENTILE($D$2:$D$10;0,75)
Pour créer une formule « Si Percentile », nous allons utiliser la fonction CENTILE avec la fonction SI dans une formule de tableau.
CENTILE SI
En combinant les fonctions CENTILE et SI dans une formule de tableau, nous pouvons essentiellement créer une fonction « PERCENTILE SI » qui fonctionne de la même manière que la fonction intégrée MOYENNE.SI.
Dans cet exemple, nous avons une liste de notes obtenues par des étudiants dans deux matières différentes :
Nous souhaitons trouver les 75e percentiles des notes obtenues pour chaque matière comme suit : 
Pour ce faire, nous pouvons imbriquer une fonction SI avec le sujet comme critère à l’intérieur de la fonction CENTILE comme suit :
=CENTILE(SI(<plage critère>=<critère>, <plage valeurs>),<percentile>)=CENTILE(SI($C$2:$C$10=$F2;$D$2:$D$10);0,75)Lorsque vous utilisez Excel 2019 et les versions antérieures, vous devez entrer la formule de tableau en appuyant sur CTRL + MAJ + ENTRÉE (au lieu d’ENTRÉE seulement), ce qui indique à Excel que la formule est une formule de tableau. Vous saurez qu’il s’agit d’une formule de tableau grâce aux crochets qui entourent la formule (voir image du haut).
Comment fonctionne la formule?
La fonction SI évalue chaque cellule de notre plage de critères comme VRAI ou FAUX, créant ainsi deux tableaux :
=CENTILE(FI({VRAI; FAUX; FAUX; VRAI; FAUX; VRAI; FAUX; 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 tableau unique, remplaçant chaque valeur par FAUX 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 CENTILE ignore les valeurs FAUX et calcule le 75e percentile des valeurs restantes (0,72 est le 75e percentile).
CENTILE SI avec Plusieurs Critères
Pour calculer une formule CENTILE SI avec plusieurs critères (de la même manière que la fonction intégrée MOYENNE.SI.ENS), vous pouvez simplement multiplier les critères entre eux :
=CENTILE(SI((<plage critère1>=<critère1>)*(<plage critère2>=<critère2>),<plage valeurs>),<percentile>)=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 est d’imbriquer plusieurs instructions SI dans la formule.
Conseils 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 2019 ou une version plus récente, vous pouvez saisir la formule sans CTRL + MAJ + ENTRÉE.
- Pour retrouver les noms des étudiants qui ont obtenu les meilleures notes, combinez cette formule avec INDEX / EQUIV.
CENTILE SI dans Google Sheets
La formule CENTILE SI fonctionne exactement de la même manière dans Google Sheets et dans Excel, sauf que vous devez entourer la formule de la fonction ARRAYFORMULA pour indiquer à Google Sheets qu’il s’agit d’une formule de tableau.
