Bien que Microsoft Excel ne fournisse pas de fonction de moyenne pondérée spéciale, il possède quelques autres fonctions qui s’avéreront utiles dans vos calculs, comme le montrent les exemples de formules qui suivent.
1 Qu’est-ce que la moyenne pondérée ?
La moyenne pondérée est une sorte de moyenne arithmétique dans laquelle certains éléments de l’ensemble de données ont plus d’importance que d’autres. En d’autres termes, chaque valeur à moyenner se voit attribuer un certain poids.
Les notes des étudiants sont souvent calculées à l’aide d’une moyenne pondérée, comme le montre la capture d’écran suivante. Une moyenne habituelle est facilement calculée avec la fonction MOYENNE( ) d’Excel . Cependant, nous souhaitons que la formule moyenne prenne en compte le poids de chaque activité répertoriée dans la colonne C.
En mathématiques et en statistiques, vous calculez la moyenne pondérée en multipliant chaque valeur de l’ensemble par son poids, puis vous additionnez les produits et divisez la somme des produits par la somme de tous les poids.
Dans cet exemple, afin de calculer la moyenne pondérée (note globale), vous multipliez chaque note par le pourcentage correspondant (converti en décimal), additionnez les 5 produits ensemble et divisez ce nombre par la somme de 5 poids :
((91*0.1)+(65*0.15)+(80*0.2)+(73*0.25)+(68*0.3)) / (0.1+0.15+0.2+0.25+0.3)=73.5
Comme vous le voyez, une note moyenne normale (75,4) et une moyenne pondérée (73,5) sont des valeurs différentes.
2. Calcul de la moyenne pondérée
Dans Microsoft Excel, la moyenne pondérée est calculée en utilisant la même approche mais avec beaucoup moins d’effort car les fonctions Excel feront la plupart du travail pour vous.
Exemple 1. Calcul de la moyenne pondérée à l’aide de la fonction SOMME( )
Si vous avez des connaissances de base sur la fonction SOMME( ) d’Excel , la formule ci-dessous ne nécessitera pratiquement aucune explication :
=SOMME(B2*C2; B3*C3; B4*C4; B5*C5;B6*C6) / SOMME(C2:C6)
Essentiellement, il effectue le même calcul que décrit ci-dessus, sauf que vous fournissez des références de cellule au lieu de nombres.
Comme vous pouvez le voir sur la capture d’écran, la formule renvoie exactement le même résultat que le calcul que nous avons effectué il y a un instant. Notez la différence entre la moyenne normale renvoyée par la fonction MOYENNE (C8) et la moyenne pondérée (C9).
Bien que la formule SOMME( ) soit très simple et facile à comprendre, ce n’est pas une option viable si vous avez un grand nombre d’éléments à moyenner. Dans ce cas, vous feriez mieux d’utiliser la fonction SOMMEPROD( ) comme illustré dans l’exemple suivant.
Exemple 2. Trouver une moyenne pondérée avec la fonction SOMMEPROD( )
La fonction SOMMEPROD( ) d’Excel convient parfaitement à cette tâche car elle est conçue pour additionner des produits, ce qui est exactement ce dont nous avons besoin. Ainsi, au lieu de multiplier chaque valeur par son poids individuellement, vous fournissez deux tableaux dans la formule SOMMEPROD (dans ce contexte, un tableau est une plage continue de cellules), puis divisez le résultat par la somme des poids :
=SOMMEPROD( plage_valeurs ; plage_poids ) / SOMME( plage_poids )
En supposant que les valeurs à moyenner se trouvent dans les cellules B2 : B6 et les pondérations dans les cellules C2 : C6, notre formule SOMMEPROD pour la moyenne pondérée prend la forme suivante :
=SOMMEPROD(B2:B6; C2:C6)/SOMME(C2:C6)
Pour voir les valeurs réelles derrière un tableau, sélectionnez-le dans la barre de formule et appuyez sur la touche F9. Le résultat ressemblera à ceci :
Ainsi, ce que fait la fonction SOMMEPROD( ) est de multiplier la 1ère valeur de tableau1 par la 1ère valeur de tableau2 (91*0,1 dans cet exemple), puis de multiplier la 2ème valeur de tableau1 par la 2ème valeur de tableau2 (65*0,15 dans cet exemple), et ainsi de suite. Lorsque toutes les multiplications sont effectuées, la fonction additionne les produits et renvoie cette somme.
Pour vous assurer que la fonction SOMMEPROD( ) donne un résultat correct, comparez-la à la formule SOMME( ) de l’ exemple précédent et vous verrez que les nombres sont identiques.
Lorsque vous utilisez la fonction SOMME( ) ou SOMMEPROD( ) pour trouver la moyenne pondérée dans Excel, les poids ne doivent pas nécessairement totaliser 100 %. Ils n’ont pas non plus besoin d’être exprimés en pourcentages. Par exemple, vous pouvez constituer une échelle de priorité/importance et attribuer un certain nombre de points à chaque élément, comme le montre la capture d’écran suivante :
Eh bien, tout tourne autour du calcul de la moyenne pondérée dans Excel.