Moyenne pondérée Excel : formule SOMMEPROD

SOMMEPROD multiplie chaque valeur par son coefficient puis additionne les résultats ; la division par la somme des coefficients transforme ce total en moyenne.

Dans ce tutoriel, nos formateurs vous montrent pas à pas comment appliquer cette formule.

Un fichier d'exercices corrigés gratuit accompagne chaque cas.

Téléchargez le fichier d'exercices corrigés

Pour mettre en pratique chaque méthode de ce tutoriel, téléchargez gratuitement notre classeur de 6 exercices corrigés — un cas par métier :

• Exercice 1 : RH — évaluation annuelle collaborateur avec pondération en % ;

• Exercice 2 : Commercial — prix de vente moyen pondéré par volume ;

• Exercice 3 : Finance — taux moyen pondéré d'un portefeuille d'obligations ;

• Exercice 4 : Marketing — satisfaction pondérée par volume de répondants ;

• Exercice 5 : Comptable — PMP en stock avec méthode manuelle ;

• Exercice 6 : Gestion de projet — score de risque pondéré avec critère de catégorie.

moyenne-ponderee-excel.xlsx

Télécharger XLSX • 112KB

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

Une moyenne pondérée est une moyenne dans laquelle chaque valeur est multipliée par un poids (appelé aussi coefficient) qui reflète son importance. La somme des produits est ensuite divisée par la somme des poids :

Moyenne pondérée = (valeur1 × poids1 + valeur2 × poids2 + …) ÷ (poids1 + poids2 + …)

À la différence de la moyenne arithmétique, où toutes les valeurs comptent autant , la moyenne pondérée donne plus d'influence aux valeurs à fort coefficient.

C'est le principe de la moyenne scolaire, mais aussi celui d'une évaluation RH pondérée par les enjeux, d'un prix moyen d'achat pondéré par les volumes, d'un taux moyen pondéré par les capitaux investis, ou d'une satisfaction pondérée par le nombre de répondants.

Si vous cherchez au contraire à calculer une moyenne simple sur Excel, la fonction MOYENNE suffit dès que les poids diffèrent, en revanche, elle renvoie un résultat faux.

La formule pour calculer une moyenne pondérée sur Excel

Excel ne propose pas de fonction MOYENNE.PONDEREE : la méthode recommandée par Microsoft et par nos formateurs repose sur le couple SOMMEPROD + SOMME

SOMMEPROD + SOMME réalise l'ensemble du calcul dans une seule cellule. Le cas typique est celui d'une évaluation annuelle collaborateur, avec quatre critères pondérés en pourcentages : c'est exactement le sujet de l'Exercice 1 : RH : Évaluation annuelle collaborateur du fichier téléchargeable.

Étape 1 : préparer le tableau de données

Structurez vos données en trois colonnes : l'intitulé du critère, la note (ici sur 20) et le poids (ici en pourcentages qui totalisent 100 %). Dans l'exercice, les notes occupent la plage C6:C9 et les poids la plage D6:D9.

Étape 2 : saisir la formule SOMMEPROD

Dans la cellule du résultat, saisissez :

=SOMMEPROD(C6:C9 ; D6:D9) / SOMME(D6:D9)

SOMMEPROD multiplie ligne par ligne les notes par les poids, puis additionne l'ensemble des produits : c'est le numérateur.

SOMME(D6:D9) additionne les poids et sert de dénominateur.

Comprenez bien la différence entre les moyennes pondérées et simple :

Moyenne Pondérée

• Poids classiques Formule SOMMEPROD(notes;poids)/SOMME(poids)

• Poids en pourcentage Formule SOMMEPROD(notes;poids)

Moyenne Simple

• Sans poids Formule : =MOYENNE(notes)

  
  

Avec les notes 14, 16, 12 et 18 pondérées à 40 %, 30 %, 20 % et 10 %, la formule renvoie 14,60 , soit une différence significative avec la moyenne simple, qui renvoie 15,00.

C'est ce que révèle l'exercice : sans pondération, on surévalue systématiquement les critères secondaires.

Étape 3 : ajouter une cellule de contrôle

Nos formateurs conseillent d'ajouter une cellule =SOMME(D6:D9) qui doit afficher 100 % : c'est le garde-fou qui empêche 90 % des erreurs de saisie.

Vous retrouverez ce contrôle sur toutes les feuilles de l'exercice, c'est un réflexe à intégrer dès la construction du modèle.

La méthode manuelle avec une colonne intermédiaire

Avant d'automatiser avec SOMMEPROD, il est utile de comprendre le calcul en le décomposant. C'est l'approche de l'Exercice 5 : Comptable : PMP en stock (méthode manuelle) : trois lots d'entrée en stock (150 unités à 12,50 €, 200 à 13,80 €, 120 à 14,20 €), et l'objectif de calculer le Prix Moyen Pondéré d'entrée en stock, un indicateur central de la comptabilité analytique.

• créez la colonne « Total ligne » = quantité × prix unitaire, obtenue par =C6*D6 étendue par la poignée de recopie ;

• additionnez ensuite les quantités avec =SOMME(C6:C8), soit 470 unités ;

• additionnez la valeur totale du stock avec =SOMME(E6:E8), soit 6 339 € ;

• divisez enfin les deux totaux : =E20/C19 renvoie 13,49 € le kilo — le PMP recherché.

La même formule condensée avec SOMMEPROD — =SOMMEPROD(C6:C8;D6:D8)/SOMME(C6:C8) — renvoie évidemment 13,49 € : les deux méthodes donnent strictement le même résultat, mais la méthode manuelle permet de tracer chaque étape, ce qui est précieux lors d'un audit ou d'un contrôle comptable.

Calculer une moyenne pondérée avec des pourcentages

Quand les poids totalisent exactement 100 %, la formule peut être simplifiée. C'est la variante illustrée dans l'Exercice 1 : RH : Évaluation annuelle collaborateur : puisque 40 % + 30 % + 20 % + 10 % = 100 %, la somme des poids vaut 1, et le dénominateur devient facultatif.

Deux formules équivalentes :

• =SOMMEPROD(C6:C9 ; D6:D9) // suffisant si la somme des poids fait 100 %

• =SOMMEPROD(C6:C9 ; D6:D9) / SOMME(D6:D9) // toujours juste, quelle que soit la somme des poids

• 
  

Nos formateurs recommandent la seconde : elle reste juste même si vous modifiez ensuite les poids et qu'ils ne totalisent plus 100 %.

Excel stocke 40 % comme la valeur décimale 0,4 : la conversion est automatique, sans aucune manipulation de votre part.

Prix moyen pondéré par volume : le cas commercial

Le prix de vente moyen d'un produit dépend fortement des volumes vendus par région : sans pondération, une région marginale pèse autant qu'un marché principal.

C'est le sujet de l'Exercice 2 : Commercial : Prix de vente moyen pondéré, avec quatre régions et des volumes très inégaux (Bordeaux 120, Lyon 80, Paris 200, Marseille 60).

=SOMMEPROD(C6:C9 ; D6:D9) / SOMME(C6:C9)

Ici, la pondération se fait par les quantités (colonne C), pas par des poids en %.

La formule renvoie 48,17 €,le prix moyen réellement perçu, contre 48,75 € si l'on avait fait une moyenne simple : un écart certes modeste, mais qui devient significatif dès que les volumes sont plus déséquilibrés.

Taux moyen pondéré d'un portefeuille : le cas finance

Le principe est identique en finance : un portefeuille d'obligations n'a pas de taux « moyen » simple, car les capitaux investis diffèrent d'une ligne à l'autre.

L'Exercice 3 : Finance : Taux moyen pondéré d'un portefeuille propose quatre obligations, respectivement 50 000 € à 3,20 %, 80 000 € à 2,80 %, 30 000 € à 4,10 % et 40 000 € à 3,50 %.

=SOMMEPROD(C6:C9 ; D6:D9) / SOMME(C6:C9)

La formule renvoie 3,24 % le rendement moyen pondéré du portefeuille, à comparer à la moyenne arithmétique de 3,40 % qui, elle, surévalue le rendement réel.

C'est la même mécanique qui permet de calculer le coût moyen pondéré du capital (WACC), le coût de la dette moyen pondéré, ou encore une durée moyenne pondérée.

Satisfaction pondérée par volume : le cas marketing

Une étude client ne peut pas être analysée en moyenne simple si les segments n'ont pas le même nombre de répondants.

L'Exercice 4 : Marketing : Satisfaction pondérée par répondants illustre le point : 320 répondants B2B, 890 B2C, 150 Enterprise et 420 PME. Sans pondération, un segment ultra-minoritaire (Enterprise) pèserait autant qu'un segment de masse (B2C) — biais méthodologique classique.

=SOMMEPROD(C6:C9 ; D6:D9) / SOMME(C6:C9)

La note moyenne pondérée obtenue est 8,11/10, contre 8,35/10 en moyenne simple.

Cet écart de 0,24 point est directement causé par le poids réel du segment B2C — le plus nombreux et aussi celui qui note le plus sévèrement.

Moyenne pondérée avec une condition

Dernier cas, particulièrement fréquent en gestion de projet : calculer une moyenne pondérée sur une portion filtrée des données.

L'Exercice 6 : Gestion de projet : Score de risque par catégorie propose une liste de six risques (fuite de données, retard fournisseur, bug critique, dépassement budget, accès non autorisé, turnover) répartis en catégories, et l'objectif de calculer le score de risque moyen pondéré des risques classés « Sécurité » la sévérité étant pondérée par la probabilité d'occurrence.

=SOMMEPROD((C6:C11="Sécurité")*D6:D11*E6:E11) / SOMME.SI(C6:C11 ; "Sécurité" ; D6:D11)

Le test (C6:C11="Sécurité") renvoie VRAI ou FAUX ligne par ligne

la multiplication convertit ces résultats en 1 ou 0, neutralisant les lignes des autres catégories.

Au dénominateur, SOMME.SI additionne uniquement les probabilités des risques Sécurité.

Résultat : 8,92/10 pour la catégorie Sécurité, contre 6,00 pour la catégorie Planning. Cette mécanique du test logique est détaillée dans notre article SOMMEPROD avec un critère SI.

Les 4 erreurs fréquentes à éviter

Oublier le dénominateur. La formule =SOMMEPROD(C6:C9;D6:D9) seule renvoie le total des points pondérés — pas la moyenne. C'est l'erreur la plus courante ; sans division par la somme des coefficients, le résultat n'a aucun sens.

Des plages de tailles différentes. Si les valeurs couvrent C6:C11 mais les coefficients seulement D6:D10, SOMMEPROD renvoie #VALEUR!. Les deux plages doivent contenir exactement le même nombre de cellules.

Des coefficients stockés en texte. Un poids copié depuis un PDF ou un e-mail peut être enregistré comme du texte : Excel affiche « 3 » mais l'ignore dans le calcul, sans aucun message d'erreur. Vérifiez l'alignement des cellules (un nombre s'aligne à droite par défaut) et utilisez Données > Convertir si nécessaire.

Des cellules vides. SOMMEPROD traite une cellule vide comme un zéro : une note manquante fait chuter la moyenne au lieu d'être ignorée. Excluez la ligne du calcul ou complétez la donnée avant de valider.

Astuce : pour recopier la formule vers d'autres cellules, pensez à figer les plages avec des références absolues en ajoutant le signe dollar. Voir notre guide sur le signe dollar sur Excel.

FAQ : vos questions sur la moyenne pondérée Excel

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

Non. Aucune version d'Excel ne propose de fonction dédiée à la moyenne pondérée. Le calcul s'effectue en combinant SOMMEPROD (somme des produits valeurs × poids) et SOMME (somme des poids) : =SOMMEPROD(valeurs;poids)/SOMME(poids).

Faut-il toujours diviser par la somme des coefficients ?

Oui, sauf si les poids totalisent exactement 1 (soit 100 %). Dans tous les autres cas la division est indispensable : sans elle, la formule renvoie un total pondéré, pas une moyenne. Conserver la division reste malgré tout la meilleure pratique — la formule demeure juste si les poids évoluent.

Pourquoi SOMMEPROD renvoie-t-elle l'erreur #VALEUR! ?

Dans la quasi-totalité des cas, les deux plages n'ont pas la même taille (six valeurs pour cinq coefficients, par exemple). Alignez les plages cellule à cellule. Une cellule contenant du texte au milieu des valeurs peut provoquer la même erreur.

Comment calculer une moyenne pondérée avec des pourcentages ?

Saisissez les poids au format pourcentage (40 %, 30 %, …). Si leur total fait 100 %, =SOMMEPROD(valeurs;poids) suffit — Excel convertit automatiquement 40 % en 0,4. Sinon, conservez la division par SOMME(poids).

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

La fonction MOYENNE attribue le même poids à toutes les valeurs (somme divisée par le nombre de valeurs). La moyenne pondérée multiplie d'abord chaque valeur par son coefficient : une valeur de coefficient 4 pèse quatre fois plus qu'une valeur de coefficient 1. Dès que les poids diffèrent, MOYENNE renvoie un résultat faux.

Comment calculer une médiane pondérée sur Excel ?

Excel ne propose pas non plus de fonction native pour la médiane pondérée. La méthode consiste à trier les valeurs par ordre croissant, à calculer le cumul des poids, puis à repérer la première valeur pour laquelle ce cumul atteint 50 % du poids total.