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.
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) 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 :

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 allez obtenir alors :  14,71
Formule simple moyenne pondérée



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)
Utiliser SOMMEPROD

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

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

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

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 cette fois 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
    Blogger Comment
    Facebook Comment

25 commentaires:

  1. vraiment merci beaucoup pour l'aide que vous m'avez apportez je ne cesse de vous remerciez car grace a votre cours j'arrive à calculer les moyennes pondereés

    RépondreSupprimer
  2. Je vous en prie et c'est un honneur pour moi de vous apporter de l'aide.

    RépondreSupprimer
  3. Merci grâce à votre explication bien illustrée j'ai réussi à me sortir d'un blocage

    RépondreSupprimer
  4. Bonjour,
    bravo et merci pour ce tutoriel qui est autrement plus étoffé que celui proposé par Microsoft !
    Je rencontre un problème auquel vous aurez peut-être une solution.
    Je suis enseignant, et j'ai donc un tableau avec les notes de mes élèves.
    En admettant que j'ai 10 élèves ayant chacun 2 notes, l'une coefficient 2 et l'autre coefficient 3, j'ai un tableau dans lequel ma première matrice sera les cellules C1 à D10 (soit 10x 2 notes). Lorsque je veux utiliser SOMMEPROD, ma seconde matrice semble devoir avoir le même format que la 1ère, c'est à dire 20 cellules (E1 à F10, par exemple) contenant 10x "2" et 10x "3" pour mes coef. Cela signifie que je dois recopier 10 fois le coefficient pour chaque épreuve, et donc doubler inutilement la taille de mon tableau. J'ai tenté ma formule avec une seconde matrice de seulement 2 cases contenant juste mes coefficients, mais cela génère une erreur par faute de valeur. A priori, Excel s'attend à ce que les deux matrices aient exactement le même nombre de cellules
    Y a-t-il un moyen d'indiquer les coefficients une seule fois (par exemple dans les cellules C11 et D11) et de pouvoir les appliquer de façon automatique à une matrice entière via SOMMEPROD ?
    Je peux bien entendu faire le calcul "à l'ancienne" avec les fonction moyenne/somme/produit, mais lorsque j'ai 8 notes pour 250 étudiants avec des coefs différents à chaque note, cela devient vite laborieux... et je ne vais pas créer 16 colonnes, 8 pour la matrice des notes et 8 pour celle des coef !
    Merci de votre aide.

    RépondreSupprimer
    Réponses
    1. Le plus simple pour vous est de créer une ligne où apparait le coefficient du 1er examen (1ère note), au-dessus de la colonne en question, et chaque cellule de la colonne "coefficient de la 1ère note" ira chercher cette valeur ; disons cellule D1 dans notre exemple pour le coefficient du 1er examen. Facile à faire en écrivant une formule =$D$1 dans chacune des cellules concernées. Ou selectionner la cellule et appuyer sur F4.

      Même processus pour le second examen.

      Supprimer
  5. merci peut-on utiliser ce moyen dans le systeme de sante?

    RépondreSupprimer
  6. Bonjour,

    Cependant dans mon tableur j'ai des matières où il n'y a pas encore de note, donc ensuite quand le tableur doit calculer la moyenne de mes matières il me note #DIV/0!

    Sachant que je suis à l'université, donc cela fonctionne par Unité d'Enseignement, donc certaines on deux ou trois matières, donc la moyenne totale ne s'affiche pas car j'ai une matière sans note. Comment puis je compléter mon SOMME PROD pour éviter cela.

    Merci à vous

    RépondreSupprimer
    Réponses
    1. Bonjour,
      Lorsque le diviseur est égal à 0 c'est normal qu'excel affiche ce message d'erreur #DIV/0!
      la formule fonctionnera lorsque vous entrerez toutes les notes.
      Si vous aimez plus d'aide vous pouvez me contacter à travers le formulaire de contact en haut à droit. N'hésitez pas !

      Supprimer
  7. Merci pour vos conseils , ça aide beaucoup.

    RépondreSupprimer
  8. Merci beaucoup, ça m'a bien aidé !

    RépondreSupprimer
  9. Bonjour,
    Et merci pour le temps précieux que vous faites gagner.

    Avez vous une méthode pour déterminer l'écart type de cette moyenne pondérée?

    Merci pour votre réponse!

    RépondreSupprimer
  10. Merci beaucoup pour votre explication !

    RépondreSupprimer

Votre commentaire m'intéresse beaucoup :)