Excel : comment obtenir la valeur maximale avec conditions (fonction MAX.SI)

Ce tutoriel présente plusieurs façons d'obtenir la valeur maximale dans Excel en fonction d'une ou de plusieurs conditions que vous spécifiez.

Dans notre tutoriel précédent, nous avons examiné les utilisations courantes de la fonction MAX conçue pour renvoyer le plus grand nombre dans un ensemble de données. Dans certaines situations, cependant, vous devrez peut-être trier vos données en trouvant la valeur maximale en fonction de certains critères.

Dans Excel, nous ne pouvons pas simplement utiliser la fonction MAX par défaut avec une condition. Si vous souhaitez obtenir la valeur maximale d'une plage à l'aide d'une condition spécifique, vous avez besoin d'une formule qui combine les deux fonctions MAX et SI.

Table des matières

Formule Excel MAX SI

Utilisation de la fonction MAX SI dans une formule matricielle

Formule MAX SI avec plusieurs critères

MAX SI sans formule matricielle

Choses à retenir sur la formule Excel Max SI

Formule Excel MAX SI

Microsoft Excel n'avait pas de fonction MAX.SI intégrée pour obtenir la valeur maximale en fonction des conditions. Cependant, ils ont introduit la fonction MAX.SI.ENS, et maintenant les utilisateurs d'Excel 2019 et ceux ayant un abonnement Office 365 peuvent calculer le max conditionnel de manière simple.

Dans Excel 2013 et les versions antérieures, vous pouvez créer votre propre formule matricielle en combinant la fonction MAX avec une instruction SI :

{=MAX (SI(plage_critères = critère; plage_max))}

La fonction Max est utilisée avec la fonction SI dans Excel pour trouver la valeur maximale à partir d’un ensemble de données avec des critères définis. Où la fonction MAX renvoie la valeur maximale de la plage de valeurs sélectionnée et la fonction SI aide à trouver ce nombre avec les critères choisis.

Syntaxe de la fonction MAX dans Excel :

=MAX(nombre 1; [nombre 2] ;…)

Arguments de la formule MAX :

nombre1 : le nombre1 fait référence à une valeur numérique ou une plage qui contient une valeur numérique.
nombre2 [facultatif] : le nombre2 fait référence à une valeur numérique ou à une plage qui contient une valeur numérique.

Syntaxe de la fonction SI dans Excel :

Ci-dessous, la formule SI dans Excel :

=SI(test_logique; [valeur_si_vrai]; [valeur_si_faux])

Arguments de la formule SI :

Test logique : qui est une valeur d'expression logique pour vérifier que la valeur est : VRAI ou FAUX.
Valeur_si_vrai : qui est une valeur que la fonction doit retourner si le test logique est vrai.
Valeur_si_faux : qui est une valeur que la fonction doit retourner si le test logique est faux.

Pour voir comment cette formule générique MAX SI fonctionne sur des données réelles, considérez l'exemple suivant. Supposons que vous ayez une table avec les résultats de saut en longueur de plusieurs élèves. Le tableau comprend les données pour trois rondes et vous recherchez le meilleur résultat d'un athlète particulier, Ahmed.

Avec les noms des élèves dans A2:A10 et les distances dans C2:C10, la formule prend cette forme :

=MAX(SI(A2:A10="Ahmed"; C2:C10))

Remarque : N'oubliez pas qu'une formule matricielle doit toujours être entrée en appuyant simultanément sur les touches « Ctrl + Shift + Entrée ». En conséquence, elle est automatiquement entourée de crochets comme indiqué dans la capture d'écran ci-dessous (la saisie manuelle des accolades ne fonctionnera pas!).

Dans les feuilles de calcul réelles, il est plus pratique de saisir le critère dans une cellule, de sorte que vous pouvez facilement changer la condition sans modifier la formule. Donc, il sera mieux de taper le nom souhaité dans une cellule (F1 dans cet exemple). Ainsi, la formule sera comme suit :

=MAX(SI(A2:A10 = F1; C2:C10))

Comment fonctionne cette formule ?

La fonction SI dans la formule est utilisée pour mettre la logique spécifique aux critères sur lesquels on essaie de trouver la valeur maximale. La fonction SI aide à exécuter la logique dans l'ensemble de données et à trouver les résultats correspondant au test logique.
La fonction Max identifie la valeur maximale de tous les résultats correspondant au test logique.
Dans le test logique de la fonction SI, nous comparons la liste des noms (A2: A10) avec le nom cible (F1). Le résultat de cette opération est un tableau de VRAI et FAUX, où les valeurs VRAI représentent des noms qui correspondent au nom cible (Ahmed) : {FAUX; FAUX; FAUX; VRAI; VRAI; VRAI; FAUX; FAUX; FAUX}
Pour l'argument « valeur_si_vrai », nous fournissons les résultats du saut en longueur (C2:C10). Donc, si le test logique est évalué à « VRAI », le nombre correspondant de la colonne C est renvoyé. L'argument « valeur_si_faux » est omis, ce qui signifie qu'il aura juste une valeur « FAUX » lorsque la condition n'est pas remplie :

Remarque : Pour voir les tableaux internes discutés ci-dessus, sélectionnez la partie correspondante de la formule dans votre feuille de calcul et appuyez sur la touche F9. Pour quitter le mode d'évaluation de formule, appuyez sur la touche « Échap ».

Une démonstration détaillée de l'utilisation de la formule Max SI est expliquée dans la section qui suit.

Utilisation de la fonction MAX SI dans une formule matricielle

Supposons que vous devez vérifier la catégorie de vente la plus importante. En utilisant uniquement la fonction Max, vous pouvez vérifier uniquement la valeur maximale, mais vous ne pouvez pas vérifier la valeur maximale des ventes pour chaque catégorie.

Dans un tel cas, vous pouvez combiner les deux fonctions MAX et SI dans une formule matricielle afin de faciliter la tâche pour trouver la catégorie ayant la plus grande valeur des ventes.

Considérez l'exemple ci-dessous où nous avons le nom du produit, la catégorie et le nombre des ventes :

Nous allons maintenant appliquer la fonction Excel MAX SI pour connaître la valeur maximale des ventes en suivant les étapes suivantes.

Tout d'abord, nous allons créer un nouveau tableau avec les noms des catégories pour afficher le résultat comme indiqué ci-dessous :

Sélectionnez la cellule F2.
Appliquer la formule MAX SI comme suit : =MAX(SI(B$2:B13 = E2; C2:C13)).

Dans la formule ci-dessus, nous avons appliqué les fonctions MAX et SI, puis nous avons sélectionné la colonne B où nous avons répertorié le nom de la catégorie comme B2:B13, puis nous avons vérifié la condition en sélectionnant la cellule E2 où la colonne E n'est rien d'autre que « Nom de catégorie ».

Maintenant, la fonction SI vérifie le nom de la catégorie entière dans la colonne B de B2 à B13 si elle correspond au nom de la catégorie mentionné dans la cellule E2. Si le nom de la catégorie correspond, la fonction retournera la plus grande valeur de vente de la colonne C sélectionnée de C2 à C13.

Verrouillez les cellules en appuyant sur F4.

La formule sera donc : =MAX(SI($B$2:$B$13 = E2; $C$2:$C$13) ).

¾ Une fois la formule MAX SI terminée, en fermant tous les crochets, maintenez les touches CTRL + SHIFT puis appuyez sur Entrée afin que vous obteniez une formule matricielle comme indiquée ci-dessous :

Ici, comme nous pouvons le voir dans la capture d'écran ci-dessus, la fonction MAX SI vérifie le nom de la catégorie dans la colonne B ainsi que dans la colonne E et si elle correspond à la condition, elle renverra la plus grande valeur. Dans cet exemple, nous avons obtenu la sortie 532 qui est la valeur de vente la plus élevée de la catégorie « Poussette ».

Faites maintenant glisser les formules MAX SI pour toutes les cellules afin d'obtenir le résultat indiqué ci-dessous :

Formule MAX SI avec plusieurs critères

Excel n'a pas de fonction MAX SI, mais nous avons vu que nous pouvons créer notre propre formule, en combinant les fonctions MAX et SI. Mais que diriez-vous d'un plus grand rêve - MAX SI avec plusieurs critères ? Pourrions-nous également créer notre propre fonction MAX SI avec plusieurs SI ?

Dans le cas où vous devez trouver la valeur maximale en fonction de plusieurs conditions, vous avez deux possibilités :

Utilisez des instructions SI imbriquées pour inclure des critères supplémentaires :

{=MAX(SI(plage_critères1 = critère1; SI(plage_critères2 = critère2; plage_max)))}

Ou :

Gérez plusieurs critères en utilisant l'opérateur de multiplication :

{=MAX(SI((plage_critères1 = critère1) * (plage_critères2 = critère2); plage_max))}

Tout cela peut être déroutant à comprendre. Alors, testons cette formule en l'exécutant sur l'exemple ci-dessous où vous avez la quantité de ventes par mois, par semaine et par date.

Disons que vous devez obtenir les ventes les plus élevées pour la semaine 2 du mois janvier. Donc, ici, vous avez deux conditions différentes, le mois est « Janv » et la semaine est « Semaine-2 ».

La formule sera :

=MAX (SI(A2:A24 = F2; SI(B2:B24 = G2; D2:D24);))

Ou en utilisant l’opérateur de multiplication :

=MAX(SI((A2:A24 = F2) * (B2:B24 = G2); D2:D24))

Et, lorsque vous le saisissez sous forme de formule matricielle, la formule devient :

{=MAX (SI(A2:A24 = F2; SI(B2:B24 = G2; D2:D24);))} ou {=MAX(SI((A2:A24 = F2) * (B2:B24 = G2); D2:D24))}

Comme indiqué dans la capture d'écran ci-dessus, les formules utilisées produisent le même résultat, de sorte que celle à utiliser est une question de préférence personnelle. Pour moi, la formule avec la logique booléenne est plus facile à lire et à construire. Ainsi, elle permet d'ajouter autant de conditions que vous le souhaitez sans imbriquer de fonctions SI supplémentaires.

Comment fonctionnent ces formules ?

La première formule utilise deux fonctions SI imbriquées pour évaluer deux critères. Dans le test logique de la première instruction SI, nous comparons les valeurs de la colonne « Mois » (A2:A24) avec le critère de F2 (Janv.). Le résultat est un tableau de valeurs VRAI et FAUX où VRAI représente des données qui correspondent au critère :

{FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}

De la même manière, la deuxième fonction SI vérifie les valeurs de la colonne « Semaine » (B2:B24) par rapport au critère de G2.
Pour l'argument « Valeur_si_vrai » dans la deuxième instruction SI, nous fournissons les quantités vendues (D2: D24), et de cette façon, nous obtenons les éléments qui ont VRAI dans les deux premières matrices aux positions correspondantes (c'est-à-dire les éléments où le mois est « Janv. » et la semaine est « Semaine-2 ») :

{FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI}

Ce dernier tableau va directement à la fonction MAX et renvoie le plus grand nombre.

La deuxième formule évalue les mêmes conditions dans un seul test logique et l'opération de multiplication fonctionne comme l'opérateur ET.
Lorsque les valeurs VRAI et FAUX sont utilisées dans toute opération arithmétique, elles sont converties respectivement en 1 et 0. Et comme la multiplication par 0 donne toujours zéro, le tableau résultant n'a la valeur 1 que lorsque toutes les conditions sont « VRAI ». Ce tableau est évalué dans le test logique de la fonction SI, qui renvoie les distances correspondant aux éléments 1 (VRAI).

MAX SI sans formule matricielle

De nombreux utilisateurs d'Excel ont des préjugés contre les formules matricielles et essaient de s'en débarrasser autant que possible. Heureusement, Microsoft Excel a quelques fonctions qui gèrent les matrices de manière native, et nous pouvons utiliser l'une de ces fonctions, à savoir SOMMEPROD, comme une sorte de "enveloppe" autour de la fonction MAX.

La formule générique MAX SI sans matrice est la suivante :

=SOMMEPROD(MAX ((plage_critères1 = critère1) * (plage_critères2 = critère2) * plage_max))

Vous pouvez ajouter plus de paires « plage / critères » si nécessaire.

Supposons que vous ayez le résultat scolaire des garçons et des filles dans une seule table et que vous souhaitiez trouver la plus grande note pour les filles dans le deuxième semestre. Pour le faire, entrez le premier critère « Féminin » dans G1, le deuxième critère « S2 » dans G2, et utilisez la formule suivante pour déterminer la valeur maximale :

=SOMMEPROD(MAX(((B2:B17 = G1) * (C2:C17 = G2) * (D2:D17)))))

Cette formule est concurrencée par une séquence de touches Entrée normale et renvoie le même résultat que la formule matricielle MAX SI :

Comme pour la formule MAX SI, nous évaluons deux critères en comparant chaque valeur des colonnes « Sexe » (B2:B17) et « Semestre » (C2: C17) avec les critères des cellules G1 et G2. Le résultat est : deux tableaux de valeurs VRAI et FAUX.

La multiplication des éléments des tableaux dans les mêmes positions convertit VRAI et FAUX en 1 et 0, respectivement, où 1 représente les éléments qui répondent aux deux critères. Le troisième tableau multiplié contient les notes des élèves (D2:D17). Et parce que multiplier par 0 donne zéro, seuls les éléments qui ont 1 (VRAI) dans les positions correspondantes survivent : {0;15,32;0;0;0;0;0;17,35;0;12,52;0;0;0;0;0;11,23}.

Dans le cas où l’argument « Plage_max » contient une valeur de texte, l'opération de multiplication renvoie l'erreur #VALEUR! et ainsi la formule entière ne fonctionnera pas.
La fonction MAX prend le résultat et renvoie le plus grand nombre qui remplit les conditions spécifiées.
Le tableau résultant composé d'un seul élément {17,35} va à la fonction SOMMEPROD et affiche le nombre maximal dans la cellule.

Choses à retenir sur la formule Excel Max SI

La fonction MAX SI retourne une erreur #VALEUR! si nous n'avons pas utilisé les touches Ctrl + SHIFT + Entrée. Assurez-vous donc d’utiliser la fonction MAX SI comme formule matricielle.
La variable à utiliser pour le test logique doit être clairement définie et doit être sans erreur, sinon une formule peut ne pas réussir le test logique.
La sélection des cellules dans Excel doit être conforme à l'exigence car une mauvaise sélection des cellules peut aboutir à des résultats erronés.
La formule du test logique doit également être appliquée avec beaucoup de prudence avec le plus grand soin pour chaque petit élément de la formule afin d'obtenir le résultat correct à partir d'un grand ensemble de données.

Télécharger le fichier Excel de ce tutoriel

Article publié le 20 Juillet 2020par Hanane Mouqqadim