Lorsque nous calculons une moyenne simple d'un ensemble donné de valeurs, l'hypothèse est que toutes les valeurs ont un poids ou une importance égal.
Par exemple, si vous vous présentez à des examens et que tous les examens ont un poids similaire, la moyenne de vos notes totales sera également la moyenne pondérée de vos notes.
Cependant, dans la vraie vie, ce n'est guère le cas.
Certaines tâches sont toujours plus importantes que d'autres. Certains examens sont plus importants que d'autres.
Et c'est là que Moyenne pondérée entre en scène.
Voici la définition classique de la moyenne pondérée :
Voyons maintenant comment calculer la moyenne pondérée dans Excel.
Calcul de la moyenne pondérée dans Excel
Dans ce didacticiel, vous apprendrez à calculer la moyenne pondérée dans Excel :
- Utilisation de la fonction SOMMEPROD.
- Utilisation de la fonction SOMME.
Alors, commençons.
Calcul de la moyenne pondérée dans Excel - Fonction SOMMEPROD
Il peut y avoir divers scénarios où vous devez calculer la moyenne pondérée. Vous trouverez ci-dessous trois situations différentes dans lesquelles vous pouvez utiliser la fonction SOMMEPROD pour calculer la moyenne pondérée dans Excel
Vous trouverez ci-dessous trois situations différentes dans lesquelles vous pouvez utiliser la fonction SOMMEPROD pour calculer la moyenne pondérée dans Excel
Exemple 1 - Lorsque les poids totalisent jusqu'à 100 %
Supposons que vous disposiez d'un ensemble de données avec les notes obtenues par un étudiant à différents examens ainsi que les pondérations en pourcentages (comme indiqué ci-dessous) :
Dans les données ci-dessus, un étudiant obtient des notes dans différentes évaluations, mais à la fin, il doit recevoir une note ou une note finale. Une moyenne simple ne peut pas être calculée ici car l'importance des différentes évaluations varie.
Par exemple, un quiz, avec un poids de 10 %, a un poids deux fois plus important qu'un devoir, mais un quart plus important qu'un examen.
Dans un tel cas, vous pouvez utiliser la fonction SOMMEPROD pour obtenir la moyenne pondérée du score.
Voici la formule qui vous donnera la moyenne pondérée dans Excel :
=SOMMEPROD(B2:B8,C2:C8)
Voici comment fonctionne cette formule: La fonction Excel SUMPRODUCT multiplie le premier élément du premier tableau avec le premier élément du deuxième tableau. Ensuite, il multiplie le deuxième élément du premier tableau avec le deuxième élément du deuxième tableau. Etc…
Et enfin, il ajoute toutes ces valeurs.
Voici une illustration pour que ce soit clair.
Exemple 2 - Lorsque les poids ne totalisent pas 100 %
Dans le cas ci-dessus, les poids ont été attribués de telle sorte que le total totalise 100 %. Mais dans des scénarios réels, ce n'est peut-être pas toujours le cas.
Regardons le même exemple avec des poids différents.
Dans le cas ci-dessus, les poids totalisent 200 %.
Si j'utilise la même formule SUMPRODUCT, cela me donnera le mauvais résultat.
Dans le résultat ci-dessus, j'ai doublé tous les poids, et il renvoie la valeur moyenne pondérée à 153,2. Maintenant, nous savons qu'un étudiant ne peut pas obtenir plus de 100 sur 100, peu importe à quel point il est brillant.
La raison en est que les poids ne totalisent pas 100 %.
Voici la formule qui obtiendra cela trié:
=SOMMEPROD(B2:B8,C2:C8)/SOMME(C2:C8)
Dans la formule ci-dessus, le résultat SOMMEPROD est divisé par la somme de tous les poids. Par conséquent, quoi qu'il en soit, les poids totaliseraient toujours 100 %.
Exemple 3 - Quand les poids doivent être calculés
Dans l'exemple couvert jusqu'à présent, les poids ont été spécifiés. Cependant, il peut y avoir des cas, où les poids ne sont pas directement disponibles, et vous devez d'abord calculer les poids, puis calculer la moyenne pondérée.
Supposons que vous vendiez trois types de produits différents, comme indiqué ci-dessous :
Vous pouvez calculer le prix moyen pondéré par produit en utilisant la fonction SOMMEPROD. Voici la formule que vous pouvez utiliser :
=SOMMEPROD(B2:B4,C2:C4)/SOMME(B2:B4)
En divisant le résultat SOMMEPROD par la SOMME des quantités, on s'assure que les poids (dans ce cas les quantités) totalisent 100 %.
Calcul de la moyenne pondérée dans Excel - Fonction SOMME
Bien que la fonction SOMMEPROD soit le meilleur moyen de calculer la moyenne pondérée dans Excel, vous pouvez également utiliser la fonction SOMME.
Pour calculer la moyenne pondérée à l'aide de la fonction SOMME, vous devez multiplier chaque élément, avec son importance attribuée en pourcentage.
En utilisant le même jeu de données :
Voici la formule qui vous donnera le bon résultat :
=SOMME(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)
Cette méthode est bien à utiliser lorsque vous avez quelques éléments. Mais lorsque vous avez de nombreux articles et poids, cette méthode peut être lourde et sujette aux erreurs. Il existe un moyen plus court et meilleur de le faire en utilisant la fonction SOMME.
En continuant avec le même ensemble de données, voici la formule courte qui vous donnera la moyenne pondérée à l'aide de la fonction SOMME :
=SOMME(B2:B8*C2:C8)
L'astuce lors de l'utilisation de cette formule consiste à utiliser Ctrl + Maj + Entrée, au lieu d'utiliser simplement Entrée. Étant donné que la fonction SUM ne peut pas gérer les tableaux, vous devez utiliser Control + Shift + Enter.
Lorsque vous appuyez sur Ctrl + Maj + Entrée, des accolades apparaissent automatiquement au début et à la fin de la formule (voir la barre de formule dans l'image ci-dessus).
Encore une fois, assurez-vous que les poids totalisent 100 %. Si ce n'est pas le cas, vous devez diviser le résultat par la somme des poids (comme indiqué ci-dessous, en prenant l'exemple du produit) :
