Excel: Calculer un min ou un max conditionnel

Publié le 11 juin 2014
par Sophie Marchand M.Sc.
Min et max conditionnels

Vous connaissez déjà les fonctions conditionnelles d’Excel, comme Somme.si (Sumif) et Somme.si.ens (Sumifs), Nb.si (Countif) et Nb.si.ens (Countifs) et Moyenne.si (Averageif) et Moyenne.si.ens (Averageifs). Mais Excel ne fournit aucune fonction pour calculer un min ou un max conditionnel. On pourrait penser qu’Excel donnerait accès à une formule du genre Min.Si (MinIF) ou Max.si (MaxIf), mais ce n’est pas le cas. Toutefois, il n’est pas, pour autant, impossible d’effectuer un calcul de minimum ou de maximum conditionnel, tel que vous le verrez dans cet article.

 

Les fonctions Min et Max de base

Débutons par l’usage conventionnel des fonctions Min et Max dans Excel. Dans l’exemple ci-bas, nous cherchons à connaître la valeur minimale et la valeur maximale des ventes contenues dans la table de référence. Pour ce faire, nous utilisons la fonction Min() et Max(). Le seul paramètre à inclure dans ces formules est la plage de référence.

Min et max conditionnels

 

Les fonctions Min et Max conditionnelles

Dans l’exemple ci-bas, nous souhaitons trouver le Min et le Max mais pour une plage de données restreinte par la catégorie des ventes. Ainsi, nous avons élaboré un petit menu déroulant pour identifier la catégorie désirée.

Min et max conditionnels

 

Nous avons ensuite écrit la formule suivante, qui est une fonction Si (If), imbriquée dans une fonction Min ou Max, selon le cas. Dans la fonction Si (If), nous avons utilisé deux paramètres. Le premier paramètre spécifie le nom de la catégorie à considérer dans la table de référence, le deuxième réfère à la colonne de ventes de la table de données. Ensuite, nous cherchons le minimum ou le maximum de ce résultat.

Min et max conditionnels

 

Mais attention! Il s’agit ici d’une formule matricielle. Alors, comme avec toutes les formules matricielles, au lieu d’appuyer sur Enter lorsque vous avez terminé de rédiger votre formule, vous devez plutôt appuyer sur CSE (Ctrl+Shift+Enter). Vous verrez alors les accolades apparaître au début et à la fin de votre formule.

Min et max conditionnels

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

12 réflexions sur “Excel: Calculer un min ou un max conditionnel”

  1. Ping : Un oeil sur le web | Excel: Calculer un min ou un max conditionnel

      1. SOMMEPROD ne fonctionne que pour le MAX conditionnel.
        Elle ne peut pas être utiliser pour le MIN, car elle retourne systématiquement 0 sur les valeurs qui ne répondent pas à la condition

  2. Hello, en VBA pour les inconditionnels:

    Public Function MINIF(ByVal rgeCriteria As Range, _
    ByVal sCriteria As String, _
    ByVal rgeMinRange As Range) As Single

    Dim iconditioncolno As Integer
    Dim inumberscolno As Integer
    Dim lrowno As Long
    Dim sngmin As Single
    Dim vcellvalue As Variant

    iconditioncolno = rgeCriteria.Column
    inumberscolno = rgeMinRange.Column
    For lrowno = 1 To rgeCriteria.Rows.Count
    vcellvalue = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, inumberscolno).Value
    If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, iconditioncolno).Value = sCriteria And _
    IsNumeric(vcellvalue) = True Then
    If sngmin = 0 Then sngmin = vcellvalue
    If vcellvalue < sngmin Then sngmin = vcellvalue
    End If

    If sngmin 0 And IsEmpty(vcellvalue) = True Then Exit For
    Next lrowno

    For lrowno = 1 To rgeCriteria.Rows.Count
    vcellvalue = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, inumberscolno).Value
    If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, iconditioncolno).Value = sCriteria And _
    IsNumeric(vcellvalue) = True Then
    If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, inumberscolno).Value < sngmin Then
    sngmin = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1, inumberscolno).Value
    End If
    End If

    If sngmin 0 And IsEmpty(vcellvalue) = True Then Exit For
    Next lrowno

    MINIF = sngmin
    End Function

  3. Ping : Schoch (sischo) | Pearltrees

  4. Bonjour,

    Peut-être que SOMMEPROD ne fonctionne que pour le MAX conditionnel et pas pour le MIN conditionnel, et si inversait le rapport ? On créé, pour le calcul, le même tableau avec les nombres inversés (1/Nombre), puis on utilise SOMMEPROD sur ce tableau pour obtenir le max, on inverse le résultat et ça nous donne le MIN, non ? Du moment qu’une des valeurs n’est pas nulle… Et si on a des valeurs nulles, pour le tableau inversé, on utilise une formule SIERREUR et on choisit 10^24 (ou plus grand possible si nécessaire…) dans le cas en erreur de l’inverse de 0, le résultat de 1/10^24 sera très proche de 0, CQFD :o)

    1. Bonjour,

      Veuillez noter que cet article a été rédigé en 2014. Il existe désormais les fonctions MAX.SI.ENS et MIN.SI.ENS.

      Au plaisir,

      Sophie

  5. Galopin voulait sans doute dire que bien que les fonctions MIN.SI.ENS et MAX.SI.ENS ont, comme vous l’indiquez, vus le jour depuis la création de cet article, il n’est néanmoins pas toujours possible de les utiliser, soit parce que sa propre version d’Excel ne les intègre pas, soit pour éviter que les utilisateurs auxquels on adresse un fichier Excel n’obtiennent une erreur dans le cas où leur version d’Excel n’intégrerait pas ces fonctions.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut