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.

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))

• Ici donc, 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.

• 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 cette fois la fonction MIN pour trouver le montant le plus bas.

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))

• 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.

• 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.

• Voici le résultat:

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 nous voulons trouver la valeur maximale. Dans notre cas c’est la colonne Montant D2:D19.
• plage_critère1 : nous devons 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 nous l’avons vu en haut, nous allons 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 puisque nous allons travailler sur un seul critère, notre formule sera la suivante :

=MAX.SI.ENS(D2:D19;B2:B19;B4)

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

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 vous avez utilisé deux critères de la même colonne.

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 :

Ensuite, Excel cherche les montants correspondant au critère Allemagne 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 exécutez de nouveau la formule :

Vous remarquez donc qu’excel affiche le bon résultat.

En réalité c’est une répétition 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.

• De la même façon donc, utilisez la fonction MIN.SI.ENS ou la fonction MIN.SI

PARTAGEZ PARTAGEZ PARTAGEZ