Formule générique
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Sommaire
Pour obtenir la valeur maximale d'un ensemble de données en fonction de plusieurs critères, vous pouvez utiliser une formule matricielle basée sur les fonctions MAX et IF. Dans l'exemple illustré, la formule en I6 est:
(=MAX(IF(color=G6,IF(item=H6,price))))
Avec une couleur de "rouge" et un élément de "chapeau", le résultat est 11,00 $
Remarque: Il s'agit d'une formule matricielle et doit être saisie en utilisant Ctrl + Maj + entrée
Explication
Cet exemple utilise les plages nommées suivantes: "color" = B6: B14, "item" = C6: C14 et "price" = E6: E14. Le but est de trouver le prix maximum pour une couleur et un article donnés.
Cette formule utilise deux fonctions IF imbriquées, enveloppées dans MAX pour renvoyer le prix maximum avec deux critères. En commençant par un test logique de la première instruction IF, color = G6, les valeurs de la plage nommée "couleur" (B6: B14) sont comparées à la valeur de la cellule G6, "rouge". Le résultat est un tableau comme celui-ci:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Dans le test logique pour la deuxième instruction IF, item = H6, les valeurs de l'élément de plage nommé (C6: C14) sont comparées à la valeur de la cellule H6, "hat". Le résultat est un tableau comme celui-ci:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
La "valeur si vrai" pour la deuxième instruction IF est la plage nommée "prix" (E6: E14), qui est un tableau comme celui-ci:
(11;8;9;12;9;10;9;8;7)
Un prix est renvoyé pour chaque article de cette plage uniquement lorsque le résultat des deux premiers tableaux ci-dessus est VRAI pour les articles aux positions correspondantes. Dans l'exemple illustré, le tableau final à l'intérieur de MAX ressemble à ceci:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Notez que les seuls prix qui «survivent» sont ceux dans une position où la couleur est «rouge» et l'article est «chapeau».
La fonction MAX renvoie alors le prix le plus élevé, ignorant automatiquement les valeurs FALSE.
Syntaxe alternative utilisant la logique booléenne
Vous pouvez également utiliser la formule matricielle suivante, qui n'utilise qu'une seule fonction IF avec une logique booléenne:
(=MAX(IF((color=G6)*(item=H6),price)))
L'avantage de cette syntaxe est qu'il est plus facile d'ajouter des critères supplémentaires sans ajouter de fonctions IF imbriquées supplémentaires. Si vous avez besoin d'une logique OU, utilisez l'addition au lieu de la multiplication entre les conditions.
Avec MAXIFS
La fonction MAXIFS, introduite dans Excel 2016, est conçue pour calculer les maximums en fonction d'un ou plusieurs critères sans avoir besoin d'une formule matricielle. Avec MAXIFS, la formule dans I6 est:
=MAXIFS(price,color,G6,item,H6)
Remarque: MAXIFS ignorera automatiquement les cellules vides qui répondent aux critères. En d'autres termes, MAXIFS ne traitera pas les cellules vides répondant aux critères comme zéro. D'autre part, MAXIFS renverra zéro (0) si aucune cellule ne correspond aux critères.
