La moyenne pondérée sur Excel repose sur une formule connue : SOMMEPROD divisé par SOMME. Cette combinaison fonctionne parfaitement tant que la plage de données reste fixe. Le problème apparaît dès qu’une ligne est ajoutée, qu’un filtre masque certaines valeurs ou que des coefficients sont laissés vides. La formule initiale ne s’adapte pas et renvoie un résultat faux, parfois sans aucun message d’erreur visible.
Pourquoi une plage fixe casse votre calcul de moyenne pondérée
La formule classique =SOMMEPROD(B2:B10;C2:C10)/SOMME(C2:C10) s’appuie sur des références figées. Si vous insérez une ligne 11 avec de nouvelles données, elle ne sera pas prise en compte. Le résultat affiché reste celui de l’ancienne plage, sans signal d’alerte.
Ce comportement pose un second problème avec les filtres. Quand vous filtrez un tableau pour n’afficher qu’une catégorie, SOMMEPROD continue de calculer sur l’ensemble des lignes, y compris celles masquées. Votre moyenne pondérée filtrée est en réalité la moyenne globale.
Troisième piège : les poids incomplets. Si un coefficient est vide ou nul dans la plage, SOMMEPROD traite la cellule comme zéro. La valeur associée disparaît du numérateur, mais le dénominateur (SOMME des poids) n’est pas affecté de la même manière selon que la cellule contient 0 ou est vide. Un poids manquant fausse silencieusement le résultat final.
Plage dynamique Excel avec la fonction DECALER
Pour que la formule s’adapte automatiquement aux ajouts de lignes, la fonction DECALER permet de construire une plage dynamique qui grandit avec les données. DECALER prend un point d’ancrage, un décalage en lignes et colonnes, puis une hauteur et une largeur.
L’idée est de remplacer la plage fixe par une plage dont la hauteur dépend du nombre de cellules remplies. La fonction NBVAL compte les cellules non vides dans une colonne et sert de hauteur à DECALER.
Construction pas à pas
Supposons vos valeurs en B2 et vos poids en C2, avec un en-tête en ligne 1. La plage dynamique des valeurs s’écrit :
=DECALER(B2;0;0;NBVAL(B:B)-1;1)
NBVAL(B:B) compte toutes les cellules non vides de la colonne B, en-tête compris. On soustrait 1 pour exclure l’en-tête. La formule complète de moyenne pondérée devient :
=SOMMEPROD(DECALER(B2;0;0;NBVAL(B:B)-1;1);DECALER(C2;0;0;NBVAL(C:C)-1;1))/SOMME(DECALER(C2;0;0;NBVAL(C:C)-1;1))
Chaque ajout de ligne en bas du tableau étend automatiquement la plage. Aucune recopie manuelle, aucune mise à jour de référence.
- NBVAL compte les cellules remplies, ce qui rend la hauteur de plage variable selon les données réellement présentes.
- DECALER recalcule à chaque modification du classeur, garantissant que les nouvelles lignes sont immédiatement intégrées.
- Si une cellule de poids est vide, NBVAL sur la colonne des valeurs et celle des poids peut renvoyer des hauteurs différentes, ce qui génère une erreur. Utiliser la même colonne de référence pour les deux DECALER évite ce décalage.
Tableau structuré Excel : la méthode la plus fiable pour une formule dynamique
Depuis Excel 2007, la conversion d’une plage en tableau structuré (Ctrl+T) résout le problème de plage fixe sans recourir à DECALER. Un tableau structuré étend automatiquement ses références quand une ligne est ajoutée en dessous.
Avec un tableau nommé « Données » contenant les colonnes « Valeurs » et « Poids », la formule devient :
=SOMMEPROD(Données[Valeurs];Données[Poids])/SOMME(Données[Poids])
Aucun NBVAL, aucun DECALER. Le tableau structuré ajuste ses plages nativement. C’est la méthode à privilégier pour tout nouveau fichier.
Gérer les poids vides ou nuls dans un tableau structuré
Un coefficient manquant reste problématique même avec un tableau structuré. SOMMEPROD multiplie la valeur par zéro (ou ignore la ligne si la cellule est vide selon la version d’Excel), mais SOMME au dénominateur ne comptabilise pas ce poids. Le ratio est faussé.
Pour exclure proprement les lignes dont le poids est absent, une formule matricielle conditionnelle filtre les deux plages simultanément :
=SOMMEPROD((Données[Poids]<>0)*Données[Valeurs];(Données[Poids]<>0)*Données[Poids])/SOMMEPROD((Données[Poids]<>0)*Données[Poids])
La condition (Données[Poids]<>0) renvoie 1 ou 0 pour chaque ligne. Multipliée par les valeurs et les poids, elle neutralise les lignes incomplètes dans le numérateur comme dans le dénominateur.
Moyenne pondérée avec filtre actif : SOUS.TOTAL et alternatives
SOMMEPROD ne respecte pas les filtres automatiques d’Excel. Même avec un tableau structuré, si vous filtrez pour n’afficher qu’une catégorie, la moyenne pondérée reste calculée sur toutes les lignes.
La fonction SOUS.TOTAL ignore les lignes masquées par un filtre. Pour la somme, l’argument 109 (SOMME en ignorant les valeurs masquées) remplace SOMME classique. Le problème : SOUS.TOTAL ne gère pas nativement le produit valeur par poids.
Colonne auxiliaire comme solution
La méthode la plus lisible consiste à ajouter une colonne « Produit » dans le tableau structuré, contenant =[@Valeurs]*[@Poids] sur chaque ligne. La moyenne pondérée filtrée s’écrit alors :
=SOUS.TOTAL(109;Données[Produit])/SOUS.TOTAL(109;Données[Poids])
- L’argument 109 correspond à SOMME en excluant les lignes masquées manuellement ou par filtre automatique.
- La colonne Produit se remplit automatiquement sur les nouvelles lignes grâce au tableau structuré.
- Cette approche fonctionne aussi avec les segments (slicers) connectés au tableau.
- Pour un calcul sans colonne auxiliaire, la fonction AGREGAT avec l’argument 6 (produit) offre une alternative, mais sa syntaxe est nettement plus complexe.
Moyenne pondérée conditionnelle avec SOMMEPROD et critères
Quand le besoin dépasse le filtre visuel (par exemple, calculer la moyenne pondérée uniquement pour une catégorie donnée dans une formule), SOMMEPROD accepte des conditions matricielles directement dans ses arguments.
Si la colonne « Catégorie » du tableau contient le critère à isoler :
=SOMMEPROD((Données[Catégorie]="A")*Données[Valeurs]*Données[Poids])/SOMMEPROD((Données[Catégorie]="A")*Données[Poids])
La condition (Données[Catégorie]="A") agit comme un masque booléen. Les lignes qui ne correspondent pas au critère sont multipliées par 0 et n’affectent ni le numérateur ni le dénominateur. Cette technique se combine avec la vérification des poids non nuls en ajoutant *(Données[Poids]<>0) dans chaque argument.
Le tableau structuré garantit que toute nouvelle ligne avec la catégorie « A » sera automatiquement incluse dans le calcul, sans toucher à la formule.
La combinaison d’un tableau structuré, d’une colonne auxiliaire pour les filtres visuels et d’une condition matricielle pour les critères en formule couvre la grande majorité des cas d’usage. La division par SOMME des poids reste la clé pour que le résultat reste juste même quand les coefficients ne totalisent pas exactement 100 %.
