MOYENNE.SI : Calculer une Moyenne Conditionnelle en Excel
=MOYENNE.SI(plage; critère; [moyenne_plage])La formule MOYENNE.SI est une fonction incontournable pour les professionnels qui manipulent régulièrement des données dans Excel. Elle permet de calculer la moyenne d'un ensemble de valeurs en fonction d'un ou plusieurs critères spécifiques, sans avoir besoin de filtrer manuellement vos données. Cette fonction est particulièrement utile dans les contextes commerciaux, financiers et administratifs où vous devez analyser des performances partielles ou des sous-ensembles de données. Contrairement à la formule MOYENNE classique qui traite toutes les cellules d'une plage, MOYENNE.SI offre une granularité remarquable en vous permettant de sélectionner uniquement les cellules qui correspondent à vos critères. Cela signifie que vous pouvez, par exemple, calculer le salaire moyen des employés d'un département spécifique, la note moyenne des étudiants ayant obtenu au moins 12/20, ou encore le chiffre d'affaires moyen par région. Cette flexibilité en fait une fonction essentielle pour toute analyse de données efficace. Dans ce guide complet, nous vous montrerons comment maîtriser MOYENNE.SI, de sa syntaxe de base aux cas d'usage avancés, en passant par les pièges courants et les meilleures pratiques pour optimiser vos analyses.
Syntaxe et paramètres
La syntaxe de MOYENNE.SI s'écrit de la manière suivante : =MOYENNE.SI(plage; critère; [moyenne_plage]). Le premier paramètre, plage (obligatoire), représente l'ensemble des cellules que vous souhaitez évaluer selon votre critère. Il peut s'agir d'une plage continue ou d'une référence nommée. Le deuxième paramètre, critère (obligatoire), définit la condition que les cellules de la plage doivent satisfaire. Ce critère peut être une valeur numérique simple (par exemple 15), une comparaison (>10, <100, =20), une chaîne de texte ("Paris"), ou même une expression avec caractères génériques ("*tion" pour tous les mots se terminant par "tion"). Le troisième paramètre, moyenne_plage (optionnel mais crucial), spécifie les cellules dont vous souhaitez calculer la moyenne. Si vous omettez ce paramètre, Excel utilisera la plage elle-même pour le calcul de la moyenne. Cela signifie que si votre plage contient des critères (texte) et des valeurs numériques mélangées, vous risquez une erreur. En pratique, il est recommandé de toujours spécifier moyenne_plage pour éviter les ambiguïtés. Par exemple, =MOYENNE.SI(A1:A100;">100";B1:B100) calculera la moyenne des valeurs de B1:B100 pour toutes les lignes où la colonne A dépasse 100. Les deux plages doivent avoir la même taille pour un fonctionnement optimal.
rangecriteriaaverage_rangeExemples pratiques
Moyenne des ventes par région
=MOYENNE.SI(A:A;"Île-de-France";B:B)Cette formule scanne l'intégralité de la colonne A à la recherche de cellules contenant exactement "Île-de-France", puis calcule la moyenne des valeurs correspondantes de la colonne B. Utiliser A:A et B:B permet de traiter dynamiquement toutes les lignes, même si de nouvelles données sont ajoutées.
Notes moyennes des étudiants ayant réussi
=MOYENNE.SI(C2:C150;">=12";C2:C150)Le critère ">=12" filtre automatiquement les notes inférieures à 12. Comme moyenne_plage est identique à plage, la formule calcule directement la moyenne des notes qualifiantes. Cette approche est très courante pour les analyses de performance ou de conformité.
Temps moyen de traitement par type de ticket
=MOYENNE.SI(D:D;"Technique";E:E)Cette formule identifie tous les tickets marqués comme "Technique" et en calcule le temps moyen de résolution. Elle permet de comparer rapidement l'efficacité du traitement par catégorie de problème et d'identifier les domaines nécessitant une optimisation.
Points clés à retenir
- MOYENNE.SI calcule la moyenne conditionnelle d'une plage en fonction d'un seul critère. Syntaxe : =MOYENNE.SI(plage; critère; [moyenne_plage])
- Toujours spécifier le paramètre moyenne_plage pour éviter les erreurs #VALUE! et assurer la clarté de votre formule
- Les caractères génériques (* et ?) fonctionnent dans le critère pour des correspondances partielles, et la fonction est insensible à la casse
- Enveloppez avec IFERROR pour gérer gracieusement l'absence de résultats, et testez d'abord avec COUNTIF pour valider votre critère
- Pour plusieurs critères simultanés, utilisez MOYENNE.SI.ENS ou combinez SUMIF et COUNTIF pour plus de flexibilité
Astuces de pro
Utilisez toujours des références absolues ($) pour les plages quand vous copiez la formule. Exemple : =MOYENNE.SI($A$2:$A$100;"Paris";$B$2:$B$100) empêche les références de glisser lors de la copie.
Impact : Économise du temps et élimine les erreurs dues aux références relatives mal adaptées. Augmente la fiabilité des modèles Excel partagés.
Pour des critères complexes, créez une colonne d'aide (helper column) avec des formules SI imbriquées, puis appliquez MOYENNE.SI sur cette colonne. Cela rend votre logique plus lisible et plus facile à déboguer.
Impact : Améliore la maintenabilité du classeur et facilite la collaboration en équipe. Les formules complexes deviennent transparentes et auditable.
Testez vos critères avec COUNTIF d'abord pour vérifier qu'ils retournent le nombre de lignes attendu. Si COUNTIF retourne 0, votre MOYENNE.SI retournera #DIV/0!.
Impact : Permet d'identifier rapidement les problèmes de critères (casse, espaces, format) avant de dépenser du temps à déboguer la moyenne.
Combinez MOYENNE.SI avec les noms de plages Excel pour des formules plus lisibles. Nommez vos plages (Formules > Définir un nom) puis utilisez : =MOYENNE.SI(Régions;"Paris";Ventes)
Impact : Les formules deviennent auto-documentées et plus faciles à comprendre pour les collègues. Les erreurs de référence sont réduites.
Combinaisons utiles
MOYENNE.SI + IFERROR pour éviter les erreurs
=IFERROR(MOYENNE.SI(A:A;"Bordeaux";B:B);"Aucune donnée trouvée")Cette combinaison affiche un message personnalisé au lieu d'une erreur #DIV/0! quand aucun résultat ne correspond au critère. Très utile pour les tableaux de bord professionnels où les erreurs doivent être gérées élégamment.
MOYENNE.SI + CONCATENER pour critères dynamiques
=MOYENNE.SI(A:A;CONCATENER("*";D1;"*");B:B)Permet de créer un critère dynamique basé sur une cellule (D1). Si D1 contient "Paris", la formule cherchera toutes les cellules contenant "Paris". Très puissant pour les tableaux de bord interactifs.
MOYENNE.SI + MOYENNE pour comparaison
=MOYENNE.SI(A:A;"Paris";B:B) - MOYENNE(B:B)Compare la moyenne conditionnelle (Paris) avec la moyenne générale. Utile pour identifier si une région performe au-dessus ou au-dessous de la moyenne globale. Résultat positif = surperformance.
Erreurs courantes
Cause : Le paramètre moyenne_plage contient du texte non numérique que Excel tente de convertir en nombre. Cela survient souvent quand on oublie de spécifier moyenne_plage et que la plage contient un mélange de texte et de chiffres.
Solution : Vérifiez que moyenne_plage contient exclusivement des valeurs numériques. Si la colonne contient du texte, isolez-la dans une autre colonne ou utilisez MOYENNE.SI.ENS pour plus de contrôle. Exemple correct : =MOYENNE.SI(A:A;"Paris";B:B) plutôt que =MOYENNE.SI(A:A;"Paris";A:A)
Cause : Une plage référencée a été supprimée ou la formule fait référence à un classeur fermé. Cela peut arriver après une fusion de fichiers ou une réorganisation de colonnes.
Solution : Vérifiez que toutes les plages existent et sont valides. Utilisez des références nommées ou des plages dynamiques avec INDIRECT pour plus de robustesse. Exemple : =MOYENNE.SI(INDIRECT("Données!A:A");"Paris";INDIRECT("Données!B:B"))
Cause : Aucune cellule ne correspond au critère spécifié, donc Excel tente de diviser par zéro pour calculer la moyenne. Par exemple, chercher "Bordeaux" dans une colonne qui ne contient que "Paris" et "Lyon".
Solution : Vérifiez l'orthographe de votre critère (attention à la casse et aux espaces). Utilisez des caractères génériques si nécessaire : =MOYENNE.SI(A:A;"*Paris*";B:B). Pour gérer gracieusement l'absence de résultats, enveloppez avec IFERROR : =IFERROR(MOYENNE.SI(A:A;"Bordeaux";B:B);"Aucune donnée")
Checklist de dépannage
- 1.Vérifiez que la plage de critères et la plage de moyennes ont exactement la même taille (même nombre de lignes)
- 2.Confirmez que le critère n'a pas d'espaces superflus : comparez-le avec les données réelles en copiant-collant une valeur
- 3.Testez d'abord avec COUNTIF(plage;critère) pour vérifier que votre critère trouve des correspondances
- 4.Assurez-vous que la colonne moyenne_plage contient uniquement des nombres (pas de texte mélangé)
- 5.Vérifiez que vous n'avez pas accidentellement supprimé des colonnes référencées par la formule (cause courante de #REF!)
- 6.Si vous utilisez des dates, convertissez-les explicitement avec DATE() plutôt que de passer des chaînes de texte : >DATE(2024;1;1) plutôt que ">2024-01-01"
Cas particuliers
La plage contient des cellules vides
Comportement : MOYENNE.SI ignore les cellules vides lors du calcul du critère. Si moyenne_plage contient des vides, elles sont aussi ignorées (n'affectent pas la moyenne).
Solution : Aucune action nécessaire - c'est le comportement attendu et souhaitable. Les vides sont traités comme "pas de donnée".
Cela diffère de MOYENNE qui compte aussi les zéros. MOYENNE.SI est plus robuste pour les données incomplètes.
Le critère est une formule ou une référence cellulaire
Comportement : =MOYENNE.SI(A:A;B1;C:C) où B1 contient "Paris" fonctionne parfaitement. La valeur de B1 est évaluée en temps réel.
Solution : Utilisez des références cellulaires pour des critères dynamiques. Cela permet des tableaux de bord interactifs : modifiez B1 et la moyenne se met à jour automatiquement.
Très utile pour les listes déroulantes ou les champs de saisie utilisateur.
Comparaison avec zéro ou valeurs négatives
Comportement : =MOYENNE.SI(A:A;">0";B:B) fonctionne correctement et ignore les zéros et négatifs. =MOYENNE.SI(A:A;0;B:B) trouve exactement les zéros.
Solution : Les comparaisons numériques fonctionnent comme attendu. Attention : =MOYENNE.SI(A:A;"0";B:B) cherche le texte "0", pas le nombre 0.
Toujours utiliser des nombres sans guillemets pour les comparaisons numériques, et du texte entre guillemets pour les comparaisons textuelles.
Limitations
- •MOYENNE.SI ne peut gérer qu'un seul critère. Pour plusieurs critères, utilisez MOYENNE.SI.ENS ou combinez SUMIF et COUNTIF.
- •Les critères ne peuvent pas utiliser de logique OU (OR). Par exemple, impossible de chercher directement "Paris" OU "Lyon" dans une seule formule - il faudrait combiner deux MOYENNE.SI.
- •MOYENNE.SI ne supporte pas les critères basés sur d'autres colonnes (comparaison entre colonnes). Pour cela, il faut utiliser des formules matricielles ou MOYENNE.SI.ENS avec logique avancée.
- •Les performances peuvent se dégrader significativement avec des plages très volumineuses (>1 million de lignes). Dans ce cas, privilégiez les tableaux croisés dynamiques ou les modèles de données.
Alternatives
Compatibilité
✓ Excel
Depuis 2007
=MOYENNE.SI(plage; critère; [moyenne_plage]) - Identique dans Excel 2007, 2010, 2013, 2016, 2019 et 365✓Google Sheets
=AVERAGE(IF(range=criteria, average_range)) - Utilise la syntaxe matricielle avec Ctrl+Maj+Entrée, ou =AVERAGEIF(range, criteria, average_range) pour la syntaxe directeGoogle Sheets supporte AVERAGEIF (équivalent anglais). La syntaxe est légèrement différente mais le résultat est identique. Les caractères génériques fonctionnent aussi.
✓LibreOffice
=MOYENNE.SI(plage; critère; [moyenne_plage]) - Syntaxe identique à Excel, utilise les points-virgules comme séparateurs