Exceller

Maximum si plusieurs critères

Maximum If Multiple Criteria

Formule Excel: maximum si plusieurs critèresFormule générique
{= MAX ( IF (rng1=criteria1, IF (rng2=criteria2,values)))}
Résumé

Pour obtenir la valeur maximale dans 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'un formule matricielle et doit être entré en utilisant Ctrl + Maj + entrée



Explication

Cet exemple utilise ce qui suit plages nommées : '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:



 
{TRUETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSE}

Dans le test logique de 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:

 
{TRUETRUETRUETRUETRUETRUEFALSEFALSEFALSE}

La `` valeur si vraie '' pour la deuxième instruction IF est la plage nommée `` prix '' (E6: E14), qui est un tableau comme celui-ci:

 
{118912910987}

Un prix est retourné pour chaque article de cette gamme seul lorsque le résultat des deux premiers tableaux ci-dessus est VRAI pour les éléments aux positions correspondantes. Dans l'exemple illustré, le tableau final à l'intérieur de MAX ressemble à ceci:



 
{1189FALSEFALSEFALSEFALSEFALSEFALSE}

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 logique booléenne :

ajouter un champ calculé au tableau croisé dynamique
 
{= 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

Le Fonction MAXIFS , introduit dans Excel 2016, est conçu 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 sera automatiquement ignorer cellules vides répondant 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 volonté renvoie zéro (0) si aucune cellule ne correspond aux critères.

Auteur Dave Bruns


^