La moyenne, c’est la fonction statistique que tout le monde utilise. Et pourtant, c’est aussi l’une de celles qui produit le plus de résultats faux sans que l’utilisateur s’en rende compte.
Le problème ne vient pas de la formule. Il vient d’un détail que beaucoup ignorent : Excel ne traite pas les zéros et les cellules vides de la même façon. Une moyenne calculée avec des zéros qui auraient dû être ignorés peut fausser une analyse entière.
Dans ce guide, on couvre tout : la syntaxe de base, le piège des zéros, la moyenne conditionnelle avec MOYENNE.SI et MOYENNE.SI.ENS, la moyenne pondérée, la moyenne mobile, et même comment gérer les valeurs aberrantes.
- Calculer une moyenne simple avec
=MOYENNE() - Comprendre la différence entre zéro et cellule vide dans une moyenne
- Exclure les zéros d’une moyenne avec MOYENNE.SI
- Faire une moyenne conditionnelle sur un critère ou plusieurs critères
- Calculer une moyenne pondérée avec SOMMEPROD
- Créer une moyenne mobile sur une série temporelle
- Détecter et exclure les valeurs aberrantes d’un calcul de moyenne
MOYENNE : syntaxe et comportement de base
La fonction MOYENNE calcule la moyenne arithmétique d’une plage de cellules. Sa syntaxe est simple :
=MOYENNE(plage)Exemple : si tes notes sont en B2:B10, la formule est :
=MOYENNE(B2:B10)Tu peux aussi passer plusieurs plages séparées par des points-virgules :
=MOYENNE(B2:B10; D2:D10)Ce que MOYENNE ignore automatiquement
Deux types de cellules sont ignorés par MOYENNE, c’est-à-dire qu’elles ne comptent pas dans le total ni dans le diviseur :
- Les cellules vides : une cellule sans valeur est invisible pour MOYENNE.
- Les cellules contenant du texte : si une cellule contient « N/A », « non renseigné » ou n’importe quel texte, MOYENNE l’ignore.
C’est un comportement utile dans la majorité des cas. Mais il a une limite importante : MOYENNE n’ignore pas les zéros.
Le piège des zéros : quand la moyenne ment
C’est le problème le plus fréquent et le moins visible. Un zéro dans une cellule est une valeur à part entière pour MOYENNE. Il est inclus dans le calcul comme n’importe quel autre nombre.
Exemple concret : tu suis les ventes hebdomadaires d’un commercial. Certaines semaines, il était en congé et tu as saisi 0 dans la cellule. D’autres semaines, tu n’as simplement rien saisi (cellule vide).
| Semaine | Ventes | Remarque |
|---|---|---|
| S1 | 12 000 € | Semaine normale |
| S2 | 15 000 € | Semaine normale |
| S3 | 0 € | Congés (saisi manuellement) |
| S4 | (vide) | Données non encore saisies |
| S5 | 18 000 € | Semaine normale |
Avec =MOYENNE(B2:B6), Excel calcule : (12 000 + 15 000 + 0 + 18 000) / 4 = 11 250 €. La cellule vide S4 est ignorée (diviseur = 4), mais le zéro de S3 est bien inclus.
Si tu voulais la moyenne des semaines travaillées uniquement, le résultat attendu est (12 000 + 15 000 + 18 000) / 3 = 15 000 €. La différence est significative.
Comment exclure les zéros avec MOYENNE.SI
La solution : utiliser MOYENNE.SI avec un critère qui exclut les zéros. La formule :
=MOYENNE.SI(B2:B6;"<>0")Le critère "<>0" signifie « différent de zéro ». MOYENNE.SI ne calculera la moyenne que sur les cellules qui ne sont pas égales à 0. Les cellules vides restent ignorées.
MOYENNE.SI : la moyenne conditionnelle sur un critère
MOYENNE.SI calcule la moyenne d’une plage, mais seulement pour les cellules qui respectent une condition. C’est l’équivalent de SOMME.SI mais pour les moyennes.
Syntaxe
=MOYENNE.SI(plage_critère; critère; [plage_moyenne])- plage_critère : la plage sur laquelle Excel vérifie la condition.
- critère : la condition à respecter (valeur, texte, opérateur comme « >500 » ou « Paris »).
- plage_moyenne : (optionnel) la plage sur laquelle calculer la moyenne. Si omis, Excel utilise plage_critère.
Exemple concret : moyenne des ventes par région
Tu as un tableau avec les régions en colonne A et les ventes en colonne B. Pour calculer la moyenne des ventes de la région « Nord » :
=MOYENNE.SI(A2:A50;"Nord";B2:B50)Excel parcourt A2:A50, identifie les lignes contenant « Nord », et calcule la moyenne des valeurs correspondantes en B2:B50.
Exemples de critères
| Critère | Ce qu’il fait |
|---|---|
"Nord" | Cellules égales à « Nord » (texte exact) |
">500" | Cellules dont la valeur est supérieure à 500 |
"<>0" | Cellules différentes de zéro |
">="&D1 | Cellules supérieures ou égales à la valeur de D1 (référence dynamique) |
"*Paris*" | Cellules contenant « Paris » (joker *) |
Pour approfondir SOMME.SI et MOYENNE.SI ensemble avec d’autres exemples pratiques, consulte le guide SOMME.SI et MOYENNE.SI sur le blog.
MOYENNE.SI.ENS : la moyenne avec plusieurs critères
Quand tu as besoin de filtrer sur plusieurs conditions en même temps, MOYENNE.SI ne suffit plus. MOYENNE.SI.ENS permet de combiner autant de critères que nécessaire.
Syntaxe
=MOYENNE.SI.ENS(plage_moyenne; plage_critère1; critère1; [plage_critère2; critère2]; ...)Exemple : moyenne des ventes, filtrée par région ET par commercial
Tu veux la moyenne des ventes uniquement pour la région « Sud » et le commercial « Dupont » :
=MOYENNE.SI.ENS(C2:C100; A2:A100; "Sud"; B2:B100; "Dupont")- C2:C100 : plage des ventes (ce qu’on veut moyenner).
- A2:A100 / « Sud » : premier critère, region = Sud.
- B2:B100 / « Dupont » : deuxième critère, commercial = Dupont.
Tous les critères sont combinés avec un ET logique : une ligne doit respecter toutes les conditions pour être incluse dans la moyenne.
Exemple avec critères numériques
Moyenne des commandes supérieures à 1 000 € passées en 2024 :
=MOYENNE.SI.ENS(B2:B500; B2:B500; ">1000"; C2:C500; 2024)Ici la plage_moyenne et la plage_critère1 sont identiques (B2:B500), ce qui est tout à fait valide.
Moyenne pondérée : utiliser SOMMEPROD
La fonction MOYENNE calcule une moyenne arithmétique simple : chaque valeur a le même poids. Mais dans de nombreux cas, certaines valeurs doivent compter davantage que d’autres.
Exemple classique : les notes scolaires avec coefficients. Une note de maths coefficient 5 doit peser 5 fois plus qu’une note de sport coefficient 1.
Formule SOMMEPROD pour la moyenne pondérée
Excel n’a pas de fonction dédiée. La formule standard est :
=SOMMEPROD(notes; coefficients) / SOMME(coefficients)Exemple concret avec les notes en B2:B8 et les coefficients en C2:C8 :
=SOMMEPROD(B2:B8; C2:C8) / SOMME(C2:C8)SOMMEPROD multiplie chaque note par son coefficient et additionne les résultats. On divise ensuite par la somme des coefficients pour obtenir la moyenne pondérée.
Exemple chiffré
| Matière | Note | Coefficient | Note × Coef |
|---|---|---|---|
| Mathématiques | 14 | 5 | 70 |
| Français | 12 | 4 | 48 |
| Histoire | 16 | 3 | 48 |
| Sport | 18 | 1 | 18 |
| Total | 13 | 184 |
Moyenne pondérée = 184 / 13 = 14,15. Avec une moyenne simple, le résultat aurait été (14+12+16+18) / 4 = 15. La différence vient du poids plus fort des maths, où la note est plus basse.
Moyenne mobile sur une série temporelle
La moyenne mobile est une technique qui permet de lisser une série de données dans le temps, pour faire apparaître la tendance en effaçant les variations ponctuelles.
Elle est très utilisée pour les ventes, le cours des actions, les données météo, ou tout indicateur qui fluctue d’une période à l’autre.
Principe
Une moyenne mobile sur N périodes calcule, pour chaque point, la moyenne des N valeurs précédentes. Ainsi, chaque point représente la tendance récente plutôt qu’une valeur isolée.
Formule simple : MOYENNE avec plage fixe
Pour une moyenne mobile sur 3 semaines avec les données en B2:B53, la formule en C4 (qui commence à partir de la 3e semaine) :
=MOYENNE(B2:B4)Puis tu copie-colles vers le bas. Cette approche fonctionne mais la plage est statique.
Formule dynamique avec DECALER
Pour une moyenne mobile qui s’adapte automatiquement, DECALER est la bonne approche :
=MOYENNE(DECALER(B2; LIGNE()-LIGNE($B$2); 0; -3; 1))Décryptage de DECALER :
- B2 : cellule de référence (début de la série).
- LIGNE()-LIGNE($B$2) : décalage dynamique en lignes selon la position actuelle de la formule.
- 0 : pas de décalage en colonnes.
- -3 : hauteur de la plage = 3 lignes vers le haut.
- 1 : largeur = 1 colonne.
Cette formule retourne toujours la moyenne des 3 valeurs précédant la position actuelle, quelle que soit la ligne où elle est placée.
Détecter et exclure les valeurs aberrantes
Une valeur aberrante (outlier) est une valeur extrême qui tire la moyenne vers le haut ou le bas de façon trompeuse. Exemple : dans une équipe de 10 personnes, un salaire de dirigeant à 300 000 € va gonfler la moyenne des salaires de façon non représentative.
Utiliser la médiane plutôt que la moyenne
La médiane est beaucoup plus robuste face aux valeurs extrêmes. Elle représente la valeur du milieu de la série triée, pas la moyenne arithmétique.
=MEDIANE(B2:B100)Si la médiane et la moyenne sont très éloignées, c’est un signal que ta distribution est asymétrique ou contient des outliers.
Exclure les extremes : formule avec MOYENNE.SI.ENS
Pour calculer la moyenne en excluant les valeurs inférieures à un seuil bas et supérieures à un seuil haut :
=MOYENNE.SI.ENS(B2:B100; B2:B100; ">"&PERCENTILE(B2:B100;0,05); B2:B100; "<"&PERCENTILE(B2:B100;0,95))Cette formule exclut les 5 % de valeurs les plus basses et les 5 % les plus hautes, ce qu’on appelle une moyenne tronquée à 5 %.
La moyenne géométrique pour les taux de croissance
Quand tu calcules une moyenne de taux de croissance ou de ratios, la moyenne arithmétique donne un résultat mathématiquement incorrect. Utilise la moyenne géométrique :
=MOYENNE.GEOMETRIQUE(B2:B12)Exemple d’usage : taux de croissance annuels successifs (+20 %, -10 %, +15 %), taux de rendement d’un placement, indices multiplicatifs.
Cas pratique : carnet de notes et suivi de ventes
Cas 1 : Carnet de notes scolaires avec coefficients
Objectif : calculer la moyenne générale d’un élève à partir de ses notes et de leurs coefficients, puis afficher une mention automatique.
Structure du tableau :
- Colonne A : matière
- Colonne B : note (sur 20)
- Colonne C : coefficient
Formule de moyenne pondérée en D1 :
=SOMMEPROD(B2:B10; C2:C10) / SOMME(C2:C10)Formule de mention automatique en D2 (avec SI imbriqués) :
=SI(D1>=16;"Très bien";SI(D1>=14;"Bien";SI(D1>=12;"Assez bien";SI(D1>=10;"Passable";"Insuffisant"))))Cas 2 : Moyenne des ventes par région, hors mois de congés
Tu veux la moyenne mensuelle des ventes pour chaque région, en excluant les mois où les ventes étaient à zéro (fermeture ou congés).
Structure du tableau :
- Colonne A : région
- Colonne B : mois
- Colonne C : ventes
Formule pour la moyenne de la région « Sud », en excluant les ventes nulles :
=MOYENNE.SI.ENS(C2:C100; A2:A100; "Sud"; C2:C100; "<>0")Cette formule filtre à la fois sur la région (« Sud ») et sur les ventes non nulles, pour ne pas polluer la moyenne avec les mois sans activité.
Tu débutes sur Excel et tu veux comprendre les bases avant de te lancer dans les fonctions statistiques ? Commence par le guide Excel débutant pour poser des fondations solides.
