=SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5) calcule une moyenne pondérée : chaque note de B est multipliée par son coefficient en C, les produits sont additionnés, et le total est divisé par la somme des coefficients. SOMMEPROD s'appelle SUMPRODUCT et SOMME SUM dans un Excel anglais ; le tableau affiche les formules sous cette forme, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Average | Result | |
| 2 | Homework | 85 | 20% | Weighted | 81.2 | |
| 3 | Quizzes | 78 | 30% | Plain AVERAGE | 80.75 | |
| 4 | Midterm | 72 | 20% | |||
| 5 | Final | 88 | 30% |
=SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5)La note pondérée vaut 81.2, alors qu'une simple MOYENNE (AVERAGE) donne 80.75, car elle traite les devoirs, qui pèsent 20 %, comme s'ils comptaient autant que l'examen final, qui pèse 30 %. Modifiez la note du final : la note pondérée bouge davantage que pour la même modification des devoirs.
Excel n'a pas de fonction de moyenne pondérée : SOMMEPROD divisée par SOMME est la formule standard. Google Sheets propose AVERAGE.WEIGHTED(B2:B5,C2:C5).
Comment fonctionne la formule de moyenne pondérée
SOMMEPROD multiplie les deux plages ligne par ligne et additionne les résultats. Décomposée avec une colonne d'aide, c'est une colonne de produits et leur SOMME :
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Score | Weight | Score x weight |
| 2 | Homework | 85 | 20% | 17.0 |
| 3 | Quizzes | 78 | 30% | 23.4 |
| 4 | Midterm | 72 | 20% | 14.4 |
| 5 | Final | 88 | 30% | 26.4 |
| 6 | Total | 100% | 81.2 | |
| 7 | Weighted average | 81.2 |
=SOMME(D2:D5)Chaque partie apporte sa note multipliée par son coefficient : 85 × 20 % donne 17,0, 78 × 30 % donne 23,4, et ainsi de suite. Le total fait 81,2. Les coefficients totalisent 100 %, donc diviser par C6 ne change rien ici, mais c'est ce qui garde la formule juste quand ce n'est pas le cas.
Des coefficients dont le total n'est pas 100 %
Les coefficients ne sont pas forcément des pourcentages. Une moyenne de type GPA est pondérée par les crédits, un prix moyen par les quantités. Diviser par la SOMME des coefficients fonctionne quel que soit le total.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Course | Grade points | Credits | Average | Result | |
| 2 | Math | 4 | 4 | Weighted GPA | 3.51 | |
| 3 | History | 3 | 3 | Plain average | 3.48 | |
| 4 | Biology | 3.7 | 4 | Total credits | 14 | |
| 5 | Art | 2.7 | 2 | |||
| 6 | Lab | 4 | 1 |
=SOMMEPROD(B2:B6;C2:C6)/SOMME(C2:C6)Sans la division, la formule renverrait la somme des points multipliés par les crédits, ici 49,2, pas une moyenne. Avec elle, F2 affiche la moyenne pondérée par 14 crédits. Les cours à quatre crédits tirent la moyenne vers leurs notes, et le TP à un crédit la fait à peine bouger : mettez 2 en B6 et comparez le faible changement de F2 avec celui de F3.
Si vos coefficients sont des pourcentages qui totalisent exactement 100 %, =SOMMEPROD(B2:B5;C2:C5) seule donne le même résultat. Gardez quand même le /SOMME(...) : le jour où un coefficient change et que le total passe à 105 %, la formule sans division est fausse et rien sur la feuille ne le signale.
Moyenne pondérée avec une condition
Pour ne pondérer que certaines lignes, multipliez par une condition dans SOMMEPROD, et additionnez les coefficients correspondants avec SOMME.SI. Ci-dessous, le prix moyen par région est pondéré par la quantité vendue.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Price | Qty | Region | Average price | |
| 2 | North | Apple | $1.20 | 100 | North | $1.45 | |
| 3 | South | Pear | $1.50 | 40 | South | $1.36 | |
| 4 | North | Pear | $1.50 | 60 | |||
| 5 | South | Apple | $1.20 | 120 | |||
| 6 | North | Plum | $2.00 | 40 | |||
| 7 | South | Plum | $2.00 | 20 |
=SOMMEPROD((A2:A7=F2)*C2:C7*D2:D7)/SOMME.SI(A2:A7;F2;D2:D7)North a vendu 100 pommes, 60 poires et 40 prunes : son prix moyen est de $1.45, plus proche du prix de la pomme que ne le serait une simple moyenne des trois prix. La condition (A2:A7=F2) vaut 1 sur les lignes North et 0 ailleurs : les autres lignes n'ajoutent rien au numérateur, et SOMME.SI n'additionne que les quantités North au dénominateur.
Exercice : prix moyen pondéré
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Batch | Price | Qty | Average | Result | |
| 2 | Jan | $4.20 | 100 | Weighted price | ||
| 3 | Feb | $4.50 | 40 | |||
| 4 | Mar | $3.90 | 250 | |||
| 5 | Apr | $4.80 | 10 | |||
| 6 | May | $4.10 | 120 |
À vous : Vous avez acheté le même article en cinq lots à des prix différents. Calculez le prix moyen par unité, pondéré par la quantité de chaque lot. Écrivez la formule en F2.
Les erreurs qui faussent une moyenne pondérée
- La MOYENNE des produits.
=MOYENNE(D2:D5)sur une colonne de note × coefficient divise par le nombre de lignes, pas par les coefficients, et donne un petit nombre sans signification. Divisez la SOMME des produits par la SOMME des coefficients. - Diviser par le nombre de valeurs au lieu des coefficients.
=SOMMEPROD(B2:B6;C2:C6)/NB(B2:B6)n'est juste que si chaque coefficient vaut 1. - Des plages décalées.
=SOMMEPROD(B2:B6;C3:C7)associe chaque valeur au coefficient de la ligne suivante. Les deux plages doivent commencer et finir sur les mêmes lignes ; des tailles différentes renvoient#VALEUR!(en anglais #VALUE!). - Un coefficient vide. Un coefficient vide compte pour 0, et la ligne est exclue sans bruit. Si un coefficient manquant doit bloquer le calcul, vérifiez d'abord avec
=NB.VIDE(C2:C6). - La moyenne de moyennes. Deux moyennes de classe de 70 (10 élèves) et 90 (30 élèves) ne font pas 80 en moyenne. Pondérez-les par l'effectif des classes et le résultat est 85 ; la page MOYENNE.SI montre le même piège avec des conditions.
Questions fréquentes
Comment calculer une moyenne pondérée dans Excel ?
Utilisez =SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5), avec les valeurs en B et les coefficients en C. SOMMEPROD multiplie chaque valeur par son coefficient et additionne les résultats ; diviser par la somme des coefficients en fait une moyenne.
Les coefficients doivent-ils totaliser 100 % ?
Non, du moment que vous divisez par la SOMME des coefficients. Des crédits de 3, 4, 2 et 1, ou des coefficients de 2, 1 et 1, fonctionnent de la même façon. Seul le raccourci =SOMMEPROD(B2:B5;C2:C5) sans la division exige des coefficients totalisant exactement 100 %.
Existe-t-il une fonction MOYENNE.PONDEREE dans Excel ?
Non. Excel n'a pas de fonction intégrée de moyenne pondérée : la combinaison SOMMEPROD et SOMME est la formule standard. Dans Google Sheets, AVERAGE.WEIGHTED(B2:B5,C2:C5) fait la même chose.
Comment calculer une moyenne pondérée avec une condition ?
Ajoutez la condition dans SOMMEPROD et utilisez SOMME.SI pour les coefficients : =SOMMEPROD((A2:A7="North")*B2:B7*C2:C7)/SOMME.SI(A2:A7;"North";C2:C7).
