La moyenne pondérée dans Excel se calcule avec deux fonctions combinées : SOMMEPROD divisée par SOMME. Il n’existe pas de fonction native dédiée comme MOYENNE.PONDEREE. Cette absence surprend beaucoup d’utilisateurs, mais la formule à construire reste courte et fiable une fois comprise.
SOMMEPROD et SOMME : la seule formule de moyenne pondérée fiable sur Excel
La formule à copier dans n’importe quelle cellule est celle-ci :
=SOMMEPROD(plage_valeurs;plage_poids)/SOMME(plage_poids)
SOMMEPROD multiplie chaque valeur par le poids situé sur la même ligne, puis additionne tous ces produits. SOMME additionne les poids. La division donne la moyenne pondérée.
Prenons un tableau de notes avec coefficients. En colonne B, les notes (14, 8, 16). En colonne C, les coefficients (5, 2, 3). La formule devient :
=SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4)
Excel calcule d’abord (14×5)+(8×2)+(16×3) = 70+16+48 = 134, puis divise par 5+2+3 = 10. Résultat : 13,4. Avec une moyenne simple (fonction MOYENNE), le résultat aurait été 12,67 – la note à fort coefficient tire le résultat vers le haut.
Poids exprimés en pourcentages sur Excel : le piège de SOMMEPROD seule
Quand les poids sont exprimés en pourcentages qui totalisent 100 %, certains guides suggèrent d’utiliser SOMMEPROD seule, sans diviser par SOMME. Le résultat est correct dans ce cas précis, mais cette approche devient fausse dès que la somme des poids s’écarte de 1.
Imaginons trois critères pondérés à 40 %, 35 % et 30 %. La somme fait 105 %, pas 100 %. Si les poids sont saisis en cellules (0,40 – 0,35 – 0,30), SOMMEPROD seule renverra un chiffre gonflé. La division par SOMME corrige automatiquement ce décalage.
La règle à retenir : toujours diviser par SOMME(poids), même avec des pourcentages. La formule complète fonctionne dans tous les cas. La version raccourcie ne fonctionne que sous condition.
Moyenne pondérée Excel avec des poids qui ne totalisent pas 100 %
Ce cas de figure est fréquent en dehors du cadre scolaire. Dans un tableau de suivi financier, les pondérations peuvent refléter des volumes échangés, des durées de placement ou des montants investis. Rien n’oblige ces valeurs à totaliser 100 ou 1.
Exemple avec des montants financiers
Un portefeuille contient trois lignes :
- Ligne A : rendement de 3,2 %, montant investi 15 000
- Ligne B : rendement de 1,8 %, montant investi 42 000
- Ligne C : rendement de 5,1 %, montant investi 8 000
Les rendements vont en colonne B (B2:B4), les montants en colonne C (C2:C4). La formule reste identique :
=SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4)
Les montants servent de poids. La ligne B, qui représente la plus grosse part du portefeuille, pèse davantage dans le rendement moyen. Le résultat reflète la réalité financière, contrairement à une moyenne simple qui donnerait un poids égal à chaque ligne.
Exemple avec des coefficients ECTS
Les crédits ECTS fonctionnent de la même façon. Une UE à 6 crédits pèse trois fois plus qu’une UE à 2 crédits. La colonne des crédits remplace celle des coefficients, et la formule ne change pas.
Erreurs #VALEUR! et #DIV/0! dans le calcul de moyenne pondérée
Deux erreurs reviennent souvent avec cette formule. Chacune a une cause précise.
#VALEUR! apparaît quand les deux plages n’ont pas la même taille. SOMMEPROD exige que les plages contiennent exactement le même nombre de cellules. Si la plage des valeurs couvre 5 lignes et celle des poids en couvre 4, Excel ne peut pas faire la multiplication terme à terme. La correction consiste à vérifier que les deux plages commencent et finissent sur les mêmes lignes.
#DIV/0! se produit quand la somme des poids vaut zéro, ce qui arrive si la colonne des poids est vide ou remplie de texte. Pour éviter un affichage d’erreur dans un tableau partagé, enveloppez la formule avec SIERREUR :
=SIERREUR(SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4); »Poids manquants »)
Cette protection renvoie un message lisible au lieu d’un code d’erreur.
Moyenne pondérée conditionnelle avec SOMMEPROD sur Excel
SOMMEPROD accepte des conditions directement dans ses arguments, ce qui permet de filtrer les données avant de calculer la moyenne pondérée.
Supposons un tableau de notes par matière (colonne A), avec notes en colonne B et coefficients en colonne C. Pour calculer la moyenne pondérée uniquement des matières scientifiques identifiées par le libellé « Sciences » en colonne A :
=SOMMEPROD((A2:A10= »Sciences »)*B2:B10*C2:C10)/SOMMEPROD((A2:A10= »Sciences »)*C2:C10)
La partie (A2:A10= »Sciences ») génère un tableau de 1 et de 0. Seules les lignes correspondant au critère entrent dans le calcul. Le dénominateur utilise aussi SOMMEPROD (et non SOMME) pour appliquer le même filtre aux poids.
- Le critère peut être numérique : (C2:C10>=3) ne retient que les lignes avec un coefficient supérieur ou égal à 3
- Plusieurs critères se combinent par multiplication : (A2:A10= »Sciences »)*(C2:C10>=3)
- Les parenthèses autour de chaque condition sont obligatoires pour qu’Excel les interprète comme des tableaux
Cette technique remplace avantageusement les colonnes auxiliaires de calcul intermédiaire et garde le tableau lisible.
La formule SOMMEPROD/SOMME couvre la totalité des cas de moyenne pondérée sur Excel, des notes scolaires aux analyses financières. Tant qu’Excel n’intègre pas de fonction dédiée, cette combinaison reste la référence. Le seul point de vigilance durable concerne la taille des plages : une ligne de décalage entre valeurs et poids suffit à produire une erreur silencieuse ou un #VALEUR! qui bloque tout le classeur.
