Max Si (Valeur Maximale avec Condition) – Excel et Google Sheets

Ce tutoriel montre comment trouver la plus grande valeur qui répond à des conditions spécifiques dans Excel et Google Sheets.

Fonction MAX.SI.ENS

Les utilisateurs de Google Sheets et d’Excel 2019 ou d’une version ultérieure peuvent utiliser la fonction unique MAX.SI.ENS.

Cet exemple utilise les fonctions MAX.SI.ENS et DATE pour afficher la plus grande taille de commande pour chaque nom de magasin pour les dates de commande antérieures au 30/04/2021 :

=MAX.SI.ENS(D3:D8;B3:B8;"A";C3:C8;"<"&DATE(2021;4;30))

La fonction MAX.SI.ENS n’oblige pas l’utilisateur à appuyer sur CTRL + MAJ + ENTRÉE lors de la saisie de la formule.

Fonction Max Si (Tableau)

Si vous n’avez pas accès à la fonction MAX.SI.ENS, vous pouvez créer une formule de tableau pour simuler la fonction MAX.SI.ENS. Nous allons vous expliquer comment procéder ci-dessous.

La fonction MAX identifie la plus grande valeur d’une série de chiffres.

=MAX(B2:B11)

Nous pouvons utiliser la fonction MAX combinée à une fonction SI pour identifier la plus grande valeur qui répond à une condition spécifique.

Cet exemple utilise les fonctions MAX et SI dans une formule de tableau pour identifier la plus grande taille de commande pour chaque nom de magasin

=MAX(SI(B3:B8="A";D3:D8))

Dans Office 365 et les versions d’Excel postérieures à 2019, vous pouvez simplement saisir la formule ci-dessus comme vous le feriez normalement (en appuyant sur ENTRÉE).

Cependant, pour Excel 2019 et les versions antérieures, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE. Après avoir fait cela, vous remarquerez que des accolades apparaissent autour de la formule.

Pour montrer comment fonctionne cette formule, décomposons-la en plusieurs étapes.

Voici notre formule finale (sans les accolades qui sont ajoutés automatiquement par Excel) :

=MAX(SI(B3:B8="A";D3:D8))

Tout d’abord, les valeurs des plages de cellules sont ajoutées à la formule sous forme de tableaux :

=MAX(SI({"A" ; "B" ; "A" ; "B" ; "A" ; "B"}="A";{500 ; 400 ; 300 ; 700 ; 600 ; 200}))

Ensuite, la condition Nom Magasin = « A » produit un tableau de valeurs VRAI/FAUX :

=MAX(SI({VRAI ; FAUX ; VRAI ; FAUX ; VRAI ; FAUX};{500 ; 400 ; 300 ; 700 ; 600 ; 200}))

Ensuite, la fonction SI change toutes les valeurs VRAIES en taille de commande appropriée :

=MAX({500 ; FAUX ; 300 ; FAUX ; 600 ; FAUX})

La fonction MAX identifie le plus grand nombre dans le tableau, tout en ignorant les valeurs FALSE, afin d’afficher la plus grande taille de commande pour le nom du magasin = « A » :

=600

Max Si – Critères Multiples

Nous pouvons également identifier la plus grande valeur en fonction de plusieurs critères en utilisant la logique booléenne.

Cet exemple montre la plus grande taille de commande pour chaque nom de magasin, mais pour les dates de commande avant le 30 avril 2021, en utilisant les fonctions MAX, SI et DATE :

{=MAX(SI((B3:B8="A")*(C3:C8<DATE(2021;4;30));D3:D8))}

Remarquez qu’ici nous multiplions deux ensembles de critères VRAI/FAUX ensemble :

(B3:B8="A") * (C3:C8<DATE(2021;4;30))

Si les deux critères sont VRAIS, la condition totale sera calculée comme VRAIE, mais si un (ou plusieurs) critère(s) est (sont) FAUX, elle sera calculée comme FAUX.

En utilisant cette méthodologie, il est possible d’ajouter de nombreux critères différents à cette formule.

Max Si – Critères Multiples avec Références de Cellule

En général, il n’est pas bon de coder des valeurs en dur dans les formules. Il est plus souple d’utiliser des cellules distinctes pour définir les critères.

Pour faire correspondre le nom du magasin à la valeur indiquée dans la colonne F, nous pouvons mettre à jour la formule de la manière suivante :

{=MAX(SI((B3:B8=F3)*(C3:C8<DATE(2021;4;30));D3:D8))}

Verrouillage des Références de Cellule

Pour faciliter la lecture de nos formules, nous avons présenté les formules sans les références de cellule verrouillées :

{=MAX(SI((B3:B8=F3)*(C3:C8<DATE(2021;4;30));D3:D8))}

Mais ces formules ne fonctionneront pas correctement lorsqu’elles seront copiées et collées ailleurs dans votre fichier. Vous devez plutôt utiliser des références de cellules verrouillées comme ceci :

{=MAX(SI(($B$3:$B$8=F3)*($C$3:$C$8<DATE(2021;4;30));$D$3:$D$8))}

Lisez notre article sur le verrouillage des références de cellule pour en savoir plus.

 

Max Si (valeur maximale avec condition) dans Google Sheets

Les exemples présentés ci-dessus fonctionnent exactement de la même manière dans Google Sheets et dans Excel, mais comme la fonction MAX.SI.ENS est disponible, il est recommandé d’utiliser cette fonction unique au lieu de combiner les fonctions MAX et SI.

Si vous souhaitez utiliser la formule de tableau MAX / SI, entrez la formule dans la fonction ARRAYFORMULA (ou utilisez simplement CTRL + MAJ + ENTRÉE et Google Sheets ajoutera cette fonction pour vous).

=ARRAYFORMULA(MAX(SI((B3:B8="A")*(C3:C8<DATE(2021;4;30));D3:D8))))