Formule de la moyenne pondérée

L’article présent de la Formation Excel vous explique comment créer une formule pour calculer la moyenne pondérée en vous servant de la fonction SOMMEPROD. La deuxième partie de l’article vous montre comment faire pour que votre formule créée calcule automatiquement une moyenne pondérée lorsque vous insérez de nouvelles valeurs ou vous en supprimez quelques-unes.
Formule moyenne pondérée


Une moyenne arithmétique simple est différente d’une moyenne pondérée. Dans l’article précédent (Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE) nous avons montré comment calculer une moyenne arithmétique en utilisant la fonction Moyenne. Et pour calculer une moyenne pondérée, Excel ne fournit aucune fonction spécifique jusqu’à la publication de cet article (26/02/2017). Cependant vous pouvez calculer cette moyenne en utilisant une fonctionne très importante qu’on appelle SOMMEPROD.

La moyenne pondérée

Une moyenne pondérée est une moyenne que l’on obtient en multipliant chaque valeur à son effectif appelé aussi poids, puis on additionne tous les produits obtenus et enfin on divise la somme obtenue par le total des poids.

Dans l’exemple suivant, un étudiant a eu les notes suivantes pour les matières : Maths, physique et anglais où chaque matière possède son propre coefficient :

Tableau notes par matières


  • Pour calculer la moyenne pondérée de cet étudiant, on écrit :

(15x6)+(13x5)+(17x3)/6+5+3
  • Ce qui donne 206/14
  • La moyenne donc est 14,71

Si vous voulez faire ce calcul dans Excel, vous pouvez procéder ainsi :
  • Sélectionnez par exemple C5 dans le tableau suivant et tapez la formule suivante :

=((B2*C2)+(B3*C3)+(B4*C4))/(C2+C3+C4)
  • Et vous obtenez alors :  14,71
Formule simple moyenne pondérée



Cette formule sera très gênante surtout si votre tableau contient de multiples lignes de données. Alors pour réduire ce travail, utilisez les deux fonctions SOMMEPROD et SOMME.

Présentation de la fonction SOMMEPROD

La fonction SOMMEPROD (Somme des Produits) permet de calculer la somme des produits des valeurs par leurs poids. C’est-à-dire que le travail que vous avez fait de calculer le produit de chaque note par son coefficient puis de calculer le total de ces produits, vous pouvez le faire aisément avec SOMMEPROD.

La syntaxe de la fonction SOMMEPROD

SOMMEPROD(matrice1, [matrice2], [matrice3], ...)

  • En l’appliquant pour notre exemple, vous allez taper à la place de l’argument matrice1 la référence de la plage de cellules B2:B4 qui contient les notes, et à la place de l’argument matrice2 tapez la référence de la plage de cellules C2:C4
  • Votre fonction sera écrite donc de cette façon : =SOMMEPROD(B2:B4;C2:C4)
Utiliser SOMMEPROD

  • Ce qui donne 206
  • Il vous reste enfin de diviser ce total par la somme des coefficients qui est calculée par la formule suivante : SOMME(C2:C4)
  • Alors votre formule pour calculer la moyenne pondérée est la suivante :

=SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4)

Formule de la moyenne pondérée

  • Et c’est le même résultat obtenu précédemment : 14,71

Les matrices doivent avoir les mêmes dimensions :

Oui, effectivement, les matrices de la fonction SOMMEPROD doivent avoir le même nombre de lignes et de colonnes, si non votre formule renvoie une erreur de type VALEUR. (pour plus de détails sur cette erreur consultez l’article suivant : Les erreurs dans Excel)

Dans notre exemple dernier, la matrice B2:B4 se compose d’une colonne et de trois lignes et aussi la matrice C2:C4.

Regardez maintenant l’exemple suivant :

matrices de sommeprod


  • Les deux matrices contiennent chacune deux colonnes et trois lignes.
  • Alors, pour trouver la somme des produits des valeurs de la première matrice par celles de la deuxième matrice, suivez la procédure illustrée dans cette image animée :
Introduire des matrices dans SOMMEPROD

  • Le travail donc effectué par cette fonction suit l’ordre suivant :

(A1*D1)+(A2*D2)+(A3*D3)+(B1*E1)+(B2*E2)+(B3*E3)

Note : Vous pouvez utiliser jusqu’à 255 matrices dans la fonction SOMMEPROD

Calculer automatiquement la moyenne pondérée lors d’une insertion ou suppression de valeurs :

Dans cet exemple, on veut calculer automatiquement le prix de vente moyen lorsque de nouveaux produits se sont ajoutés au stock mais qui se sont achetés à des prix différents.
  • Dans la cellule B7, tapez la formule pour calculer la moyenne pondérée comme suit :

=SOMMEPROD(A2:A6;B2:B6)/SOMME(A2:A6)

Exemple 2 de formule pour calculer moyenne pondérée

  • Faites maintenant de petites modifications, en imbriquant la fonction DECALER :
  • Remplacez la cellule A6 dans SOMMEPROD et SOMME par DECALER(A7;-1;0), et la cellule B6 par DECALER(B7;-1;0)
Fonction Decaler imbriquée dans SOMMEPROD

  • Insérez maintenant une nouvelle ligne juste en dessus de la cellule B7 qui contient votre formule, puis tapez les nouvelles valeurs : Quantité et Prix de vente.
Calcul automatique de moyenne pondérée lors d'insertion de nouvelles valeurs

  • Remarquez donc que la moyenne pondérée s’est calculée automatiquement.
  • Essayez de supprimer une ligne de ce tableau et remarquez aussi que votre formule moyenne pondérée s’effectue aussi automatiquement.
Calcul automatique de moyenne pondérée lors de suppression de valeurs


PARTAGEZ

Excel Info

  • Image
  • Image
  • Image
  • Image
  • Image