Calculer une moyenne pondérée dans Excel, et pourquoi MOYENNE se trompe

Calculez une moyenne pondérée dans Excel avec SOMMEPROD et SOMME, ajoutez une condition, affichez-la dans un tableau croisé et évitez le piège de MOYENNE.

Fondateur, Glow Reports. , 4 min de lecture
  • Excel
  • Formules
  • Reporting
Une balance avec des piles de blocs de hauteurs différentes, la plus haute faisant pencher le fléau, à côté d'un petit tableau
Sur cette page
  1. Qu'est-ce qu'une moyenne pondérée ?
  2. Comment calculer une moyenne pondérée dans Excel ?
  3. Pourquoi la moyenne simple donne-t-elle un résultat faux ?
  4. Comment calculer une moyenne pondérée avec une condition ?
  5. Comment afficher une moyenne pondérée dans un tableau croisé dynamique ?
  6. Comment calculer une moyenne pondérée dans Google Sheets ?
  7. Où se place Glow Reports ?
  8. Questions fréquentes

Vous voulez le prix moyen payé par vos clients, vous sélectionnez la colonne des prix unitaires et vous tapez MOYENNE. Le résultat est faux dès que certaines lignes ont vendu beaucoup plus d'unités que d'autres. La moyenne pondérée corrige ce biais : chaque valeur compte en proportion de son poids, ici les unités vendues. Dans Excel, fait le calcul dans une seule cellule. Voici la formule, pourquoi la moyenne simple trompe, la version avec une condition, la méthode par tableau croisé dynamique et Google Sheets.

Qu'est-ce qu'une moyenne pondérée ?

Une moyenne pondérée multiplie chaque valeur par son poids, additionne les résultats, puis divise par la somme des poids. Les valeurs au poids élevé tirent le résultat vers elles. Quand tous les poids sont égaux, elle donne le même résultat qu'une moyenne simple.

En formule : moyenne pondérée = (valeur 1 × poids 1 + valeur 2 × poids 2 + ...) / (poids 1 + poids 2 + ...).

Le poids, c'est ce qui fait compter une ligne plus qu'une autre. Les unités vendues pour un prix moyen. Le chiffre d'affaires pour un taux de marge moyen. Le nombre de clients pour un taux d'attrition moyen entre segments. Les heures pour un coût horaire moyen.

L'exemple ci-dessous utilise six lignes de ventes : la région en colonne A, le produit en B, les unités vendues en C et le prix unitaire en D.

RégionProduitUnitésPrix unitaire
NordBasique9004,50
NordPlus20012,00
NordPro2030,00
SudBasique3004,50
SudPlus10012,00
SudPro3030,00

Comment calculer une moyenne pondérée dans Excel ?

Utilisez SOMMEPROD pour le haut de la fraction et SOMME pour le bas. Avec les unités en C2 :C7 et les prix en D2 :D7, tapez . L'exemple renvoie 6,77, le prix moyen payé par unité.

Prix moyen pondéré, 6,77
=SOMMEPROD(C2:C7;D2:D7)/SOMME(C2:C7)

SOMMEPROD renvoie la somme des produits des plages ou matrices correspondantes Microsoft Support. Ici, elle multiplie les unités par le prix sur chaque ligne et additionne les six résultats : 10 500 de chiffre d'affaires. SOMME additionne les unités : 1 550. Le chiffre d'affaires divisé par les unités donne 6,77.

Trois règles gardent la formule exacte :

  1. Les deux plages couvrent les mêmes lignes. Microsoft précise que les matrices doivent avoir les mêmes dimensions, sinon SOMMEPROD renvoie #VALEUR!.
  2. Le bas de la fraction additionne les poids, jamais les valeurs. à cet endroit donne un chiffre sans aucun sens.
  3. Les nombres stockés en texte comptent pour zéro, car SOMMEPROD traite les entrées non numériques comme des zéros. Un prix collé depuis un export sous forme de texte disparaît du calcul sans la moindre erreur. Vérifiez avec , qui doit renvoyer le nombre de lignes.

Pourquoi la moyenne simple donne-t-elle un résultat faux ?

MOYENNE donne la même importance à chaque ligne. Un produit vendu à 20 unités pèse autant qu'un produit vendu à 900. Dans l'exemple, renvoie 15,50, plus du double des 6,77 payés par les clients.

15,50 est la moyenne simple des six prix. Les clients ont payé en moyenne 6,77 par unité.

L'écart vient de la gamme Pro. Son prix de 30,00 compte pour un tiers de la moyenne simple, alors qu'elle représente 50 des 1 550 unités vendues. Un reporting qui affiche 15,50 comme prix de vente moyen le surestime.

Le même piège revient à trois endroits :

  • La moyenne de taux. Deux régions à 25 % et 45 % de marge ne font pas 35 % en moyenne si l'une pèse 80 000 de chiffre d'affaires et l'autre 20 000. Pondérée par le chiffre d'affaires, la marge est de 29 %.
  • La moyenne de moyennes. Le prix moyen du Nord est de 6,29 et celui du Sud de 8,02. Leur moyenne simple fait 7,16, pas les 6,77 de l'ensemble des ventes. Un chiffre annuel construit à partir de moyennes mensuelles tombe dans le même travers, comme le montre comment calculer le panier moyen.
  • La moyenne de variations. Des taux de croissance calculés sur des mois de tailles différentes demandent le même soin. La base compte, comme l'explique comment calculer un pourcentage d'évolution dans Excel.

Comment calculer une moyenne pondérée avec une condition ?

Multipliez la condition dans SOMMEPROD, et utilisez SOMME.SI.ENS pour les poids. Pour la région Nord, renvoie 6,29.

Prix moyen pondéré du Nord, 6,29
=SOMMEPROD((A2:A7="Nord")*C2:C7*D2:D7)/SOMME.SI.ENS(C2:C7;A2:A7;"Nord")

Le test renvoie VRAI ou FAUX sur chaque ligne. Multiplié par les autres plages, VRAI vaut 1 et FAUX vaut 0 : seules les lignes du Nord entrent dans le total. La page de Microsoft sur SOMMEPROD utilise le même principe pour totaliser les ventes d'une région et d'un article Microsoft Support.

SOMME.SI.ENS prend d'abord la plage à additionner, puis chaque plage de critères suivie de son critère Microsoft Support. Pour une deuxième condition, ajoutez un deuxième test dans SOMMEPROD et une deuxième paire dans SOMME.SI.ENS. Placez la région dans une cellule, par exemple F2, et écrivez et  : une seule formule sert alors pour toutes les régions.

Le Nord sort à 6,29 et le Sud à 8,02. Le Sud a vendu une plus grande part d'unités Pro, 30 sur 430, ce qui relève son prix moyen.

Comment afficher une moyenne pondérée dans un tableau croisé dynamique ?

Ajoutez une colonne d'aide à la source et divisez deux sommes avec un champ calculé. La synthèse Moyenne d'Excel donne la moyenne simple, elle ne suffit donc pas. Avec une colonne CA égale à , un champ calculé renvoie la moyenne pondérée sur chaque ligne du tableau croisé.

  1. Ajoutez au tableau source une colonne nommée CA, avec sur chaque ligne.
  2. Actualisez le tableau croisé pour faire apparaître le nouveau champ.
  3. Sélectionnez Analyse du tableau croisé dynamiqueChamps, éléments et jeuxChamp calculé.
  4. Nommez-le « Prix moyen » et tapez comme formule.

La question revient sur les forums Excel, où des utilisateurs constatent que « Moyenne de » dans un tableau croisé renvoie une moyenne simple des moyennes forum Chandoo.

La méthode fonctionne parce que, selon Microsoft, « les formules associées aux champs calculés fonctionnent sur la somme des données sous-jacentes » Microsoft Support. Pour le Nord, le champ divise 7 050 de chiffre d'affaires par 1 120 unités et affiche 6,29. Le total général affiche 6,77.

La même règle casse le raccourci. Un champ calculé multiplie le total des unités par le total des prix, 1 550 × 93,00, et renvoie 144 150. Construisez toujours le produit dans la source, puis divisez des sommes dans le tableau croisé. Pour bâtir le tableau croisé lui-même et grouper les dates, voyez comment faire un tableau croisé dynamique et le grouper par mois.

Comment calculer une moyenne pondérée dans Google Sheets ?

Utilisez AVERAGE.WEIGHTED, avec les valeurs en premier et les poids en second. Dans l'exemple, renvoie 6,77, comme la formule SOMMEPROD, qui fonctionne aussi dans Sheets.

L'aide de Google précise que les poids ne peuvent pas être négatifs mais peuvent valoir zéro, et qu'au moins un poids doit être positif. La plage des poids doit avoir la même taille que celle des valeurs, selon la même page Aide Éditeurs Google Docs. Excel n'a pas d'équivalent, donc SOMMEPROD reste la formule à connaître.

Où se place Glow Reports ?

Les formules ci-dessus suffisent pour un tableau. Le travail grossit quand la moyenne pondérée est un chiffre parmi vingt dans un reporting mensuel, sur un nouvel export à chaque fois, et que quelqu'un doit revérifier chaque plage.

Glow Reports part de cet export. Vous importez le fichier CSV ou Excel et vous demandez, en langage courant, le prix moyen par région pondéré par les unités. L'IA écrit le SQL, qui divise la somme du chiffre d'affaires par la somme des unités pour chaque groupe, construit le graphique et rédige le texte. Le SQL figure sous chaque chiffre : vous pouvez vérifier la pondération et la corriger si la mauvaise colonne a servi. Les rapports s'exportent en PDF. L'offre gratuite donne 300 crédits une fois, et un rapport coûte 100 crédits.

Questions fréquentes

Quelle différence entre une moyenne et une moyenne pondérée ?

Une moyenne simple donne la même importance à chaque valeur. Une moyenne pondérée donne à chaque valeur une importance fixée par son poids, comme les unités vendues ou le chiffre d'affaires. Les deux coïncident seulement quand tous les poids sont égaux.

Les poids doivent-ils faire 100 % ?

Non. La formule divise par la somme des poids, donc des quantités brutes comme les unités fonctionnent aussi bien que des pourcentages. Des poids de 900, 200 et 20 donnent le même résultat que ces poids convertis en parts de 1 120.

Existe-t-il une fonction MOYENNE.PONDEREE dans Excel ?

Non. Excel n'a pas de fonction de moyenne pondérée intégrée. fait le travail. Google Sheets propose AVERAGE.WEIGHTED.

Comment calculer un pourcentage moyen pondéré ?

Pondérez chaque taux par sa base. Pour des marges, utilisez le chiffre d'affaires comme poids : . Le résultat est égal à la marge totale divisée par le chiffre d'affaires total.

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez votre export dans Glow Reports et obtenez des moyennes pondérées par groupe, avec le SQL derrière chaque chiffre.

À lire aussi