Vous ouvrez un tableau croisé dynamique Excel, vous appliquez un filtre par région ou par mois, et la moyenne affichée change du tout au tout. Le dénominateur a bougé sans prévenir. Pire, des lignes contenant des zéros ou des cellules vides sont encore comptées dans le calcul. Ce problème de calcul moyenne Excel avec filtres touche tous les utilisateurs dès que les données ne sont pas parfaitement propres.
Pourquoi la moyenne d’un TCD Excel change quand on filtre
Un tableau croisé dynamique recalcule automatiquement chaque synthèse en fonction des lignes visibles. Quand vous sélectionnez un segment ou un filtre de rapport, Excel ajuste le numérateur (la somme des valeurs) et le dénominateur (le nombre de valeurs) en même temps.
A lire en complément : Optimiser l'utilisation de Substitue Excel : conseils et astuces pratiques
Le problème survient quand votre source contient des lignes avec un zéro explicite ou une cellule vide. La fonction Moyenne d’un TCD inclut les zéros dans le dénominateur, mais ignore les cellules vides. Ce comportement asymétrique fausse le résultat dès que votre jeu de données mélange les deux.
Prenons un cas concret. Vous suivez les ventes mensuelles de cinq commerciaux. En janvier, deux d’entre eux n’ont rien vendu : l’un a une cellule vide, l’autre affiche zéro. Filtrez sur janvier dans le TCD, et la moyenne sera calculée sur quatre valeurs (en ignorant la cellule vide) au lieu de cinq. Le résultat est faux dans les deux cas, mais pour des raisons opposées.
A lire aussi : Scinder une colonne en deux : conseils et astuces pratiques pour Excel
Moyenne dans un tableau croisé dynamique : exclure les zéros et les valeurs nulles
La méthode la plus fiable pour exclure les zéros d’une moyenne dans un TCD consiste à agir en amont, directement dans la source de données.
- Remplacez les zéros par des cellules vides si le zéro signifie « pas de donnée » et non « une valeur mesurée à zéro ». La fonction Moyenne du TCD ignorera ces cellules.
- Ajoutez une colonne auxiliaire avec une formule du type =SI(B2=0; » »;B2) pour transformer les zéros en vide, puis basez votre TCD sur cette colonne.
- Utilisez un filtre d’étiquette ou de valeur dans le TCD lui-même : clic droit sur le champ concerné, Filtre de valeurs, « est différent de 0 ». Cette approche masque les lignes zéro du calcul.
Chacune de ces options modifie le dénominateur différemment. La colonne auxiliaire est la plus prévisible parce que vous contrôlez exactement ce qui entre dans le TCD.
SOUS.TOTAL et MOYENNE.SI : calculer la moyenne des lignes visibles hors TCD
Vous avez déjà remarqué que la fonction MOYENNE classique ne tient pas compte des filtres appliqués à un tableau ? Elle additionne toutes les valeurs de la plage, visibles ou masquées. C’est un piège fréquent.
Pour obtenir la moyenne des données filtrées uniquement, utilisez la fonction SOUS.TOTAL. Sa syntaxe : =SOUS.TOTAL(101;plage). Le code 101 correspond à la moyenne en ignorant les lignes masquées par un filtre automatique. Le code 1 calcule aussi une moyenne, mais il inclut les lignes masquées manuellement.
Si vous devez en plus exclure les zéros, SOUS.TOTAL ne suffit pas seul. Combinez avec MOYENNE.SI ou MOYENNESI :
=MOYENNE.SI(B2:B100; »<>0″) calcule la moyenne en excluant toutes les cellules égales à zéro. Cette formule fonctionne sur l’ensemble de la plage, filtres ou non.
Pour combiner les deux contraintes (lignes visibles seulement et zéros exclus), il faut passer par une formule matricielle ou par un champ calculé dans le TCD. La formule matricielle classique avec MOYENNE.SI.ENS et une colonne de test de visibilité reste lourde à maintenir. Le TCD avec une source nettoyée en amont reste la solution la plus robuste.
Champ calculé ou modèle de données Excel : la bonne moyenne dans un TCD
Quand vous voulez un ratio (panier moyen, taux de conversion, coût unitaire moyen), la moyenne ligne par ligne n’est pas la bonne approche. Vous avez besoin d’une mesure qui divise une somme par une autre somme, pas d’une moyenne de moyennes.
Excel propose deux chemins pour cela dans un tableau croisé dynamique.
Champ calculé du TCD
Clic droit dans le TCD, puis « Champs, éléments et jeux ». Vous créez un champ qui divise, par exemple, la somme du chiffre d’affaires par le nombre de commandes. Ce champ calculé s’adapte automatiquement aux filtres actifs.
Limite : le champ calculé opère sur les valeurs agrégées du TCD, pas sur les lignes individuelles. Si vous filtrez par catégorie, le calcul porte sur les sous-totaux visibles. Pour la plupart des ratios simples, c’est le comportement souhaité.
Modèle de données et mesures DAX
Pour des calculs plus fins, le modèle de données (Power Pivot) permet d’écrire des mesures DAX. Une mesure comme AVERAGEX avec un filtre CALCULATE recalcule la moyenne en parcourant chaque ligne de la table source, en tenant compte de tous les filtres du TCD et en excluant les zéros via une condition explicite.
Cette approche est plus puissante, mais elle demande d’activer le complément Power Pivot et de charger les données dans le modèle. Elle devient pertinente quand un simple champ calculé ne suffit plus, par exemple pour un panier moyen qui exclut les retours produits.
Erreur courante : formater une somme en pourcentage au lieu de calculer une proportion
Un piège signalé régulièrement dans les formations Excel concerne les pourcentages dans un TCD. Afficher une somme « en % du total » n’est pas la même chose que calculer une proportion réelle. La première option formate un chiffre existant. La seconde effectue une division.
Quand vous faites clic droit sur un champ de valeur, puis « Afficher les valeurs », puis « % du total général », Excel divise chaque sous-total par le total général. Si vos filtres masquent des lignes, le total général change, et tous les pourcentages bougent.
Si vous voulez une proportion stable (par exemple, la part d’un produit sur le total annuel même quand vous filtrez par trimestre), vous devez créer un champ calculé ou une mesure DAX qui fige le dénominateur. Sinon, chaque modification de filtre redistribue les pourcentages sur un périmètre différent.
Résumé des formules utiles pour le calcul de moyenne Excel
| Besoin | Formule ou méthode | Gère les filtres |
| Moyenne simple | =MOYENNE(plage) | Non |
| Moyenne des lignes visibles | =SOUS.TOTAL(101;plage) | Oui |
| Moyenne hors zéros | =MOYENNE.SI(plage; »<>0″) | Non |
| Moyenne TCD standard | Paramètre Synthèse par : Moyenne | Oui (recalcul auto) |
| Ratio dans un TCD | Champ calculé ou mesure DAX | Oui |
Le point qui fait la différence dans la fiabilité d’un calcul moyenne Excel, c’est la qualité de la source. Un TCD ne peut pas deviner si un zéro signifie « absence de donnée » ou « valeur réelle à zéro ». Nettoyez la colonne source avant de construire le tableau croisé dynamique, et le dénominateur suivra sans surprise à chaque changement de filtre.
