MAX et MIN conditionnelles

Dans la leçon précédente utilisation de MAX et MIN, nous avons montré une procédure pour trouver la valeur maximale ou minimale selon un critère de filtrage. Aujourd’hui nous allons proposer d’autres méthodes pour rechercher le Max et le Min des valeurs dans une plage de cellules en répondant à certaines conditions contenues dans les autres colonnes de cette plage.

Max et Min conditionnelles

Utiliser une formule matricielle en imbriquant SI dans MAX ou MIN

Avec un seul critère

Dans l’exemple suivant, nous voulons trouver le montant le plus élevé et le plus bas pour la région de l’Italie.
  • Dans la première cellule qui va afficher le montant le plus élevé, tapez =MAX(SI(B2:B19=B4;D2:D19))
Utiliser Max et Si pour afficher le max conditionnel

  • Donc ici nous avons imbriqué la fonction SI dans la fonction MAX.
  • Excel doit alors chercher dans la plage de cellules Région B2:B19 toutes les cellules contenant Italie puis il va renvoyer le montant le plus élevé lié à cette région et ceci à partir de la plage de cellules Montant D2:D19.

Note : les deux plages de cellules doivent avoir le même nombre de lignes pour qu’Excel renvoie le résultat demandé.
  • Tapez Entrée pour valider la formule.
  • Vous remarquez que la formule renvoie une erreur #Valeur.
  • Pour afficher donc le bon résultat, vous devez tapez Ctrl+Shift+Entrée parce que ce type de formules est considéré comme une formule matricielle, et remarquez les accolades qui se sont ajoutées dans la formule.
Formule matricielle Max et Si imbriquée

  • Excel affiche donc le montant le plus élevé dans la région de l’Italie.

Note : une formule matricielle permet d’effectuer des calculs impossibles à réaliser.

Procédez de la même manière en utilisant la fonction MIN pour trouver le montant le plus bas.

Formule matricielle Min et Si imbriquée

Avec plusieurs critères

Reprenons le même exemple précédent, et essayons maintenant de trouver la valeur maximale et minimale dans les deux régions Allemagne et Belgique.
  • Sélectionnez la cellule qui va afficher la valeur minimale puis écrivez : =MIN(SI(B2:B19=B3;D2:D19);SI(B2:B19=B9;D2:D19))
Deux conditions pour trouver le Min des valeurs

  • Vous remarquez que nous avons utilisé deux critères ici: la première fonction SI va chercher les montants correspondant à l’Allemagne : SI(B2:B19=B3;D2:D19)
  • puis la deuxième fonction SI va chercher les montants correspondant à la Belgique : SI(B2:B19=B9;D2:D19)
  • ensuite Excel exécute la fonction MIN pour renvoyer le montant le plus bas dans ces deux résultats trouvés.
  • Composez encore Ctrl+Shift+Entrée pour valider cette nouvelle formule matricielle.
Trouver le min selon deux critères dans la même colonne

  • Vous pouvez également chercher le Max et le MIN selon deux critères appartenant à deux colonnes  différentes.
  • Par exemple vous pouvez chercher le montant le plus élevé du produit Céréales dans la région Allemagne.
trouver le Max selon deux critères dans deux colonnes


  • Voici le résultat:
Résutat Max avec deux critères de recherche


Dans Excel 2016, les choses se sont simplifiées avec l’arrivée des fonctions MAX.SI et MIN.SI

MAX.SI et MIN.SI ou MAX.SI.ENS et MIN.SI.ENS sont deux fonctions qui se sont apparues avec la venue de la version Excel 2016. (Elles sont 6 en total, vous les trouverez dans l'ebook gratuit Fonctions Excel 2016).
Ces deux fonctions vont faire le même travail que vous avez vu précédemment, mais d’une façon plus simple.
Vous devez donc posséder la version Excel 2016 ou bien vous connecter à Office365 et travailler sur Excel Online, ou même utiliser l’application Excel sur Android.
  • Voici la syntaxe de la fonction MAX.SI.ENS (ou MAX.SI)
MAX.SI.ENS(plage_max; plage_critère1; critère1; [plage_critère2; critère2]; ...)
  • plage_max : cet argument doit contenir la référence de la plage de cellules des valeurs dont on veut trouver la valeur maximale. Dans notre cas c’est la colonne Montant D2:D19.
  • plage_critère1 : on doit référencer la plage de cellules où se trouve notre premier critère. C’est la colonne Région B2:B19 exactement.
  • critère1 : c’est la référence de la cellule ou une valeur qui va permettre de définir la valeur maximale recherchée. Et comme vous l’avez vu en haut, vous allez choisir la région de l’Italie et sélectionner donc B4.

Note : Vous pouvez aussi taper « Italie » tout simplement entre guillemets.

Avec un seul critère

  • Alors puisqu’on va travailler sur un seul critère, notre formule sera la suivante :
=MAX.SI.ENS(D2:D19;B2:B19;B4)

Insertion de MAX.SI.ENS

  • Tapez Entrée pour valider. Vous n’avez pas besoin donc de composer Ctrl+Shift+Entrée !!
  • Et voici le résultat.
Résultat d'utilisation de MAX.SI



Avec plusieurs critères

Si vous voulez utiliser plusieurs critères, la fonction MAX.SI.ENS exige que vous définissiez des critères appartenant à des colonnes différentes. Ce qui n’est pas le cas dans notre exemple en haut (MAX + SI).
Par exemple si vous avez inséré la fonction MAX.SI.ENS de cette façon :
=MAX.SI.ENS(D2:D19;B2:B19;B4;B2:B19;B2)
Excel renvoie 0 Euros, car on a utilisé deux critères de la même colonne.

Impossible d'executer MAX.SI.ENS avec deux critèes sur colonne


et pour vous expliquer comment les choses fonctionnent :
Excel exécute la fonction de gauche à droite et mémorise en premier lieu les valeurs dont on veut trouver la valeur maximale, ici les montants de la colonne Montant .
Puis il sélectionne celles qui correspondent au critère défini, c’est-à-dire tous les montants de la région Italie dans notre cas :

Montants de la région Italie


Ensuite Excel cherche les montants correspondant au critère Allemagne non dans tout le tableau mais dans la dernière plage trouvée (regardez l’image précédente), ce qui est impossible.
Alors quand il exécute la fonction MAX.SI.ENS, il affiche 0.
Pour vous aider encore à bien comprendre cette dernière étape, Modifiez B2 par « Italie » ou une référence de cellule qui contient Italie et réexuctez la formule :

Répétition du même critère dans MAX.SI


Vous remarquez donc qu’excel affiche le bon résultat.
En réalité c’est une répétion des arguments pas de plus!

La bonne utilisation de MAX.SI.ENS en incluant plusieurs critères

  • Saisissez la formule suivante =MAX.SI.ENS(D2:D19;B2:B19;B4 ;
  • Sélectionnez maintenant la deuxième plage de critère, par exemple la colonne Produit C2:C19.
  • Tapez point-virgule et sélectionnez Produits laitiers dans la cellule C15 et qui est l’une des valeurs correspondant à la région Italie.
  • Fermez la parenthèse et validez.
La bonne utilisation de MAX.SI.ENS en incluant plusieurs critères

  • De la même façon utilisez donc la fonction MIN.SI.ENS ou MIN.SI
MIN.SI.ENS incluant plusieurs critères




PARTAGEZ

Excel Info

  • Image
  • Image
  • Image
  • Image
  • Image