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. Vous allez voir aussi comment faire pour que votre formule créée calcule automatiquement une moyenne pondérée lorsque vous insérez de nouvelles valeurs ou vous supprimez quelques-unes.

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) j'ai 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. 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 :

• 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 allez obtenir alors :  14,71

Cette formule sera très gênante surtout si votre tableau contient de multiples lignes. Alors et 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], ...)

• Vous allez donc 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 de cette façon : =SOMMEPROD(B2:B4;C2:C4)

• Ce qui donne 206
• Il vous reste enfin de diviser ce total par la somme des coefficients, c'est à dire : SOMME(C2:C4)
• Alors pour calculer la moyenne pondérée, la  formule sera :

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

• 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 renverra une erreur de type VALEUR. (pour plus de détails sur cette erreur consultez l’article suivant : Les erreurs dans Excel)

Dans l'exemple vu en haut, la matrice B2:B4 se compose d’une colonne et de trois lignes et c'est le même nombre de colonnes et de lignes pour la matrice C2:C4.

Regardez maintenant l’exemple suivant :

• 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 :

• Le travail 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, vous allez voir comment 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)

• 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)

• 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.

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

PARTAGEZ PARTAGEZ PARTAGEZ