Téléchargez l'exemple de classeur
Ce didacticiel montrera comment calculer « grand si » ou « petit si », en récupérant la nième valeur la plus grande (ou la plus petite) en fonction de critères.
Fonctions GRAND & PETIT
La fonction LARGE est utilisée pour calculer la nième plus grande valeur (k) dans un tableau, tandis que la fonction SMALL renvoie la nième valeur la plus petite.
Pour créer un "Large If", nous utiliserons la fonction LARGE avec la fonction SI dans une formule matricielle.
GRAND SI
En combinant LARGE (ou SMALL) et IF dans une formule matricielle, nous pouvons essentiellement créer une fonction «LARGE IF» qui fonctionne de la même manière que la formule SUMIF intégrée. Parcourons un exemple.
Nous avons une liste des notes obtenues par les étudiants dans deux matières différentes :
Supposons qu'on nous demande de trouver les trois meilleures notes obtenues 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 LARGE comme ceci :
=LARGE(SI(=, ),)
=LARGE(SI($C$2:$C$10=$F3,$D$2:$D$10),G$2)
Lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE pour obtenir les accolades autour de la formule.
Comment fonctionne la formule ?
La formule fonctionne en évaluant chaque cellule de notre plage de critères comme VRAI ou FAUX.
Trouver la meilleure note (k=1) en mathématiques :
=LARGE(SI($C$2:$C$10=$F3,$D$2:$D$10),G$2)
=LARGE(SI({VRAI; FAUX;FAUX; VRAI; FAUX; VRAI; FAUX; VRAI; FAUX}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Ensuite, la fonction SI remplace chaque valeur par FALSE si sa condition n'est pas remplie.
=LARGE({0.81;FAUX;FAUX;0.42;FAUX;0.63;FAUX;0.58;FAUX},1)
Maintenant, la fonction LARGE ignore les valeurs FALSE et calcule la plus grande (k=1) des valeurs restantes (0,81 est la plus grande valeur entre 0,42 et 0,81).
PETIT SI
La même technique peut également être appliquée avec la fonction SMALL à la place.
=PETIT(SI($C$2:$C$10=$F3,$D$2:$D$10),G$2)
GRAND SI avec plusieurs critères
Pour utiliser LARGE IF avec plusieurs critères (similaire au fonctionnement de la formule SUMIFS intégrée), imbriquez simplement plus de fonctions IF dans la fonction LARGE comme suit :
=LARGE(SI(=, SI(=, )),)
=LARGE(SI($D$2:$D$18=$H3,SI($B$2:$B$18=$G3,$E$2:$E$18)),I$2)
Une autre façon d'inclure plusieurs critères est de multiplier les critères ensemble comme indiqué dans cet article
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 saisir 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
