Calculer une moyenne sur Excel semble trivial, jusqu’au moment où une cellule vide, un filtre actif ou un poids différent par ligne fausse le résultat sans prévenir. La fonction MOYENNE couvre le cas de base. Les écarts apparaissent dès qu’on sort de ce cas de base, et c’est là que la plupart des erreurs de tableur passent inaperçues.
MOYENNE, MOYENNEA et SOUS.TOTAL : ce que chaque formule renvoie sur les mêmes données
Le tableau ci-dessous compare trois formules appliquées à une même colonne de six cellules contenant des nombres, une cellule vide, un texte et la valeur logique VRAI.
A voir aussi : Modélisation des données dans Excel : définition et exemples pratiques
| Formule | Cellules prises en compte | Traitement du texte et de VRAI |
|---|---|---|
| =MOYENNE(A1:A9) | Nombres uniquement | Texte et booléens ignorés |
| =MOYENNEA(A1:A9) | Nombres, texte, booléens | Texte = 0, VRAI = 1, FAUX = 0 |
| =SOUS.TOTAL(101;A1:A9) | Nombres des lignes visibles | Texte ignoré, lignes masquées par filtre exclues |
La différence entre MOYENNE et MOYENNEA piège régulièrement. MOYENNEA compte le texte comme zéro et l’ajoute au dénominateur. Sur une colonne de notes où un élève a la mention « ABS » au lieu d’un chiffre, MOYENNEA tire la moyenne vers le bas parce qu’elle traite « ABS » comme 0 tout en augmentant le nombre de valeurs.
MOYENNE, elle, ignore cette cellule. Le résultat est plus élevé, et souvent plus fidèle à ce qu’on cherche. Choisir l’une ou l’autre dépend de la question posée : veut-on la moyenne des notes obtenues, ou la moyenne en comptant les absents comme zéro ?
Lire également : Créer une liste déroulante sur Excel ? Facile !
Calcul moyenne Excel avec filtre actif : pourquoi MOYENNE donne un résultat faux
Quand un filtre automatique masque des lignes, la fonction MOYENNE continue de calculer sur la totalité de la plage, lignes masquées comprises. Le chiffre affiché ne correspond pas aux données visibles à l’écran.
C’est le rôle de SOUS.TOTAL avec le code 101. Ce code demande à Excel de calculer la moyenne en excluant les lignes masquées par un filtre automatique. La syntaxe reste simple :
=SOUS.TOTAL(101;B2:B50)
Un détail à connaître : le code 1 (sans le préfixe 10) exclut seulement les lignes masquées manuellement, pas celles masquées par un filtre. En revanche, le code 101 exclut les lignes masquées par filtre tout en incluant celles masquées manuellement. Dans un fichier de suivi commercial où les filtres changent souvent, le code 101 est le seul fiable.
Vérifier rapidement le résultat
Sélectionnez les cellules visibles à la souris. La barre d’état en bas d’Excel affiche la moyenne de la sélection. Si ce chiffre correspond à celui renvoyé par SOUS.TOTAL(101;…), la formule est correcte.
Moyenne pondérée sur Excel : la formule que MOYENNE.SI ne remplace pas
MOYENNE.SI et MOYENNE.SI.ENS filtrent les valeurs selon un critère, puis calculent une moyenne arithmétique classique. Chaque valeur retenue pèse le même poids. Ces fonctions ne gèrent pas les coefficients.
Pour une moyenne pondérée (notes avec coefficients, prix avec quantités, scores avec poids), la formule passe par SOMMEPROD :
=SOMMEPROD(B2:B10;C2:C10)/SOMME(C2:C10)
B2:B10 contient les valeurs, C2:C10 contient les poids. SOMMEPROD multiplie chaque valeur par son poids, puis SOMME(C2:C10) divise par le total des poids.
Exemple concret : notes et coefficients
| Matière | Note (B) | Coefficient (C) |
|---|---|---|
| Maths | 14 | 5 |
| Français | 12 | 4 |
| Histoire | 16 | 2 |
La formule =SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4) renvoie la moyenne pondérée. Une simple =MOYENNE(B2:B4) donnerait 14, sans tenir compte du fait que la note de maths pèse plus lourd. L’écart entre les deux résultats dépend de la dispersion des coefficients : plus les poids varient, plus la différence est marquée.
Moyenne conditionnelle Excel : quand utiliser MOYENNE.SI et MOYENNE.SI.ENS
MOYENNE.SI prend trois arguments : la plage de critère, le critère, et la plage à moyenner.
=MOYENNE.SI(A2:A50;"Paris";C2:C50)
Cette formule calcule la moyenne des valeurs en colonne C uniquement pour les lignes où la colonne A contient « Paris ». Pratique pour isoler un commercial, une région ou une catégorie de produit dans un tableau de suivi.
MOYENNE.SI.ENS étend le principe à plusieurs critères simultanés. La syntaxe change : la plage à moyenner vient en premier.
=MOYENNE.SI.ENS(C2:C50;A2:A50;"Paris";D2:D50;">2024-01-01")
- Le premier argument (C2:C50) désigne les valeurs à moyenner
- Chaque paire suivante associe une plage de critère et un critère : ici, région = « Paris » et date postérieure au 1er janvier 2024
- Tous les critères doivent être remplis simultanément pour qu’une ligne soit incluse dans le calcul
Une erreur fréquente : inverser l’ordre des arguments entre MOYENNE.SI et MOYENNE.SI.ENS. Dans MOYENNE.SI, la plage de critère vient en premier. Dans MOYENNE.SI.ENS, c’est la plage de moyennage. Mélanger les deux renvoie un résultat sans message d’erreur, ce qui rend le problème difficile à repérer.
Erreurs courantes dans le calcul de moyenne Excel
Quelques pièges reviennent systématiquement dans les fichiers partagés en entreprise :
- Cellules contenant un espace ou une apostrophe invisible : Excel les considère comme du texte. MOYENNE les ignore, MOYENNEA les compte comme 0. Vérifier avec =ESTNUM() sur la cellule suspecte
- Zéros intentionnels ou non : un zéro dans une colonne de notes peut signifier « non noté » plutôt que « 0/20 ». MOYENNE le compte. Pour l’exclure, utiliser MOYENNE.SI avec le critère « <>0″
- Plage qui ne s’étend pas automatiquement : ajouter une ligne sous un tableau non structuré ne met pas à jour la plage de la formule. Convertir la plage en tableau structuré (Ctrl+T) règle le problème
Le calcul de moyenne sur Excel ne se limite pas à une seule fonction. Le choix entre MOYENNE, MOYENNEA, SOUS.TOTAL et SOMMEPROD dépend de la structure des données et de la question posée. Tester la formule sur un petit jeu de données où le résultat est connu à l’avance reste le moyen le plus direct de vérifier qu’on a sélectionné la bonne approche.
