Quelle est la différence entre MAX.SI.ENS et MAX.SI?

MAX.SI n'existe pas nativement dans Excel. MAX.SI.ENS est la fonction dédiée pour trouver un maximum avec critères multiples. Avant sa création, on utilisait des formules complexes avec MAX et IF en tableau (Ctrl+Maj+Entrée). MAX.SI.ENS simplifie cette syntaxe et fonctionne directement sans formule matricielle.

Peut-on utiliser des caractères génériques dans les critères de MAX.SI.ENS?

Oui, les caractères génériques sont supportés : l'astérisque (*) remplace zéro ou plusieurs caractères, et le point d'interrogation (?) remplace exactement un caractère. Par exemple, =MAX.SI.ENS(D:D;A:A;"*Paris*") trouvera le maximum pour toutes les régions contenant 'Paris'.

Comment gérer les valeurs vides ou les cellules avec du texte dans MAX.SI.ENS?

Vous pouvez exclure les vides avec le critère "<>" ou les inclure explicitement avec "". Pour les cellules contenant du texte, MAX.SI.ENS les ignore automatiquement et ne considère que les nombres. Si vous voulez vérifier l'absence de valeur, utilisez : =MAX.SI.ENS(D:D;A:A;"Région";B:B;"<>")

Quel est le nombre maximal de critères dans MAX.SI.ENS?

Excel permet jusqu'à 127 paires critère/plage dans MAX.SI.ENS, ce qui offre une flexibilité remarquable. En pratique, au-delà de 5-6 critères, il devient préférable d'utiliser des approches alternatives comme les tableaux croisés dynamiques ou Power Query pour améliorer la lisibilité.

MAX.SI.ENS fonctionne-t-elle avec les dates?

Absolument. Les dates sont traitées comme des nombres en Excel, donc vous pouvez utiliser des critères comme ">="&DATE(2024;1;1) ou simplement comparer des dates. Par exemple : =MAX.SI.ENS(D:D;C:C;">=2024-01-01") fonctionne parfaitement pour filtrer par période.

MAX.SI.ENS : Trouvez le maximum avec plusieurs critères en Excel

Intermédiaire

=MAX.SI.ENS(plage_max; plage_critère1; critère1; ...)

La formule MAX.SI.ENS est l'une des fonctions les plus puissantes d'Excel pour analyser des données complexes. Elle permet de trouver la valeur maximale dans une plage en appliquant plusieurs critères simultanément, ce qui en fait un outil indispensable pour les analystes de données et les gestionnaires de projets. Contrairement à la fonction MAX classique qui retourne simplement le maximum d'une plage, MAX.SI.ENS offre une granularité bien supérieure en permettant de filtrer les données selon plusieurs conditions avant de calculer le résultat. Cette fonction est particulièrement utile dans les environnements professionnels où les données doivent être analysées selon plusieurs dimensions : ventes par région et par période, salaires par département et par niveau d'expérience, ou performances de produits par catégorie et par trimestre. En maîtrisant MAX.SI.ENS, vous optimiserez vos rapports Excel et gagnerez un temps considérable dans vos analyses de données. Disponible depuis Excel 2016 et intégrée nativement dans Excel 365, cette formule représente une évolution majeure par rapport aux approches plus complexes utilisant des combinaisons de MAX et IF. Elle simplifie considérablement la syntaxe et améliore la lisibilité de vos feuilles de calcul.

Syntaxe et paramètres

La syntaxe de MAX.SI.ENS suit le modèle suivant : =MAX.SI.ENS(plage_max; plage_critère1; critère1; [plage_critère2; critère2]; ...). Le premier paramètre, plage_max, est obligatoire et désigne la plage contenant les valeurs dont vous souhaitez trouver le maximum. C'est dans cette plage que la fonction recherchera le plus grand nombre respectant tous vos critères. Les paramètres plage_critère1 et critère1 forment le premier couple de conditions. La plage_critère1 contient les cellules à évaluer, tandis que critère1 spécifie la condition à appliquer (peut être un nombre, un texte, une expression ou une référence de cellule). Vous pouvez ajouter jusqu'à 127 paires critère supplémentaires, ce qui offre une flexibilité exceptionnelle pour des analyses multi-niveaux. Conseil pratique : assurez-vous que plage_max et toutes les plages_critère ont la même hauteur (même nombre de lignes). Les critères peuvent utiliser des opérateurs comme ">10", "<>vide", ou des caractères génériques comme "*" et "?". Si aucune valeur ne satisfait les critères, la fonction retourne une erreur #NUM!.

max_range

Plage pour trouver le maximum

criteria_range1

Première plage à évaluer

criteria1

Premier critère

Exemples pratiques

Vente maximale par région et trimestre

=MAX.SI.ENS(D:D;A:A;"Île-de-France";B:B;"Q1")

Cette formule recherche dans la colonne D (Montant) la valeur maximale où la colonne A (Région) égale 'Île-de-France' ET la colonne B (Trimestre) égale 'Q1'. Elle retourne uniquement le résultat correspondant à ces deux conditions simultanément.

Salaire maximum par département et expérience

=MAX.SI.ENS(C:C;A:A;"IT";B:B;">5")

La formule extrait le salaire maximal (colonne C) pour le département IT (colonne A) avec une expérience supérieure à 5 ans (colonne B). Les opérateurs de comparaison comme '>' permettent des critères numériques sophistiqués.

Performance maximale par produit et mois

=MAX.SI.ENS(E:E;C:C;"Premium";D:D;2)

Cette formule retourne la note maximale (colonne E) pour les enregistrements où le produit (colonne C) est 'Premium' et le mois (colonne D) est 2 (février). Les critères numériques peuvent être des nombres simples.

Points clés à retenir

MAX.SI.ENS trouve la valeur maximale dans une plage en appliquant plusieurs critères simultanément, disponible depuis Excel 2016.
La syntaxe est =MAX.SI.ENS(plage_max; plage_critère1; critère1; ...) et supporte jusqu'à 127 paires critère/plage.
Toutes les plages doivent avoir la même hauteur (même nombre de lignes) et les critères doivent être correctement formatés.
Utilisez COUNTIFS pour déboguer et vérifier que vos critères retournent des résultats avant d'utiliser MAX.SI.ENS.
Combiné avec INDEX-MATCH, SUMIFS ou d'autres fonctions, MAX.SI.ENS crée des analyses multidimensionnelles puissantes et sophistiquées.

Astuces de pro

Utilisez des références de plage nommées pour rendre vos formules MAX.SI.ENS plus lisibles. Définissez des noms comme 'Ventes', 'Région', 'Trimestre' et écrivez : =MAX.SI.ENS(Ventes;Région;"IDF";Trimestre;"Q1"). Beaucoup plus clair que des références de colonnes.

Impact : Améliore la maintenabilité de vos fichiers et facilite la compréhension par d'autres utilisateurs.

Combinez MAX.SI.ENS avec des critères dynamiques utilisant des références de cellules. Au lieu d'écrire le critère en dur, référencez une cellule : =MAX.SI.ENS(D:D;A:A;$E$1;B:B;$F$1). Vous pouvez ainsi créer un tableau de bord interactif.

Impact : Permet de créer des rapports dynamiques où l'utilisateur change simplement les valeurs dans deux cellules pour mettre à jour tous les résultats.

Testez vos critères avec COUNTIFS avant de les utiliser dans MAX.SI.ENS. Si COUNTIFS retourne 0, MAX.SI.ENS génèrera une erreur #NUM!. Cela vous aide à déboguer rapidement : =COUNTIFS(A:A;"IDF";B:B;"Q1")

Impact : Économise du temps en identifiant immédiatement les problèmes de critères avant de chercher des erreurs complexes.

Utilisez les opérateurs de comparaison intelligemment. Pour exclure une valeur, utilisez "<>Valeur". Pour des plages : ">100" ou "<=50". Pour des dates : ">="&DATE(2024;1;1). Ces techniques élargissent considérablement les possibilités d'analyse.

Impact : Vous pouvez créer des analyses très sophistiquées sans recourir à des formules matricielles complexes.

Combinaisons utiles

MAX.SI.ENS avec SUMIFS pour analyse comparative

=MAX.SI.ENS(D:D;A:A;"Région1";B:B;"Q1") vs =SUMIFS(D:D;A:A;"Région1";B:B;"Q1")

Combinez MAX.SI.ENS pour trouver la valeur maximale et SUMIFS pour obtenir le total dans la même région/période. Cela permet de comparer la performance maximale par rapport à la performance globale.

MAX.SI.ENS imbriquée avec AVERAGEIFS

=MAX.SI.ENS(D:D;A:A;"Région";B:B;"Q1") - AVERAGEIFS(D:D;A:A;"Région";B:B;"Q1")

Calculez l'écart entre le maximum et la moyenne pour une même région/période. Utile pour identifier les valeurs aberrantes ou les performances exceptionnelles.

MAX.SI.ENS avec INDEX-MATCH pour retourner des informations associées

=INDEX(C:C;MATCH(MAX.SI.ENS(D:D;A:A;"Région";B:B;"Q1");D:D;0))

Trouvez le maximum avec MAX.SI.ENS, puis utilisez INDEX-MATCH pour retourner la valeur correspondante d'une autre colonne (comme le nom du produit ou du vendeur). Cela enrichit votre analyse.

Erreurs courantes

#NUM!

Cause : Aucune valeur dans plage_max ne satisfait tous les critères spécifiés, ou toutes les valeurs correspondant aux critères sont des erreurs.

Solution : Vérifiez que vos critères sont corrects et que des données existent réellement. Utilisez COUNTIFS pour vérifier combien de lignes correspondent à vos critères : =COUNTIFS(plage_critère1;critère1;plage_critère2;critère2)

#VALUE!

Cause : Les plages de critères et la plage_max n'ont pas les mêmes dimensions (nombre de lignes différent), ou un critère contient un type de données incompatible.

Solution : Vérifiez que toutes les plages utilisées ont exactement le même nombre de lignes. Assurez-vous que les critères sont bien formatés (texte entre guillemets, nombres sans guillemets, expressions entre guillemets).

#REF!

Cause : Une référence de plage a été supprimée ou la plage référencée n'existe plus dans le classeur.

Solution : Vérifiez que toutes les colonnes et feuilles référencées existent toujours. Si vous avez supprimé des colonnes, recréez la formule avec les bonnes références. Utilisez des références absolues ($) pour éviter ce problème lors de copies.

Checklist de dépannage

1.Vérifiez que plage_max et toutes les plages_critère contiennent exactement le même nombre de lignes.
2.Confirmez que vos critères sont correctement formatés : texte entre guillemets, nombres sans guillemets, expressions comme ">10" entre guillemets.
3.Utilisez COUNTIFS pour vérifier qu'au moins une ligne satisfait tous vos critères simultanément.
4.Assurez-vous que la plage_max contient des nombres (pas du texte) pour que MAX.SI.ENS puisse calculer un maximum.
5.Vérifiez que les références de plage ne contiennent pas de cellules supprimées ou de feuilles renommées (erreur #REF!).
6.Testez avec des critères simples d'abord, puis complexifiez progressivement pour identifier où l'erreur apparaît.

Cas particuliers

Toutes les valeurs correspondant aux critères sont négatives

Comportement : MAX.SI.ENS retourne la valeur négative la moins négative (la plus proche de zéro), ce qui est le comportement correct d'une fonction de maximum.

Solution : Comportement normal, aucune action requise. Si vous voulez la valeur absolue maximale, utilisez : =MAX(ABS(IF((A:A="Critère");D:D)))

Exemple : avec des valeurs -50, -30, -10, MAX.SI.ENS retourne -10.

La plage_max contient des cellules vides parmi les valeurs

Comportement : MAX.SI.ENS ignore les cellules vides et considère uniquement les nombres. Les cellules vides ne sont jamais retournées comme maximum.

Solution : Comportement normal. Si vous voulez traiter les vides comme des zéros, utilisez : =MAX.SI.ENS(IF(D:D="";0;D:D);A:A;"Critère") en formule matricielle.

Les blancs sont naturellement exclus du calcul du maximum.

Un critère contient un caractère générique qui doit être interprété littéralement (ex : chercher le texte '*')

Comportement : MAX.SI.ENS interprète * comme un caractère générique (zéro ou plusieurs caractères), pas comme un astérisque littéral.

Solution : Échappez le caractère générique avec un tilde : =MAX.SI.ENS(D:D;A:A;"~*") pour chercher un astérisque littéral.

Utilisez ~* pour l'astérisque et ~? pour le point d'interrogation littéral.

Limitations

•MAX.SI.ENS ne fonctionne que sur les nombres. Si vous appliquez des critères à une plage contenant du texte ou des dates, seules les valeurs numériques de plage_max sont considérées.
•La fonction retourne #NUM! si aucune ligne ne satisfait tous les critères simultanément. Il n'existe pas de paramètre par défaut pour retourner une valeur alternative en cas d'absence de résultat.
•Les critères ne peuvent pas être des formules complexes ou des résultats de calculs ; ils doivent être des valeurs statiques, des références de cellules, ou des expressions simples comme ">10" ou "<>Vide".
•MAX.SI.ENS n'est pas disponible dans Excel 2013 et versions antérieures. Pour la compatibilité rétroactive, utilisez des formules matricielles MAX+IF ou des tableaux croisés dynamiques.

Alternatives

Combinaison MAX + IF (formule matricielle)

Compatible avec les anciennes versions d'Excel antérieures à 2016.

Quand : =MAX(IF((A:A="Critère1")*(B:B="Critère2");D:D)) validée avec Ctrl+Maj+Entrée. Plus complexe mais fonctionne partout.

MINIFS pour le minimum

Fonction sœur de MAX.SI.ENS pour trouver des valeurs minimales avec critères.

Quand : Quand vous avez besoin du minimum au lieu du maximum : =MINIFS(D:D;A:A;"Critère1";B:B;"Critère2")

Tableau croisé dynamique (TCD)

Interface visuelle, recalcul automatique, analyse multidimensionnelle avancée.

Quand : Pour des analyses exploratoires complexes impliquant plusieurs critères et agrégations différentes, les TCD offrent plus de flexibilité qu'une formule unique.

Compatibilité

✓ Excel

Depuis 2016 et versions ultérieures (2019, 365)

=MAX.SI.ENS(plage_max; plage_critère1; critère1; [plage_critère2; critère2])

✓Google Sheets

=MAXIFS(plage_max; plage_critère1; critère1; [plage_critère2; critère2]) - Note : Google Sheets utilise MAXIFS au lieu de MAX.SI.ENS

Syntaxe légèrement différente (MAXIFS au lieu de MAX.SI.ENS) mais fonctionnalité identique. Les séparateurs peuvent être des virgules au lieu de points-virgules selon les paramètres régionaux.

✓LibreOffice

=MAXIFS(plage_max; plage_critère1; critère1; [plage_critère2; critère2]) - LibreOffice utilise également MAXIFS

Questions fréquentes

Maîtrisez MAX.SI.ENS et optimisez vos analyses Excel avec ElyxAI, votre assistant intelligent pour les formules complexes. Testez nos templates prédéfinis et gagnez des heures de travail chaque semaine avec notre plateforme spécialisée.

MAX

MIN